Loop through and Update (INSERT INTO)

[Wayne-I-M]

Hi

I have a filtered continous form and I am trying to (loop through and)
update each record on the form with a time value
The form control is txtFlightDate
The table name is tblflights
The table field is FlightDate

I have cut and pasted these names from the table and the form into the code
so they are correct but I am getting

Error 3265
Item not found in this collection

Private Sub cmdUpdate_Click()
Set RS = Me.RecordsetClone
If Not RS.EOF Then RS.MoveFirst
While Not RS.EOF
RS.Edit
RS!CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"
RS.Update
RS.MoveNext
Wend
End Sub


Any ideas on this would be really helpful

Thanks

 

[Peter Hibbs]

Wayne,

Try this :-

RS!FlightDate = Me.txtFlightDate

instead of this :-

RS!CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"

HTH

Peter Hibbs.

 

[Wayne-I-M]

That did the trick

Thank you

 

[Wayne-I-M]

Sorry - spoke too soon

That will overwrite the existing data

I am try to append

So you may have a group going on a flight on 25/12/2010
They may also be going on a flight (returning) 01/01/2011

So I think I do need the Insert into statment (I think ?)

 

[kc-mass]

Change your rs.edit to rs.addnew
rs.edit lets you change the values in an existing record. rs.addnew inserts
a new record into the table

Regards

Kevin

 

[Wayne-I-M]

Thank for the tip

Am still getting the same error (I "have" check the names and they are
correct)

Private Sub cmdUpdate_Click()
Set rs = Me.RecordsetClone
If Not rs.EOF Then rs.MoveFirst
While Not rs.EOF
rs.AddNew
rs!CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"
rs.Update
rs.MoveNext
Wend
End Sub


This is the line that being flagged
rs!CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"


Driving me mad - this "should" work

I could use a zillion make (temp) tables and append from that but am just
trying to clean things up and this is not going well

The basics

I have a list of flights that people have booked
I noticed that most people within a group are on the same flight some I am
just trying to have an number of unbound text boxes that the leader can input
the flight details and then update that group's flight details.

I can't use this normalised as some peoiple within the group may have
slightly different details - ie they may fly from Manchester to join the
london flight etc.

So - they have a choice of imputting each person's flight details or adding
the details to the unbound boxes and then clicking update button to append
these details each group member's arrangments.

Sounds simple - it "should" bge --- didn't think there would be a problem
with this but.....; - )

--
Wayne
Manchester, England.

Change your rs.edit to rs.addnew
rs.edit lets you change the values in an existing record. rs.addnew inserts
a new record into the table

Regards

Kevin

 

[Jon Lewis]

Isn't rs!CurrentDb.Execute the problem? !CurrentDb indicate a field in the
rs recordset called CurrentDb.
Try:
CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"
or what is wrong with using
rs.AddNew
rs!FlightDate = " & Me.txtFlightDate.Value
and so on

HTH

Jon

Thank for the tip

Am still getting the same error (I "have" check the names and they are
correct)

Private Sub cmdUpdate_Click()
Set rs = Me.RecordsetClone
If Not rs.EOF Then rs.MoveFirst
While Not rs.EOF
rs.AddNew
rs!CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"
rs.Update
rs.MoveNext
Wend
End Sub


This is the line that being flagged
rs!CurrentDb.Execute "INSERT INTO tblflights (FlightDate) VALUES (" &
Me.txtFlightDate.Value & " )"


Driving me mad - this "should" work

I could use a zillion make (temp) tables and append from that but am just
trying to clean things up and this is not going well

The basics

I have a list of flights that people have booked
I noticed that most people within a group are on the same flight some I am
just trying to have an number of unbound text boxes that the leader can
input
the flight details and then update that group's flight details.

I can't use this normalised as some peoiple within the group may have
slightly different details - ie they may fly from Manchester to join the
london flight etc.

So - they have a choice of imputting each person's flight details or
adding
the details to the unbound boxes and then clicking update button to append
these details each group member's arrangments.

Sounds simple - it "should" bge --- didn't think there would be a problem
with this but.....; - )