Populate field based on list box selection. Coding help please

[barit]

Can someone please provide some coding help.

I currently have a form, that in the header has an unbound multi-select list box. I also have a command button that is used to take the names selected in the multi-select list box and when clicked, automatically populate the fields on the form so that the end-user can then continue to enter new information.

I am having trouble with the code behind the command button
I am currently getting run time error message 2465 - Can't find 'Forms' referred to in your expression.

The code behind the command button is:

Dim Criteria As String
Dim I As Variant

Criteria = ""
For Each I In Me![List2].ItemsSelected
Criteria = Criteria & " OR [MailingListNameID]=" & Me![List2].ItemData(I)
Next I
If Criteria <> "" Then
Criteria = Mid(Criteria, 5)
End If

Me.Filter = Criteria
Me.FilterOn = True

Other potentially key information

I am trying to take the mailinglistnameID selected in the list box and have it automatically populate a field on the form called mailinglistname.mailinglistnameID.

The form is based on the following query.

SELECT MailListName.MailingListNameID, MailListName.MailingListName1, MailListName.Selected, EventMailList.MailingListNameID, EventMailList.[Event ID], EventMailList.Invited, EventMailList.EventMailListID
FROM MailListName LEFT JOIN EventMailList ON MailListName.MailingListNameID = EventMailList.MailingListNameID
WHERE (((EventMailList.[Event ID])=[Forms]![Event Planning]![Event ID]));

The list box is based on the following query
SELECT MailListName.MailingListNameID, MailListName.MailingListName1
FROM MailListName
ORDER BY MailListName.MailingListName1;

I would really appreciate some help or direction on how to make this code work.

Thanks in advance for any suggestions

 

[PHV]

1) Is the Event Planning form open ?
2) I'm not sure you can apply a filter to a form with a RecordSource having already a WHERE clause.
BTW, what is the highlighted line when the error 2465 is raised ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[barit]

Thanks for trying to help

Yes the Event Planning form is open, however, just to check to see if this was the problem I removed that filter and I still got the same error message.

When I check debug the follow line is highlighted

Me.FilterOn = True

Any suggeastions?

BTW I did have this working but my form was only bound to one table MailingListName. Unfortunately, I really require both tables.

Thanks again for trying to help, I really would appreciate any further suggestions

 

[PHV]

The MailingListNameID name is ambiguous: from MailListName or EventMailList ?
You may try this:
Criteria = ""
For Each I In List2.ItemsSelected
Criteria = Criteria & "," & List2.ItemData(I)
Next I
If Criteria <> "" Then
Criteria = "MailListName.MailingListNameID IN (" & Mid(Criteria, 2) & ")"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244