Results 1 to 7 of 7
  1. #1
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237

    String in VBA code

    Hi,
    I am having a syntax error with this VBA code:

    Dim lngBookID As Long


    Set db = CurrentDb()


    lngBookID = Me.txtpkBookId


    .txtpkBookId is a control on the Form (frmBooks).


    strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) " _
    & "(SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries), _
    & FROM qryBooks WHERE "[pkBookId] = " & lngBookID;"

    db.Execute strSQL, dbFailOnError

    It seems there is a syntax error in the strSQL.

    any suggestions please?


    Khalil

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Remove parens from around SELECT query and last comma and fix position of quotes. Don't need semi-colon.
    Code:
    strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) " _ & "SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries " _ & "FROM qryBooks WHERE [pkBookId] = " & lngBookID
    Now you will also have to run a DELETE action.

    However, instead of 'moving' records, recommend just flagging a field IsActive and apply filter criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    June7,

    With all the courage, I prefer the "moving" than the "flagging" and the "hiding" than the "deleting". We can keep details for the "deleting" when we are "moving" a record, with just "flagging" we can't.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Khalil,

    What exactly are you trying to do in simple terms?
    You have given a little SQL but no context????

  5. #5
    Khalil Handal is offline Competent Performer
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    237
    Hi,
    Thank you all for the reply.
    I have piles of book in a library and I want to remove all the books that do not exist on the shelves from the database. I am afraid that latter on I might find a book that was deleted from the database; in a case like this I will move it again to the database until all books are arranged in order.
    The syntax error is fixed but I am now having a type mismatch error message 13.

    Below is the complete code:
    Private Sub cmdDeleteRecord_Click()


    On Error GoTo ProcError


    Dim db As DAO.Database
    Dim strSQL As String
    Dim lngBookID As Long


    Set db = CurrentDb()


    lngBookID = Me.txtpkBookId
    ' lngBookID = [Forms]![frmBooks]![txtpkBookId] 'Or lngBookID = Me.txtpkBookId <--If on the same form.

    strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) " _
    & "SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries " _
    & "FROM qryBooks WHERE [pkBookId] = " & lngBookID




    Debug.Print "lngBookID: " & lngBookID
    Debug.Print "strSQL =" & vbCrLf & strSQL

    db.Execute strSQL, dbFailOnError



    ExitProc:


    Set db = Nothing


    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description & ", " _
    & vbCritical, "Message Box Title Text"
    Resume ExitProc


    End Sub

    Any ideas for the run time error 13???

    Khalil

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    For future, please post lengthy code between CODE tags to retain indentation and readability.

    Which line errors?

    I still recommend you just flag record instead of moving. I don't understand what you mean by can't keep details.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like you have a control, txtpkBookId, on a form that can be NULL. The variable in the code, lngBookID, is declared as a Long.....
    A Long cannot hold a NULL, thus the run time error 13.

    I am guessing that a book id would never be 0 so I made a couple of changes in your code.
    Code:
    Private Sub cmdDeleteRecord_Click()
        On Error GoTo ProcError
    
        Dim db As DAO.Database
        Dim strSQL As String
        Dim lngBookID As Long
    
        Set db = CurrentDb()
    
        'variable cannot contain a NULL, using the NZ() function to change the NULL to Zero
        lngBookID = Nz(Me.txtpkBookId, 0)
        ' lngBookID = [Forms]![frmBooks]![txtpkBookId] 'Or lngBookID = Me.txtpkBookId <--If on the same form.
    
        'check if there is a book id entered in the control
        If lngBookID = 0 Then
            MsgBox "Missing Book ID"
        Else
            'lngBookID <> 0
            strSQL = "INSERT INTO tblDeletedBooks (pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber,"
            strSQL = strSQL & " CallNumberNum, CallNumberText, NumberOfPages, YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries) "
            strSQL = strSQL & " SELECT pkBookId, BookTitle, Subtitle, ISBNNumber, DateRegistered, EditionNumber, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages,"
            strSQL = strSQL & " YearPublished, MemComments, fkcategoryId, fkformatId, fkPublisherId, fkLanguageId, fkSeries "
            strSQL = strSQL & " FROM qryBooks "
            strSQL = strSQL & " WHERE [pkBookId] = " & lngBookID
    
            Debug.Print "lngBookID: " & lngBookID
            Debug.Print "strSQL =" & vbCrLf & strSQL
    
            'execute the SQL
            db.Execute strSQL, dbFailOnError
        End If
    
    ExitProc:
        Set db = Nothing
        Exit Sub        '<<-- added this line - otherwise you would have fallen into the ProcError and been in an endless loop
    
    ProcError: 
        MsgBox "Error " & Err.Number & ": " & Err.Description & ", " & vbCritical, "Message Box Title Text"
        Resume ExitProc
    
    End Sub
    The lines in BLUE are the lines I modified/added

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  2. Replies: 5
    Last Post: 09-18-2013, 06:44 PM
  3. Replies: 7
    Last Post: 05-28-2013, 09:11 AM
  4. Replies: 2
    Last Post: 09-11-2012, 12:34 PM
  5. Search for a String in VBA code.
    By dandoescode in forum Access
    Replies: 3
    Last Post: 06-21-2012, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums