Autopopution of a field from another table based on another field criteria

[tonycl69]

I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number. Thanks in advance for your help.
Hopefully this will make more sense. This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.

If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then
[tblinvoices]![UnitID] = [tblinvoices]![UnitID]
End If

 

[Pat Hartman]

There seems to be something wrong with your schema. Can you post it please. When you have a hierarchy you would enter data in the top table first and then in the child table. Or you would search for a record in the top table and on finding it enter data in the child table. You don't start with the child table and look "up".

 

[tonycl69]

Hi Pat thanks for your insight and youre right I have had a rethink and created as combo box for the unit from the main table then inserts the unitid into the invoice table based on the choice this works great. However, if an item doesn't exist in the main table how do I redirect the user to enter the new information and there create a new entry in the main table and enter the unitid into the invoice table.

 

[tonycl69]

Hi and thanks for all your help but found a cracking article in MSDN Microsoft library did the trick exactly what I wanted. It takes the not in list item typed opens up a new form to enter the item, creates a new entry into the main table and then returns to the opening form so any other details can be added.