code doesn't work on some PC's; does on others

[benb]

I have form that looks a lot like a search bar for the user to search
for records matching specified criteria (e.g. first names containing
"ben"). For robust results, an intermediary form displays all records
matching the criteria (hmm...sound like a popular web site I know).
Here is the immediate problem: my code (below) works fine on my
computer, but on some of those belonging to my work colleagues (who
will be the end users of the db) it does not seem to work. This is
particularly hard to debug because I have to use other PC's to
duplicate the error. Other controls with similar event code DO work.
The only difference between working controls/event code and nonworking
ones seems to be in the "whencondition" of the DoCmd.OpenForm. Do I
need to add something to the code so that it will work on all PC's?
(Aside: Security is not enable so all users should be "Admin"; the
problem would appear to be related to the PC, not the user.) HELP
PLEASE!

**********code that works************
Private Sub cmdEdit_Click()
DoCmd.OpenForm FormName:="frm_Search", view:=acNormal
Forms![frm_Search].Tag = 1
Forms![frm_Search].txtSubject = ""
End Sub

**********code that doesn't always work**************
Private Sub cmdSearch_Click()
Dim category As String
Dim subject As Variant

subject = Forms!frm_Search!txtSubject.Text
category = Forms!frm_Search!cmbCategory.Text
DoCmd.OpenForm FormName:="frm_Browse", view:=acNormal,
wherecondition:= category & " Like " & Chr$(34) & "*" & subject &
"*" &
Chr$(34)

End Sub

[jimfortune]

benb wrote:

I have form that looks a lot like a search bar for the user to search
for records matching specified criteria (e.g. first names containing
"ben"). For robust results, an intermediary form displays all records
matching the criteria (hmm...sound like a popular web site I know).
Here is the immediate problem: my code (below) works fine on my
computer, but on some of those belonging to my work colleagues (who
will be the end users of the db) it does not seem to work. This is
particularly hard to debug because I have to use other PC's to
duplicate the error. Other controls with similar event code DO work.
The only difference between working controls/event code and nonworking
ones seems to be in the "whencondition" of the DoCmd.OpenForm. Do I
need to add something to the code so that it will work on all PC's?
(Aside: Security is not enable so all users should be "Admin"; the
problem would appear to be related to the PC, not the user.) HELP
PLEASE!

**********code that works************
Private Sub cmdEdit_Click()
DoCmd.OpenForm FormName:="frm_Search", view:=acNormal
Forms![frm_Search].Tag = 1
Forms![frm_Search].txtSubject = ""
End Sub

**********code that doesn't always work**************
Private Sub cmdSearch_Click()
Dim category As String
Dim subject As Variant

subject = Forms!frm_Search!txtSubject.Text
category = Forms!frm_Search!cmbCategory.Text
DoCmd.OpenForm FormName:="frm_Browse", view:=acNormal,
wherecondition:= category & " Like " & Chr$(34) & "*" & subject &
"*" &
Chr$(34)

End Sub

Here's something to try as a first step:

Does it only work when cmbCategory.Value matches the name of a field
contained in the RowSource of the form? Your wherecondition should
probably start with the literal name of the field you are searching on.
Something like:

wherecondition := "Category Like " & Chr(34) & "*" & cmbCategory.Value
& "*" & Chr(34)

or

wherecondition := "Subject Like " & Chr(34) & "*" & txtSubject.Value &
"*" & Chr(34)

or

wherecondition := "(Category Like " & Chr(34) & "*" & cmbCategory.Value
& "*" & Chr(34) & ") AND (" & "Subject Like " & Chr(34) & "*" &
txtSubject.Value & "*" & Chr(34) & ")"

(Note that Like "**" is equivalent to Like "*") Like "*" doesn't get
Null values so I often do something like:

If Not IsNull(cbxCity.Value) Then
strWhere = strWhere & " AND ([City] LIKE '" & cbxCity.Value & "')"
Else
strWhere = strWhere & " AND (([City] LIKE '*') OR ([City] Is Null))"
End If

when building a SQL string if I want a Null in a search textbox to
return all records. I have seen mysterious situations where the SQL
created with "((MyField Like '*') Or (MyField IS NULL))" for multiple
fields failed to return all the records so the most robust approach
when possible is more like:

