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

 

[Remou]

How about checking first?

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

Set rs=CurrentDB.OpenRecordset(strSQL)

If rs.EOF Then
   'Whatever you want to do
Else
    Me.RecordSource = strSQL
End If

http://lessthandot.com

 

[THWatson]

Remou
Thanks for this.

I added, prior to the code:
Dim strSql as string
Dim rs as DAO.Recordset

But it errors out on the line
Set rs = CurrentDb.OpenRecordset(strSql)

Runtime error 3061
Too few parameters. Expected 1

Tom

 

[TheAceMan1]

Howdy THWatson . . .

As a precursor why not check if the SQL returns any records with a recordset ... ie:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT tA.AttendanceID, " & _
                "tM.MemberID, " & _
                "[LastName] & ', ' & [PreferredName] AS FullName, " & _
                "tA.MeetingDate, " & _
                "tA.Present, " & _
                "tM.LastName, " & _
                "tM.FirstName, " & _
                "tM.PreferredName " & _
         "FROM tblMembers [purple][b]AS tM[/b][/purple] " & _
         "RIGHT JOIN tblAttendance [purple][b]AS tA[/b][/purple] ON tM.MemberID = tA.MemberID " & _
         "WHERE (tA.Present = True) And " & _
               "(tM.LastName = Forms!frmAttendanceEdit!txtNameCheck) And " & _
               "(tA.TypeOfMeeting = 'Regular Meeting') And " & _
               "(tM.Status <> 'Deceased') And " & _
               "(tM.Status <> 'Transferred Out') " & _
         "ORDER BY tA.MeetingDate, tM.LastName, tM.FirstName;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      MsgBox "There's no attendance for " & Me!txtNameCheck & "!"
   Else
      Me.RecordSource = SQL
   End If
      
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[THWatson]

TheAceMan1


Same error 3061. Two few paramenters. Expected 1

Errors out on line
Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

Tom

 

[ZmrAbdulla]

http://allenbrowne.com/bug-13.html

In my experience ADO does better than DAO in many cases.
Here is a simple example of ADO usage.

