Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Edted by HansV to break extremely long lines in codebox.

    Using Access 2003 in 2000 format

    The database keeps track of club members and their attendance.

    A form to edit attendance has 3 visible fields - FullName, MeetingDate and Present (true/false) ... plus 3 invisible fields - MemberID, AttendanceID and LastName

    On the form there is also a text box where the user can enter the last name of a Member and bring up only his/her attendance record. The code behind the AfterUpdate event for this text box is
    [codebox]Me.RecordSource = "SELECT tblAttendance.AttendanceID, tblMembers.MemberID, " & _
    "[LastName] & ', ' & [PreferredName] AS FullName, tblAttendance.MeetingDate, tblAttendance.Present, " & _
    "tblMembers.LastName, tblMembers.FirstName, tblMembers.PreferredName " _
    & "FROM tblMembers RIGHT JOIN tblAttendance ON tblMembers.MemberID = tblAttendance.MemberID " _
    & "WHERE (tblAttendance.Present = True) And (tblMembers.LastName = " & _
    "Forms!frmAttendanceEdit!txtNameCheck) And (tblAttendance.TypeOfMeeting = 'Regular Meeting') And " & _
    "(tblMembers.Status <> 'Deceased') And (tblMembers.Status <> 'Transferred Out') " _
    & "ORDER BY tblAttendance.MeetingDate, tblMembers.LastName, tblMembers.FirstName;"[/codebox]

    This works fine providing that the user enters a last name for which there is attendance recorded. However, if there is no attendance record for that person then the screen goes blank because no records are returned.

    I have tried requerying the form. I have tried closing and reopening the form if the last name does not exist in the recordset. These don't work.

    Can anyone suggest a method to fix this?

    Thanks.

    Tom

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I would change the text box to a combo box that only offered the people who have an attendance record. This would have the ID field as a hidden first column.
    What happens with your form if two people have the same last name?
    Regards
    John



  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='johnhutchison' post='762731' date='02-Mar-2009 05:44']I would change the text box to a combo box that only offered the people who have an attendance record. This would have the ID field as a hidden first column.
    What happens with your form if two people have the same last name?[/quote]

    Good point, John.

    I have resolved the existing code by changing it to[codebox]Dim strSql As String
    Dim rs As DAO.Recordset
    strSql = "SELECT a.AttendanceID, m.MemberID, [LastName] & ', ' & [PreferredName] AS FullName, " _
    & "a.MeetingDate, a.Present, m.LastName, m.FirstName, m.PreferredName " _
    & "FROM tblMembers AS m RIGHT JOIN tblAttendance AS a ON m.MemberID = a.MemberID " _
    & "WHERE (a.Present = True) And m.LastName = '" & Forms!frmAttendanceEdit!txtNameCheck & "' " _
    & "And (a.TypeOfMeeting = 'Regular Meeting') And (m.Status <> 'Deceased') " _
    & "And (m.Status <> 'Transferred Out') " _
    & "ORDER BY a.MeetingDate, m.LastName, m.FirstName;"

    Set rs = CurrentDb.OpenRecordset(strSql)

    If rs.EOF Then
    MsgBox "Oops"
    Me.Requery
    Me.lblNameCheck.Visible = True
    Me.txtNameCheck.Visible = False
    Me.txtNameCheck = Null
    Exit Sub
    Else
    Me.RecordSource = strSql
    End If[/codebox]

    However, this doesn't solve the case you raise - two people with the same surname - nor does it allow the user to enter just the first few initials of a surname. And, what happens if they leave it blank?

    So I am working on a form that has a list box with the members who have an Attendance record, with a subform that would show the Attendance records for the name selected in the list box.

    Tom

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have been working on the alternative procedure. That is a form which has a list box, plus a subform in which the Attendance records for the name selected in the list box are shown.

    On the AfterUpdate event for the list box on the main form, I have placed the following code.
    [codebox]Dim sql As String

    sql = "SELECT a.AttendanceID, m.MemberID, [LastName] & ', ' & [PreferredName] AS FullName, " _
    & "a.MeetingDate, a.Present, m.LastName, m.FirstName, m.PreferredName " _
    & "FROM tblMembers AS m RIGHT JOIN tblAttendance AS a ON m.MemberID = a.MemberID " _
    & "WHERE (a.Present = True) And m.MemberID = '" & Forms!Form1!lstMembers.Column(0) & "' " _
    & "And (a.TypeOfMeeting = 'Regular Meeting') And (m.Status <> 'Deceased') " _
    & "And (m.Status <> 'Transferred Out') " _
    & "ORDER BY a.MeetingDate, m.LastName, m.FirstName;"

    Me.fsubAttendanceEdit.SetFocus

    Me!fsubAttendanceEdit.Form.RecordSource = sql[/codebox]

    However, this errors out saying "You cancelled the previous operation" and it highlights the "Me!fsubAttendanceEdit.Form.RecordSource = sql" line.

    I need a push in the right direction.

    Tom

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have it now.

    The main form, frmAttendanceEdit, is bound to a query, qryAttendanceRegular. It has a list box, lstMembers, which pulls up all members with Attendance recorded.

    The subform, fsubAttendanceEdit, is linked by Child = MemberID to Master = Forms!frmAttendanceEdit!lstMembers

    The record source for the subform is blank, and the form is hidded until called from the List Box on the main form.

    On the AfterUpdate event for the list box, there is this code

    [codebox]Dim sql As String

    sql = "SELECT a.AttendanceID, m.MemberID, [LastName] & ', ' & [PreferredName] AS FullName, a.MeetingDate, a.Present, m.LastName , m.FirstName, m.PreferredName " _
    & "FROM tblMembers AS m RIGHT JOIN tblAttendance AS a ON m.MemberID = a.MemberID " _
    & "WHERE (a.Present = True) And (a.TypeOfMeeting = 'Regular Meeting') And (m.Status <> 'Deceased') And (m.Status <> 'Transferred Out') " _
    & "ORDER BY a.MeetingDate;"

    Me.fsubAttendanceEdit.Visible = True
    Me.fsubAttendanceEdit.SetFocus

    Me!fsubAttendanceEdit.Form.RecordSource = sql[/codebox]

    This works well.

    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
  •