Updating combo-box source based on another field in a form

[nharrison]

So I am setting up a Marketing Targets form, which essentially identifies targets that need to be pursued as potential clients. I have two fields that identify the "target": Contact ID and Company ID. (yes I know, the person who started the database had never heard of naming conventions...)

However, when creating a new target, the company will ALWAYS be known, and the individual contact is an optional value. Therefore, I want to populate a combo box list for Contact ID with a list of all contacts who are associated with that company (My contacts table has a field Company ID which has a M:O relationship with the Companies table). So I tried modifying the source property with this code:

SELECT [Contacts_formsrc].[Contact Name], [Contacts_formsrc].[File As], [Contacts_formsrc].[Contact ID] FROM Contacts_formsrc WHERE Contacts_formsrc.[Company ID]=Me.[Company ID] ORDER BY [File As];

However, I'm pretty sure the "Me" argument is VBA, when this is an SQL statement. Could anyone give me a pointer?

Thanks.

 

[Simon_MT]

Try a Function:

Function LookupTarget()
Dim MyControl As Control
        Set MyControl = Screen.ActiveControl
    With CodeContextObject
        MyControl.RowSource = "SELECT [Contacts_formsrc].[Contact Name], [Contacts_formsrc].[File As], [Contacts_formsrc].[Contact ID] FROM Contacts_formsrc WHERE Contacts_formsrc.[Company ID]=" & .[Company ID] & " ORDER BY [File As];"
    End With
End Function

On entry to the combi for Target requery and call LookupTarget()

Simon

 

[nharrison]

worked perfect. appreciate the help.

 

[Simon_MT]

Another handy bit of code is ListDisplay used if the combi has been populated underneath the MyControl.RowSource:

Function ListDisplay()
    Dim MyControl As Control
    Set MyControl = Screen.ActiveControl
        If IsNull(MyControl) Then
            MyControl.Dropdown
        End If
End Function

Simon