LookUp or SetValue

[Sandy]

Hi,

I have a small problem which should be easy to solve, but hasn't been.

I am trying to take the customer name from my customer table and insert it
into my orders table when the customer number is entered. I want to index by
number because we have over 500 customers and some of the names are very
similar.

Please Help!!!!!!!

 

[Joan Wild]

You shouldn't store the customer name in the orders table at all. That's
redundant and you can get it from the customer table at any time.

Just create a query based on the two tables and pull any customer info you
want to see with their orders from the customer table.

 

[Sandy]

Thanks Joan.

That wasn't exactly what I was thinking of, but you're right.
Now I have another question, for Validation purposes, I would still like to
have the customer name come up on the form, or maybe a message box showing
the vendor's name with an ok button asking if this is the right customer.

Thank you again,

 

[Joan Wild]

You should create a form for data entry, and not rely on nor allow users to
enter orders via the tables.

The form wizard will walk you through creating a main form/ child form that
will likely meet your needs.

If you run the wizard, you can choose your Customers table, and add all the
fields, and then before going to the next step, select the orders table and
add its fields. If you've created a relationship between these tables (as
you should), Access will automatically detect this and assume you want a
main/child form.

You'll end up with a customer form and a subform, that shows all the orders
for only that customer.

You can then add a combo box to the main form (again the wizard will offer
you the option to 'find a record on my form'.

Pursue those options and post back with any stumbling blocks.

 

[John Vinson]

Now I have another question, for Validation purposes, I would still like to
have the customer name come up on the form, or maybe a message box showing
the vendor's name with an ok button asking if this is the right customer.

Just to agree with Joan - the users should never even SEE the customer
number! You can provide a Combo Box which *stores* the customer number
in the table, while *showing* the user the customer's name (and
probably some additional information allowing them to identify which
Jane Smith they are referencing).

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

Take a look at the northwind.mdb. It comes with all versions of Access and
is an example of an order entry system. It should give you an idea of how
the forms should work.