Need help with Combo boxes

[SAM453]

Hi folks,

I'm working on a task. I need some help.

I have one command button and 2 combo boxes. combobox1 and combobox2. I am using sql as a back end.
to display values in combo boxes i selected row source as table and wrote sql query and everything is working fine.

Now what i want is initially my combobox1 should display all records .... if user selects a value from a combobox2 drop down list then values in my combobox1 drop down has to be filtered as per combobox2 value and should display only filtered records according to combobox2 value which is selected.

how i can implement this logic????
please help me with some sample code.

Thanks,

SAM.

 

[CindyK]

Hi Sam -
You can base the query on your second combo box equal to the value that is in your first box by adding it to the where clause - ie WHERE lngAutoID=[YourFOrm]![FormControl]![ComboName]

Then the only missing piece is to add one line of code on the After Update event of the first combo box -

Me.Combo2Name.Requery

 

[SAM453]

Hi CindyK

thanks for your help. I'm trying to implement this
select * from [tablename]
where bc=[Form]![LK2]![Combo13]

after selecting a value in combobox13 and if i click on combobox 1 then it is asking me to enter a parameter not taking from combobox13. if i enter that parameter manually i don't see any result in combobox1 drop down list ...

another question if i add this where clause will it represent all values intially ??/ before i select any value in combobox13.

I hope you got it what i'm asking.

SAM.

 

[randy700]

Sam,

This is all explained in the FAQ section of this forum.
Combo Box - dependant on another Combo Box faq702-4289


Randy

 

[CindyK]

Sam - that FAQ Randy lists is a good one - but I think you are saying that your first combo box is an OPTIONAL one. If you don't have the first combo box populated, then you want your second combo box to show everything. That's not covered in the FAQ - although there is code in that FAQ that tells the user they need to fill in the first combo box.

You could change the sql code that's used in the row source via code as well - so that, for example, when the user enters the second combo box, you do the check for a value in the first one, and if it's empty, then in that on enter spot you would CHANGE the sql to drop the where clause. (and do the requery). Hope that helps.

 

[SAM453]

Randy,

thanks it's working..

but i also want combobox1 to display the all values when no value is selected in combo13.

basically it should be something like ..
if combo13.value = isnull then my combobox1 drop down should hav all values
if combo13.value= XXX then my combobox1 drop down should be fileted according to combo13.

how i can implement this???

 

[CindyK]

Sam - you'd have to change this so it applies to you, but here is an example of some On Enter code in a combo box that changes the SQL to either dynamic SQL or a stored query based on the entry in another field..


Dim stSQL As String
Dim lngsite As Integer


'if we have a site, change the query row source
If Not IsNull(Me!lngSiteID) Then
lngsite = [Forms]![frmClientDataSCH]![lngSiteID]


stSQL = "SELECT tblStaff.lngStaffID, [strStaffLast] & "", "" & [strStaffFirst] "
stSQL = stSQL & "FROM tblStaff INNER JOIN tbl_MaintainStaffBySiteAndProgram ON tblStaff.lngStaffID = tbl_MaintainStaffBySiteAndProgram.lngStaffId "
stSQL = stSQL & "WHERE (((tblStaff.lngStaffID) <> 20) And ((tblStaff.ysnActive) = True) And ((tbl_MaintainStaffBySiteAndProgram.strProgram) = 'SCH') And ((tbl_MaintainStaffBySiteAndProgram.[lngSiteID]) = " & lngsite & "))"
stSQL = stSQL & "ORDER BY [strStaffLast] & "", "" & [strStaffFirst]; "


'change the query so it only shows staff members who work in this program
Me!cboChangeStaff.RowSource = stSQL

Else 'just show them all
Me!cboChangeStaff.RowSource = "qcboStaffAll"

End If

 

[SAM453]

Hi cindy,

Thanks for your help.

where i should place this code?? under combobox1 onclick ?? what i should represent source type and row source for this combobox1??

Can you modify the script a little bit please.. let me explain again..

I have station(combobox1) and name(combobox13)
Initially both combo boxes should display all values. Both boxes uses same query in back end.

if user don't select anything NAME in combobox13 then i should have all records(STATIONS) in combobox1 drop down by default
if user selects any NAME in combobox13 then i should see only those stations which falls under that name.

 

[CindyK]

OK - let's start from the beginning... I complicated things by throwing in all that sloppy code anyway. Sorry.


You have two combo boxes. One is called Combo13 - that's the first one on the form that is sometimes filled in, sometimes not. I'll call the other one combo14 since I don't know what you called it - that's the one where the value should change based on the contents of Combo 13 IF combo13 is selected.
This assumes that Combo13 and Combo14 hold numbers. The code would be slightly different if those combo boxes have text or dates. This also assumes your Combo13 doesn't have a default value filled in - that when the user hasn't picked anything, it is null.


On the On Enter event of Combo14, enter this code:


Dim stSQL As String
Dim lngID As Integer


'if combo13 is filled in, it will have a number in it - otherwise it will be null

If NOT isnull(Me![Combo13]) then

lngID = Me![Combo13]

stSQL = "SELECT field1, field2 FROM [tablename] WHERE bc = " & lngID & ";"
Else
stSQL = "SELECT field1, field2 FROM [tablename];"

End if

Me!Combo14.RowSource = stSQL



You'll replace the words field1, field2 with whatever fields you really want in your combo box of course.
If the field bc is text, change the WHERE bc = " & lngID to be this:
WHERE bc = " & chr(34) & lngID & chr(34) & ";"

 

[SAM453]

Thank you very much Cindy for your time.

 

[CindyK]

Hope that did the trick! You're welcome.

 

[RenaG]

Can I jump in on this post? I have a similar situation except that I have one combo box and one Option group. I want to set the default in the option group based on what is selected in the combo box.

I have looked at the link posted above to the FAQ page and tried to modify it for my situation but it didn't work. I have also tried something similar to your sql stmt, Cindy, and it didn't work either.

The cmbUser box is based on a table that consists of ID, FName, LName. The optProgramID would come from a table that consists of ID(pk), UserID, ProgramID. I would like to take the UserID selected in the cmbUser box, find the ProgramID that goes with it and set that as the Default value for the optProgramID option group.

I would appreciate any suggestions you might have.

~RLG

 

[TheAceMan1]

How are ya SAM453 . . .

[blue]On 10 Jun 11 15:05 ... but i also want combobox1 to [purple]display all values when no value is selected in combo13[/purple].[/blue]

Try the following:

[blue]SELECT * FROM [tablename]
WHERE (bc=[Form]![LK2]![Combo13]) OR [Form]![LK2]![Combo13] IS NULL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chunter33]

In your optional combo box, have something like the following in your change event code:

Private Sub Combo0_Change()
If Combo0.Value = "Option 1" Then
Combo2.RowSource = "SELECT EmpID FROM tbltimes;"
End If
End Sub

You can modify that for some sort of case structure instead of just the single Option 1.

Also, just so that none of the options are selected by default and to avoid the awqwardness of the change event I recommened setting the optional combo boxes values property in the form load event to something like "Select an option", so that the user will be forced to have to trigger the change event for the combo box if they want a particular option selected.

 

[RenaG]

FYI ~

I got the answer to my combobox/option group question.

I added the ProgramUserXRef table to the User table in the SQL stmt on the combobox so that I had available to me the programID. Put in the after Update event

Me!optProgramID.DefaultValue = Me!cmbUser.Column(3)
Me!optProgramID.Requery

Worked like a charm!

~RLG