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

    Unanswered: Recordset reading code help

    Hi,

    Hopefully someone from the forum would help me.

    Thru a form textbox , I would like to compare / check a record set in a tbl called Product_master if it is available in table. I could compare it in below way.

    Set rst = CurrentDb.OpenRecordset("Select * From Product_Master " & _
    "where CtnBarcodeNumber= '" & Replace(TxtSearchByBarcode, Chr(39), Chr(39) & Chr(39)) & "'")

    Please note Chr(n) I used to avoid special character line ' ; " that might had inetered in the data.

    This is easily working but the barcode number I am searching for could in any one of below three columns of the table.

    1. CtnBarcodeNumber (may be null or holding another barcode number)
    2. PktBarcodeNumber (may be null or holding another barcode number)
    3. PcBarcodeNumber (may be null or holding another barcode number)

    Can someone please help me out here?

    Appreciate your response in this regard.
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    revist your physical design so that the barcode is only stored in one column
    or
    make the query into an OR

    safeBarCode=Replace(TxtSearchByBarcode, Chr(39), Chr(39) & Chr(39))
    Set rst = CurrentDb.OpenRecordset("Select * From Product_Master " & _
    "where CtnBarcodeNumber= & chr$(34) & safeBarCode & chr$(34) & _
    "OR PktBarcodeNumber = & chr$(34) & safeBarCode & chr$(34) & _
    "OR PcBarcodeNumber = & chr$(34) & safeBarCode & chr$(34)

    of the two the first is a more robust solution
    it may well be the same product to the end user, but if its packed in different quantities then its a different product. The fact that you may have to duplicate much (if not most) of the product detail they are different products

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks healdem,

    Your query is asking to define safebarcode variable. Do you think it should be difned as Dim safebarcode As (?)

    safeBarCode=Replace(TxtSearchByBarcode, Chr(39), Chr(39) & Chr(39))
    Set rst = CurrentDb.OpenRecordset("Select * From Product_Master " & _
    "where CtnBarcodeNumber= & chr$(34) & safeBarCode & chr$(34) " & _
    "OR PktBarcodeNumber = & chr$(34) & safeBarCode & chr$(34) " & _
    "OR PcBarcodeNumber = & chr$(34) & safeBarCode & chr$(34) ")

    I have added " and put closed parenthesis at last as it was not there before.

    Producces Run-time error 3075
    Syntax error in (missing expression)
    CtnBarcodeNumber= & chr$(34) & safeBarCode & chr$(34) " & _
    "OR PktBarcodeNumber = & chr$(34) & safeBarCode & chr$(34) " & _
    "OR PcBarcodeNumber = & chr$(34) & safeBarCode & chr$(34) ")


    Where might have gone wrong?
    With kind regards,
    Ashfaque

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

    Thumbs up

    I little bit played with it and it worked perfectly healdem. Thanks for your guidelines.

    Dim safebarcode As Double

    safebarcode = Replace(TxtSearchByBarcode, Chr(39), Chr(39) & Chr(39))

    Set rst = CurrentDb.OpenRecordset("Select * From Product_Master " & _
    "where CtnBarcodeNumber = '" & safebarcode & "'" & _
    "or PktBarcodeNumber = '" & safebarcode & "'" & _
    "or PcBarcodeNumber = '" & safebarcode & "'")
    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
  •