ComboBox Help Needed 2

[rlf1957]

I have two tables being used in combo boxes.
#1 Counties
CountyId CountyName
1 County1
2 County2
3 County3
etc
#2 Cities/Towns
TownsId CountyID TownName
1 1 Town1
2 1 Town2
3 2 Town3
4 3 Town4
5 1 Town5
etc

Would like to populate combobox 2 with only those towns that belong to the previously selected county.
Thanks for any guidance.

 

[M8KWR]

I was having issue with this.

There is probably an easier way but i know this way works.

What you need to do is create a textbox, but make not visible to the user. I have used text1 for this example

When you change counties combo box you need some code to update the textbox that you have create with the IDNumber of the counties table. this would go in the AfterUpdate procedure.

i.e text1 = cmbCounty.column(0) - this assumes that the IDNumber is bound as column 0, if not you wouldhave to change this.

Now in the city/towns combo box edit the rowsource, and add [forms]![NAME_OF_FORM]![text1]

this goes in the criteria under the countyID field that hopefully should be in there if not drag it down.

then go into the afterUpdate procedure of the first combo box and enter this

combo2.requery

you would need to replace the combo2 with what ever you have called your second combo box, and this will work.

Hope this helps if not let me know.

 

[PHV]

In the AfterUpdate event procedure of the Counties combo:
Me![Name of Cities/Towns combo].RowSource = "SELECT TownsId,CountyID,TownName FROM [Name of Cities/Towns table]" _
& " WHERE CountyID=" & Me![Name of Counties combo]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rlf1957]

Tried both of these and they didn't quite work.
After rereading my first post, it is a little vague, I think.
1st combo box is "County Name" and uses table "Counties", with fields CountyID and County Name.
2nd combo box is "TownName' and uses table "Towns", with fields TownsID, CountyCD, TownName.

After selecting a county (valid codes 1-10), then only towns with that county code would be available in the towns combo box.

Sorry for my confusion ....
rlf1957

 

[PHV]

they didn't quite work
Any error message ? Unexpected behaviour ?
Anyway, in the AfterUpdate event procedure of the "County Name" combo:
Me![TownName].RowSource = "SELECT TownsId, CountyID, TownName FROM Towns" _
& " WHERE CountyID=" & Me![County Name].Column(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rlf1957]

PHV - Hope you can continue this after such a long wait.
Here is what I did and the results.

Private Sub County_Name_AfterUpdate()
Me![TownName].RowSource = "SELECT TownName FROM [Towns]" _
& " WHERE CountyCD=" & Me![County Name].Column(0)
End Sub
(only want TownName to be selected in second combobox)

After Selecting County (combobox 1) and clicking the selection arrow on combobox 2 'TownName', a msg box appears, asking for a numeric value, I think for CountyCd because if I enter the appropriate # (ie 2 for Hillsboro) the correct cities and towns properly populate combobox TownName.

For Example: (This appears in a msg box)
Hillsboro
'Enter Parameter'
2

Also,
If I change the County - can I force a reselection of Town?

thanks for getting me this far.....
RLF

 

[TheAceMan1]

How are ya rlf1957 . . . . .

In:

[blue]Me![TownName].RowSource = "SELECT TownName FROM [Towns]" _
 & " WHERE [purple][b]CountyCD[/b][/purple]=" & Me![County Name].Column(0)[/blue]

In your post origination you show County[purple]ID[/purple] for both tables. So:

Is it County[purple]CD[/purple] or County[purple]ID[/purple]?

See Ya! . . . . . .

 

[rlf1957]

Hey AceMan1 - sorry for the confusion
In the 'Counties' table its "CountyID"
In the 'Towns' table its "CountyCD"

 

[TheAceMan1]

rlf1957 . . . . .

As I thought . . . . The code provided by [blue]PHV[/blue] would be [blue]legitimate[/blue] if proper names were provided in the beginning.

Try again ([blue]you[/blue] provide proper names in [purple]purple[/purple]):

[blue]Me![[purple][b]ComboBox2Name[/b][/purple]].RowSource = "SELECT TownsId, County[b]CD[/b], TownName FROM Towns" _
 & " WHERE County[b]CD[/b]=" & Me![[purple][b]ComboBox1Name[/b][/purple]].Column(0)[/blue]

See Ya! . . . . . .

 

[rlf1957]

In my post on Nov 26 - I showed table and field names.
This doesn't seem to be the pblm.

What happens now - after selecting a county in Combobox1, i proceed thru the form. When I click the drop down arrow on combobox2, a popup msg box showing the previously chosen county name, asking for a county number.

When I enter the number (CountyID), combobox2 gets properly populated with the towns in that county for selection.

Any further thoughts ? Thanks

 

[TheAceMan1]

rlf1957 . . . . .

[blue]CountyID[/blue] & [blue]CountyCD[/blue] are Text or numeric datatypes (look in the tables to be sure)?

See Ya! . . . . . .

 

