Entries from form won't stick 1

[THWatson]

The tblAttendance has the following fields...
AttendanceID (Auto number, PK)
MemberID (number, linked to tblMembers)
MeetingDate (date)
Present (Yes/No)
HoursSpent (number)
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.

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)")
Exit Sub
End If

If IsNull(Me.txtMeetingDate) Then
Me.txtMeetingDate.SetFocus
Call MsgBox("Please select a Meeting Date from the Calendar!", vbExclamation, "Meeting Date needed")
Exit Sub
End If

[b]SelectionProcedure:
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")
    Case vbYes
       GoTo PostingProcedure
    Case vbNo
        Call MsgBox("Posting will be cancelled.", vbExclamation Or vbDefaultButton1, "Cancelling posting")
        Call cmdCancel_Click
        Exit Sub
End Select

PostingProcedure:
DoCmd.SetWarnings False
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)"
       DoCmd.RunSQL sql
   End If
Next ndx
DoCmd.SetWarnings True[/b]

Call cmdCancel_Click


   On Error GoTo 0
   Exit Sub

cmdPost_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPost_Click of VBA Document Form_frmAttendance"

End Sub

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).

Can anyone spot the glitch? I've looked at it and looked at it and am a little bleary-eyed.

Tom

 

[THWatson]

I found the problem!

When the field MakeupID was created, Access slipped in a default value of 0 and that was messing things up, and not allowing entries to be added.

Tom

 

[missinglinq]

That really shouldn't make a difference, unless the form is Read-0nly. Assuming it can be updated, you probably need to very careful about making a backup copy often, as it may ne a sign of corruption.

Of course, unless digits are going to be in performing math, they should actually be dedined as Datatype Text rather than Numbers.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[THWatson]

missinglinq
Well, I wondered about that as well. It is not Read-only. Funny thing is that taking away the 0 Default solved the problem. At least, for now!

This field has to be Number, because it's the foreign key to a PK in tblMeetingType. It can't be text.

Tom

 

[missinglinq]

This field has to be Number, because it's the foreign key to a PK in tblMeetingType. It can't be text.

That field shouldn't be a Number either!

Good luck!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[THWatson]

Why shouldn't that field be a number?

The PK field to which it is linked is an Autonumber.

Seeking to understand...

Tom

 

[missinglinq]

Well, if you were matching an Autonumber, that would make a difference. But why would you use an Autonumber when your post indicates that there will only be two of them, 1 for a Regular Meeting, or 2 for a MakeupMeeting?

Also, Autonumbers should never be used in this manner.

Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, Access MVP, explaining how autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[THWatson]

Missinglinq
Thanks for the explanation. That helps me to see where you were going with this.

As far as I know at the moment, there will only be 2 types of Meetings - Regular and Makeup Activity.

It is possible, I suppose, that the future will bring other types of meetings to add. Not sure why, but you never know.

So, in any event, I will rethink this, given your guidance.

Tom