how do I join combo boxes?

[smwabill]

Let say I have a form called Petty Cash Entry which ties back to Petty Cash Query which ties back to Petty Cash Table. This form has two fields. Field 1 is called Vendor and Field 2 is called Category. Both fields are a look up from the table back to Vendors table.

What I want to be able to do is:

When I call up the Vendor I want the Category to come up automatically. The Vendors and Categories are are existant in the Vendor Table

For example:

Vendor = 7-Eleven
Category=Food

Vendor = Staples
Category = Office Supplies

Also, will this only work with forms or will it also work with queries.

Thank you for your help
smwabill

 

[jfgambit]

Add the following to the On_Change Event of your Vendor Textbox:

Dim rsCat as Varient

rsCat = DLookup("[CatagoryFieldName]", "VendorTable Name", "[VendorFieldName] = '" & Me.VendorTextBoxName & "'")

me.CatagoryTextBoxName = rsCat

Replace the Above names with those of your table names, field names and form textbox names.

HTH

 

[smwabill]

joining combo boxes

to jfgambit:

the solution you gave me does not seem to work.

Dim rsCat as Varient

rsCat = DLookup("[CatagoryFieldName]", "VendorTable Name", "[VendorFieldName] = '" & Me.VendorTextBoxName & "'")

me.CatagoryTextBoxName = rsCat

perhaps if I give you the exact names you can further enlighten me.

VendorTable=PettyVendors
field names in PettyVendors:
Vendors
Vendcat

Petty Cash table= PettyCash
field names in PettyCash:
Pvendor
Pcategory2

Form name= PettyCash
textbox names in form PettyCash:
Pvendor
Pcategory2

I double checked these names and spacing--they're correct

Thank you for your continued help.

smwabill

 

[jfgambit]

Try:


Dim rsCat as Varient

rsCat = DLookup("[Vendcat]", "PettyVendors", "[Vendors] = '" & Me.PVendor & "'")

me.Pcatagory2.setFocus
me.PCatagory2 = rsCat

HTH

 

[smwabill]

Still does not work

comes up with compile error and rscat as variant comes up hightlighted and says user-defined type not defined

maybe I'm overlooking something but your coding seems straight forward. Both solutions have same error?

any more suggestions?
smwabill

 

[bradcccs]

Include the "category" in the rowsource of your combo box (hide the column if you wish)

Then set the control source of a text box equal to:

=CboNAMEOFCOMBOBOX.column(2)

remember that column numbering starts at 0

Note:

This is done for form reference only. You should not be restoring this category information in the petty cash table, as it can be looked up from the vendor table when required.

Brad.