Entering query criteria through form

[sstasiak]

I've never created a form where query criteria can be entered.

I want to be able to sort records by 7 different fields. Fields are analyst, system, issue, department, user, start date, and end date. I'd like to have a form where the user can enter criteria for any combination of fields: ie, all records that have XXXX as the analyst assigned to system YYYY from start date A to end date B.

Can anyone point me in the right direction as far as what the control sources would be or any VBA that may be needed?

Thanks

 

[spasticus]

how do you want the results to be displayed? on a form, report..?

 

[sstasiak]

I'd like it to be displayed in a form(using datasheet view?? would this make it look like a table?) I'd also like to give the user the option, maybe through a button, to export the data to an excel file for data analysis.

 

[spasticus]

yes datasheet view will look like a table. try this:

on the form where the user enters criteria:

make 7 text and tick boxes - one for each field ... then ...
in the onclick event for the button that "searches" put this code
(obviously you need to check the names so they match yours)

Dim strCriteria As String

If checkbox1 = True Then
 strAnalyst = "[analyst]=""" & txtanalyst & """"
End If

If checkbox2 = True Then
 strSystem = "[system]=""" & txtsystem & """"
End If

If checkbox3 = True Then
 strIssue = "[issue]=""" & txtissue & """"
End If

If checkbox4 = True Then
 strDepartment = """ & txtdepartment & """""
End If

If checkbox5 = True Then
 strUser = "[user]=""" & txtuser & """"
End If

If checkbox6 = True Then
 strStartDate = "[start date]=#" & txtstartdate & "#" 'you might need to use Format(txtstartdate, "long date") - i did with one of my databases
End If

If checkbox7 = True Then
 strEndDate = "[end date]=#" & txtenddate & "#" 'same here Format(Booking_Date, "long date")
End If

strCriteria = strAnalyst & strSystem & strIssue & strDepartment & strUser & strStartDate & strEndDate

addand = Split(strCriteria, "[")

strCriteria = ""

For i = 0 To UBound(addand)
 If i = 1 Then
  strCriteria = "[" & addand(i) & " and "
 ElseIf i = UBound(addand) Then
  strCriteria = strCriteria & "[" & addand(i)
 Else
  strCriteria = strCriteria & "[" & addand(i) & " and "
 End If
Next i

Me!SubFormName.Form.Filter = Strcriteria
Me!SubFormName.Form.FilterOn = True

 

[sstasiak]

I'm going to try this and see how it works out.

Could the analyst, system, issue, department, and user criteria be selected from a combo box instead of free text? If the users are selecting data through combo boxes, I'd like them to be able to specify search criteria in a similar manner.

How would the code for that look?

 

[sstasiak]

After a search, I found this thread that gives an example of what I'm trying to do.

Can anyone tell me how I would modify the query so that I could search by multiple criteria specified through multiple combo boxes and text boxes?

 

[spasticus]

well.. for example, just change the txtwhatever to the name of you combo box.value (i think .value im not sure - ive been wrong about it before) ...

If checkbox1 = True Then
strAnalyst = "[analyst]=""" & cmbanalyst.value & """"
End If

If checkbox2 = True Then
strSystem = "[system]=""" & txtsystem & """"
End If

or if you don't want check boxes try something like

If cmbanalyst.value <> "" True Then
strAnalyst = "[analyst]=""" & cmbanalyst.value & """"
End If

If txtsystem <> "" Then
strSystem = "[system]=""" & txtsystem & """"
End If

hope that does it for you

 

[sstasiak]

spasticus

Should my subform be my main table in datasheet view?

 

[spasticus]

that depends. do you just want to show records with fields in..
1)your main table? - yes the sub form is based on you main table
or
2)other tables? - no. use a query, selecting the fields you want, and base the sub form on that

 

[sstasiak]

I took the code that you provided and modified it to reflect my field names. One thing I'm confused about are the fields in red. If i'm searching a string, I need to search other tables, because the main table just holds ID#'s. See code:

Private Sub Command23_Click()
Dim strCriteria As String

If Check7 = True Then
 strAnalyst = "[[COLOR="Red"]Analyst[/COLOR]]=""" & Combo5.Value & """"
End If

If Check3 = True Then
 strSystem = "[[COLOR="red"]system[/COLOR]]=""" & Combo1.Value & """"
End If

If Check10 = True Then
 strIssue = "[[COLOR="red"]IssueType[/COLOR]]=""" & Combo8.Value & """"
End If

If Check14 = True Then
 strDepartment = "[[COLOR="red"]Department[/COLOR]]=""" & Combo14.Value & """"""
End If

If Check13 = True Then
 strUser = "[[COLOR="red"]Users[/COLOR]]=""" & Combo11.Value & """"
End If

If Check21 = True Then
 strStartDate = "[[COLOR="red"]StartDate[/COLOR]]=#" & Text17 & "#" 'you might need to use Format(txtstartdate, "long date") - i did with one of my databases
End If

If Check22 = True Then
 strEndDate = "[[COLOR="red"]EndDate[/COLOR]]=#" & Text19 & "#" 'same here Format(Booking_Date, "long date")
End If

strCriteria = strAnalyst & strSystem & strIssue & strDepartment & strUser & strStartDate & strEndDate

addand = Split(strCriteria, "[")

strCriteria = ""

For i = 0 To UBound(addand)
 If i = 1 Then
  strCriteria = "[" & addand(i) & " and "
 ElseIf i = UBound(addand) Then
  strCriteria = strCriteria & "[" & addand(i)
 Else
  strCriteria = strCriteria & "[" & addand(i) & " and "
 End If
Next i

Me!frmIssueSearchQuery.Form.Filter = strCriteria
Me!frmIssueSearchQuery.Form.FilterOn = True
End Sub

Now, the actual analyst text is in table 'Analyst', system text is in table 'System', etc....the only fields in my main table that are text are the StartDate and EndDate. Not sure what the syntax would be....[tblAnalyst]![AnalystName] maybe??

 

[spasticus]

make a query, in design view, using all the tables the fields you want to show are in. use the primary key in you main table and then add the fields you want .. so ..

[primary key] [analyst name] ... etc.
[maintable] [analyst table] ...

then have the record source for you sub form as the query.

 

[sstasiak]

i'm attaching a copy of the relevant tables, forms, queries from my DB so you can review. Please take a look and advise me what I'm doing wrong. I've been trying all suggestions without success.

 

[spasticus]

hey sstasiak,

i made a few changes. it works how you want it to now, i think.

i think the problem you had was down to the users table. no record were showing up because that table is empty. you can add it to the query once you've added some users and it should work.

 

[sstasiak]

spasticus

that's awesome....almost there. the filter is working, but certain combinations of criteria don't filter the correct records. I'll do some testing and post back.

Thanks for the help

 

[Simon_MT]

There is another way. Combine the combi with wildcards:

"[Artist_Surname] like '" & .[Surname] & "*" & "'"

It no longer matter if the combis are empty as not entry will result in:
[Artist_Surname] like "*"

Simon