Variable Combo Box RowSource

[MOTOWN44]

Afternoon all

I need a quick hand with a couple of combo boxes and would be much obliged if your could help me out

I have am creating a search form where the search type is in 1 combo box (called cboSearch) that calls to a table tblSearchCriteria to get its drop down values – the values are

Impact
Sheet Name
Assigned to
Site

What I want is for my second combo box called cboSearchObjective to populate with different values depending on the value of the first box (cboSearch)

I have tables with the search values in called

tblImpact
tblSite
tblSheets
tblAssigned

ive been playing on with something along the lines of:

Private Sub cboSearch_Click()
If Me.cboSearch = "Impact" Then
Dim strSQL As String
strSQL = "SELECT [TblImpact].[ID], [TblImpact].[Impact] FROM [TblImpact]"
Forms!FrmSearch!cboSearchObjective.RowSource = strSQL
End If
End Sub

(repeated for if cboSearch = Site or if cboSearch = Sheets etc etc)

Obviously trying to change the rowsource of the cboSearchObjective box depending on the value of the cboSearchCriteria box

But have come a cropper

If anyone could shed some light on the situation I would be very grateful

Thanks
Matthew

 

[DCrake]

Private Sub cboSearch_Click()
If Me.cboSearch = "Impact" Then
Dim strSQL As String
strSQL = "SELECT [TblImpact].[ID], [TblImpact].[Impact] FROM [TblImpact]"
Forms!FrmSearch!cboSearchObjective.RowSource = strSQL
End If
End Sub

Private Sub cboSearch_Click()
    Dim strSQL As String
Select Case Me.CboSearch
    Case "Impact"
         strSQL = "SELECT ID, Impact FROM TblImpact"
    Case "Site"
         strSQL = "SELECT ID, Site FROM TblSite"

    Case "Sheet"
         strSQL = "SELECT ID, Sheet FROM TblSheets"

End Select

Me.cboSearchObjective.RowSource = strSQL

End Sub

End Sub

 

[MOTOWN44]

Thanks for getting back to be DCrake

but unfortuanatly ive still had no luck the cboSearchObjective box still isnt displaying any results.

could it be somthing to do with where the event prodecure is? i was just assuming it would be in the on_click

Cheers

 

[DCrake]

You cold move it to the AfterUpdate event of the cbosearch combo and you may also need to issue Me.cboSearchObjective.Requery after the rowsource is set

 

[MOTOWN44]

Still no luck
im not even sure if this is possible in access. im not getting any error messages anywhere its just the cboSearchObjective isnt populating with the values it should depending on the variable in cboSearchCriteria

 

[John Big Booty]

Perhaps you can post a copy of your DB (pre 07 if possible)

 

[MOTOWN44]

See attched...if it worked

Thanks

 

[DCrake]

Several things wrong with it.

Take a look at this revision and see if you can find out what is different.

 

[MOTOWN44]

Thanks for taking a look Dcrake but can you post it in a ZIP? the security systems at work wont let me open it atm

Thanks

 

[DCrake]

Here is the zipped file

 

[MOTOWN44]

Thats brilliant thanks very much! exactly what i needed

could this thread be removed? or at least the attachements are i realised i left some potentially "private" info in the tables and i dont want to get shot

Thanks for all your help again

Matthew