filter a control when form opened by command button?

[Guest]

The following code, created by the Command Button Wizard on my frmClients
form, opens my frmOrders form filtered to show orders for that client.

What is the best way to extend the code so that when the orders form is
opened from this command button you can carry on adding new orders for the
same client without each time having to reselect the client (from the order
form's cboOrderedByID combo box). In other words I want the cmbOrders button
also to apply the appropriate filter to the cboOrderedByID combo on the
orders form, the same way as it filters the form itself. Presently the
cboOrderedByID combo still displays the full list of clients when the form is
opened from the button.

A possible work-around I can see would be for the button to open a different
version of the orders form based on a parameter query, but it seems a good
idea to try and keep things simple and avoid a proliferation of forms.

I'm still feeling my way around as I learn to use VBA and I'd be very
grateful for some help here.

Here's the present code:

Private Sub cmbOrders_Click()
On Error GoTo Err_cmbOrders_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOrders"

stLinkCriteria = "[OrderedByID]=" & Me![ClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbOrders_Click:
Exit Sub

Err_cmbOrders_Click:
MsgBox Err.Description
Resume Exit_cmbOrders_Click

End Sub

 

[fredg]

The following code, created by the Command Button Wizard on my frmClients
form, opens my frmOrders form filtered to show orders for that client.

What is the best way to extend the code so that when the orders form is
opened from this command button you can carry on adding new orders for the
same client without each time having to reselect the client (from the order
form's cboOrderedByID combo box). In other words I want the cmbOrders button
also to apply the appropriate filter to the cboOrderedByID combo on the
orders form, the same way as it filters the form itself. Presently the
cboOrderedByID combo still displays the full list of clients when the form is
opened from the button.

A possible work-around I can see would be for the button to open a different
version of the orders form based on a parameter query, but it seems a good
idea to try and keep things simple and avoid a proliferation of forms.

I'm still feeling my way around as I learn to use VBA and I'd be very
grateful for some help here.

Here's the present code:

Private Sub cmbOrders_Click()
On Error GoTo Err_cmbOrders_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOrders"

stLinkCriteria = "[OrderedByID]=" & Me![ClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbOrders_Click:
Exit Sub

Err_cmbOrders_Click:
MsgBox Err.Description
Resume Exit_cmbOrders_Click

End Sub

Dim stDocName as String
Dim stCriteria As String
stDocName = "frmOrders"
stLinkCriteria = "[OrderedByID]=" & Me![ClientID]

DoCmd.OpenForm "frmBasicData", , , stLinkCriteria
Forms!frmOrders!OrderedByID.DefaultValue = "'" & [OrderedByID] & "'"

The default value of the combo box will be whatever the value of the
combo box is for the filtered record. You can over-write the value at
any time by simply manually entering a new OrderByID. However, the
default value will not change.

 

[Guest]

Thanks very much for the help. This has taken me a lot further.

The following code, created by the Command Button Wizard on my frmClients
form, opens my frmOrders form filtered to show orders for that client.

What is the best way to extend the code so that when the orders form is
opened from this command button you can carry on adding new orders for the
same client without each time having to reselect the client (from the order
form's cboOrderedByID combo box). In other words I want the cmbOrders button
also to apply the appropriate filter to the cboOrderedByID combo on the
orders form, the same way as it filters the form itself. Presently the
cboOrderedByID combo still displays the full list of clients when the form is
opened from the button.

A possible work-around I can see would be for the button to open a different
version of the orders form based on a parameter query, but it seems a good
idea to try and keep things simple and avoid a proliferation of forms.

I'm still feeling my way around as I learn to use VBA and I'd be very
grateful for some help here.

Here's the present code:

Private Sub cmbOrders_Click()
On Error GoTo Err_cmbOrders_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOrders"

stLinkCriteria = "[OrderedByID]=" & Me![ClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbOrders_Click:
Exit Sub

Err_cmbOrders_Click:
MsgBox Err.Description
Resume Exit_cmbOrders_Click

End Sub

Dim stDocName as String
Dim stCriteria As String
stDocName = "frmOrders"
stLinkCriteria = "[OrderedByID]=" & Me![ClientID]

DoCmd.OpenForm "frmBasicData", , , stLinkCriteria
Forms!frmOrders!OrderedByID.DefaultValue = "'" & [OrderedByID] & "'"

The default value of the combo box will be whatever the value of the
combo box is for the filtered record. You can over-write the value at
any time by simply manually entering a new OrderByID. However, the
default value will not change.