asked on

Add a where clause to expression builder - Easy fix?

Hello,

This should be a quick fix - but access is driving me nuts.

In the simplest of terms: I have a two tables, A and B. When a user clicks a particular field in table A, they are passed through to table B. Table B contains a field called "NameField" which relates to a username. The ID of this user is also passed to table B, and this appears in the NameField.

What I want to do, is run an epxression within the NameField similar to:

NameField = select username from users where userID = tableB.NameField....

Easy right? I've tried creating macros, expressions, yadda yadda, and nothing. This baffles me as it is the corner-stone of all forms: passing ID's and then looking up the rest of the info based on the ID.

carazuul

Hi south paw,
When you run the OpenForm action from Form A you can include a where clause to filter the results of the new form. In your case, adding a NameField = [Selected Name]. The code below shows an example of this.
Regards,
carazuul

Private Sub OpenNewFormButton_Click()
    
    Dim strForm As String, strFilter As String
    
    strForm = "FormB"
    strFilter = "NameField = '[userID]'"
    
    DoCmd.OpenForm strForm, , , strFilter
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

south_paw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial