Alter ComboBox Data by Option Group

[tfayer]

I want to create an Option Group with two (maybe more later) options as follows:

1 = Research Site
2 = Storage Location

If research site is selected I want the combo to show the sites from the table tblResearchSites.

If Storage Location is selected I want the combo box to show the locations from the tblResearchLocations.

I'm running into the problem that the data is in two different tables. I've thought about creating a third table that pulls together the data from both, and then just limiting the combo box....But theres got to be an easier way.

Any Suggestions?

 

[KenReay]

Hi

In the after update event of the option group:

Select Case opgWhich
Case 1
strSQL = "SELECT col1, col2.. etc FROM tblResearch ...etc"
Case 2
strSQL = "SELECT col1, col2..etc FROM tblStorage ...etc"

End Select
cboSelector.Rowsource = strSQL
You need to substitute your table, control and column names of course. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[tfayer]

I will try this...However I though of another way that I tried first.

In the combo0 Before Update I put the following code, however my combo box is not populated at all. In the properties of the combo box I left blank.

Private Sub Combo0_BeforeUpdate(Cancel As Integer)

If optGroup1 = "1" Then
Combo0.RowSource = "SELECT tblSites.site_name FROM tblSites"

ElseIf optGroup1 = "2" Then
Combo0.RowSource = "SELECT tblStorageSites.storage_name FROM tblSites"

Else
Combo0.RowSource = "Select Option"

End If

 

[KenReay]

Hi

Your solution is essentially the same as mine.

I used a case statement becuase it is easire to extend and keep it readable, than multiple IF's, and you said that the number of option may increase.

I think you will find putting the code in the before update event is too late, this does not fire until you have chosen an item from the list. If you want to put the code behind the combo box rather than the optio group, may I suggest the ongotfocus event?

Regards
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK