form to edit existing records 2

[THWatson]

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

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;"

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

 

[RoyVidar]

IMHO - shorter SQL statements, which is easier to read and maintain.

Roy-Vidar

 

[THWatson]

Thanks, Roy-Vidar, for YHO.

I didn't know if there was something that worked better from an Access point of view. Your HO has helped clarify.

Tom

 

[ZmrAbdulla]

search for SQL Formatter in google

Zameer Abdulla