Please help with combo box auto fill-in field in form written into table

[circuit slave]

Okay, I've tried to read various posts but can't seem to find quite
the exact answer. What I need is to be able to have two fields in a
table auto fill in, from a form entry--either by combo box (first
field) and/or auto fill in (second field) based on what's selected in
first field combo. I need to have these two fields in the form
written into the table database after entry because I will need to
mail merge using these fields, and because I also need to have a hard
copy of the database--not just a report.

So in the form, the combo box has a list of vendor numbers, the second
field has the vendor name which would fill in with the corresponding
name. Now this vendor list is always the same and this list rarely
needs updating, but it's possible. I thought of creating first
another table from which it's derived called [vendor list] which I
would have first column (Vendor #) and second columm(Vendor name).


I have read other posts about redundancy in tables or proper
calculated fields, unbound, so I'm a little confused. I want to creat
this properly, but this separate table needs to have the vendor # and
corresponding name entered into it, and this information, I'd like to
create in advance so entry is easier. Remember, I need to be able to
print this table database, merge from it.

thanks!

 

[John W. Vinson]

Okay, I've tried to read various posts but can't seem to find quite
the exact answer. What I need is to be able to have two fields in a
table auto fill in, from a form entry--either by combo box (first
field) and/or auto fill in (second field) based on what's selected in
first field combo. I need to have these two fields in the form
written into the table database after entry because I will need to
mail merge using these fields,

You're assuming that you must have the two fields in the same table in order
to do a mail merge. That assumption is incorrect; you can - and usually will -
do a mail merge from a Query, not directly from a table.

and because I also need to have a hard
copy of the database--not just a report.

A Report IS a hard copy of the data in the database. There is no such thing as
"a hard copy of a database" since a database is a *container* for multiple
Forms, Tables, Queries, Reports, VBA code, etc. It is not a document!

So in the form, the combo box has a list of vendor numbers, the second
field has the vendor name which would fill in with the corresponding
name. Now this vendor list is always the same and this list rarely
needs updating, but it's possible.

If a Vendor name changes, what should happen to Invoices which were generated
prior to the change?

I thought of creating first
another table from which it's derived called [vendor list] which I
would have first column (Vendor #) and second columm(Vendor name).


I have read other posts about redundancy in tables or proper
calculated fields, unbound, so I'm a little confused. I want to creat
this properly, but this separate table needs to have the vendor # and
corresponding name entered into it, and this information, I'd like to
create in advance so entry is easier. Remember, I need to be able to
print this table database, merge from it.

So... you want a COMPLETE printout of ALL of the data in the database, every
time you generate a new invoice!? I take it you own stock in Weyerhauser or
another paper company... Why? I don't see any sense to this!

John W. Vinson [MVP]

 

[circuit slave]

So... you want a COMPLETE printout of ALL of the data in the database, every
time you generate a new invoice!? I take it you own stock in Weyerhauser or
another paper company... Why? I don't see any sense to this!

John W. Vinson [MVP]

No, not every time I generate a new invoice. I just may want to print
out all the info at some point.

Okay, so what would be the most practical why if setting this up? All
I really want is to be able to

do is have two controls in a form that get written into the table for
the vendor number and corresponding

vendor name. If this info already exists in a table and does not need
to be redundantly created, how could

I set it up correctly?

 

[John W. Vinson]

Okay, so what would be the most practical why if setting this up? All
I really want is to be able to

do is have two controls in a form that get written into the table for
the vendor number and corresponding

vendor name. If this info already exists in a table and does not need
to be redundantly created, how could

I set it up correctly?

Store the VendorID field (but not the vendor name) in the Invoice table using
a combo box.

If you need to see the vendor name in conjunction with information in the
invoice table onscreen, use a Combo Box bound to the vendorID but displaying
the vendor name.

If you need to see it on a Report, base the Report on a query joining the
Invoice table to the Vendor table.

It's a very common misperception that everything needs to be all in one table
in order to use it. That's NOT how Access works though! It's a relational
database, so you can store each kind of information in its own table, and use
Queries to pull the information together.

John W. Vinson [MVP]

 

[circuit slave]

Store the VendorID field (but not the vendor name) in the Invoice table using
acombobox.

If you need to see the vendor name in conjunction with information in the
invoice table onscreen, use aComboBoxbound to the vendorID but displaying
the vendor name.

If you need to see it on a Report, base the Report on a query joining the
Invoice table to the Vendor table.

It's a very common misperception that everything needs to be all in one table
in order to use it. That's NOT how Access works though! It's a relational
database, so you can store each kind of information in its own table, and use
Queries to pull the information together.

John W. Vinson [MVP]

Okay, thanks John, that makes sense. Thanks for your help !