 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / September 2005code doesn't work on some PC's; does on others
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 > 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 [quoted text clipped - 32 lines] > > 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 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. > Thanks for the tips. The problem, however, isn't with the results of > the wherecondition. There aren't any results because the code just [quoted text clipped - 5 lines] > 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 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. > 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 [quoted text clipped - 3 lines] > 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 > 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 [quoted text clipped - 3 lines] > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.