Variable Combo Box RowSource

MOTOWN44

Registered User.
Local time
Today, 10:10
Joined
Aug 18, 2009
Messages
42
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
 
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


Code:
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
 
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
 
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
 
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
 
See attched...if it worked

Thanks
 
Last edited by a moderator:
Several things wrong with it.

Take a look at this revision and see if you can find out what is different.
 
Last edited by a moderator:
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
 
Here is the zipped file
 
Last edited by a moderator:
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
 

Users who are viewing this thread

Back
Top Bottom