how to create a from that fill in automatically

[Guest]

I have vendor information in one table, with each vendor and all their
information as individual records. I would like to create a form that will
automatically fill in my vendor phone,address,fax and other information when
I select a vendor from a combo box on my main form. I have had no luck
getting the results I want from auto lookup queries. All I really want to do
is avoid entering vendor infomation for each invoice I enter.

 

[Pat Hartman\(MVP\)]

Create a query that joins the order table to the vendor table. You can
select whatever you need from each table. Change the RecordSource for the
form to be the query rather than a table. You will be able to choose the
vendor attributes from the dropdown in each control's ControlSource. Be
sure to set the Locked property of these fields to Yes to avoid accidental
updates to the vendor data.

 

[John Vinson]

I have vendor information in one table, with each vendor and all their
information as individual records. I would like to create a form that will
automatically fill in my vendor phone,address,fax and other information when
I select a vendor from a combo box on my main form. I have had no luck
getting the results I want from auto lookup queries. All I really want to do
is avoid entering vendor infomation for each invoice I enter.

You're not storing vendor information in the invoice table, I hope!?
What IS the structure of your tables?

You can use the Combo Box wizard to *FIND* and display the existing
data for a vendor, but the information for an invoice should be
entered on a Subform of the vendor form, or possibly simply displayed
for information purposes on the form (and not stored). You can do this
by including up to ten fields from the Vendor table in the vendor
combo box's RowSource, and using textboxes with control sources like

=cboVendor.Column(n)

where (n) is the *ZERO BASED* subscript of the field that you want to
see. That is, if the vendor's fax number is the sixth field in the
combo, you'ld use (5).

John W. Vinson[MVP]

 

[Guest]

Thanks for writing John. This is how my tables are structured, all vendor
information (phone, fax, address, etc.) is located in the vendor table and
all invoice information (date, PO#, etc) is in the invoice table. And all
the Invoice details information (quantity, description, price etc.) is in a
invoice details table. I plan to have an invoice input form with subforms
for invoice infomation. So, when I enter a vendor name at the top of the
form, all vendor information will fill in automatically (fax, phone, address)
and I just need to fill in the invoice details in the invoice subform. I am
still unclear on how I'm going to get the vendor info to auto fill.

I hope this helps clarify things, thanks for your response!

 

[John Vinson]

Thanks for writing John. This is how my tables are structured, all vendor
information (phone, fax, address, etc.) is located in the vendor table and
all invoice information (date, PO#, etc) is in the invoice table. And all
the Invoice details information (quantity, description, price etc.) is in a
invoice details table. I plan to have an invoice input form with subforms
for invoice infomation. So, when I enter a vendor name at the top of the
form, all vendor information will fill in automatically (fax, phone, address)
and I just need to fill in the invoice details in the invoice subform. I am
still unclear on how I'm going to get the vendor info to auto fill.

Why do you need the fax, phone, and address fields to "fill in"? Just
for visual verification, or what?

What you can do is use a Combo Box (not a textbox, which would require
that you type the complete vendor name and never, ever make a typing
mistake) based on a query of the Vendor table. Select all of the
fields that you want to see on the invoice form in the query; you can
use the combo's ColumnWidths property to set the width of some of the
fields to 0 so they don't show up when you drop down the combo box.

Then, on the Invoice form, you can put textboxes with Control Sources
like

=comboboxname.Column(n)

to *display* the n-th (zero based, e.g. Column(3) is the fourth field
in the combo box's query) field in the combo box.

If you're printing the invoice, base a Report on a query joining all
three tables - vendors, invoices, invoicedetails - so you have all
fields available for printing. Don't try to print the Form; they're
not designed for that purpose!

John W. Vinson[MVP]

 

[Guest]

Thanks for the input, It sounds a little tricky but I'll give it a try.
Thanks Again! nikki