Changing Record Source at Runtime

[NeroMaj]

I have looked everywhere for a solution to this and I always get that same answer, set the RecordSource property of the form with FormName.RecordSource = "RecordSource", however, when I do this, I get nothing from my form.

Here is some background. I created a form using the wizard and associated it with one of the "representative" queries that the record source could be set to. After creating the form, I deleted the data source, so that I could set the supporting query at runtime.

However, I get no reaction from the form at all when I do this. Here is an example of my code.

Private Sub LoadNav()
    Dim strSQL As String
    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
    Form_frmMain.RecordSource = Me.RecordSource
End Sub

Do I need to change the data source of each control at runtime as well, or should it automatically adjust to the new record source.

 

[DCrake]

On the OnLoad event of the form

Call LoadNav

Watch out though you do not have an Else clause. Need a catch all clause incase something is wrong with the currentuser being none of the above.

 

[KenHigg]

What event runs the code?

 

[NeroMaj]

I have a call to LoadNav in Private Sub Form_Load().

Should I put this call in Form_Open()?

And it is username/password authentication, so it will always be one of those users.

 

[KenHigg]

The me. thing will not work if you run it from a stand alone mod.

 

[DCrake]

As Ken said is the private sub in the form declarations?
Also have you tried putting a breakpoint on it to step though the code?

 

[NeroMaj]

The Private Sub Form_Load is in the form's declarations.

Let me be a little more specific, because this may be an issue of when it is changing the record source.

I have a main form (Form_frmWelcome) that has two subforms on it. One subform is a left hand "navigation" type form (Form_frmNavigation). The other is a right hand "content" type form (Form_frmMain).

Form_frmWelcome is the startup form, and the other two forms are initialized with it when Access starts. I am assuming that Form_frmWelcome opens, then it opens --> loads Form_frmNavigation. This is where (Private Sub Form_Load() which contains a call to LoadNav) I am setting the record source based on the user authentication.

I am assuming that once this step is complete Form_frmWelcome opens --> Loads Form_frmMain.

So any sugggestions on where to start to identify where the disconnect is coming from?

 

[NeroMaj]

Solved!

It was an issue of order. Apparently Acces Opens Subforms prior to opening the main forms that contain them. So I put the call to change the record source in the Private Sub Form_Open() of Form_frmWelcome and it worked.

Now, how do I prevent it from asking for values when changing record source and how do I handle null values?