How to loop through a table, check for values, and insert if they don't exist?

[beacon]

Hi everybody,

[Access 2003]

I have three tables: tblForms, tblFormDeficiency, tblDeficiencyType. The tblForms and tblDeficiencyType have a many-to-many relationship with each other with tblFormDeficiency acting as the JOIN table.

Over the past couple of years, I haven't had to update the forms or the deficiency types that often and I've always used INSERT INTO when a user selects a form or deficiency type on a form. This allowed me to delete the JOIN records for the forms/deficiencies when it was decided that a form/deficiency would no longer be needed.

However, yesterday I learned that I will need to make mass changes to the tblFormDeficiency and determined that the best way for me to do this would be to make sure there was a deficiency type linked to every form and use a yes/no field to determine whether or not it would be available to the user. This may not be the best route, but I want to try this out in expectation of another component that I will be working on in the near future that will allow others to update the forms and deficiencies through a form.

So, my question is how do I loop through all the records in tblFormDeficiency, compare the FormIDFK (foreign key for tblForms) and the DeficiencyTypeIDFK (foreign key for tblDeficiencyType) for each record to a counter for both the FormID (primary key for tblForms) and DeficiencyTypeID (primary key for tblDeficiencyType), and insert a record if that particular form/deficiency combo doesn't exist in tblFormDeficiency?

There's one other slight issue to consider. The FormID in tblForms is easy...it's counter will be 1 to 96. However, the counter for DeficiencyTypeID will be 1 to 67, skip 68 (at some point this record was deleted), then continue from 69 through 84.

I'm not very good with DAO or ADO, but would prefer a solution that uses DAO, if possible.

If I've left anything out in my explanation, please let me know.

Thanks,
beacon

[TheSmileyCoder]

This is code written as is, not tested. If this doesn't work for you, and you need more help, it would be nice if could make a blank database, copy the 3 tables into it, and pm me, or attach it.

Anyway here goes (Please backup, before running code :O):

Dim rsForm as Dao.Recordset
Dim rsDef as Dao.Recordset
Dim rsJoin As Dao.Recordset
dim strFind as string
 
set rsForm=CurrentDB.Openrecordset("tblForms",dbOpenDynaset)
set rsDef=CurrentDB.Openrecordset("tblFormDeficiency",dbOpenDynaset)
set rsJoin=CurrentDB.Openrecordset("tblDeficiencyType",dbOpenDynaset)
 
 
'Loop through Forms
Do While Not rsForm.Eof
  'Reset rsDef back to start
    rsDef.MoveFirst
 
  'Loop Through defs
    Do While Not rsDef.Eof
      'Check whether combination exists
        strFind="(FormIDFK=" & rsForm!FormID & ") AND (DeficiencyTypeIDFK=" & rsDef!DeficiencyTypeID & ")" )
        rsJoin.FindFirst strFind
        If rsJoin.NoMatch then
          'Combo not found, add it
          rsJoin.AddNew
            rsJoin!DeficiencyTypeIDFK=rsDef!DeficiencyTypeID
            rsJoin!FormIDFK=rsForm!FormID 
          rsJoin.Update
        End If
      rsDef.Movenext
    Loop
 
 
 
  rsForm.MoveNext
loop
 
'Cleanup
set rsForm=Nothing
set rsDef=Nothing
set rsJoin=Nothing

[beacon]

That was perfect Smiley...you have no idea how much that helps.

Thanks again,
beacon

[TheSmileyCoder]

Im glad that it worked for you. :)

[NeoPa]

For this I would suggest an APPEND query in SQL.

If the data is available from [tblForms] and [tblDeficiencyType] then create a subquery with a cartesian product (Both tables in the FROM clause with no JOIN specified) and append the result-set into [tblFormDeficiency]. If you're worried about records failing (as would happen for pre-existing combinations) then simply link that to the existing table and filter for combinations that don't already exist.