By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,548 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

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

beacon
100+
P: 579
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
Nov 16 '11 #1

✓ answered by 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):
Expand|Select|Wrap|Line Numbers
  1. Dim rsForm as Dao.Recordset
  2. Dim rsDef as Dao.Recordset
  3. Dim rsJoin As Dao.Recordset
  4. dim strFind as string
  5.  
  6. set rsForm=CurrentDB.Openrecordset("tblForms",dbOpenDynaset)
  7. set rsDef=CurrentDB.Openrecordset("tblFormDeficiency",dbOpenDynaset)
  8. set rsJoin=CurrentDB.Openrecordset("tblDeficiencyType",dbOpenDynaset)
  9.  
  10.  
  11. 'Loop through Forms
  12. Do While Not rsForm.Eof
  13.   'Reset rsDef back to start
  14.     rsDef.MoveFirst
  15.  
  16.   'Loop Through defs
  17.     Do While Not rsDef.Eof
  18.       'Check whether combination exists
  19.         strFind="(FormIDFK=" & rsForm!FormID & ") AND (DeficiencyTypeIDFK=" & rsDef!DeficiencyTypeID & ")" )
  20.         rsJoin.FindFirst strFind
  21.         If rsJoin.NoMatch then
  22.           'Combo not found, add it
  23.           rsJoin.AddNew
  24.             rsJoin!DeficiencyTypeIDFK=rsDef!DeficiencyTypeID
  25.             rsJoin!FormIDFK=rsForm!FormID 
  26.           rsJoin.Update
  27.         End If
  28.       rsDef.Movenext
  29.     Loop
  30.  
  31.  
  32.  
  33.   rsForm.MoveNext
  34. loop
  35.  
  36. 'Cleanup
  37. set rsForm=Nothing
  38. set rsDef=Nothing
  39. set rsJoin=Nothing

Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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):
Expand|Select|Wrap|Line Numbers
  1. Dim rsForm as Dao.Recordset
  2. Dim rsDef as Dao.Recordset
  3. Dim rsJoin As Dao.Recordset
  4. dim strFind as string
  5.  
  6. set rsForm=CurrentDB.Openrecordset("tblForms",dbOpenDynaset)
  7. set rsDef=CurrentDB.Openrecordset("tblFormDeficiency",dbOpenDynaset)
  8. set rsJoin=CurrentDB.Openrecordset("tblDeficiencyType",dbOpenDynaset)
  9.  
  10.  
  11. 'Loop through Forms
  12. Do While Not rsForm.Eof
  13.   'Reset rsDef back to start
  14.     rsDef.MoveFirst
  15.  
  16.   'Loop Through defs
  17.     Do While Not rsDef.Eof
  18.       'Check whether combination exists
  19.         strFind="(FormIDFK=" & rsForm!FormID & ") AND (DeficiencyTypeIDFK=" & rsDef!DeficiencyTypeID & ")" )
  20.         rsJoin.FindFirst strFind
  21.         If rsJoin.NoMatch then
  22.           'Combo not found, add it
  23.           rsJoin.AddNew
  24.             rsJoin!DeficiencyTypeIDFK=rsDef!DeficiencyTypeID
  25.             rsJoin!FormIDFK=rsForm!FormID 
  26.           rsJoin.Update
  27.         End If
  28.       rsDef.Movenext
  29.     Loop
  30.  
  31.  
  32.  
  33.   rsForm.MoveNext
  34. loop
  35.  
  36. 'Cleanup
  37. set rsForm=Nothing
  38. set rsDef=Nothing
  39. set rsJoin=Nothing
Nov 16 '11 #2

beacon
100+
P: 579
That was perfect Smiley...you have no idea how much that helps.

Thanks again,
beacon
Nov 16 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im glad that it worked for you. :)
Nov 16 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 17 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.