Synchronizing Combo Boxes on Forms in Access 2007

[pathfinder225]

hi all,

i have a small task here that ,when i select an item in the first combo box, that selection limits the choices in the second combo box.

tblItems
ID AUTONUMBER
item_name TEXT

tblProducts
ID AUTONUMBER
pro_name TEXT
item_name NUMBER ( this is look up from above table:1)

combobox1: item_name (values from tblItems)
combobox2: pro_name (values should be fetched from tblProducts depending up on value selected in combobox1)

To achieve this i placed the Following code in AfterUpdate event of combobox1

Private Sub Combo1_AfterUpdate()
    ' Update the row source of the combo2 combo box
    ' when the user makes a selection in the combo1
    ' combo box.
    Me.Combo2.RowSource = "SELECT pro_name FROM" & _
                         " tblProducts WHERE item_name = " & _
                               Me.Combo1 & _
                               " ORDER BY pro_name"
                            
    Me.Combo2 = Me.Combo2.ItemData(0)
End Sub

but the problem here is when i execute this, it is taking Me.Combo1(in Query) as parameter but not Values selected a dialog box appears asking to enter value. Anybody please help me to resolve this any help would be greatly appreciated.Hope i have made my problem clear.

Best Regards,
pathfinder.

 

[DCrake]

This is the age old mistake of not encompassing the crieria in quotes

"SELECT pro_name FROM" & _
                         " tblProducts WHERE item_name = [B][COLOR="Red"]'[/COLOR][/B]" & _
                               Me.Combo1 & _
                               "[B][COLOR="red"]'[/COLOR][/B] ORDER BY pro_name"

The only time you do not need quotes is when using numeric values. Dates need #'s around them.

David

 

[pathfinder225]

Many Thanks Dcrake for taking your valuable time to reply...anyway i managed to solve the problem ,just an overlook.

well i'm having another trouble the same query doen't work when i put like this..

"SELECT pro_name FROM" & _
                         " tblProducts WHERE item_name = [B][COLOR=Red]'[/COLOR][/B]" & _
                               Me.Combo1 & _
                               "[B][COLOR=red]'[/COLOR][/B]" 
/*i removed [B]order by pro-name[/B]*/

I don't want to put order by there, then it is again taking as parameter and Syntax Error at last ("'").I will be waiting eagerly for your answer.

 

[SOS]

Just move the "'" in place of the last underscore _

"SELECT pro_name FROM" & _
" tblProducts WHERE item_name = '" & _
Me.Combo1 & "'"

 

[pathfinder225]

Just move the "'" in place of the last underscore _

"SELECT pro_name FROM" & _
" tblProducts WHERE item_name = '" & _
Me.Combo1 & "'"

Thanks SOS, but this doesn't make any help, the _ is used to go for next line(continuation) it's same error again.

 

[giovaanni]

If me.combo1 is text you should be using triple double quotes on each side of it. """ & string & """

 

[SOS]

If me.combo1 is text you should be using triple double quotes on each side of it. """ & string & """

Actually a single quote is fine, but the line should perhaps just read:

"SELECT pro_name FROM" & _
" tblProducts WHERE item_name = '" & Me.Combo1 & "'"

And get rid of the continuation line.

You could also use Chr(34) which is double quotes:

"SELECT pro_name FROM" & _
" tblProducts WHERE item_name = " & Chr(34) & Me.Combo1 & Chr(34)

 

[dhunter]

I am having a simlar problem. I am trying to synch a combo box selection with three list boxes, code for all three is the same. After selecting an option on the combo boxes, I get a pop up asking for a event procedure parmeter. I am very new to VB.net. I tried the quotes around the criteria and all other suggestions but when I do any of them the list box is empty and no popup happens.

This is the code from the AfterUpdate event on my combo box for the first listbox:

Private Sub cboFleet_AfterUpdate()
Me.lbSenderCode.RowSource = "SELECT tblSV_SRT.SenderCode " & _
"FROM tblSV_SRT " & _
"INNER JOIN tblSV_Accts_and_Reps " & _
tblSV_SRT.Fleet = tblSV_Accts_and_Reps.Fleet " & _
"WHERE tblSV_Accts_and_Reps.Fleet = " & _
Me.cboFleet.AfterUpdate & _
" GROUP BY tblSV_SRT.SenderCode"
End Sub

Also anyone having suggestions on where I can find information about generating a report from this form?

Thanks!

 

[SOS]

Well, the first thing I noticed is the line in red:

Private Sub cboFleet_AfterUpdate()
Me.lbSenderCode.RowSource = "SELECT tblSV_SRT.SenderCode " & _
"FROM tblSV_SRT " & _
"INNER JOIN tblSV_Accts_and_Reps " & _
tblSV_SRT.Fleet = tblSV_Accts_and_Reps.Fleet " & _
"WHERE tblSV_Accts_and_Reps.Fleet = " & _
[COLOR=red][B]Me.cboFleet.AfterUpdate[/B][/COLOR] & _
" GROUP BY tblSV_SRT.SenderCode"
End Sub

It should just be

Me.cboFleet

withOUT the .AfterUpdate part.

 

[dhunter]

I tried without the .AfterUpdate but then the list boxes are blank after entering in a value in the pop up.

I still get the pop up that says to "Enter a Parmeter Value" and in the box it says event procedure. If I type in the fleet name it will show the sendercodes associated with the fleet in the listbox sender code.

Thanks for a quick reply tho!

 

[SOS]

I think I would need to actually see the database for this one. If you can post it that would be great. If not, perhaps someone else has some ideas.

 

[dhunter]

It is an extemely large database. Don't think I can do that. Here is a sample:

I basically have one table tblSV_SRT containg the fields: Fleet, SenderCode, RecieverCode and Transaction

Fleet = 123GHT, THG123 etc
SenderCode = 123123, AP456,89TOP etc
RecieverCode = same type as sender code
Transaction = 404,417,996,4x4

A fleet is a group of train cars carrying a type of product.
There are specific sender codes associated with a fleet.
There are specific reciever codes associated with a fleet.
And then if you pick a specific sender and a specific reciever there are different types of transactions that those senders and recievers can have.

Maybe too much information for you but this is all based off one table = tblSV_SRT, I have another table that I pull the fleet names from = tblSV_Accts_and_Reps. The only reason I pull the fleet name from this second table is b/c there is a many to many relationship that exists between the senders and recievers of the fleets in the tblSV_SRT which shows a duplicated list in the combo box.

At this point all I want the form to do is:
1)Allow a user to pick a fleet
2)Show all the senders associated with that fleet
3)Select a reciever associated with that sender and that fleet
4)Select a transaction associated with the sender, reciever and fleet
5)Generate a printable report from that data.

From what you now know, do you think this is possible? I am still on step two =( I don't know if that is what is needed but I appreciate any help!

 

[pathfinder225]

MY Problem Solved Guys anyway Many thanks for taking ur time in helping me.

 

[noble]

MY Problem Solved Guys anyway Many thanks for taking ur time in helping me.

would u please tell me how did u solve ur problem?

I have such tables and form as u! but the same code doesn't work at all!
the second combo doesn't show any item

 

[noble]

hey all

I followed this tutorial step by step!

h*t*t*p://msdn.microsoft.com/en-us/library/office/bb404901(v=office.12).aspx

but it didn't work!
anyone could help me?

thnx in advance