Cascading Combo Box - Box won't show data

[SBelyea]

Sorry for clogging the boards!

Within my database I have a form which has two combo boxes. The first combo box is for the drainage basin of a BMP, and the second combo box is the HUC code for that drainage basin. I decided that a cascading combo box would be the easiest method to reduce operator error. My goal is to have the user select the drainage basin, and then have only one choice in the second combo box (the correct HUC code). Here's the data for the first and second combo boxes.

tbl_creeks
[tab]creek_id (PK)
[tab]creek_name

tbl_hucdata
[tab]huc_id (PK)
[tab]creek_id (FK)
[tab]huc_name

The fields that I want to show up are creek_name (combo box 1) and huc_name (combo box 2). My two combo boxes are named

cbocreeks

and

cbohucdata

This is the code I am currently using. You select a creek_name, and then - after you close the form and re-open it - the proper HUC value appears. I'm trying to understand why the form must be closed and re-opened to work.

Private Sub cbocreeks_AfterUpdate()
	Dim shucsource As String
	
	shucsource = "SELECT [tbl_hucdata].[Autoid], [tbl_hucdata].[creek_name], [tbl_hucdata].[huc_name] " & _
		"FROM tbl_hucdata " & _
		"WHERE [creek_name] = " & Me.cbocreeks.Value
	Me.cbohucdata.RowSource = shucsource
	Me.cbohucdata.Requery
End Sub

Any help would be appreciated!

 

[Remou]

Did you have a chance to read:

http://www.tek-tips.com/faqs.cfm?fid=4289

 

[TheAceMan1]

How are ya SBelyea . . .

Most important: Are the comboboxes [blue]bound[/blue] or [blue]unbound[/blue]?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[danvlas]

WHERE [creek_name] = '" & Me.cbocreeks.Value & "'"

might fix it, if creek_name is a string.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SBelyea]

AceMan - my combo boxes are bound, and put their data in bmpDrainageBasin and bmpHUC respectively.

 

[SBelyea]

davlas - I tried your solution, and when I tried to open the second combo box, it prompted me for the three variables that were supposed to be found automatically (huc_name, creek_id, huc_id).

Remou - I looked at the FAQ, but I think that it is an example using unbound combo boxes.

Is it possible for me to do this when I have two bound combo boxes?

 

[TheAceMan1]

SBelyea . . .

Changing the recordset of a bound combobox is a very bad Idea for the following reason:

[blue]The recordset of a bound combobox exists for all records. If you change the recordset, those records with previous selected values not included in the new recordset, will show blanks in the combobox![/blue]

I hope you can see this!

To correct this, unbound the combobox [blue]cbohucdata[/blue] and update a textbox in its place.

As for the cascading combo's:
[ol][li]In form design view, set the RowSource of [bluecbohucdata[/blue] to an SQL that includes the criteria:

[blue]   [creek_id] = Forms!MainFormName!cbocreeks[/blue]

[/li]
[li]In the AfterUpdate event of [blue]cbocreeks[/blue], requery [blue]cbohucdata[/blue]:

[blue]   Me!cbohucdata.requery[/blue]

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

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[SBelyea]

Ok, let me propose this:

I believe I've read about a way to take a calculated value from an unbound textbox and store it in the database by sending it to an actual field (e.g. - bmpHUC).

Is it possible to have a combo box to select my bmpDrainageBasin which then automatically fills an unbound textbox, which then fills a field in one of my tables with the value in the unbound textbox?

I realize this may be getting confusing, but I want to eliminate as much user-induced error as possible in this database! Thanks for all of the help you guys have already provided!