Using text boxes to enter a date range to control subform contents

M

MCB

I have a form that allows users to search for specific records via a number
of criteria. They can use any one of several combo boxes to search for
records by Associate, Team Lead, Issue Number, etc., and the records are then
displayed in a subform. That part works, and I've listed the code below.

I also need to add functionality to allow users to search by two date
ranges: Issue Creation and Issue Completion. The set-up is that I have a
frame for each, inside which are two option buttons and two text boxes. The
first option button is "All Dates", which disables the date range text boxes.
The second is for the date range, and beneath it are two text boxes
(txtCrStartDate and txtCrEndDate for Issue Creation). How do I get the code
to "pick up" the user-entered date range when the Search button is clicked,
and use it to display records falling within that date range? As I mentioned,
I got it working for single combo box items, and I know how to use "Between"
criteria in queries, but I can't get everything working together. TIA.

+++++++++++++++++++++++++++++
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmIssueSearchSubForm"

If Me!cboSelectAssociate > "0" Then

stLinkCriteria = "[Associate] Like '" & "*" & Me.cboSelectAssociate
& "*'"

ElseIf Me!cboSelectTL > "0" Then

stLinkCriteria = "[Team Lead] Like '" & "*" & Me.cboSelectTL & "*'"

ElseIf Me!cboSelectDept > "0" Then

stLinkCriteria = "[Department] Like '" & "*" & Me.cboSelectDept & "*'"

ElseIf Me.cboSelectGroup > "0" Then

stLinkCriteria = "[Group ID] Like '" & "*" & Me.cboSelectGroup & "*'"

ElseIf Me.cboSelectIssueCat > "0" Then

stLinkCriteria = "[Issue Category] Like '" & "*" &
Me.cboSelectIssueCat & "*'"

ElseIf Me.cboSelectIssueSubcat > "0" Then

stLinkCriteria = "[Issue Subcategory] Like '" & "*" &
Me.cboSelectIssueSubcat & "*'"

Else
MsgBox "Please select a value in one of the search fields.", vbOKOnly
GoTo Exit_cmdSearch_Click

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub
 

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

Similar Threads

Need code fix for searching 2
Creating a Search using a combo box and then creating a report. 4
Open form and filter form & subform using combo-boxes 2
Using textbox in form as wildcard function search 1
Display records on the form based on the selection 1
combo and text boxes to search a form 2
Error using Like* operator 1
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0

Top