Re: Strange

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It sounds like "All" is causing a problem - it is a reserved word. The problem may be in the function buildcritera, where it may not be be putting something in quotes.

Can you post the code for BuildCriteria please?

John


Ray C wrote:

Hi. I have a Form that looks up Customer information via a number of Combo Boxes. The Text Boxes are Bound to the Customers Table and the "find" combo Boxes arte unbound. I use the following External Function to locate the record and set the bookmark to the record so the Text boxes show the information held in the record. I then place the apropriate info into the various "Find" Combo Boxes. I do this with the following code

Public Function util_ShowInfo(frm As Form, strField As String, varInfo As Variant) As Boolean

If IsNumeric(varInfo) Then
strCriteria = BuildCriteria(strField, dbInteger, varInfo)
Else
strCriteria = BuildCriteria(strField, dbText, varInfo)
End If
With frm.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
frm.Bookmark = .Bookmark
frm!cmbFindStoreNo = .StoreNo ' cmbFindStoreNo
frm!cmbFindStoreName = .StoreName ' cmbFindStoreName
frm!cmbFindAccNo = .AccountNo ' cmbFindAccountNo
frm!cmbFindCustomerName = .CustomerName ' cmbFindCustomerName
frm!cmbContractNo = .ContractNo
util_ShowInfo = True
Else
util_ShowInfo = False
End If
End With

End Function

This all works fine EXCEPT when I look up a customer called "All Sorts" in this instance I get the following error :- Run -time Error '7956' The syntax of the sub queryin this expression is incorrect.
This is generated by the following line in the above code :- strCriteria = BuildCriteria(strField, dbText, varInfo)
I can search for a customer called "Allied Dunbar! and find it wth no problem as I can with othe customers in the table but when I search for "All Sorts" I always get this Error.
Even stranger, if I change the information in the Field to "A", it finds the record with no problem. Change the information in the Field to "Al" it finds the record with no problem. Change the information in the Field to "All" and I get an Error but it is slightly different from the one above :- Run-time Error '2431' The Expression you entered contains invalid syntax. Change the information in the Field back to "All Sorts" and I am back to the Message - Run -time Error '7956' The syntax of the sub query in this expression is incorrect .

Does anyone know what could be happening?

Thanks RayC





.

Relevant Pages

  • Re: Strange
    ... Sounds as though you are right, if I Change to Alli Sorts, Alla Sorts, Allx ... The Text Boxes are Bound to the Customers Table and the "find" combo ... Public Function util_ShowInfo(frm As Form, strField As String, varInfo As ...
    (microsoft.public.access.gettingstarted)
  • Re: Strange
    ... Sounds as though you are right, if I Change to Alli Sorts, Alla Sorts, Allx Sorts, it runs without a problem. ... The Text Boxes are Bound to the Customers Table and the "find" combo Boxes arte unbound. ... Public Function util_ShowInfo(frm As Form, strField As String, varInfo As Variant) As Boolean ...
    (microsoft.public.access.gettingstarted)
  • Strange
    ... I have a Form that looks up Customer information via a number of Combo ... The Text Boxes are Bound to the Customers Table and the "find" combo ... Public Function util_ShowInfo(frm As Form, strField As String, varInfo As ... strCriteria = BuildCriteria ...
    (microsoft.public.access.gettingstarted)
  • Re: An incident at the grocery store
    ... The grocer has some fruit lined up in boxes along the sidewalk ... There were about a dozen customers therein ... in reply and left the store, leaving a cloud of attitude behind him. ... Free demo online at www.pmilligan.net/palm/ ...
    (rec.motorcycles)
  • Re: An incident at the grocery store
    ... The grocer has some fruit lined up in boxes along the sidewalk ... There were about a dozen customers therein ... I verbally informed the checkout clerk of the incident. ... in reply and left the store, leaving a cloud of attitude behind him. ...
    (rec.motorcycles)