Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Execute an INSERT INTO where table is named on a Set statement field

    Hi, All! it has been some time since I've been playing with Access database, I was trying to brush up a little . I have the below event procedure that is giving me debug errors and can't see why it not executing the insert into with the given text content pulled from the Set named expressions...Any guru help is super appreciated!

    Private Sub CurrentCondition_AfterUpdate()

    Dim strSQL As String
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset

    If Not IsNull(Me!CurrentCondition) Then

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("tblOptionsCurrentConditionAction List", dbOpenDynaset)
    Set NameList = RS![ActionList]
    Set TableList = RS![TableInput]
    Set TitleList = RS![TitleField]


    Do While RS.EOF = False

    DoCmd.RunSQL "INSERT INTO " & "'" & TableList & "'" & " (PID, " & " '" & TitleList & "'" & ",CreatedBy,CreatedDate) VALUES (Forms!frmPersonnel!PID," & "'" & NameList & "'" & ",CurrentUser(),Date())"

    RS.MoveNext

    Loop

    Set NameList = Nothing
    Set TableList = Nothing
    Set TitleList = Nothing
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

    Exit Sub

    End Sub

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Like this is rather hard to figure out what is going on. I suggest that you store the SQL statement in a string and Debug.Print the string value. That should allow you to see the SQL being executed and check it for errors.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hey, Ruirib! thanks for the response. My problem is I know after the INSERT INTO you have to state the name of the table, then respective fields where i want the VALUES data to be inserted. I looking for a way how to make the table name be chosen by the SET TableList but is not acknowledging
    " & "'" & TableList & "'" & " as the proper table name; how can that be accomplish? The same goes for the other SET values. When I place a table name and respective fields it works but I want to be able to place the information in a single table and the code copy all required text accordingly.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    A normal variable (not an object) does not need set. Try removing the set and check the values of those 3 variables.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    As ruirib suggested, put the SQL statement into a string variable before you try to execute it, and then do a debug print and copy it into a new query and see what it looks like. I suspect you are having problems with single and double quotes.
    Wendell

  6. The Following User Says Thank You to WendellB For This Useful Post:

    Lastcall (2012-04-25)

  7. #6
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Ok, this is what I got...Everything looks good, in the local window, until trying to execute the RunSQL, there is a "enter parameter value" prompt for the variables already established (TableList, Title List, NameList). It doesnt want to utilize the value of the variables in the INSERT INTO statement. See below:

    Private Sub CurrentCondition_AfterUpdate()


    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim TableList As String
    Dim TitleList As String
    Dim NameList As String


    If Not IsNull(Forms!frmPersonnel!CurrentCondition) Then

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset(NAME:="SELECT * FROM tblOptionsCurrentConditionActionList WHERE Condition = '" & Forms!frmPersonnel!CurrentCondition & "'", Type:=dbOpenDynaset)
    TableList = RS![TableInput]
    TitleList = RS![TitleField]
    NameList = RS![ActionList]

    Do While RS.EOF = False

    DoCmd.RunSQL "INSERT INTO TableList (PID,TitleList,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,NameList,CurrentUser(),Dat e())"

    RS.MoveNext

    Loop

    RS.Close
    Set RS = Nothing
    Set DB = Nothing

    End If


    End Sub

  8. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    That SQL statement is not correct. I don't know why you changed the first version of the RunSQL command, where you were actually getting the value of the variables. Replace the last RunSQL command by your first version for that command.

    If it doesn't work, please assign the SQL to a variable and debug print the value, as it has been suggested before.

  9. The Following User Says Thank You to ruirib For This Useful Post:

    Lastcall (2012-04-25)

  10. #8
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Sorry, I think I got what you all were suggesting; I did the debug print and it is displaying what I would like to insert into the output table; I remove the single quote before executing the runSQL using the REPLACE function. See below, thanks you guys for all the help:

    Private Sub CurrentCondition_AfterUpdate()

    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim TableList As String
    Dim TitleList As String
    Dim strSQL As String

    If Not IsNull(Me!CurrentCondition) Then

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset(NAME:="SELECT * FROM tblOptionsCurrentConditionActionList WHERE Condition = '" & Me!CurrentCondition & "'", Type:=dbOpenDynaset)
    TableList = RS![TableInput]
    TitleList = RS![TitleField]
    Set NameList = RS![ActionList]

    Do While RS.EOF = False

    strSQL = Replace( "INSERT INTO " & "'" & TableList & "'" & "(PID," & "'" & TitleList & "'" & ",CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID," & "'" & NameList & "'" & ",CurrentUser(),Date())","'","",1,4)

    DoCmd.RunSQL strSQL

    RS.MoveNext

    Loop

    Set NameList = Nothing
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

    Exit Sub
    End If

    End Sub


    Below is what the debug print is showing which is what I would like to send to the output table:

    INSERT INTO tblDataIDDocuments (PID,IDType,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,'ID',CurrentUser(),Date())
    INSERT INTO tblDataIDDocuments (PID,IDType,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,'COMPLETE AUDIT',CurrentUser(),Date())
    INSERT INTO tblDataIDDocuments (PID,IDType,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,'DRIVER LICENSE',CurrentUser(),Date())
    INSERT INTO tblDataIDDocuments (PID,IDType,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,'PNOK',CurrentUser(),Date( ))
    INSERT INTO tblDataIDDocuments (PID,IDType,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,'GEAR ISSUE',CurrentUser(),Date())
    INSERT INTO tblDataIDDocuments (PID,IDType,CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID,'PASSPORT',CurrentUser(),D ate())
    Last edited by Lastcall; 2012-04-25 at 17:52. Reason: Update for Final Results

  11. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You do need to remove the single quotes from the table name and the IDtype column.

    Code:
    strSQL = "INSERT INTO & TableList & "(PID,"& TitleList & ",CreatedBy, CreatedDate) VALUES (Forms!frmPersonnel!PID," & "'" & NameList & "'" & ",CurrentUser(),Date())"

Tags for this Thread

Posting Permissions

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