Updating Form Field From Another Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a "ProductName" table with two fields: "Product" and "ProductCode".
I have this because the product name tends to be very long and I have
thousands of products.

I have a form that populates fields in a "Customer" table listing the
product they have.

In this form I have an unbound text box named "Code". I would like to enter
the ProductCode into this text box and on exit I would like it to populate my
"Product" control in the form for that customer only. I currently do this by
running a macro that runs a query. I would rather do this through VB.

Your help is much appreciated.
 
You've only describe the fields in your ProductName table.

It would be unusual to have a table with customer information that also
contact customer-product information -- that is not a well-normalized
design.

A well-normalized design would have a customer table with customer-only info
.... name, address, phone, ... and a CustomerProduct table that resolved the
many-to-many relationship that exists between customers and products (one
customer can have many products, one product can be had by many customers).

Your description of "populate fields in a Customer table" sounds like a
spreadsheet, with one column for each product.

Or am I reading too much between the lines...?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Mr. Boyce,

The tables have a lot more data than described and have many relationships
that are crucial already exist. I was trying to keep things simple for the
sake of the question. Also I used Product and Customer as examples only.
This is my offices medical database I have created and in reality the
"products" are the "tools" that I use during surgery in each patient. That
is why I don't want to create a relationship between these two tables. So I
was just hoping by me entering the abbreviation in an unbound textbox, I
could get MSAccess to fill in the whole name in the appropriate control.
Hope I was able to clear this up. Sorry for the confusion.
 
My comments related more to the possibility that your table has "repeating
fields" (one for each Tool/Product). You will not get good use of Access'
features and functions if you feed it spreadsheet data...

If you design your data structure relationally, you will be able to use a
combo box to select, instead of requiring entry of a code or abbreviation.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top