Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Whats Wrong With This Code ? - Urgent Help Pls.

    Hi,

    I placed a combo box named CboSearchItem (Based on a query receiving data from table Product_master)) on form to select and bring the record onto the form. It works smoothly.

    But when data in field ProductName contains " mark, it produces following error 3075 :

    Syntax error in string in query expression "ProductName = "Hose White 3/4"'"

    When I replaced all " mark from the data field ProductName, it works fine.
    For example Product Name are as follows:

    Elbow 2 Way
    ABC Pipe 3"
    Shower Pipe 1 1/5 Meter

    Getting all the above record but not the second one because it contains " mark.

    I can not tell my client not to use " while entering the product name. There should be a solution.

    My full code is as follows:

    Dim db As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim fld As DAO.Field

    Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
    "where Productname= " & """" & CboSearchItem & """") ‘ error is here….

    For Each fld In rst1.Fields
    Me(fld.Name) = rst1(fld.Name)
    Next fld

    rst1.Close
    Set rst1 = Nothing

    For-Next loop will bring recordset from table on to the form instead of writing whole bunch of every field names in code

    It runs well when there are no " mark in the data. But produces error 3075 when product name contains “ mark somewhere. I need it to read all the characters contained in the data fields ProductName.

    Your immediate help will be highly appreciated.

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Ashfaque

    How about something like:
    Code:
    "Select * From Product_Master where Productname= '" & Replace(CboSearchItem, Chr(34), Chr(34) & Chr(34)) & "'"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks for the quick response.

    I used your trick but it produced following error;

    Runtime Error 2113

    The Value you entered isn't valid for this field.

    This method also brings all the data from ProdcutName field EXCEPT those contain " mark.

    Please advice.


    Regards,
    Ashfaque
    Last edited by Ashfaque; 07-12-06 at 04:58.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cooo. It's been a while since I played around with this sort of thing. I think my mind has just gone blank. This is ugly but works:
    Code:
    ...WHERE ProductName = """ & Replace(Me.CboSearchItem, Chr(34), Chr(34) & Chr(34)) & """"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Thumbs up

    Yes got it...

    I just played with the code and it worked.


    Set rst1 = CurrentDb.OpenRecordset("Select * From Product_Master " & _
    "where Productname= '" & Replace(CboSearchItem, Chr(39), Chr(34) & Chr(34)) & "'")


    instead of keeping chr(34), Chr(34), & Chr(34) I replaced it as above and thats all.

    Thanks for the tricky help.

    With kind regards
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •