Combo Box Question

[aporkalypse_now]

I am trying to create a combo box on an order entry screen. How can I set the combo box so that when I pick a supplier name, all the pertinent info pops up in supporting fields. For some reason I only get info to go into one field and that is all the wizard asks me (which field) that the data will be stored in. It does not give me the option of multiple fields for storage.

Thank You

 

[Jimbob]

It won't. A combo box CAN be used to select records on a form...is this what you mean?

If so use the combo box wizard and select the third option,

Otherwise, if you want to choose a supplier from a list, then have code which populates values on the order form, is all the info in the Suppliers Table?

If so do the following:

call your combo box cboSupplier

Go to it's AfterUpdate event and paste the folowing code into the code window:

Private Sub cboSupplier_AfterUpdate
dim rst as Recordset
dim db as Database
dim strSQL as String
set db = CurrentDb

StrSQL = "SELECT Field1, Field2, Field3, etc " & _
"FROM tblSuppliers " & _
"WHERE YourCriteriaField = " & Me!cboSupplier & " "

set rst = Db.OpenRecordset(StrSQL)

With rst

If .RecordCount = o then
msgbox "No Records Found"
.close
exit sub

else

Me!Field1 = !Field1
Me!Field2 = !Field2
.close

end if
end with


Try that, change the tblSuppliers bit to the name of the table you are querying.

with the "SELECT Field1, Field2" you just name the fields in the table that you want to bring into the query and seperate them with a comma

*No comma however after the last fieldname and before the FROM statement*

"YourCriteriaField" is the name of the field in the tbale being queried that holds the criteria value

IF cboSupplier holds a atext value (as opposed to a numeric value) then change StrSQL to this:

StrSQL = "SELECT Field1, Field2, Field3, etc " & _
"FROM tblSuppliers " & _
"WHERE YourCriteriaField = ' " & Me!cboSupplier & " ' "

the "ME!Field1 = !Field1" are where the values are carried form the recordset to the form. Me!Field1 relates to a control on your form.

it should be Me!ControlName = !TableFieldName


Hope this all makes sense, if not give us a shout

[This message has been edited by Jimbob (edited 03-27-2002).]

[This message has been edited by Jimbob (edited 03-27-2002).]

[This message has been edited by Jimbob (edited 03-27-2002).]

 

[Fizzio]

Instead of populating the form with already existing data, just store the supplier ID with the order then you can pull off the supplier details from the suppliers form. If you have to show the supplier details, in the query populating the combo add all the fields you want to display then on unbound textboxes on your form, set the controlsource to =[NameofCombo].Column(ColumnWhereDataIs) but in a combo, the first column is 0. This will show the information but not store it twice.

HTH

 

[juice75]

A third option is to set up the query that supports your data entry form to include the SupplierID field from the Order Table, I'm assuming supplierID is a foreign key in the Order table, and include the supporting supplier information fields from the Supplier table. Then set up a combo box to look up SupplierIDs and Names. Once a supplier is selected the remaining fields should be automatically looked up but not stored a second time.