Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003, 2000 Format

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

    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


    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[/codebox]
    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).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In what sense isn't it working? Do you get an error message? If so what does it say? Or does the code do something different from what you intended? Or doesn't it do anything at all?
    It might help if you posted a stripped down, compacted and zipped copy of the database.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='766377' date='20-Mar-2009 12:55']In what sense isn't it working? Do you get an error message? If so what does it say? Or does the code do something different from what you intended? Or doesn't it do anything at all?
    It might help if you posted a stripped down, compacted and zipped copy of the database.[/quote]
    Hans
    No error message. The code appears to work but nothing at all happens back in the table.

    Attached is a stripped down version. The form to use is frmAttendance. (frmMakeupActivities works fine and it's constructed similarly)

    Tom
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the future, please compact the database before zipping it (Tools | Database Utilities | Compact and Repair Database). Your frontend went from 1,000 KB to 232 KB and the zip file from 149 KB to 42 KB - significant size reductions.

    Could you post a stripped down, compacted and zipped copy of the backend too, please? I can't do anything with the frontend by itself.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='766384' date='20-Mar-2009 13:42']In the future, please compact the database before zipping it (Tools | Database Utilities | Compact and Repair Database). Your frontend went from 1,000 KB to 232 KB and the zip file from 149 KB to 42 KB - significant size reductions.

    Could you post a stripped down, compacted and zipped copy of the backend too, please? I can't do anything with the frontend by itself.[/quote]
    Sorry, Hans. That was pretty stupid of me.

    Here's what is needed.

    Tom
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The MakeUpID field in tblAttendance has a default value of 0, so if you add a record to tblAttendance without explicitly providing a value for MakeUpID, Access will try to assign the default value of 0. But this is not allowed because MakeUpID is linked to the field of the same name in tblMakeUps with referential integrity enforced, and 0 does not occur in that table.

    So you should clear the Default Value property of MakeUpID.
    In general, you should avoid having 0 as default value for number fields unless you really need it.

    I found the cause of the problem by temporarily commenting out the line DoCmd.SetWarnings False in the code, so that I could see the error message generated by the SQL statement. It is often a good idea to display error messages during development instead of suppressing them.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='766390' date='20-Mar-2009 14:28']The MakeUpID field in tblAttendance has a default value of 0, so if you add a record to tblAttendance without explicitly providing a value for MakeUpID, Access will try to assign the default value of 0. But this is not allowed because MakeUpID is linked to the field of the same name in tblMakeUps with referential integrity enforced, and 0 does not occur in that table.

    So you should clear the Default Value property of MakeUpID.
    In general, you should avoid having 0 as default value for number fields unless you really need it.

    I found the cause of the problem by temporarily commenting out the line DoCmd.SetWarnings False in the code, so that I could see the error message generated by the SQL statement. It is often a good idea to display error messages during development instead of suppressing them.[/quote]
    Well, I surely didn't find that. THANKS, HANS! You're a lifesaver.

    That default value of 0 went in there by itself when I changed things around. Something so simple.

    Thanks again!

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •