Identical forms that use slightly different subforms

[bookenz]

Howdy all.

Pertaing to a customer requests DB I am designing.
"frmMain" form lets the user select a customer from a combo box. Then user either clicks command button: "Display All Requests" or command button: "Display Open Requests".

When user selects "Display All Requests" the "frmCustomerRequest" opens. "frmCustomerRequest" contains the customer details and "subfrmRequests" which is a subfrm. "subfrmRequests" is based on "tblRequests" table and shows all requests associated with the chosen customer.

If the user clicks "Display Open Requests" I want the same thing to happen except the subform will be based on "qryOpenRequests" query (instead of "tblRequests") which displays only the requests with status: "Open".

How do I do this in access without having to create two different forms that are practically identical except for the Record source of the subform (being based on "tblRequests" for the "Display All Requests" button and "qryOpenRequests" for the "Display Open Requests" button?

 

[PHV]

Provided that qryOpenRequests and tblRequests have the same fields simply change the subform's RecordSource property in the Click event procedure of the button.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bookenz]

Thanks, however: "simply change the subform's RecordSource property in the Click event procedure of the button." - Not sure exactly how to do this. The Click event procedure for the "Display All Requests" button 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 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
"
So how do I change (or set) the subform's RecordSource in the above code?

Sorry if this is a newbie question..

 

[bookenz]

Ok I've found it. The code I'm looking for is:

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