What is the best way to do this?

[aftershokk]

I have a form with vendor name. I also have a second form with my vendor list and corresponding vendor number.

Here is what I want to do.

When a user selects vendor name from the drop down list (this is already in place), the form would match vendor name in this form to the vendor name in my vendor form and then return the vendor # to my main form.

Currently, I run an update query after data entry to perform the lookup. I would rather have the form fill this real time.

Thanks!

 

[neileg]

Eh?

Why isn't your vendor list in the first form populated with both name and number? I'd use a combo box based on a query that pulls this info from wherever you are getting for the second form (which you now don't need).

 

[aftershokk]

response

My list box does populate the vendor name when the drop down is selected. Not sure how to populate 2 different fields at once this way.

When the vendor is selected I would need the vendor name and # to be populated on these 2 sep fields on the table.

thanks

 

[boblarson]

You shouldn't be storing the name as well as the number in the table. The name should ONLY be in ONE table. You can get at the name when pulling data by linking the two tables together in a query.

By storing the name again in the second table, you are violating the rules of normalization and it becomes a potential nightmare if a name should change and you then have to go change it in multiple places.

 

[neileg]

No you don't. You only store vendor number so bind the combo box to the vendor number field. Adjust the combo so it shows the name but stores the number.

 

[GaryPanic]

Huhh ??

zip it and post it on the forum
not quite clear as to what you are after

From the looks of things it should be an after update function of your drop down box

 

[aftershokk]

response

It was inherited and it will be 2 cubersome to re-design.

My main form has one table behind it.

When a user selects the vendor name from the list box, I need the vendor name and vendor number to be stored in 2 sep fields on my main table. The list box is created from a vendor table.

thanks

 

[boblarson]

Okay, to get around this (band-aid solution) -

Bind the combo box to the vendor id and put a text box on the form that is bound to the vendor name and in the after update event of the combo box put

Me.YourTextBoxName = Me.YourComboBoxName.Column(1)

Assuming of course that the name is the second column in the combo box.

 

[GaryPanic]

right have your combo tied to a qry -- yes good
on your combo box option you can have 1, 2 3 or more heading in there

if you only want to see 1 then mess around with widths etc



now in your after update field

you will get afterdate_comboboxname....

now oyu need to state
field on form =combobox.column(0)

so hte field on the form will now be populated by the first column of your qry

(access starts from 0) weird but it does
now your second field name
will be
field on form 2 ==combobox.column(1)
you can carry on down all the way with as many as required

but they must be in your qry and in your combo box (even if you cannot see them
Put a refresh option and a save option in as well
so when you change the option form your drop down everything should also change
and a save option in case some muppet bail's out of the system without doing a proper save .
not the tidest way
g

 

[GaryPanic]

you got there again first box .
g

 

[neileg]

It was inherited and it will be 2 cubersome to re-design.

But your thread is titled "What is the best way to do this?