1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Filtering a form using selection of a combo box on a another form (user ID)

Discussion in 'Business Applications' started by ken90004, Feb 19, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. ken90004

    ken90004 Thread Starter

    Joined:
    Feb 19, 2013
    Messages:
    6

    I have this small database I am converting from A97 toA2010. I created a new A2010 db and pulled over objects I needed. Everything is tested out and working fine.
    I also added a drop down box to the main switchboard toselect a "user". Its purposeis so the filter through all the records and pull up only the list of drawing #for that specific drafter.
    So I have a table called tblSign_In which has UID autonumber, and the employees name. Thiswill be the user names for the drop down of the Main Switchboard.

    I have a table called SHEET LIST that list all the data Ineed to display. This will end upholding tens of thousands of records of information about drawing. I added to this table a field called theLogInID field (UID) to link back to the tblSign_IN, and the correct number andcombination.
    I also have a query called qrySHEET LIST which selectsall SHEET TABLE and inner joins to thetblSign_IN to pull the Employee Name linking on a LogInID field.

    What I was trying to do is filter SHEET LIST form (my outputform) by the user selected on the MAIN SWITCH form in the drop down box Icalled cboSignInEmployeeName.
    For example:
    Sheet List (tbl) might contain information like: Sheet# 22a6; description Dryer; buildhours:12; drafter #4.

    qrySHEET LIST (also my ouptut data) is pulling all theabove, but replace 4 with actual drafterÂ’s name, John Smith.
    To filter, I have two methods:
    The query is my record source for my form SHEET LIST, so Iadded

    WHERE (((tblSIGN_IN.[Employee Name])=[Forms]![MAINSWITCHBOARD]![cboSignInEmployeeName]));
    This prompts me to enter: Forms!MAINSWITCHBOARD!cboSignInEmployeeName

    I removed that, went into the forms property:
    Change Filter on Load to Yes, and added
    [Forms]![MAIN SWITCHBOARD]![cboSignInEmployeeName]
    but again I am prompted to enter a value.

    I also tried to play with VBA coding, but skill is limitedin that matter, so I really had no idea what I was doing.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are you sure that the Combo actually holds the Users Name and not the UserID, as that will prevent
    [Forms]![MAIN SWITCHBOARD]![cboSignInEmployeeName] form working.
    This especially happens if you use "Lookup" fields.
    Try using the UserId instead of the User Name.
     
  3. ken90004

    ken90004 Thread Starter

    Joined:
    Feb 19, 2013
    Messages:
    6
    Thanks for the help.

    I've pulled in just the name field, and just the ID, and both, bounding the name field, setting column counts to 2, and widths to 0";1". It's not "seeing" the combo box.

    I've tried creating a new object and used a list box this time and it will not capture the data in the box.

    I've been playing with this for weeks and I need to report on it by Friday. It is pretty basic stuff, not to be working. It doesn't look good.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I would help if I could but I do not have 2010.

    What I do use is a Public Variable to pass data from form to form, you could try that.
     
  5. ken90004

    ken90004 Thread Starter

    Joined:
    Feb 19, 2013
    Messages:
    6
    I found the problem at least. Hopefully the solution is close by.
    I created a new form, with a new combo box, and a new query to try to pull in the data selected. It was pulling in my selection as I expected. The old form still was not. I decompiled and compacted with no luck. Finally relalized that once I leave the Main Switchboard form, I lose whatever was stored in that combo box.

    Not sure why exactly. I was expecting the combo box selection to be a storage variable.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The combo box normally is, but only if the Form stays open, once closed the data is lost. Which is why I use a Public Variable, which is available to all forms, queries and reports.
     
  7. ken90004

    ken90004 Thread Starter

    Joined:
    Feb 19, 2013
    Messages:
    6
    I am clumsy with VBA. Could I ask for an example. :)
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Create a VBA Module, to do so use Alt + F11 and then on the Main Menu>Insert>Module then enter
    Public myuser as string
    for a name, or
    Public myuser as long
    for an Autonumber type ID

    Then in the Combo's After Update Event Procedure enter
    myuser = me.comboname
    where comboname is the actual name of the combo.
    Note that this is not typed or copied in to the combo property's After Update Event line, instead you click on the line, select "Event Procedure" and then click on the three dots on the right hand side to enter the VBA Editor at the correct place to enter the code.

    Of course you could keep the Main menu open and just use usual query criteria.
     
  9. ken90004

    ken90004 Thread Starter

    Joined:
    Feb 19, 2013
    Messages:
    6
    many thanks for the help. I might actually have this done for my meeting after all. :)
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You then need to set the second form's filter to the new public variable, this goes in the form's "On Open Event Procedure"
    and would use something like
    me.filter = "[UserID] =" & myuser
    me.filteron = true

    where UserID is the actual name of the field you are using.
     
  11. ken90004

    ken90004 Thread Starter

    Joined:
    Feb 19, 2013
    Messages:
    6
    works like a charm now. I just set the forms to popup and modal to keep them open.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Good.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090215

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice