Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    trying to update an Access field (VB6/Access 97)

    I need to stop an order from duplicating while a VB process is running. I decided to add a field to the Access table it's looking at to determine the serial number, which would, hopefully, only let the process get this serial number once ( then I could prevent duplicate orders from coming out).

    Here's my code:

    Public Function VerifySerialNumber(sDocumentNbr As String, sDocumentLin As String, sDocumentPkg As String) As Boolean
    Dim bFound As Boolean
    Dim strFind As String

    strFind = "SELECT * FROM LABEL_SERIAL WHERE SHPMNT_NO = '" & sDocumentNbr & "' and LN_NO = '" & sDocumentLin & "' and PKGNO = '" & sDocumentPkg & "' and NODUP = 0 "
    Set rstVISIBAR = dbVISIBAR.OpenRecordset(strFind, dbOpenSnapshot, dbReadOnly)
    If Not rstVISIBAR.BOF Then
    bFound = True
    sSerialNbr = rstVISIBAR!SERIAL_NO
    rstVISIBAR!NODUP = -1

    VerifySerialNumber = bFound
    End If

    End Function

    I know this isn't right, but I keep getting the error message that the table is read-only and I don't know how to change it so it will change the NODUP field to "Yes". I've tried playing around with the dbVISIBAR.OpenRecordset options, but can't change it so it updates.

    Any suggestions? <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Carpy Diem, it&#39;s .

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: trying to update an Access field (VB6/Access 97)

    Hi Peggy,
    Try opening a dynaset, not read-only and you'll need something like the following:
    rstVISIBAR.edit
    rstVISIBAR!NODUP = -1
    rstVISIBAR.update
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: trying to update an Access field (VB6/Access 97)

    Thanks, Rory ! It works! Just need to test it a few more times and we'll be good to go. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Carpy Diem, it&#39;s .

Posting Permissions

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