Setting the recourd source of a subform

[bookenz]

I have a form named "frmCustomerRequests" which contains a sub form: "subfrmRequests" (linked via Customer_ID). On "frmMain" I have 2 command buttons 1. "Display All Requests" & 2. "Display Open Requests.

The coding for "Display All Requests" is as follows:

"
Private Sub cmdDisplayAllRequsets_Click()
On Error GoTo Err_cmdDisplayAllRequsets_Click

'exit this sub if customer name is cleared
If cboSearch.Value = "" Then
Msg = "Please enter the customer's name."
a = MsgBox(Msg, , "Blank Entry")
Exit Sub
End If

Me.Visible = False

Dim subfrmRequests As New Form_subfrmRequests
subfrmRequests.RecordSource = "tblRequests"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCustomerRequest"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.cboSearch = ""

Exit_cmdDisplayAllRequsets_Click:
Exit Sub

Err_cmdDisplayAllRequsets_Click:
MsgBox Err.Description
Resume Exit_cmdDisplayAllRequsets_Click

End Sub
"

The coding for "Display Open Requests" is as follows:

"
Private Sub cmdDisplayOpenRequests_Click()
On Error GoTo Err_cmdDisplayOpenRequsets_Click

'exit this sub if customer name is cleared
If cboSearch.Value = "" Then
Msg = "Please enter the customer's name."
a = MsgBox(Msg, , "Blank Entry")
Exit Sub
End If

Me.Visible = False

Dim subfrmRequests As New Form_subfrmRequests
subfrmRequests.RecordSource = "qryOpenRequests"

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCustomerRequest"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.cboSearch = ""

Exit_cmdDisplayOpenRequsets_Click:
Exit Sub

Err_cmdDisplayOpenRequsets_Click:
MsgBox Err.Description
Resume Exit_cmdDisplayOpenRequsets_Click

End Sub
"

When user clicks on "Display Open Requests" I want "subfrmRequests" to display only the "open" requests, so I have created "qryOpenRequests" where the criteria for the "Requests_Status" is ="open". And in the coding I have tried to set the recordsource for "subfrmRequests" to "qryOpenRequests".

However this is not working. When I click on "Display Open Requests" it is still displaying requests with a "closed" status.

Is there something wrong with the command button coding for setting the sub form's record source (as in the above code)?

 

[Crowley16]

a subform doesn't have a native recordsource, instead it uses the recordsource of the form that it's bound to...

so if you want to change the recordsource, then you'd have to do it in the onOpen event of the form itself...

I've done this before by generating the sql, and then passing it to the form in the .OpenArgs, and in the form, to bound the recordsource to whatever is in .OpenArgs, (after testing what's in there of course)

--------------------
Procrastinate Now!

 

[tonyflavell]

Hi there

You can change the "recordsource" atribute of a subform but you have to change the focus to a control on the subform first. This requires two operations. One to move focus to the subform object and two to move to a control on the subform object.

An example:- On a main form called "DataEntryContacts" which has a subform called "DECSubForm1" If I want to change the "recordsource" attribute for the subform without changing the main form:-

Forms!DataEntryContacts!DECSubform1.SetFocus
Forms!DataEntryContacts!DECSubform1!ContactName.SetFocus
Forms!DataEntryContacts!DECSubform1!ContactName.recordsource = OtherQuery

This works for me.

Regards

Tony

 

[bookenz]

Thanks all for the replies. I've solved this with the help of Crowley16's method as follows:

Code:
Private Sub cmdDisplayAllRequests_Click()
On Error GoTo Err_cmdDisplayAllRequests_Click

'exit this sub if customer name is cleared
If cboSearch.Value = "" Then
Msg = "Please enter the customer's name."
a = MsgBox(Msg, , "Blank Entry")
Exit Sub
End If

Me.Visible = False

Dim stDocName As String
Dim stLinkCriteria As String
Dim RequestStatus As String

RequestStatus = "All"

stDocName = "frmCustomerRequest"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , RequestStatus
Me.cboSearch = ""

Exit_cmdDisplayAllRequests_Click:
Exit Sub

Err_cmdDisplayAllRequests_Click:
MsgBox Err.Description
Resume Exit_cmdDisplayAllRequests_Click

End Sub

Private Sub cmdDisplayOpenRequests_Click()
On Error GoTo Err_cmdDisplayOpenRequests_Click

'exit this sub if customer name is cleared
If cboSearch.Value = "" Then
Msg = "Please enter the customer's name."
a = MsgBox(Msg, , "Blank Entry")
Exit Sub
End If

Me.Visible = False

Dim stDocName As String
Dim stLinkCriteria As String
Dim RequestStatus As String

RequestStatus = "Open"

stDocName = "frmCustomerRequest"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , RequestStatus
Me.cboSearch = ""

Exit_cmdDisplayOpenRequests_Click:
Exit Sub

Err_cmdDisplayOpenRequests_Click:
MsgBox Err.Description
Resume Exit_cmdDisplayOpenRequests_Click

End Sub
"

Then in "frmCustomerRequests" On_Open event procedure:

Code:
Private Sub Form_Open(Cancel As Integer)
If OpenArgs = "All" Then
Me.subfrmRequests.Form.RecordSource = "tblRequests"
ElseIf OpenArgs = "Open" Then
Me.subfrmRequests.Form.RecordSource = "qryOpenRequests"
End If
End Sub
"

And it works