[rlf1957]

Ace...

CountyID in table:Counties is autonumber

CountyCD in table:Towns is number

rlf

 

[TheAceMan1]

OK rlf1957 . . . . .

This is suppose be easy . . . . .

Post the code you have now, in the [purple]RowSource[/purple] of [blue]County Name[/blue] combobox . . .

See Ya! . . . . . .

 

[rlf1957]

ACEMAN
"Easy" is a relative term.

My original design of the form and combos, (very simple indeed) probably is the cause of our difficulties.

**Combo Box: County Name
RowSource: SELECT DISTINCTROW [Counties].[County Name]FROM[Counties];
Col Count: 1
Col Heads: No
Col Widths: .8855"
Bound Col: 1
LimitToList:Yes
[EVENT Procedure]
Private Sub County_Name_AfterUpdate()
Me![TownName].RowSource = "SELECT TownsID, CountyCD, TownName FROM [Towns]" _
& " WHERE CountyCD=" & Me![County Name].Column(0)
End Sub

**Combo Box:TownName
RowSource: SELECT [Towns].[Town Name]FROM[Towns];
Col Count: 1
Col Heads: No
Col Widths: blank
Bound Col: 1
LimitToList:Yes

No events
As usual -- Thanks for your efforts

 

[TheAceMan1]

[blue]My original design of the form and combos, (very simple indeed) [purple]probably is the cause of our difficulties[/purple].[/blue]

[purple]Indeed![/purple] (not trying to sound demeaning here) . . . .

[blue]The Problem:[/blue]
[ol][li]ComboBox [blue]County Name[/blue] returns a [blue]single field[/blue] of [blue]data type Text[/blue].[/li]
[li]In the SQL we pass to combobox [blue]TownName[/blue], the criteria is trying to ping [purple]CountyCD[/purple] ([purple]numeric data type[/purple]) against the only field in [purple]County Name[/purple] ([purple]text data type[/purple]).[/li][/ol]
[blue]Not gonna work![/blue]

[purple]To correct the problem, change the following for combobox [blue]County Name[/blue]:[/purple]
[ol][li] Change [blue]RowSource[/blue] to:

[blue]SELECT DISTINCTROW [CountyId], [County Name] FROM [Counties];[/blue]

I hope it was a typo, but there was also a spacing problem in [purple][County Name]FROM[Counties][/purple][/li]
[li][blue]Col Count[/blue]: 2[/li]
[li][blue]Col Widths[/blue]: 0", .8855"[/li][/ol]

By the way . . . . when you change the [blue]RowSource[/blue] of a combobox, it [purple]automatically requires![/purple]

[purple]Thats it . . . . give it a whirl and let me know . . . .[/purple]

See Ya! . . . . . .

 

[rlf1957]

You wrote:
"By the way . . . . when you change the RowSource of a combobox, it automatically requires!"

Was this an interrupted thought?

The rest is getting closer -

ComboBox1 - I select the County (it shows the County Name)
ComboBox2 - Drops down with only numeric data (TownsID)

After selecting one the table that I am entering into stores only ID#'s for both fields rather than the text names. I use this data to merge into Word documents so I really need the text. Is this a problem?

I'd like to give you a couple of stars now for your efforts and patience.

 

[TheAceMan1]

rlf1957 . . . . .

I sould've told you in my prior post. You origionally just wanted [blue]TownName[/blue] in combobox TownName. So lets put that back. In the AfterUpdate event of [blue]County Name[/blue], go back to:

[blue]Me![TownName].RowSource = "SELECT TownName FROM [Towns]" _
 & " WHERE CountyCD=" & Me![County Name].Column(0) ORDER BY [TownName];[/blue]

and properties should be:
[ol][li][blue]Col Count[/blue]: 1[/li]
[li][blue]Col Widths[/blue]: blank[/li][/ol]

See Ya! . . . . . .

 

[rlf1957]

AceMan1...

thanks for your diligence and patience. It seems to work
fine. I need to do some testing but it looks good.

One thing I noticed is - when I go in to edit an existing record, if I happen to click the second combobox first - there is nothing in the backgrnd to limit the second list, it drops down the complete Town list.

This isn't a very big pblm at this time but might be later on in the life of this project.

Many stars to you - *****

RLF1957

 

[TheAceMan1]

rlf1957 . . . . .

Great! . . . . I told you it was easy! ;-)

Thats due to the [blue]Default RowSource[/blue] (your origional). To eliminate it, in [blue]form design view[/blue] just [blue]delete it & save[/blue].

Where your troubleshooting other problems in your database, bear in mind what held-up this thread . . . [blue]a simple missing field![/blue]

Also . . . what [blue]PHV[/blue] gave you in the beginning would've worked fine if not for the above. Credit is due here . . . . .

See Ya! . . . . . .

 

[rlf1957]

Just went back and starred PHV also - thanks again ...
Onward, ever onward ....

Happy New Year

RLF1957