Help working with recordsets and append queries

[Joe Miller]

I'm relatively new to VBA coding and am wondering if someone can help me with
the methodology required in the following problem. I want to automate the
AfterUpdate event of a control on a form to add a number of records to a
table.

I have a form with a control to allow the user to enter a date. Once that
date is entered, I want VBA to automatically add one record to Table2 for
each of the records in Table1 (with whom it shares a one to many
relationship) with the date in the control being automatically entered into a
field for each of the newly created records.

To make matters more complicated, I then want to repeat this process for the
four consecutive dates after the date entered by the user, without requiring
the user to enter those dates.

i appreciate I am being vague, but if anyone can help me with the structure
my code should take I'd be most appreciative!

Thanks
Joe

 

[ErezM via AccessMonster.com]

hi
paste that code in the afterupdate event

Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim i As Integer

rs1.Open "Table1", CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
rs2.Open "Table2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rs1.EOF
For i = 0 To 4
rs2.AddNew
rs2!field1 = Something (or maybe rs1!field1)
rs2!Field2 = SomethingElse (or maybe rs1!field2)
rs2!Field3 = DateAdd("d", i, TheDateTextboxOnTheForm)
rs2.Update
Next i
rs1.MoveNext
Wend
rs2.Close
rs1.Close
Set rs2 = Nothing
Set rs1 = Nothing

you might need to add a reference to Microsoft activeX data object (ADO) of
some version (2-2.6 depending on the jet version you have) if you dont have
one

i beleive it's better to do this kind of automation after a button click
(afterupdate can happen by mistake and will lead to unwanted records being
added)

and, pay attention that the code above adds 5 new records to table2 for each
record in table1 - hope that's what you wanted

goof luck
Erez