Using Access 2003, 2000 Format
tblAttendance has the following fields...
AttendanceID (Auto number, PK)
MemberID (number, linked to tblMembers)
MakeupID (number, linked to tblMakeups)
MeetingTypeID (number, linked to tblMeetingType)
The value of MeetingTypeID can either be 1 for a Regular Meeting, or 2 for a MakeupMeeting.
In this case we are only interested in MeetingTypeID = 1
In frmAttendance, the user selects a member from a multi-select list box called lstMembers. A date is entered in a text box called txtMeetingDate.
When the user presses the Post command button, the number of members chosen shows so the number can be verified. If so, posting continues.
This is the entire code behind the Post button. I have put the relevant posting piece in bold.
[codebox]Private Sub cmdPost_Click()
Dim sql As String
Dim ndx As Integer
On Error GoTo cmdPost_Click_Error
If Me.txtCountPosted = 0 Then
Call MsgBox("Please select 1 or more names for posting.", vbExclamation, "Select name(s)")
If IsNull(Me.txtMeetingDate) Then
Call MsgBox("Please select a Meeting Date from the Calendar!", vbExclamation, "Meeting Date needed")
Select Case MsgBox(" You have selected " & Me.txtCountPosted & " Members" _
& vbCrLf & " for Attendance posting." _
& vbCrLf & "Do you wish to continue with posting?" _
, vbYesNo Or vbExclamation Or vbDefaultButton1, "Posting check")
Call MsgBox("Posting will be cancelled.", vbExclamation Or vbDefaultButton1, "Cancelling posting")
For ndx = 0 To Me.lstMembers.ListCount - 1
If Me.lstMembers.Selected(ndx) Then
sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, MeetingTypeID) VALUES (" & _
Me.lstMembers.ItemData(ndx) & ", #" & Me.txtMeetingDate & "#,True,1)"
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPost_Click of VBA Document Form_frmAttendance"
This looks correct to me. So why isn't it working?
It worked until today, when I further normalized tblAttendance, changing the former MeetingType (a text field with a value of either "Regular Meeting" or "Makeup Activity") to MeetingTypeID (a number field with a value of either 1 or 2).