Update a second table with records if they don't already exist

[jbrowne]

Hi,

I am using access 2007. I have a front end form in access that runs a query to give me a list of information when the form opens. I can input into the form and its supposed to save my input on closing the form. My problem is that a secondary table exists with a link field to the main table. If the records exist on the secondary table then the imputed data will be saved. But I have a situation that the secondary table does not always have a record to match the main table and then the data won't save as it can't find a record to update (it throws an invalid key error).

How do I check when opening the form to see if all records have a matching record on the second table - and if not then insert them at this stage so that my input from the form works on closing.

I'm new to all this - VB6 programmer by trade, so any help is appreciated. Is there a way to do this using the VB add on for access or is there a way to construct an sql statement (I've a pretty basic knowledge of sql so be gentle please) to loop through all the records and insert as needed in the second table?

Thanks in advance
John B

 

[Andrzejek]

Do the 2 tables have the same structure (the same number of fields in the same order)?

You can get the records from TableA (Some Primary Key ID field):
[tt][blue]
Select SomePKID From TableA[/blue][/tt]

Then you can check if there are records 'missing' in another TableB:

[tt]Select SomeOtherPKID From TableB
Where SomeOtherPKID NOT IN ([blue]Select SomePKID From TableA[/blue])[/tt]



Have fun.

---- Andy

 

[jbrowne]

No they are not the same structure at all. The reason for the second table was that the information was requiring too many fields for just one table. Therefore a second table was built with a field linking back to unique number field in the first table - one to one relationship. But there are cases where there is only a record on the first table and in these cases the input screen throws an error if I try to update any field on the second table. So my idea would be to insert any missing records in the second table every time this form is opened before any update is done.

 

[Andrzejek]

Did you try my approach?
DId you get the list of PKs of the 'missing' records?

That's just the 'stepping stone' to the INSERT statement
Something like:
[tt]
INSERT INTO TableA (SomeField1, Field2, ...)
VALUES(Select SomeField1, Field2, ... FROM TableB
Where TableB.PKField NOT IN (Select PKField From TableA))[/tt]

Have fun.

---- Andy