Need to add code to code filtering form...

G

Guest

The following code was built with the command wizard & works great, however,
I need to add addtional code to filter with more content. Currently, when
the command button is clicked, it opens the form based on the Plan and
Product chosen. The user will also have to choose the location, effective
and term dates as well to drill down to the form they wish to view. How do I
add code to include these items so when the user clicks the command button,
not only will the form open to the correct plan and product, but to the
matching location, effective and term dates as well.
Thank you for your help.

Private Sub cmdClickforContractInformation_Click()
On Error GoTo Err_cmdClickforContractInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"

stLinkCriteria = "[ProductId]=" & Me![cmbProduct]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation_Clic:
Exit Sub

Err_cmdClickforContractInformation_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation_Clic

End Sub
 
F

fredg

The following code was built with the command wizard & works great, however,
I need to add addtional code to filter with more content. Currently, when
the command button is clicked, it opens the form based on the Plan and
Product chosen. The user will also have to choose the location, effective
and term dates as well to drill down to the form they wish to view. How do I
add code to include these items so when the user clicks the command button,
not only will the form open to the correct plan and product, but to the
matching location, effective and term dates as well.
Thank you for your help.

Private Sub cmdClickforContractInformation_Click()
On Error GoTo Err_cmdClickforContractInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"

stLinkCriteria = "[ProductId]=" & Me![cmbProduct]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation_Clic:
Exit Sub

Err_cmdClickforContractInformation_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation_Clic

End Sub

Generic code would be something like this (all on one line, or on
several lines if you use the line continuation characters):
stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & " AND [ATextField]
= '" & Me![OtherControl] & "' AND [ADateField] = #" &
Me![DateControlOnForm] & "#"

You would have to substitute your actual field and control names.

Please read VBA Help on
Restrict + Restrict Data to a Subset of Records + Multiple Fields in
Criteria Expressions
 
G

Guest

Well, I have decided to add the location to the product table, so that the
command button code I have working continues to pick the correct form.
Now my only dilema left is that there are multiple forms to open/filter for
each plan and product because of their different effective dates. Now, the
only location of these effective dates is in the form to open, there is no
separate table for just the effective dates. (I decided that the term date
was not a necessity for the user to choose)
So, how do I make the effective date part of what is needed for the user to
choose before clicking the command button that will take them to the correct
form?
Thanks again for all your help.
fredg said:
The following code was built with the command wizard & works great, however,
I need to add addtional code to filter with more content. Currently, when
the command button is clicked, it opens the form based on the Plan and
Product chosen. The user will also have to choose the location, effective
and term dates as well to drill down to the form they wish to view. How do I
add code to include these items so when the user clicks the command button,
not only will the form open to the correct plan and product, but to the
matching location, effective and term dates as well.
Thank you for your help.

Private Sub cmdClickforContractInformation_Click()
On Error GoTo Err_cmdClickforContractInformation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContractRates-Specifications"

stLinkCriteria = "[ProductId]=" & Me![cmbProduct]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdClickforContractInformation_Clic:
Exit Sub

Err_cmdClickforContractInformation_Click:
MsgBox Err.Description
Resume Exit_cmdClickforContractInformation_Clic

End Sub

Generic code would be something like this (all on one line, or on
several lines if you use the line continuation characters):
stLinkCriteria = "[ProductId]=" & Me![cmbProduct] & " AND [ATextField]
= '" & Me![OtherControl] & "' AND [ADateField] = #" &
Me![DateControlOnForm] & "#"

You would have to substitute your actual field and control names.

Please read VBA Help on
Restrict + Restrict Data to a Subset of Records + Multiple Fields in
Criteria Expressions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top