query Def Add New Adding Two Records

[MarkA70]

Below in blue is the code I am running:

Private Sub AddtoEvents()
Dim db As DAO.Database
Dim qd_AttendanceUpdate As DAO.QueryDef
Dim prm As DAO.Parameter
Dim AttendanceUpdate As DAO.Recordset


Me.Event_ID = Forms!frm_hidden.txt2_Hidden

Set db = CurrentDb

Set qd_AttendanceUpdate = CurrentDb.QueryDefs("qry_Attendance_0")
For Each prm In qd_AttendanceUpdate.Parameters
prm.Value = Forms!frm_hidden.txt2_Hidden
Next prm

Set AttendanceUpdate = qd_AttendanceUpdate.OpenRecordset


With AttendanceUpdate
.AddNew
!Member_ID = Forms!frm_hidden.txt0_Hidden
!Event_ID = Forms!frm_hidden.txt2_Hidden
.Update
End With


AttendanceUpdate.Close
Set AttendanceUpdate = Nothing
qd_AttendanceUpdate.Close
Set qd_AttendanceUpdate = Nothing
Set prm = Nothing
db.Close
Set db = Nothing

End Sub

When run it adds two, 2, records to the recordset, the next time it is run, one of the those records is overwritten, but the new record is added twice, and so on.

What to do????

 

[sneuberg]

Would you post the SQL of the "qry_Attendance_0" query. A screen shot of it in the query builder would be nice too.

 

[MarkA70]

Sure, here goes:

SELECT Attendance.Attendance_ID, Attendance.Member_ID, Members.Saluation, Attendance.Event_ID, Attendance.Event_Entry_Sequence_No, Aggie_Events.Event_Date, Aggie_Events.Event_Description, Attendance.Adult_Tickets, Attendance.Child_Tickets, Attendance.Guest_Tickets, Attendance.Family_Attending, Attendance.Guests_Attending, Attendance.Raffle_Tickets, Aggie_Events.Event_Raffle_Price, Attendance.Raffle_Amount, Attendance.Donation_Type, Attendance.Donation_Value, Attendance.Dues_Family_Years_Paid, Attendance.Dues_Paid_Family, Attendance.Dues_Single_Years_Paid, Attendance.Dues_Paid_Single, Attendance.Auction_Total, Attendance.Auction_Items, Attendance.Check_Number, Attendance.Check_Amount, Attendance.Cash_Amount, Attendance.Dues_Event_Check
FROM Members INNER JOIN (Aggie_Events INNER JOIN Attendance ON Aggie_Events.Event_ID = Attendance.Event_ID) ON Members.Member_ID = Attendance.Member_ID
WHERE (((Attendance.Event_ID)=[Forms]![frm_Hidden]![txt2_Hidden]))
ORDER BY Attendance.Event_Entry_Sequence_No;

 

[MarkK]

To insert a record with two values you could get away with something as lean as this . . .

    Const SQL_INSERT As String = _
        "INSERT INTO Attendence " & _
            "( EventID, MemberID ) " & _
        "VALUES " & _
            "( p0, p1 )"
            
    With CurrentDb.CreateQueryDef("", SQL_INSERT)
        .Parameters(0) = Forms!frm_hidden.txt2_Hidden
        .Parameters(1) = Forms!frm_hidden.txt0_Hidden
        .Execute
    End With

 

[sneuberg]

Given the join in the record set I think !Member_ID is ambiguous. What table is that supposed to belong to, Members or Attendance? Same thing for EVENT_ID.

What table are you trying to add to and why aren't you just using an SQL insert for this?

 

[Cronk]

I think the OP wants to add the record to table 'Attendance'.

I'd go for something a lot simpler

currentdb.execute "INSERT INTO Attendance ( EventID, MemberID ) VALUES " & Forms!frm_hidden.txt2_Hidden & "," & Forms!frm_hidden.txt0_Hidden

 

[MarkA70]

MarkK and Cronk: THANKS a bunch. Wish I had asked this prior to spending about 24 man hours messing with this.