What is wrong with my code?

[Noreene Patrick]

This code works fine except it only "rolls over" the first record. I need it to roll over all records that show up in my form. The recordsource for my form is a query that asks for day (Mon, Tue) and then pulls out those records... On clicking the command button, I want it to insert all records that are on my form to the tblcustomerhistory. What else do I need to do?

Private Sub cmdrunqry_Click()
Dim sqlstr As String

sqlstr = "INSERT INTO tblcustomerhistory (txtdeliveryday,txtcustomerid, txtdeliverytime, txtcarrier, txtontime, txtproblem, txtcomments, txtdate)"
sqlstr = sqlstr & "VALUES ('" & Me.txtDeliverydate & "','"
sqlstr = sqlstr & Me.txtCustomerID & "','"
sqlstr = sqlstr & Me.txtDeliveryTime & "','"
sqlstr = sqlstr & Me.txtCarrier & "','"
sqlstr = sqlstr & Me.txtOnTime & "','"
sqlstr = sqlstr & Me.txtProblem & "','"
sqlstr = sqlstr & Me.txtComments & "','"
sqlstr = sqlstr & Me.thisday & "');"
CurrentDb.Execute sqlstr

end sub

Thanks, Noreene

 

[pbaldy]

Try this. You'll have to substitute references to the recordset for your form references. For instance,

Me.txtDeliverydate

would become:

rs!DeliveryDateFieldName

  Dim sqlstr           As String
  Dim rs               As DAO.Recordset

  Set rs = Me.RecordsetClone
  Do While Not rs.EOF

    'YOUR CODE HERE

    rs.MoveNext
  Loop

  Set rs = Nothing

 

[Noreene Patrick]

Private Sub cmdrunqry_Click()

Dim sqlstr As String
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
Do While Not rs.EOF

sqlstr = "INSERT INTO tblcustomerhistory (txtdeliveryday,txtcustomerid, txtdeliverytime, txtcarrier, txtontime, txtproblem, txtcomments, txttoday)"
sqlstr = sqlstr & "VALUES ('" & rs!Deliverydatefieldname & "','"
sqlstr = sqlstr & rs!CustomerIDfieldname & "','"
sqlstr = sqlstr & rs!DeliveryTimefieldname & "','"
sqlstr = sqlstr & rs!Carrierfieldname & "','"
sqlstr = sqlstr & rs!OnTimefieldname & "','"
sqlstr = sqlstr & rs!Problemfieldname & "','"
sqlstr = sqlstr & rs!Commentsfieldname & "','"
sqlstr = sqlstr & rs!thisdayfieldname & "');"
CurrentDb.Execute sqlstr

rs.MoveNext
Loop
Set rs = Nothing

End Sub

I copied my code exactly so that you could see if something I typed was not right...When I try to run the code it gives me a compile error and highlights the Dim rs as DAO.Recordset

Thanks, Noreene

 

[Rich]

Why not just use an Append Query?

 

[pbaldy]

Make sure the the Microsoft DAO X.X Object Library is checked in Tools/References. In A2k, X.X would be 3.6

 

[Noreene Patrick]

Rich,

I did start out with an append query suggested by SJ McAbney...and it does work...

however, by using the dayofweek query as my controlsource on the form(Deliverydate criteria in the query is [Enter day of week]) it would ask me for dayofweek again when I tried to run the append query and close form.

I was trying to keep users from typing anything unnecessary..Is there a simpler way around this?

Thanks, Noreene

 

[Noreene Patrick]

pbaldy.

The Microsoft Object DAO library has been checked now...and it goes past that line without stopping,

But, now it stops on first line that has VALUES and says "rs!deliverydatefieldname = <Item not found in this collection>"

Thanks, Noreene

 

[Rich]

Use a form to enter the date, toggle its visible property if you like, just keep it open whilst running the queries

 

[pbaldy]

The bit after rs! must be the field name as it appears in the table or query that is the source of the form. So if the field in the query is DeliveryDate, you'd have:

rs!DeliveryDate

 

[Noreene Patrick]

Rich,

I tried your suggestion and it works great....there are a few quirks to work out, but it does what I need. Thanks so MUCH!!!

And thanks to you also, pbaldy, I appreciate all your time and thought...

Thanks, Noreene