Sub GetData()
    '''///  Database Connection
    Dim dbConnection As ADODB.Connection
    Set dbConnection = CurrentProject.Connection

    '''///  RecordSet from Temporary table
    Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset

    '''/// SQL to get data from Temporary table.
    strSQLTemp = "SELECT * FROM tblNewRequest"

    '''///  Open Temporary table an go through it
    rsTemp.Open strSQLTemp, dbConnection, adOpenKeyset, adLockOptimistic
    Do Until rsTemp.EOF

        '''/// Do stuff
        
        rsTemp.MoveNext

    Loop
End Sub

Zameer Abdulla

 

[TheAceMan1]

THWatson . . .

[blue][purple][b]Change:[/b][/purple]
"[LastName] & ', ' & [PreferredName] AS FullName, " & _
[purple][b]To:[/b][/purple]
"[LastName]" & ", " & "[PreferredName] AS FullName, " & _[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RoyVidar]

You have parameters in your SQL that you haven't resolved, that's what the message is about, your reference Forms!frmAttendanceEdit!txtNameCheck isn't resolved.

Just enter it into the string

[tt]... And " & _
"tblMembers.LastName = '" & Forms!frmAttendanceEdit!txtNameCheck & "' And " & _
...[/tt]

If it's on the current form, you could use Me!txtNameCheck, if you wish partial match, you could use the Like operator

[tt]... And " & _
"tblMembers.LastName Like '*" & Forms!frmAttendanceEdit!txtNameCheck & "*' And " & _
...[/tt]

This would allow for returning all if no text is entered. Is that what you want? I don't seem to find what the problem is, only that it doesn't work.

Roy-Vidar

 

[RoyVidar]

That said, assigning to the recordsource should work also when the reference is included in the SQL string, as the Jet Expression Service is used when resolving recordsources of forms etc, though one would usually use something like the following for that

[tt]... And " & _
"(tblMembers.LastName = Forms!frmAttendanceEdit!txtNameCheck OR " & _
"Forms!frmAttendanceEdit!txtNameCheck Is Null) And " & _
...[/tt]

That should give the correct member if typed correctly, all if nothing is typed and none if someone msipelled the name ;-) (note again - should work when assigning to recordsource, but probably not when opening recordset)

Though, since it seems you have a table with these members, I don't think I'd do text search at all, just use a combo with all the names, let the user select name, and use the primary key (MemberID) to search/filter the other table (tblAttendance). You could use syntax similar to my last suggestion to also return records either the selected member or all (if none is selected).

Roy-Vidar

 

[THWatson]

Well, this continues to be a hornet's nest.

TheAceMan1
Making the suggested change has no effect.

Zameer
Seems that ADO doesn't help in this instance. Same error.

RoyVidar
Your suggested changes to existing code still result in the same runtime error.

However I have been pondering your final thought about having the user select the member name from a combo box, and will pursue that.

I'll post back later to advise of results.

Tom

 

[Remou]

Sorry, I posted in a hurry. You must pass data from forms etc as values in VBA. The error you got is nearly always due to this problem. I also find aliases make life easier, so I have used a and m for tblAttendances and tblMembers:

strSQL = 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 = [red]'" & Forms!frmAttendanceEdit!txtNameCheck) & "' " _[/red]
& "And (a.TypeOfMeeting = 'Regular Meeting') And (m.Status <> 'Deceased') " _
& "And (m.Status <> 'Transferred Out') " _
& "ORDER BY a.MeetingDate, m.LastName, m.FirstName;"

http://lessthandot.com

 

[THWatson]

Remou
Well, with one line edit that does it.

Your line

& "WHERE (a.Present = True) And (m.LastName = '" & Forms!frmAttendanceEdit!txtNameCheck) & "' " _

has to be changed to

& "WHERE (a.Present = True) And [COLOR=blue]m.LastName = '" & Forms!frmAttendanceEdit!txtNameCheck & "' " _[/color]

For whatever reason, the left bracket before m.LastName has to be removed, as well as the right bracket after !txtNameCheck ... or VBA says "expected end of statement" and highlights the right bracket after !txtNameCheck.

Thanks, Remou.

Tom

 

[Remou]

Oops. Sorry, cut-and-pasting without due caution

http://lessthandot.com

 

[THWatson]

No problem, Remou.

Now what I have to do is incorporate RoyVidar's thoughts about using either the Like or IsNull operator to accommodate blank entries, or allowing the user to enter just the first few letters of the last name.

Tom

 

[Remou]

I suspect that you will regularly have the same surname as, I believe, your work deals largely with families. Is there some reason why you should not use a combobox, as suggested, and a person ID?

I think I would be inclined to have a main form that allowed the user to pick a name, and a subform that listed attendances, with the PersonID as the Link Child field, and the name of the ComboBox as the Link Master field (Bound column: PersonID).

http://lessthandot.com

 

[THWatson]

Remou
Yep, been working on that too.

You are right that my work usually deals with families in a church. In this case, however, the database is for a Kiwanis Club, so there are only a few repeated surnames.

Tom

 

[THWatson]

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.

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

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

I need a push in the right direction.

Thanks.

Tom

 

[Remou]

I suggest that you set the recordsource of the subform in advance to:

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, m.LastName, m.FirstName;

That is, a normal enough str string. Now look at the properties of the subform control (not the form contained) and set the Link Child and Link Master properties. Link Child should be MemberID and Link Master should be lstMembers.

http://lessthandot.com

 

[THWatson]

Remou
Well, by jove...methinks we've got 'er.

My first hurdle was the message saying I couldn't link unbound forms. So I bound the main form to qryAttendanceRegular.

Then the subform, fsubAttendanceEdit, I set its RecordSource to

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, m.LastName, m.FirstName;

That allowed me to join the form and subform. Child field was MemberID.
Master field was Forms!frmAttendanceEdit!lstMembers.

Then on the AfterUpdate event for lstMembers, on the main form, I put the code

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

Me!fsubAttendanceEdit.Form.RecordSource = sql

Now I find that I don't have to set a RecordSource for the subform itself...in fact, can make it invisible until called from the lstMembers AfterUpdate event.

Works well.

This has been a long and exciting trip. Thanks.

Tom

 

[THWatson]

What is the particular benefit of using table aliases in sql statements?

Tom