creating search box in vba

  • Thread starter Thread starter Preston
  • Start date Start date
P

Preston

Hi,
i am trying to make a search form that users can use to enter criteria
like name, date range, etc and then have the results be queried upon and
displayed in the subform on the bottom of the main form. i was able to get
this to work to some degree using form text boxes as filters for the subform
query but there is more logic that i want to be able to apply. specifically i
want the query to look at beg date and end date and if they're both filled in
to use BETWEEN if only beg then dates >= beg if only end then dates <= end. I
wasn't able to do this in the access query creation boxes so i switched to
vba. I can't seem to get this to work. i am able to create a correct sql
statement and then i use currentdb.querydefs("qry").sql to update the
underlying query. next i run docmd.requery "qryMarkPaid subform" this is the
actual subform object on the main form. it worked for a little while then
stopped. i'm not even sure if this is the best approach. thank you for any
help you can give. i will include the code thus far:

+++++++++++++++++++++++++++++++++++
Private Sub Command14_Click()
'On Error GoTo ErrorHandler
Dim strWHERE As String
Dim strSQL As String
Dim qryMarkPaid As QueryDef


If Not IsNull(Me!txtSONum) Then strWHERE = " tblsvcordersdet.svcorder = "
& Me!txtSONum & " And"
If Not IsNull(Me!cmbTechName) Then strWHERE = strWHERE & "
tblsvcordersdet.techid = " & Me!cmbTechName & " AND"
If Not IsNull(Me!txtBegDate) And Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcordersdet.dateworked BETWEEN #" &
Me!txtBegDate & _
"# AND #" & Me!txtEndDate & "#"
ElseIf Not IsNull(Me!txtBegDate) And IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcordersdet.dateworked >=#" & Me!txtBegDate
& "#"
ElseIf IsNull(Me!txtBegDate) And Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcrodersdet.dateworked <=#" & Me!txtEndDate
& "#"
End If
If Len(strWHERE) <> 0 Then strWHERE = " WHERE" & strWHERE
If Right(strWHERE, 4) = " AND" Then strWHERE = Left(strWHERE,
Len(strWHERE) - 4)
strSQL = "SELECT tblsvcordersdet.svcorder, tblsvcordersdet.dateworked,
tblsvcordersdet.techid," _
& " tblsvcordersdet.[reg hours], tblsvcordersdet.[OT Hours],
tblsvcordersdet.paid FROM tblsvcordersdet" _
& strWHERE

CurrentDb.QueryDefs("qryMarkPaid").SQL = strSQL
DoCmd.Requery "qryMarkPaid subform"
Exit Sub
ErrorHandler:
'show error message and end sub
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub
 
