Populating Combo Box from table based on selection - QUESTION

[kealaz]

Hello,

I have a combo box [chooseMANUF] with a selection of MANUF to choose from.
Once selected, I would like to populate another combo box [chooseVENDOR] with
the vendors that carry the selected MANUF. I have a table [tblMANUF] with
the fields MANUF, VENDORNAME & VENDORNO. Each MANUF can be carried by
multiple VENDORS and each VENDOR can and usually does carry more than one
MANUF. How do I get the second combo box to populate with the appropriate
vendors? I have tried the following...

Combo Box: chooseVendor
Control Source: [blank]
Row Source Type: Table/Query
Row Source: SELECT DISTINCT VENDORNAME FROM tblMANUF WHERE MANUF =
me.chooseMANUF.column(0)

This results in an error:
Undefined function 'me.chooseMANUF.column' in expression.

If you can help me figure out what I'm doing wrong... THANK YOU VERY MUCH!

 

[Jeanette Cunningham]

Hi kealaz,
the row source is not set up correctly.

Delete the row source from the choose vendor combo.
Use code something like this:
--------------
Private sub chooseMANUF_AfterUpdate()
Dim strSQL as String

If Len(Me.chooseManuf) > 0 Then
Me.chooseVendor = Null

strSQL = "SELECT DISTINCT VENDORNAME " _
& "FROM tblMANUF " _
& "WHERE MANUF = " & me.chooseMANUF

Debug.Print strSQL
Me.chooseVendor.RowSource = strSQL

End Sub
-------------

If the field called Manuf is a text data type, you will need extra quotes
like this
& "WHERE MANUF = """ & me.chooseMANUF & """"

To test out this code, open your form and choose a manufacturer, then click
on the combo for choose vendor.
Close the form and press Ctl + G to open the immediate window.
You will see the query string there in the immediate window.
Make a copy of the query string from the immediate window.
Create a new query in sql view and paste the query string in.
Change the query to datasheet view, if there are any errors with the query
string, access will tell you at this step.
If no errors, and all the expected data is there, then you are done.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[kealaz]

Jeanette,

THANK YOU so much! It works great!!!

 

[kealaz]

Hi,

I have a form, that get's it's information from tblPOTODO. The information
is for parts that need to be purchased. In the form, selections are made to
choose the manuf of the part to be purchased and the vendor that the part
will be purchased from. Once those selections are made, I need to write all
that information to another table, tblBUY. How do I do this? I think I need
to create a command button... but do I use one of the already configured
buttons, or will I need to write a macro? or other coding?


Other info...

Form Name: frmPOTODO_process
Info coming from: tblPOTODO
Info going to: tblBUY

On the form, there are 3 combo boxes and 2 text boxes.
Combo boxes:
PART_NO --- goes to field PART_NO in tblBUY
chooseMANUF --- goes to field MANUF in tblBUY
chooseVENDOR --- goes to field VENDORNAME in tblBUY
Text boxes:
DESCRIP --- goes to field DESCRIP in tblBUY
NAME --- goes to field NAME in tblBUY


Other information that needs to get from tblPOTODO to tblBUY after the
vendor selection is made includes...
MANUF_PN
QTY_ORDER
TAXABLE
DELIV_TO

This information is not on frmPOTODO_process, because it is not needed to
make the MANUF and VENDOR selection, however it does need to go along. Can I
do this all in one step? or do I need to bring all that information into the
form, via text boxes and then write it to tblBUY when I write the other
information?


Thank you SO MUCH for all of your help with this.

 

[Jeanette Cunningham]

Give the form a recordsource based on tblBuy.
Put the fields that need to get information on the form.
Access will save the info to tblBuy.
Note: If you put a close or save button on the form, you will need to put
this code on the button:

If Me.Dirty = True Then
Me.Dirty = False
End If


Then if you want to close the form go
DoCmd.Close acForm, Me.Name
If you just the red X to close the form, access will save the data for you.

If you have the normal access record navigation buttons, just going to the
next record will save your changes.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia