Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sql inserts duplicate record 1

Status
Not open for further replies.
THWatson

THWatson

Technical User
Apr 25, 2000
2,601
CA
Using Access 2000

The form is called frmMakeupMeetings. It's a data entry form to record member attendance at makeup meetings (meetings separate from Regular meetings).

The form is opened from another form, frmAttendance, by a command button. When the command button on frmAttedance is clicked, it inserts the date in a text box, txtMeetingDate, on frmMakeupMeetings.

Also on the form are 2 list boxes. One is lstMembers from which the user selects 1 member. The second is lstMakeupType from which the user selects 1 makeup type.

Then a command button is pressed to Post the selection.

Following is the code behind the Post button, cmdPost
Code: 
Private Sub cmdPost_Click()
On Error GoTo Err_cmdPost_Click

Dim sql As String

If IsNull(Me.txtMeetingDate) Then
    MsgBox "Record cannot be saved without a Makeup Date!" _
    & vbCrLf & "Returning to Attendance form.", vbExclamation, "Makeup Date required."
    Me.Undo
    Exit Sub
    Call cmdClose_Click
    End If

If IsNull(Me.lstMemberID) Then
MsgBox "Please select Member from list.", vbExclamation, "Member needed"
Me.lstMemberID.SetFocus
Exit Sub
    
    ElseIf IsNull(Me.lstMakeupType) Then
    MsgBox "Please select Makeup Meeting Type from list.", vbExclamation, "Makeup Meeting Type needed"
    Me.lstMakeupType.SetFocus
    Exit Sub
