Changing Record Source

[NeroMaj]

I have a subform that will change what it contains based on which user logs in to the database.

This is done by changing the record source of the subform. It worked at one point and now it is having problems.

Here is my code:

Private Sub LoadNav()
    If currentUser = "AC" Then
        Form_frmNavigation.lblCurrentUser.Caption = "Alex Chavers' Associated Tasks"
        Me.RecordSource = "qryACEA"
    ElseIf currentUser = "PH" Then
        Form_frmNavigation.lblCurrentUser.Caption = "Paul Herring's Associated Tasks"
        Me.RecordSource = "qryPHEA"
    ElseIf currentUser = "DK" Then
        Form_frmNavigation.lblCurrentUser.Caption = "Donald Brown's Associated Tasks"
        Me.RecordSource = "qryDBEA"
    End If
End Sub

This works fine if the first if statement is true, but if either of the two other users logs in it puts an inputbox on the screen with the title "tblEAD.Name" which is reference to a field in the main table.

I believe this is being caused by the requery that changing the recordsource activates, but I have no idea how to counter it. Any suggestions?

 

[MarkK]

Some member of the form requires this value but it is not being supplied by the query, for instance, open the query 'qryPHEA' and make sure it exposes a column called Name.

 

[boblarson]

Additional: Don't use NAME as a field or object name. It is an Access Reserved Word and one of the worst to use as there are so many places where it can blow up on you as every object in Access has a .Name property.

 

[NeroMaj]

Alright that got it figured out...The column used to be named "Name" in tblEAD, but I changed it to Permittee because the "reserved access word" reason. However, somewhere in my code it is still referencing this, so I just changed it back to name and everything works fine.