Calling a Function (Setting a RecordSource)

[dlerh]

My goal is to change the RecordSource of a report depending on which form
accesses it. My attempted strategy was to use a function (frmLoaded) to
determine whether one of the forms was open, and call that function in the
subroutine of the OnOpen arguments for the report. (I don't particularly
like this strategy, since it only allows for two options for the
RecordSource: frmLoaded True and frmLoaded False.)

The code defaults to the second option no matter which form accesses the
report.(The form is not open, "frmLoaded(ByVal MyForm) <> True "). I have
tried "= True" and "= False" instead of "= True" and "<> True", and I have
tried "= -1" and "= 0") with the exact same results. I have also tried
embedding the function within the sub, but I triggered the Compile Error:
"Expected End Sub."

I am new to calling functions (and writing them for that matter), so I have
a sneaking suspicion that my entire code may be just a fantasy.

Function frmLoaded(ByVal MyForm As String) As Integer
If SysCmd(acSysCmdGetObjectState, acForm, MyForm) <> 0 Then
If Forms(MyForm).CurrentView <> 0 Then
frmLoaded = True
End If
End If
End Function

Private Sub Report_Open(Cancel As Integer)
Dim MySQL As String
Dim MyForm As String
MyForm = "Forms!frmTransmittals"
If frmLoaded(ByVal MyForm) = True Then
MySQL = "SELECT * FROM tblYouGuysAreGreat"
ElseIf frmLoaded(ByVal MyForm) <> True Then
MySQL = "SELECT * FROM qryWhatsWrongWithMe"
End If

Me.RecordSource = MySQL

End Sub

 

[Ofer]

Change this line
MyForm = "Forms!frmTransmittals"
To
MyForm = "frmTransmittals"

You added the forms in the function
==========================
Try this
Private Sub Report_Open(Cancel As Integer)
Dim MySQL As String
Dim MyForm As String
MyForm = "frmTransmittals"
If frmLoaded(MyForm) = True Then
MySQL = "SELECT * FROM tblYouGuysAreGreat"
Else
MySQL = "SELECT * FROM qryWhatsWrongWithMe"
End If

Me.RecordSource = MySQL

End Sub

 

[Marshall Barton]

My goal is to change the RecordSource of a report depending on which form
accesses it. My attempted strategy was to use a function (frmLoaded) to
determine whether one of the forms was open, and call that function in the
subroutine of the OnOpen arguments for the report. (I don't particularly
like this strategy, since it only allows for two options for the
RecordSource: frmLoaded True and frmLoaded False.)

The code defaults to the second option no matter which form accesses the
report.(The form is not open, "frmLoaded(ByVal MyForm) <> True "). I have
tried "= True" and "= False" instead of "= True" and "<> True", and I have
tried "= -1" and "= 0") with the exact same results. I have also tried
embedding the function within the sub, but I triggered the Compile Error:
"Expected End Sub."

I am new to calling functions (and writing them for that matter), so I have
a sneaking suspicion that my entire code may be just a fantasy.

Function frmLoaded(ByVal MyForm As String) As Integer
If SysCmd(acSysCmdGetObjectState, acForm, MyForm) <> 0 Then
If Forms(MyForm).CurrentView <> 0 Then
frmLoaded = True
End If
End If
End Function

Private Sub Report_Open(Cancel As Integer)
Dim MySQL As String
Dim MyForm As String
MyForm = "Forms!frmTransmittals"
If frmLoaded(ByVal MyForm) = True Then
MySQL = "SELECT * FROM tblYouGuysAreGreat"
ElseIf frmLoaded(ByVal MyForm) <> True Then
MySQL = "SELECT * FROM qryWhatsWrongWithMe"
End If

Me.RecordSource = MySQL

End Sub

I think the problem is that MyForm is not right. Try:
MyForm = "frmTransmittals"
but I don't like this approach either.

How abut letting the calling form tell the report what the
record source should be by passing it in the OpenReport
method's OpenArgs argument? The code in frmTransmittals
would be:
DoCmd.OpenReport "thereport", _
WhereCondition:= "SELECT * FROM tblYouGuysAreGreat"
and for the other form:
DoCmd.OpenReport "thereport", _
WhereCondition:= "qryWhatsWrongWithMe"

The code in the report's Open event would be:
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

 

[dlerh]

way cool! thnx!

I think the problem is that MyForm is not right. Try:
MyForm = "frmTransmittals"
but I don't like this approach either.

How abut letting the calling form tell the report what the
record source should be by passing it in the OpenReport
method's OpenArgs argument? The code in frmTransmittals
would be:
DoCmd.OpenReport "thereport", _
WhereCondition:= "SELECT * FROM tblYouGuysAreGreat"
and for the other form:
DoCmd.OpenReport "thereport", _
WhereCondition:= "qryWhatsWrongWithMe"

The code in the report's Open event would be:
If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

 

[Marshall Barton]

Arrggghhh. My copy/paste got away from me. That should be
OpenArgs instead of WhereCondition. Sorry if that caused
any confusion.

 

[dlerh]

no worries. instead of just copying and pasting your helpful code like a
mindless robot (not that i haven't or won't) i have taken the opportunity to
more fully explore open arguments. i wondered what that was when i was
ignoring it before. this will actually be useful in a number of places.

thnx again!
dale

Arrggghhh. My copy/paste got away from me. That should be
OpenArgs instead of WhereCondition. Sorry if that caused
any confusion.
--
Marsh
MVP [MS Access]

way cool! thnx!