Trouble Linking Information in Tables

[James]

Hi,
I am hopping someone can help me with what I assume is a very simple
problem. I am fairly new to access but have managed to crate a few databases
that work well. I am working on one now and can not get past one problem.

I am working in a parts database and I have a table called products that
list part numbers, part names, part class and, part description. I have
another table that list inventory transactions. In the part transaction table
through a form I have the part number and quantity sold. I would like to have
the part name come along as well. I can not seem to figure this out. What I
want to do is say if I put part number 1 then the name of Part number one
will auto fill where I want. I can get the product ID to show or I get the
good old #NAME? showing up.

Thanks for any help,
James

 

[KARL DEWEY]

Use a query and join both tables on the part number fields.

 

[John W. Vinson]

Hi,
I am hopping someone can help me with what I assume is a very simple
problem. I am fairly new to access but have managed to crate a few databases
that work well. I am working on one now and can not get past one problem.

I am working in a parts database and I have a table called products that
list part numbers, part names, part class and, part description. I have
another table that list inventory transactions. In the part transaction table
through a form I have the part number and quantity sold. I would like to have
the part name come along as well. I can not seem to figure this out. What I
want to do is say if I put part number 1 then the name of Part number one
will auto fill where I want. I can get the product ID to show or I get the
good old #NAME? showing up.

Thanks for any help,
James

Your transaction table should NOT contain a redundant field for the part name.

You can *display* the part name on the Form you use to update the transaction
table; one way is to bind the part number to a Combo Box which displays the
part name, but stores the part number. If you want to see both part number and
part name, you can include both fields in the combo's row source and put a
textbox on the form with a control source

=comboboxname.Column(n)

where n is the zero based position of the part name.

 

[James]

Thank you your answer, I am afraid what you sugest in beyond my skill level
at this time. I assume what I want to do is very easy but I sure can't seem
to do it.

Thank you,
James

 

[James]

Thank you for your reply, I am afraid I can't figure out how to create the
needed query at this time.

Thank you,
James Lasby

 

[John W. Vinson]

Thank you your answer, I am afraid what you sugest in beyond my skill level
at this time. I assume what I want to do is very easy but I sure can't seem
to do it.

Please post the fieldnames and datatypes of your parts table. We'll be glad to
help walk you through the steps, but there's not enough info in your original
post to give specific help.

 

[James]

Thank you,

The Inventory Transactions Table is linked to the Products Table, Both
tables contain and are linked by the ProductID fields. The ProductID field
Data type in the Inventory Transactions Table a Number Type and under lookup
in it properties it is listed as follows
Display Type: Combo box
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW Products,* FROM Products ORDER BY
Products.ProductsName;

I am working with a form whose control source is inventory transaction. It
lists the part number as follows. Part number test box
Control Source is ProductID,
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW Products,* FROM Products ORDER BY
Products.ProductsID;

The text field I want to fill with the part name in the form is called Part
Name. So when I put part number 1001 it will auto fill the part name of Shaft
and so on.

Please let me know what other info you need and thank you very much for your
help.

Thank you,
James

 

[John W. Vinson]

Thank you,

The Inventory Transactions Table is linked to the Products Table, Both
tables contain and are linked by the ProductID fields. The ProductID field
Data type in the Inventory Transactions Table a Number Type and under lookup
in it properties it is listed as follows
Display Type: Combo box
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW Products,* FROM Products ORDER BY
Products.ProductsName;

I am working with a form whose control source is inventory transaction. It
lists the part number as follows. Part number test box
Control Source is ProductID,
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW Products,* FROM Products ORDER BY
Products.ProductsID;

The text field I want to fill with the part name in the form is called Part
Name. So when I put part number 1001 it will auto fill the part name of Shaft
and so on.

Is this a *textbox* or a *combo box* - i.e. does it have a dropdown? I'd guess
it does; if not then I'd strongly suggest using a combo instead.

You can set the COntrol Source of the textbox named [Part name] to

=[Part number test box].Column(1)

assuming that the Name property of the part number control is in fact Part
number test box, and that the *second* column in the Products table is the
product name.

 

[James]

John,
Thank you once again. Still not quite working. Here is some mroe info
that may help. The PartNumber in the Inventory Transactions Table is a
Dropdown combo box. In the Products Table the first colum is the ProductID
(AutoNumber) the second is Part Number (Text) and the third is ProductName
(Text). In the Inventory Transactions Table the first is TransactionID
(AutoNumber), Transaction Date (Date/Time), ProductID (Number)(Row Source
Type: Table/Query)(Row Source: SELECT DISTINCTROW Products,* FROM Products
ORDER BY Products.ProductsName. I wish I could just post the database on
here for you to see. Thanks for your help and time.

Thank you,
James

Thank you,

The Inventory Transactions Table is linked to the Products Table, Both
tables contain and are linked by the ProductID fields. The ProductID field
Data type in the Inventory Transactions Table a Number Type and under lookup
in it properties it is listed as follows
Display Type: Combo box
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW Products,* FROM Products ORDER BY
Products.ProductsName;

I am working with a form whose control source is inventory transaction. It
lists the part number as follows. Part number test box
Control Source is ProductID,
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW Products,* FROM Products ORDER BY
Products.ProductsID;

The text field I want to fill with the part name in the form is called Part
Name. So when I put part number 1001 it will auto fill the part name of Shaft
and so on.

Is this a *textbox* or a *combo box* - i.e. does it have a dropdown? I'd guess
it does; if not then I'd strongly suggest using a combo instead.

You can set the COntrol Source of the textbox named [Part name] to

=[Part number test box].Column(1)

assuming that the Name property of the part number control is in fact Part
number test box, and that the *second* column in the Products table is the
product name.