Search form with list box doesn't search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

carriey - 03 May 2005 17:01 GMT

I am extremely green with coding. I have a form/subform that I need the user
to be able to click on the Find Button and have a Search Form open (done)
where they can type in all or part of any number of fields (18 search fields)
and a list of available records will appear in list box and the user can
double click on the correct one to go to that record.

I have searched everywhere on the internet and finally found a free download
that does exactly that so I copied the code and changed all the fields to
reflect what is in my database. It doesn't work! When I debug, I am having
problems with these two lines:

'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 18)

'Pass the SQL to the RowSource of the listbox
Me.List_Results.RowSource = strSQL & " " & strWhere & "" & strOrder

I'm hoping an expert may be able to tell me, is there an error here, or is
there another way of doing this? Thank you!

Reply to this Message

Nikos Yannacopoulos - 04 May 2005 11:16 GMT

Carriey,

See below.

HTH,
Nikos

> I am extremely green with coding. I have a form/subform that I need the user
> to be able to click on the Find Button and have a Search Form open (done)
[quoted text clipped - 9 lines]
> 'Remove the last AND from the SQL statement
> strWhere = Mid(strWhere, 1, Len(strWhere) - 18)

I presume this follows loop which adds each criterion in turn to
strWhere, followed by and AND, right? Well, this line of code truncates
strWhere by the last 18 characters, in order to remove the final AND
(and what goes with it). This may well be correct in the example where
you got it from, but not necessarily in your code! It might well be that
you need to cut off more or less than 18 characters.

> 'Pass the SQL to the RowSource of the listbox
> Me.List_Results.RowSource = strSQL & " " & strWhere & "" & strOrder

This could be fine, or it could not... we have no idea what strOrder is,
and the fact that you don't include a space between strWhere & "" &
strOrder (like you would by adding a space etween the quotes) also looks
suspicious.

> I'm hoping an expert may be able to tell me, is there an error here, or is
> there another way of doing this? Thank you!

Suggestion:
Add the following:

Debug.Print strSQL
Debug.Print strWhere
Debug.Print strOrder

right before the Me.List_Results.RowSource = etc line, so you get the
chance to see the values assigned to them in the immediate window. Are
they what you expected? My guess is not all of them.

If that doesn't help you solve the problem, post back the whole sub code
and the expressions you got in the immediate window for the three variables.

Reply to this Message