Fill Field with Data from Table

J

jimswinder

I know what I am asking is simple..but I can't figure it out. I want to fill
in a filed on a form automatically when another field is filled in with some
info. IE: I have a vendor table with Vendor Numbers and Vendor Names. When I
put in the Vendor Number I need the Vendor Name to fill in automatically. I
would like to do this with Visual Basic.

Thanks...
 
J

JimS

Use the fieldname_on_update event procedure of the first field to populate
the second control, then requery to post it. You probably want to make the
second control unavailable (grey'd out...)
 
J

John Vinson

I know what I am asking is simple..but I can't figure it out. I want to fill
in a filed on a form automatically when another field is filled in with some
info. IE: I have a vendor table with Vendor Numbers and Vendor Names. When I
put in the Vendor Number I need the Vendor Name to fill in automatically. I
would like to do this with Visual Basic.

Thanks...
Click to expand...

One question:

WHY?

Are you attempting to store the vendor name redundantly in a second
table? This is generally neither necessary nor desireable; if you need
the vendor name onscreen you can set the Control Source of a textbox
to

=cboVendor.Column(n)

where cboVendor is the name of the combo box in which you're selecting
the vendor number, and (n) is the zero based subscript of the vendor
name field in the combo's RowSource query. And if you need it in a
Report, simply base the report on a query joining this table to the
Vendor table by vendor number.

Am I missing something here? Do you really NEED to store the vendor
name twice, in two different tables, subject to the risk that an error
correction in one table won't be reflected in the other?

John W. Vinson[MVP]
 
J

jimswinder

Nope...not trying to store it in a table...just want it to show up on a form
(Purchase Order). Actually it would be just the Primary Key of that record
that would be stored in the table. Right?
 
J

John Vinson

Nope...not trying to store it in a table...just want it to show up on a form
(Purchase Order). Actually it would be just the Primary Key of that record
that would be stored in the table. Right?
Click to expand...

Good!

The simplest way to do this is simply to use a Combo Box for the
vendor ID. Have the primary key as the bound column, with its width
set to zero so the computer sees it but the user doesn't; make the
first (only?) visible column the vendor name.

If for some reason you want to see both the vendor ID and the vendor
name, still use a combo box; you can put a Textbox on the form with a
control source

=cboVendor.Column(n)

where cboVendor is the name of the combo box, and (n) is the zero
based subscript of the field you want to show - e.g. (1) means display
the second field in the combo box's RowSource query.

John W. Vinson[MVP]
 
J

jimswinder

aaaaarghhhh!!!! why are the simple things so difficult? I cannot get it to
work. All I want it to do is display the following fields in a form.

From the Table "Procurement - Vendors", I have 2 fields..."VendorNumber" and
"VendorName" which I want to appear on a form "Purchase Orders". When I type
in the Vendor Number in the List Box on the form, I want the Vendor Name to
appear also in the List Box below Vendor Number.
 
J

John Vinson

From the Table "Procurement - Vendors", I have 2 fields..."VendorNumber" and
"VendorName" which I want to appear on a form "Purchase Orders". When I type
in the Vendor Number in the List Box on the form, I want the Vendor Name to
appear also in the List Box below Vendor Number.
Click to expand...

Are these two separate listboxes? Or do you want a single listbox with
alternating rows for vendor number and vendor name? And why a listbox
anyhow? Could you use a two-column listbox displaying both fields
(side by side, rather than above and below)?

Normally a listbox is used for *SELECTING* an existing value from a
list, rather than typing the vendor number in. I'm not sure that this
is the right tool!

My suggestion was that you use a Combo Box (rather than a listbox)
based on your table. Assuming that users know the vendor number, you
could use a two-column combo box based on the [Procurement - Vendors]
table (or a query sorting by ascending vendor number); a Textbox
elsewhere on the form (below the combo perhaps?) with a control source

=combobox.Column(1)

using the name of your combo box would display the name.

John W. Vinson[MVP]
 
Top