End If

    sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, TypeOfMeeting,MakeupType) VALUES(Forms!frmMakeUpMeetings!lstMemberID , #" & Me.txtMeetingDate & "#, True,""Makeup Meeting"",Forms!frmMakeUpMeetings!lstMakeupType)"
    DoCmd.RunSQL sql
    
Dim Response As Variant
Dim Response2 As Variant
Dim MyDate As Date
    
    Response = MsgBox("Do you wish to Post another Makeup Meeting?", vbYesNo, "Posting check")
    If Response = vbYes Then
        Response2 = MsgBox("For the SAME Date or a DIFFERENT Date?" _
        & vbCrLf & vbCrLf & "If SAME Date select <Yes>, if DIFFERENT Date select <No>.", vbYesNo, "Same or Different Date check")
            If Response2 = vbYes Then
            MyDate = Me.txtMeetingDate
            Me.txtMeetingDate = MyDate
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.lstMemberID.SetFocus
        Else
                    
        MsgBox "      Please enter desired date..." _
        & vbCrLf & "Then select Member and Makeup Meeting Type.", vbInformation, "Entries needed"
            Me.txtMeetingDate.Enabled = True
            Me.txtMeetingDate = Null
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.txtMeetingDate.SetFocus
        End If
    Else
    DoCmd.Close
    End If
    

Exit_cmdPost_Click:
    Exit Sub

Err_cmdPost_Click:
    MsgBox Err.Description
    Resume Exit_cmdPost_Click
    
End Sub

This inserts the MemberID from lstMemberID, the Makeup Type from lstMakeupType, "Makeup Meeting" (to differentiate between a Regular meeting, as regular meetings are kept in the same table but entered from frmAttendance), and True (to check a Present Yes/No box as true.

What happens is that 2 entries are made in tblAttendance when the Post button is pushed. The first entry is accurate. The second entry has the same member number, the same Makeup Type, but the Present Yes/No is not checked and the "Makeup Meeting" is not entered in the Meeting Type in tblAttendance.

Can anybody spot anything in the code that is causing the double entry?

Thanks.

Tom
 
Sort by date Sort by votes
Your "DoCmd.RunSQL sql" should be moved to a place where your cluse meets true. In this it will run
Code: 
Private Sub cmdPost_Click()
    On Error GoTo Err_cmdPost_Click

    Dim sql As String

    If IsNull(Me.txtMeetingDate) Then
        MsgBox "Record cannot be saved without a Makeup Date!" _
             & vbCrLf & "Returning to Attendance form.", vbExclamation, "Makeup Date required."
        Me.Undo
        Exit Sub
        Call cmdClose_Click
    End If

    If IsNull(Me.lstMemberID) Then
        MsgBox "Please select Member from list.", vbExclamation, "Member needed"
        Me.lstMemberID.SetFocus
        Exit Sub

    ElseIf IsNull(Me.lstMakeupType) Then
        MsgBox "Please select Makeup Meeting Type from list.", vbExclamation, "Makeup Meeting Type needed"
        Me.lstMakeupType.SetFocus
        Exit Sub
    End If

  [b]  sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, TypeOfMeeting,MakeupType) VALUES(Forms!frmMakeUpMeetings!lstMemberID , #" & Me.txtMeetingDate & "#, True,""Makeup Meeting"",Forms!frmMakeUpMeetings!lstMakeupType)"
    DoCmd.RunSQL sql[/b]

    Dim Response As Variant
    Dim Response2 As Variant
    Dim MyDate As Date

    Response = MsgBox("Do you wish to Post another Makeup Meeting?", vbYesNo, "Posting check")
    If Response = vbYes Then
        Response2 = MsgBox("For the SAME Date or a DIFFERENT Date?" _
                         & vbCrLf & vbCrLf & "If SAME Date select <Yes>, if DIFFERENT Date select <No>.", vbYesNo, "Same or Different Date check")
        If Response2 = vbYes Then
            MyDate = Me.txtMeetingDate
            Me.txtMeetingDate = MyDate
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.lstMemberID.SetFocus
        Else

            MsgBox "      Please enter desired date..." _
                 & vbCrLf & "Then select Member and Makeup Meeting Type.", vbInformation, "Entries needed"
            Me.txtMeetingDate.Enabled = True
            Me.txtMeetingDate = Null
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.txtMeetingDate.SetFocus
        End If
    Else
        DoCmd.Close
    End If


Exit_cmdPost_Click:
    Exit Sub

Err_cmdPost_Click:
    MsgBox Err.Description
    Resume Exit_cmdPost_Click

End Sub

Code: 
Private Sub cmdPost_Click()
    On Error GoTo Err_cmdPost_Click

    Dim sql As String
  [COLOR=red]  sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, TypeOfMeeting,MakeupType) VALUES(Forms!frmMakeUpMeetings!lstMemberID , #" & Me.txtMeetingDate & "#, True,""Makeup Meeting"",Forms!frmMakeUpMeetings!lstMakeupType)"[/color]

    If IsNull(Me.txtMeetingDate) Then
        MsgBox "Record cannot be saved without a Makeup Date!" _
             & vbCrLf & "Returning to Attendance form.", vbExclamation, "Makeup Date required."
        Me.Undo
        Exit Sub
        Call cmdClose_Click
    End If

    If IsNull(Me.lstMemberID) Then
        MsgBox "Please select Member from list.", vbExclamation, "Member needed"
        Me.lstMemberID.SetFocus
        Exit Sub

    ElseIf IsNull(Me.lstMakeupType) Then
        MsgBox "Please select Makeup Meeting Type from list.", vbExclamation, "Makeup Meeting Type needed"
        Me.lstMakeupType.SetFocus
        Exit Sub
    Else
      [COLOR=red]  DoCmd.RunSQL sql[/color]
    End If



    Dim Response As Variant
    Dim Response2 As Variant
    Dim MyDate As Date

    Response = MsgBox("Do you wish to Post another Makeup Meeting?", vbYesNo, "Posting check")
    If Response = vbYes Then
        Response2 = MsgBox("For the SAME Date or a DIFFERENT Date?" _
                         & vbCrLf & vbCrLf & "If SAME Date select <Yes>, if DIFFERENT Date select <No>.", vbYesNo, "Same or Different Date check")
        If Response2 = vbYes Then
            MyDate = Me.txtMeetingDate
            Me.txtMeetingDate = MyDate
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.lstMemberID.SetFocus
        Else

            MsgBox "      Please enter desired date..." _
                 & vbCrLf & "Then select Member and Makeup Meeting Type.", vbInformation, "Entries needed"
            Me.txtMeetingDate.Enabled = True
            Me.txtMeetingDate = Null
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.txtMeetingDate.SetFocus
        End If
    Else
        DoCmd.Close
    End If


Exit_cmdPost_Click:
    Exit Sub

Err_cmdPost_Click:
    MsgBox Err.Description
    Resume Exit_cmdPost_Click

End Sub


________________________________________________________
Zameer Abdulla
Help to find Missing people
 
Upvote 0 Downvote
Zameer
Oops. This now posts 3 records.

What happens is this
1. It gives the standard warning message "You are about to append one record. Do you wish to continue?"
2. Two records get posted in tblAttendance - one with correct data, one with missing data.
3. Then it asks the warning message again, and if you reply Yes it posts one more.

Tom
 
Upvote 0 Downvote
I see something strange.

I commented out the DoCmd [DoCmd.runSQL sql[/b] line and ran the Post.

The record with missing data is entered.

Why would the code run with the DoCmd line commented out?

Tom
 
Upvote 0 Downvote
For the last question, your form is bound to the same table you update.

When you run the SQL, you insert all information, then clear the controls (except for the listboxes, see below), and when the form closes, that "partial record" is also saved, as that is standard Access behaviour.

There's an exception for the listboxes, where setting them to Null does not remove previous selection. You could alter their row source, though

me!TheList.RowSource = me!TheList.RowSource ' unselect

Now - you could unbind the form, and probably have the behaviour you wish.

Roy-Vidar
 
Upvote 0 Downvote
Roy
Yes, you're right. The frmMakeupMeetings is bound to tblAttendance.

What happens is that when I Unbind and then open it from frmAttendance, I get the message "You can't assign a value to this object."

frmAttendance places a selected Date value in the text box, txtMeetingDate on frmMakeupMeetings.

Tom
 
Upvote 0 Downvote
And when I unbind the form, the list boxes aren't clickable.

Tom
 
Upvote 0 Downvote
You must also unbind each control - i e - remove their controlsource.

Note - especially if you need to enter dates manually, you should probably ensure correct date formatting is applied to the control, likewise with numeric fields that's supposed to be entered manually through the interface. For a checkbox, set a default value (either true of false).

Roy-Vidar
 
Upvote 0 Downvote
I unbound the form and all of its controls.

I left the code exactly as I had it before I originally made this post.

Now, things seem to be working properly. I'll let you know after further testings.

Thanks.

Tom
 
Upvote 0 Downvote
I didn't even think of that. Good point Roy.

________________________________________________________
Zameer Abdulla
Help to find Missing people
 
Upvote 0 Downvote
Yep. Seems to be working just fine.

Thanks.

Tom
 
Upvote 0 Downvote
By the way...
Me.lstMemberID.RowSource = Me.lstMemberID.RowSource does not work

but
Me.lstMemberID = Null does work

Tom
 
Upvote 0 Downvote
Yeah, was mixing up multiselect none vs simple/extended [blush]

Roy-Vidar
 
Upvote 0 Downvote
Roy
Thanks for the clarification.

In addition to Me.lstMemberID = Null, I tried "Me.lstMemberID = Me.lstMemberID as sometimes that requeries the control better.

But that didn't work either.

Thanks again for your thoughtful help.

Tom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
522
Andrzejek
Andrzejek
TrekBiker
  • Locked
  • Question
Duplicate Customers 1
Replies
6
Views
362
Andrzejek
Andrzejek
priac
  • Locked
  • Question
Create a Duplicate of an Existing Record in Microsoft Access but leave some fields blank.
Replies
7
Views
1K
Andrzejek
Andrzejek
applestoapples
  • Locked
  • Question
Create duplicate rows based on Qty field
Replies
7
Views
238
Andrzejek
Andrzejek
DB_Bowes
  • Locked
  • Question
Selection from Listbox to GoTo Record in Bound form Gives Error 2105
Replies
1
Views
223
DB_Bowes
DB_Bowes

Part and Inventory Search

Sponsor

Back
Top