Insert (1 Viewer)

[mike60smart]

Hi Everyone

I have the following on the On Click Event of a Command Button named cmdIn:-

[B]Private Sub cmdIn[/B]_Click()

    On Error GoTo cmdIn_Click_Error

Dim rsTest As dao.Recordset

Set rsTest = Me.RecordsetClone
rsTest.Filter = "[Updated] = -1"

Call Test(rsTest)
Set rsTest = Nothing

    On Error GoTo 0
    Exit Sub

cmdIn_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdIn_Click of Sub Form_frmTwo"

End Sub

Which when clicked runs the Sub Test procedure below.

It should only insert those records which have the Checkbox named "Updated" set to -1 (True)

In my testing I only selected 2 records but when it runs it inserts all 14 Records available.

Any help appreciated.

Code is as follows:-

Sub Test(rs As dao.Recordset)


    On Error GoTo Test_Error

Dim dbsMydbs As dao.Database
Dim rstMyTable As dao.Recordset

Set dbsMydbs = CurrentDb
Set rstMyTable = dbsMydbs.OpenRecordset("tblOrganisationOpenCourseBooking")

If rs.EOF Then
    MsgBox "No recs"
Else
With rs
    .MoveLast
   .MoveFirst
    MsgBox .RecordCount
Do Until .EOF
rstMyTable.AddNew
rstMyTable!ShortCourseBookingID = Me.txtShortCourseBookingID
rstMyTable!ContactFirstName = Me.ContactFirstName
rstMyTable!ContactSurname = Me.ContactSurname
rstMyTable!ContactEMail = Me.ContactEMail
rstMyTable!ContactPhoneNumber = Me.ContactPhone
rstMyTable!OrganisationNameID = Me.txtOrg
rstMyTable!Address = Me.txtOrgAddress
rstMyTable!BillingAddress = Me.txtBillingAddress
rstMyTable!NrPlacesBooked = Me.NrofParticipants
rstMyTable!PONumber = Me.PONumber
rstMyTable!Comments = Me.AdditionalComments
rstMyTable.Update
   .MoveNext
Loop

End With
End If

MsgBox "Participants have been added.", vbInformation, "Complete"

    On Error GoTo 0
    Exit Sub

Test_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Test of Sub Form_frmTwo"

End Sub

 

[static]

Filter applies to a recordset opened from that recordset

Private Sub cmdIn_Click()
On Error GoTo cmdIn_Click_Error
    Me.RecordsetClone.Filter = "[Updated] = -1"
    Test Me.RecordsetClone.openrecordset
Exit Sub
cmdIn_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdIn_Click of Sub Form_frmTwo"
End Sub

I haven't read your other code but I'm guessing a simple insert query would be a better idea.

 

[mike60smart]

Hi Static

That works just great

I also had to insert an If Me.Dirty = False to get it working

Many thanks again