If Not IsNull(cbxCity.Value) Then
strWhere = strWhere & " AND ([City] LIKE '" & cbxCity.Value & "')"
End If

so that there are no restrictions of any kind when the value in a
search control is Null. You should be able to do something like:

wherecondition := strWhere

For debugging you can put your strWhere in a SQL WHERE clause to see if
it's returning the correct records.

Hope this helps,
James A. Fortune

[benb]

Thanks for the tips. The problem, however, isn't with the results of
the wherecondition. There aren't any results because the code just
doesn't execute at all. To answer your question, cmbCategory is a
combo box limited to the names of four field names so that the code
filters the form to show just records where the value in field
cmbCategory.Value is like txtSubject.Value. I'm hoping that I'll find
some machines just don't have the right libraries enabled, but I doubt
I'll be so lucky. I just don't have many ideas as to why code would
work on some machines and not others.

[jimfortune]

benb wrote:

Thanks for the tips. The problem, however, isn't with the results of
the wherecondition. There aren't any results because the code just
doesn't execute at all. To answer your question, cmbCategory is a
combo box limited to the names of four field names so that the code
filters the form to show just records where the value in field
cmbCategory.Value is like txtSubject.Value. I'm hoping that I'll find
some machines just don't have the right libraries enabled, but I doubt
I'll be so lucky. I just don't have many ideas as to why code would
work on some machines and not others.

I see what you're doing now. I've never seen anyone do it quite like
that but that's O.K. A missing reference would cause code not to run
but it would give some kind of error message. Concentrate on what's
needed to get it working since you have a show-stopper. If that
doesn't work, then For different machines acting differently I would,
as a start, check for missing references, different OS's, different
SP's, different versions of Access, different versions of Jet,
different versions of other DLL's. Try the /decompile switch and
reload a fresh copy of the app. Uninstall AOL 6.0 :-).

James A. Fortune

[benb]

I got it working. It was an issue with the references. But now you
have my curiousity. Is there a better way of doing this? With regard
to the .Value vs. .Text, I used .Text because the combo box would
return the index value (1, 2, 3, etc.) rather than the text value.
Anyway, thanks for your help!

Just to pick your brain a bit, I'm trying to come up with some code to
add to this that will count the number of records matching the criteria
(e.g. records of contact with the first name "Jim"). If there is only
one, I want to circumvent the intermediary form that displays the
search results and just go straight to the full record. Does that make
sense? I know there has to be a way of doing this, but I don't know
how easy/difficult it may be.

[jimfortune]

benb wrote:

I got it working. It was an issue with the references. But now you
have my curiousity. Is there a better way of doing this? With regard
to the .Value vs. .Text, I used .Text because the combo box would
return the index value (1, 2, 3, etc.) rather than the text value.
Anyway, thanks for your help!
I'm glad I was able to help point you toward a solution. Value works
fine even when index values are returned. Google this NG to find out
the subtle difference between .Value and .Text.
Just to pick your brain a bit, I'm trying to come up with some code to
add to this that will count the number of records matching the criteria
(e.g. records of contact with the first name "Jim"). If there is only
one, I want to circumvent the intermediary form that displays the
search results and just go straight to the full record. Does that make
sense? I know there has to be a way of doing this, but I don't know
how easy/difficult it may be.

Bringing up only the full record when there is only one match is an
excellent idea. I think I'll implement that into some of my search
forms tonight. Thanks for the idea. I've used that idea many times
before but not with search forms. I was just being lazy I guess. My
unbound search form generates ad hoc SQL that can be used to count the
number of results. If the RecordCount is one, I can skip the
SearchResults form and go directly to the Info form. Post back if you
have any difficulties.

James A. Fortune

Do you do anything besides play pool? -- Tom

[jimfortune]

benb wrote:

Just to pick your brain a bit, I'm trying to come up with some code to
add to this that will count the number of records matching the criteria
(e.g. records of contact with the first name "Jim"). If there is only
one, I want to circumvent the intermediary form that displays the
search results and just go straight to the full record. Does that make
sense? I know there has to be a way of doing this, but I don't know
how easy/difficult it may be.

This turned out to be pretty simple.

On the Form_Load event of frmSearchResults I now have something like:

Me.RecordSource = GetSearchSQL()
If Me.RecordsetClone.RecordCount = 1 Then
Call cmdSelect_Click
End If

James A. Fortune