Here's an example with just the date boxes:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
(It's the 2nd method.)

And here's a more comprehensive example showing the date range mixed with
other data types:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Preston said:
i am trying to make a search form that users can use to enter criteria
like name, date range, etc and then have the results be queried upon and
displayed in the subform on the bottom of the main form. i was able to get
this to work to some degree using form text boxes as filters for the
subform
query but there is more logic that i want to be able to apply.
specifically i
want the query to look at beg date and end date and if they're both filled
in
to use BETWEEN if only beg then dates >= beg if only end then dates <=
end. I
wasn't able to do this in the access query creation boxes so i switched to
vba. I can't seem to get this to work. i am able to create a correct sql
statement and then i use currentdb.querydefs("qry").sql to update the
underlying query. next i run docmd.requery "qryMarkPaid subform" this is
the
actual subform object on the main form. it worked for a little while then
stopped. i'm not even sure if this is the best approach. thank you for any
help you can give. i will include the code thus far:

+++++++++++++++++++++++++++++++++++
Private Sub Command14_Click()
'On Error GoTo ErrorHandler
Dim strWHERE As String
Dim strSQL As String
Dim qryMarkPaid As QueryDef


If Not IsNull(Me!txtSONum) Then strWHERE = " tblsvcordersdet.svcorder = "
& Me!txtSONum & " And"
If Not IsNull(Me!cmbTechName) Then strWHERE = strWHERE & "
tblsvcordersdet.techid = " & Me!cmbTechName & " AND"
If Not IsNull(Me!txtBegDate) And Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcordersdet.dateworked BETWEEN #" &
Me!txtBegDate & _
"# AND #" & Me!txtEndDate & "#"
ElseIf Not IsNull(Me!txtBegDate) And IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcordersdet.dateworked >=#" & Me!txtBegDate
& "#"
ElseIf IsNull(Me!txtBegDate) And Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcrodersdet.dateworked <=#" & Me!txtEndDate
& "#"
End If
If Len(strWHERE) <> 0 Then strWHERE = " WHERE" & strWHERE
If Right(strWHERE, 4) = " AND" Then strWHERE = Left(strWHERE,
Len(strWHERE) - 4)
strSQL = "SELECT tblsvcordersdet.svcorder, tblsvcordersdet.dateworked,
tblsvcordersdet.techid," _
& " tblsvcordersdet.[reg hours], tblsvcordersdet.[OT Hours],
tblsvcordersdet.paid FROM tblsvcordersdet" _
& strWHERE

CurrentDb.QueryDefs("qryMarkPaid").SQL = strSQL
DoCmd.Requery "qryMarkPaid subform"
Exit Sub
ErrorHandler:
'show error message and end sub
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub
Click to expand...
 
your search box is actually where i got the idea for the beefed up date
criteria search. thank you for responding. i would like to have this done in
vba if possible. i am successful at getting the query to update to the
required sql statement and am not having any trouble with sql but i am having
trouble getting the query output to show on the subform. i click the search
and it updates the querydef but doesn't show the results on the subform. i
know there is a step missing in between somewhere. maybe i need to close the
query to get it to update then reopen it? i'm not sure. anyway, thank you
again
 
Instead of re-writing the SQL for the query, you could just assign it to the
subform's RecordSource:
Me.[Sub1].Form.RecordSource = strSql
or if the code is running in a different form:
Forms!Form1!Sub1.Form.RecordSource = strSql

If you prefer to re-write the SQL, an alternative to:
DoCmd.Requery "qryMarkPaid subform"
would be:
Forms!Form1!Sub1.Form.Requery
 
Preston said:
i am trying to make a search form that users can use to enter criteria
like name, date range, etc and then have the results be queried upon and
displayed in the subform on the bottom of the main form. i was able to get
this to work to some degree using form text boxes as filters for the subform
query but there is more logic that i want to be able to apply. specifically i
want the query to look at beg date and end date and if they're both filled in
to use BETWEEN if only beg then dates >= beg if only end then dates <= end. I
wasn't able to do this in the access query creation boxes so i switched to
vba. I can't seem to get this to work. i am able to create a correct sql
statement and then i use currentdb.querydefs("qry").sql to update the
underlying query. next i run docmd.requery "qryMarkPaid subform" this is the
actual subform object on the main form. it worked for a little while then
stopped. i'm not even sure if this is the best approach. thank you for any
help you can give. i will include the code thus far:

+++++++++++++++++++++++++++++++++++
Private Sub Command14_Click()
'On Error GoTo ErrorHandler
Dim strWHERE As String
Dim strSQL As String
Dim qryMarkPaid As QueryDef


If Not IsNull(Me!txtSONum) Then strWHERE = " tblsvcordersdet.svcorder = "
& Me!txtSONum & " And"
If Not IsNull(Me!cmbTechName) Then strWHERE = strWHERE & "
tblsvcordersdet.techid = " & Me!cmbTechName & " AND"
If Not IsNull(Me!txtBegDate) And Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcordersdet.dateworked BETWEEN #" &
Me!txtBegDate & _
"# AND #" & Me!txtEndDate & "#"
ElseIf Not IsNull(Me!txtBegDate) And IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcordersdet.dateworked >=#" & Me!txtBegDate
& "#"
ElseIf IsNull(Me!txtBegDate) And Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " tblsvcrodersdet.dateworked <=#" & Me!txtEndDate
& "#"
End If
If Len(strWHERE) <> 0 Then strWHERE = " WHERE" & strWHERE
If Right(strWHERE, 4) = " AND" Then strWHERE = Left(strWHERE,
Len(strWHERE) - 4)
strSQL = "SELECT tblsvcordersdet.svcorder, tblsvcordersdet.dateworked,
tblsvcordersdet.techid," _
& " tblsvcordersdet.[reg hours], tblsvcordersdet.[OT Hours],
tblsvcordersdet.paid FROM tblsvcordersdet" _
& strWHERE

CurrentDb.QueryDefs("qryMarkPaid").SQL = strSQL
DoCmd.Requery "qryMarkPaid subform"
Exit Sub
ErrorHandler:
'show error message and end sub
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub
Click to expand...


I think this kind of logic is simpler:

.. . .
If Not IsNull(Me!txtSONum) Then
strWHERE = " AND svcorder = " & Me!txtSONum
End If
If Not IsNull(Me!cmbTechName) Then
strWHERE = strWHERE & " AND techid = " & Me!cmbTechName
End If
If Not IsNull(Me!txtBegDate) Then
strWHERE = strWHERE & "AND dateworked >= #" _
& Me!txtBegDate & "#"
End If
If Not IsNull(Me!txtEndDate) Then
strWHERE = strWHERE & " AND dateworked <= #" _
& Me!txtBegDate & "#"
End If

If Len(strWHERE) > 0 Then
strWHERE = " WHERE " & Mid(strWHERE, 6)
End If

strSQL = "SELECT svcorder, dateworked, techid," _
& " [reg hours], [OT Hours], paid " _
& "FROM tblsvcordersdet " & strWHERE

Me.[qryMarkPaid subform].Form.RecordSource = strSQL

Exit Sub

Double check that the name of the subform **control** is
[qryMarkPaid subform]

Note that there is no use of the saved querydef.
 
setting the recordsource worked really well. thank you for your help. i was
leary of this at first because i recently dealt with recordsources for
reports and you can't change them unless they're in a certain mode.
thankfully forms don't have this same requirement. thank you again :)
Preston
 
That's right: you can't change the RecordSource of a report after its Open
event.

You can with a form. Tips:
a) Make sure you don't drop any fields from the RecordSource statement, or
let them change data type (particularly for calculated fields.) If you do,
Access can crash.

b) It's always a good idea to explicitly save any edits before assigning a
new RecordSource or filter, e.g.:
If Me.Dirty Then Me.Dirty = False
This will generate a trappable error if the record cannot be saved (e.g.
required field missing.
 
Back
Top