Getting information from Combo Drop Down list (1 Viewer)

[tonez90]

Hi,

I have developed a form with a drop down combo box containing information gleened from a SQL.
Infor contained is recordID, CompanyID, CompanyName and Sitename (all on one line). There may be a number of sites for each company.

What I am trying to do is take the contents of the line selected from the combolist and then split into 4 variables (as above) then use two of these to open another form based on the Company ID and the Sitename. (Note used these name only as names for the SQL below)

What I was hoping to do was use these two bits of information to pass them in a filter to open another form with the record equivalent to these two bits of info.

I wanted to use something like this
Dim StrSQL as strin
StrSQL="[RecordID] = " & CompanyID & " AND [Companyname] LIKE "'" & sitename & "'"
DoCmd.OpenForm "frmCompany", , , StrSQL, , , "Menu_Main"
DoCmd.Close acForm, "Menu_Main"

Has anyone done this before or maybe a better way of doing this.

I hope this is clear enough. Any assistance would be appreciated.

 

[pbaldy]

As answered elsewhere:

Are you looking for the Column property?

StrSQL="[RecordID] = " & Me.ComboName.Column(0) & " AND [Companyname] LIKE "'" & Me.ComboName.Column(1) & "'"

Note that the property is zero based, so the first column is 0.

 

[missinglinq]

I've only got a sec, but I can answer your first question. The column index is zero-based, so the first column in the combobox is Column(0), the second is Column(1) and so forth. Something like this:

Private Sub YourCombo_AfterUpdate()
    Var1 = YourCombo.Column(0).Value  'recordID
    Var2 = YourCombo.Column(1).Value  'CompanyID
    Var3 = YourCombo.Column(2).Value  'ComapanyName
    Var4 = YourCombo.Column(3).Value  'SiteName
End Sub

Someone will be along to help you with the rest.

Good Luck!

 

[tonez90]

Thanks for all the responses