Filter a listbox in pop-up form from another form (1 Viewer)

[cjmitton]

I have a form where I enter in either Contact or business details, I can then add multiple email addresses to contact or business by updating a table with the additional email addresses and using the contact or business 'ID' of the record to associate them to email. (I hope I explained that okay!)

This all works fine, I now want to add a 'pop-up' window to show the various email addresses associated to contact or business.

I've set up my pop-up form with a list box, assigned a query to the list box (showing email address Column1 and ID in Column2). This now show's every record.

I try to open the popup window from the form using the ID in a 'where condition' to try and filter the listbox results and its not working? below is the code I'm playing with!

Code on the button I click to open the 'pop-up'

Private Sub cmd_find_AddEmail_Click()
Dim strWhere As Variant
    strWhere = Null
    strWhere = "Me.List_Add_Email_Bus.[AE_BusinessID] = " & [B_ID] & ""
    DoCmd.OpenForm "frm_List_Add_Emails_Bus", WhereCondition:=strWhere
    Forms.frm_List_Add_Emails_Bus.SetFocus
End Sub

You'll have to forgive my Naming! in the above code I've tried to reference the listbox (List_Add_Email_Bus) in the form 'frm_List_Add_Emails_Bus'.

 

[Mr. B]

You do not need to filter your form, the filter needs to be on the Listbox.

I have to assume from your post that you have an ID field on your form (not the popup form) that has the ID of the contact of business. If this is the case, you can add a reference to that field on your primary form as the criteria in the query or sql statement you are using to populate your listbox on your popup form.

Once you have added this reference you should be able to simply open the popup form and the listbox would be filtered to only show the emails for the current contact or business on the primary form.

 

[cjmitton]

Thanks for that, I've tried to do it that way now using a SQL statement but I'm not sure if I've got the 'formatting' correct for referencing a field on a different form?

Heres the statement:

SELECT AE_EmailAddress, AE_BusinessID FROM tbl_AdditionalEmails ORDER BY AE_EmailAddress Where AE_BusinessID = " & Forms!frm_Business_Mgt!B_ID &"

 

[cjmitton]

I've done it with a query now! works a treat Thanks Mr. B

 

[Mr. B]

Gald to here you got it working. I wanted to let you know that you can do the same thing using the sql statement, you just have to get the reference correct. The easiest way to do this is to use the Build option in the QBE to locate the control in the appropriate form and apply that criteria to your sql statement.

 

[boblarson]

If you didn't remove it, this part shouldn't be there:

&"

 

[boblarson]

Gald to here you got it working. I wanted to let you know that you can do the same thing using the sql statement, you just have to get the reference correct. The easiest way to do this is to use the Build option in the QBE to locate the control in the appropriate form and apply that criteria to your sql statement.

Hey, Mr. B - congrats on hitting 1,000 posts!

 

[cjmitton]

Thanks for the help, Its opened a whole new world of searching / filtering! will also make my life easier too

I think I'll be using the queries option for now until I get me SQL up to scratch!

Thanks Gents.

 

[Mr. B]

Hey, Mr. B - congrats on hitting 1,000 posts!

Thanks, Bob

And, also, thanks for the "eye opener" the other day.

 

[Mr. B]

Thanks for the help, Its opened a whole new world of searching / filtering! will also make my life easier too

I think I'll be using the queries option for now until I get me SQL up to scratch!

Thanks Gents.

Just, FYI, it's not necessary to specifically use a query to do this same thing. You can still use the sql statement in the Record Source. If you recall, you can click the button at the end of the option and the QBE will open. You can then still use the Build option within the QBE.

Just thought I would make that clear.