Help with a where statement

[Guest]

I have one form (Inventory Input) and two tables (Inventorymain and
hpdatadesc).

The form is used to input part numbers, description and qtys into my
Inventorymain table.

I need to be able to input a part number in the part number field of the
Inventory Input form. And then have the Description Field of the Inventory
Input form populated by a description that corresponds with the part number
from the hpdatadesc table. This of course would update the Inventorymain
table. Something like an autofill.

I guess the statement would be something like;

If field inventoryinput (partnumber) afterupdate = hpdatadesc (partnumber)
then inventoryinput (description) = hpdatadesc (description)

Can anyone help me write this code? Or is there something I am missing in
the GUI?

 

[John Vinson]

I have one form (Inventory Input) and two tables (Inventorymain and
hpdatadesc).

The form is used to input part numbers, description and qtys into my
Inventorymain table.

I need to be able to input a part number in the part number field of the
Inventory Input form. And then have the Description Field of the Inventory
Input form populated by a description that corresponds with the part number
from the hpdatadesc table. This of course would update the Inventorymain
table. Something like an autofill.

I guess the statement would be something like;

If field inventoryinput (partnumber) afterupdate = hpdatadesc (partnumber)
then inventoryinput (description) = hpdatadesc (description)

Can anyone help me write this code? Or is there something I am missing in
the GUI?

There may be something missing in the normalization of your tables!

Generally one would NOT want to store the description redundantly in
the inventoryinput table, if it can be looked up using a Query or a
combo box at any time. It should be stored in the hpdatadesc table,
and (unless you have some critical reason to allow a partnumber to
have two different descriptions in the two different tables) it should
ONLY be stored there.

Am I missing something?

John W. Vinson[MVP]

 

[Guest]

Well the issue is that the hpdatadesc is a hp parts list and we do not carry
all the parts on the list. The Inventorymain is our inventory file. We do not
want to mix the tables, however I know that is an option. The idea was to
have the desc part of the form Autofill based on our receiving clerk
inputting a part number in the form using a scanner and then having the
description field fill in automatically from the coorelating Part Number row
in hpdatadesc. Does that make sense?

 

[John Vinson]

Well the issue is that the hpdatadesc is a hp parts list and we do not carry
all the parts on the list. The Inventorymain is our inventory file. We do not
want to mix the tables, however I know that is an option. The idea was to
have the desc part of the form Autofill based on our receiving clerk
inputting a part number in the form using a scanner and then having the
description field fill in automatically from the coorelating Part Number row
in hpdatadesc. Does that make sense?

I'm not suggesting "mixing" the tables. I'm suggesting that you should
NOT mix the tables!

If you base the Form on a query joining the inventory table to the hp
parts list, using a Left Outer Join so you see all records with or
without hp parts data, you can simply include the description field
from the joined hp parts table.

Alternatively, if the Description is a text field (rather than a
memo), you could use a Combo Box in your form based on the hp parts
table and including both the part number and the description. You can
then set the Control Source of a textbox to

=comboboxname.Column(n)

where (n) is the zero based position of the description in the query
(i.e. if the description is the second field in the query use (1)).

John W. Vinson[MVP]