AutoLookup Query

[Jane]

Hello,

I would like certain fields in an orders form to update
automatically when I manually update a specific field.
i.e. when I enter a product code, it automatically shows
which of my teams is responsible for that product.

This should be fairly straightforward using an autolookup
query. However our product codes are in the form 012345,
where the first two digits specify the team. For example,
codes starting 00, 01 or 02 are owned by team A, codes
starting 03, 04 or 05 are owned by team B.

I can easily translate 012345 into 01 by using LEFT
([product],2) in a query. This can then be linked to my
team codes table through a second query, allowing me to
add [team] to each order.

The problem is though that when a product number (012345)
is entered in my form, the shortened code (01) updates
automatically (not that this is really needed), but [team]
will only update once the form has been closed and
reopened.

Is there any way to get this to update automatically also?

Thanks,
Jane.

 

[Jeff Boyce]

Jane

One of the principles of normalization is 'one fact, one field'. By
embedding multiple facts (i.e., Product ID AND Team ID) in your field, you
make it much more difficult for Access to do what it was designed to do. My
first suggestion would be to break that field into two (?!or more) fields,
one for each separate "fact".

Then, build a query that joins the product info to the team info, and
include the team info field(s) you want displayed on your form. If you used
a combo box to allow the user to select a product, instead of manually
entering a product code, you could use the combo box's AfterUpdate event to
"fill in" whatever unbound text boxes you wanted to show team info.

Or, if the first two characters are ALWAYS TeamID, you could have Access do
a DLookup() function using those first two characters.