Loop (ADODB) from continous form

[Wayne-I-M]

OK - try this (different tack)

Would it be possible to loop something like this through each record on a
filtered (continous) form and append a new record in the table

Dim wayne1test As ADODB.Connection
Set wayne1test = CurrentProject.Connection
wayne1test.Execute "INSERT INTO tblflights (bookingref,flightdate) VALUEs ("
& Me.txttempBookingRef.Value & "," & Me.txtFlightDate.Value & ")"

Basically trying to (still) append a new record to the table for each record
in the form

Any help would be great

Thanks

 

[Peter Hibbs]

Wayne,

Your post is not making a lot of sense. If I understand you correctly,
you want to create a new record for each record that is currently
being displayed on a Continuous form (normalisation issue a bit
suspect here but you seem to be happy with that).

Can I assume that you want the BookingRef and FlightDate field data
copied into the new record from the old record. If so the code below
should work, this is assuming that BookingRef is a Number field and
FlightDate is a Date/Time field. If txtTempBookingRef and
txtFlightDate are unbound Text controls on the form and you use those
as the source data for each new record you will get the same Ref and
Date in every new record which doesn't sound right to me.

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
Do Until rs.EOF
CurrentDb.Execute "INSERT INTO tblflights " _
& "(BookingRef, FlightDate) VALUES (" _
& rs!BookingRef & ", " _
& "#" & Format(rs!FlightDate, "yyyy/m/d") & "#)"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.Requery

HTH

Peter Hibbs.

 

[Tom van Stiphout]

On Fri, 7 Aug 2009 04:59:01 -0700, Wayne-I-M

Yes, but I would add the dbFailOnError argument to the Execute method.
Then at least you are notified if something goes wrong.
Why not use an Append query and DAO?

-Tom.
Microsoft Access MVP