Looping through continuous forms

[db777]

Hello all,

I've scoured the forum for this, but none of the items seem to be what I'm after (or I can't quite understand them so don't realise they are what I am after!). I'll outline my problem and what I think I need to do.

I have a parent form that contains a subform (displayed as a continuous form). The parent form is unbound, the subform is bound to a table. Users can enter items on the subform (it's a data entry form so no records from the table will be visible) and these appear in the bound table. All ok so far. What I need to do is loop through the continuous form and get the value of the ID field (this is an autonumbered hidden field on the form) so that this can be inserted along with a ProjectID in a junction table (the Project ID is passed into an unbound textbox on the parent form). I know I could get it working if it was just a standard form with one record, but I can't seem to get my head around how I loop through a variable number of records on a continuous form and pick out one value.

I know I can use an INSERT INTO SQL statement to put stuff into the junction table - I just don't know how to loop it through the continuous form to pick up the ID so if someone could show me how to construct the loop I can take it from there.

Any help would be greatly appreciated.

 

[RuralGuy]

I would think you would use the BeforeUpdate event of the SubForm to capture the current record's information as it was creating the new records.

 

[db777]

Thanks RuralGuy. Was coming at it from the wrong angle - the BeforeUpdate suggestion did the trick.

For anyone following along the code below sorted the problem (replace the [text] with your own tables/fields/forms):

'Declare variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Open database connection
Set db = CurrentDb
Set rs = db.OpenRecordset("[Your table here]")
'Turn off warnings to stop user being prompted with confirmation for append
DoCmd.SetWarnings False
'Insert [Field 1] and [Field 2] into junction table
DoCmd.RunSQL "INSERT INTO [Your table here] ([Field 1 Name], [Field 2 Name] VALUES ('" & [Parent form here].[Unbound text box on parent form] & "', '" & Me.[ID field on continuous form] & "')"
'Turn warnings back on
DoCmd.SetWarnings True
'Close database connection
rs.Close
Set rs = Nothing
Set db = Nothing

Thanks again!

 

[RuralGuy]

If you use CurrentDB.Execute then you do not get the warnings. Your code also does not need a recordset so it can be simplified to:

   Dim MySQL As String
   'Insert [Field 1] and [Field 2] into junction table
   MySQL = "INSERT INTO [Your table here] ([Field 1 Name], [Field 2 Name] " & _
           "VALUES ('" & Me.Parent.[Unbound text box on parent form] & _
           "', '" & Me.[ID field on continuous form] & "')"
   CurrentDb.Execute MySQL, dbFailOnError

...of course your procedure will need error handling.

 

[db777]

Thanks again RuralGuy. Amended the code as per your suggestion (error handling to come later - just proof of concept right now ). What I have noticed though is that if I create a new record via the form, then go back and edit the record the BeforeUpdate event triggers again creating duplicate entries in the junction table. How do I stop this happening?

Thanks.

 

[RuralGuy]

Only execute your code when Me.NewRecord = True.

 

[db777]

Thanks RuralGuy. All your help is much appreciated.

 

[RuralGuy]

Glad to help.