strSQL in Search Form

[Londa Sue]

Hello,

I've a search form and am getting a strSQL = BuildWhereString error. I
don't know why and would like some help in resolving. (You're great at
that!)

The form is a search form. Users can search on one of three (or any
combination) of criteria: System, DocumentType, and Status. These are combo
boxes. The form is built on qry_record2. When the search button is clicked,
an error for strSQL is generated "Variable not defined". Here is the code
for BuildWhereString:

Private Function BuildWhereString() As String
Dim strWhere As String
Dim varItemSel As Variant

On Error Resume Next

' ... build "csi system" criterion expression
If Len(Me.cboSystem.Value & "") > 0 Then _
strWhere = strWhere & "ComputerName='" & Me.cboSystem.Value & "' And "

' ... build "document type" criterion expression
If Len(Me.cboType.Value & "") > 0 Then _
strWhere = strWhere & "ComputerName='" & Me.cboType.Value & "' And "

' ... build "status" criterion expression
If Len(Me.cboStatus.Value & "") > 0 Then _
strWhere = strWhere & "ComputerName='" & Me.cboStatus.Value & "' And "

' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))


BuildWhereString = strWhere
Exit Function
End Function
-------------
Here is the code for cmd_search:

Private Sub cmd_search_Click()
On Error GoTo Err_cmd_search_Click

DoCmd.Hourglass True

' move focus to clear button
Me.cmd_clear.SetFocus

' build sql string for form's RecordSource
strSQL = BuildWhereString
strSQL = "SELECT * FROM " & strMainformRecordSource & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"

Me.RecordSource = ""
Me.RecordSource = strSQL

Call SetVisibility(True)

DoCmd.Hourglass False

End Sub
-------
I'm also not getting all the results in a linear fashion. A query may have
four matches, but the form details will only show one. Any ideas why?
(Well, apart from programming, that is.)

Thank you,

Londa Sue

 

[Mark Andrews]

Couple of points:
1. The best way to debug is to use debugging, did you try putting a break
point and just stepping thru the code?
Just press F9 on a line early in the sub cmd_search_Click()
and the code will stop and then you can press F8 to step through the code
until it breaks and you will know what line is the problem.
Also did you put Option Explicit at the top of the module (to force you to
define all variables etc...)?

2. As a guess after looking at it quickly

You probably don't want single quotes around the variables when you are
building the string
(unless you know for certain you will never get a quote in the variable).
However that's not causing the error.

The lines similar to:

If Len(Me.cboSystem.Value & "") > 0 Then _
strWhere = strWhere & "ComputerName='" & Me.cboSystem.Value & "' And "

should be changed to
if (NZ(Me.cboSystem.Value,"") <> "") then
strWhere = strWhere & "ComputerName='" & Me.cboSystem.Value & "' And "

because is the value is Null and you try and use (Null & "some string") you
will get an error
That doesn't sound like your error either.

The line:
On Error GoTo Err_cmd_search_Click

is looking for "Err_cmd_search_Click"
I don't see that label? That could be the error.

I also don't see "strSQL" defined in the Click event?
That could be the error.

On:

I'm also not getting all the results in a linear fashion. A query may
have
four matches, but the form details will only show one. Any ideas why?
(Well, apart from programming, that is.)

I'm not sure what you mean so I can't give an answer. Maybe fix the first
problem and then post again
with a better description of the second problem.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[Londa Sue]

You're marvelous. I'd obviously looked and looked and...overlooked the
strSQL for the search command. That solved that one for me.

As for the second prob, the results are supposed to show in a list in the
details section of the form. What I am getting is the correct number of
matching records, but they are listing in rows. I have to click the arrow on
the bottom of the form to move to the next record. It is desirable that
users see all matching records on one screen (as long as screen size allows).
A list box without being a list box, so to speak (at least that is what K.
Snell's sample database looks like).

Thank you,

LS

 

[Mark Andrews]

always use Option Explict in every module

if you are using Access2007 checkout the free CRM template on my site:
http://www.rptsoftware.com for an example of simple filtering.
or the more advanced CRM template for lots of tricky filtering senerios.

if you are using an earlier version of access you could have the comboboxes
used for the filter
in the main form and a subform showing in datasheet or continuous form view

Not sure exactly how your form is setup right now, but experiment with
continous form view and
search for some examples. I don't know what K Snell's sample db is but it
is probably using continuous form view
if I had to guess.

HTH,
Mark

 

[Ken Snell MVP]

As Mark notes, be sure that you've set Default View property for the form to
Continuous Forms.