Get Value based on another from Same Table

[Ode]

Hello all,

I'm trying to make this work, but dunno how.

I have a tblProducts table that consists of:

ID_Products (autonumber)
Product(text)
PartNumber1 (number)
Partnumber2(number)

I have an input form where i have a combobox thats related to that tblProducts that lists a series of products.
The field next to it deliveres the ID_Number associated to that Product

Now what i'm trying to do is retrieve (automatically) the products PartNumber1 and PartNumber2 based on the ID_Products primary key.

Is this possible?

Thanks in advance for your help.

Regards,
Ode.

 

[Smart]

Create a query that uses the Id number (from the combo box) to select partnumber1 and partnumber2.
Two fields on the form to hold the partnumber1 and partnumber2 values
On the after update of the combo box do a dlookup on the newly created query for partnumber1 and again for partnumber2
IE
Txtpartnumber1 = (the name of the field on the form)

TxtPartnumber1 = "partnumber1","your new query"
TxtPartnumber2 = "partnumber2","your new query"

 

[neileg]

Bad design alert. By having these two fields you have created a repeating group. In reality, you might have any number of related components. It would be better to have a parts table that holds your parts details and a junction table to manage the many to many relationship. Search in these forums for junction or many to many for advice on this subject.

Is it possible that products can also be parts? If so, they should all be in the same table then the junction table will link the table back to itself.