VBA Insert query using form selections

[scor1pion]

I'm trying to perform an INSERT query through VBA.



I make my choices through a form. (The form remains open through the process)



The query works correctly when I run as a standalone query. However, when I try to run through VBA, nothing happens, not even an error message or a pop up warning.



Cannot get the INSERT to ReverUserAccessLogT query to work through VBA

Any ideas?

Private Sub GenerateGroupReports_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim sFolder As String
    
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose your save location"
        .ButtonName = "OK"
        If .Show = -1 Then ' if OK is pressed assign folder name to sFolder variable
            sFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT DISTINCT ManagerName, Prod From GroupManagerForReportQ", dbOpenDynaset)

Do While Not rs.EOF
    temp = rs("ManagerName")
    MyFileName = "Group Access to " & rs("Prod") & " Resources by Manager Report - " & rs("ManagerName") & ".PDF"
    
    DoCmd.OpenReport "GroupManagerR", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
    DoCmd.OutputTo acOutputReport, "GroupManagerR", acFormatPDF, sFolder & "\" & MyFileName
    DoCmd.Close acReport, "GroupManagerR"

    rs.MoveNext
Loop

        DoCmd.SetWarnings False
        
        ' This works INSERT INTO ReverReportDetailsT
        
        DoCmd.RunSQL "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
                "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
                "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
                "FROM GroupManagerForReportQ;"
                
        DoCmd.SetWarnings True  ' I turned warnings back on to see if I would get any messages, nothing.
        
        ' Neither of the below work (Yes, I remove the comment tags)
        ' I've commented out the options I've tried that don't work.  Keeping here so I know what I've already tried
                
        'DoCmd.RunSQL "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
        '        "SELECT DISTINCT MgrSerNum, EmployeeID, UserIDPK, SystemID, Production " & _
        '        "FROM ReverUserIDsForAccessLogTQ;"
        
        'DoCmd.RunSQL "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
        '        "VALUES (""" & MgrSerNum & """, EmployeeID, UserIDPK, """ & SystemID & """, """ & Production & """)" & _
        '        " FROM ReverUserIDsForAccessLogTQ;"
        
        'DoCmd.RunSQL "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
        '        "VALUES (""" & MgrSerNum & """, """ & EmployeeID & """, """ & UserIDPK & """, """ & SystemID & """, """ & Production & """)" & _
        '        " FROM ReverUserIDsForAccessLogTQ;"
        
        'DoCmd.OpenQuery "ReverUserIDsForAccessLogTQ"

Set rs = Nothing
Set db = Nothing

End Sub

[June7]

If fields are number type, don't use delimiters. I prefer apostrophe to doubled quote marks - easier to read.

"VALUES ('" & MgrSerNum & "', " & EmployeeID & ", " & UserIDPK & ", " & SystemID & ", " & Production & ")" & _

Are these references to fields or controls of form? Why use VBA and not just data entry to bound form?

I usually use CurrentDb.Execute then don't need SetWarnings.

Why do you need Eval() function in query?

[kd2017]

I don't immediatly spot any problems with your code. I've made some changes, can you run it and report what, if any, errors you get?

Private Sub GenerateGroupReports_Click()
On Error GoTo ErrHandler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim sFolder As String
    Dim qry As String
    
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose your save location"
        .ButtonName = "OK"
        If .Show = -1 Then ' if OK is pressed assign folder name to sFolder variable
            sFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT DISTINCT ManagerName, Prod From GroupManagerForReportQ", dbOpenDynaset)
    
    If Not (rs.BOF And rs.EOF) Then 'make sure your results actually returned something or you'll get an error
        rs.MoveFirst
        Do While Not rs.EOF
            temp = rs("ManagerName")
            MyFileName = "Group Access to " & rs("Prod") & " Resources by Manager Report - " & rs("ManagerName") & ".PDF"
            
            DoCmd.OpenReport "GroupManagerR", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
            DoCmd.OutputTo acOutputReport, "GroupManagerR", acFormatPDF, sFolder & "\" & MyFileName
            DoCmd.Close acReport, "GroupManagerR"
        
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    
    'DoCmd.SetWarnings False
    
    ' This works INSERT INTO ReverReportDetailsT
    
    'DoCmd.RunSQL "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
    '        "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
    '        "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
    '        "FROM GroupManagerForReportQ;"
            
            
    'use db.Execute rather than RunSQL so you don't have to risk turning warnings off and on
    qry = "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
            "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
            "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
            "FROM GroupManagerForReportQ;"
    db.Execute qry, dbFailOnError
            
    'DoCmd.SetWarnings True  ' I turned warnings back on to see if I would get any messages, nothing.
    
    ' Neither of the below work (Yes, I remove the comment tags)
    ' I've commented out the options I've tried that don't work.  Keeping here so I know what I've already tried
    
    qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
            "SELECT DISTINCT MgrSerNum, EmployeeID, UserIDPK, SystemID, Production " & _
            "FROM ReverUserIDsForAccessLogTQ;"
    db.Execute qry, dbFailOnError
    
    qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
            "VALUES (""" & MgrSerNum & """, EmployeeID, UserIDPK, """ & SystemID & """, """ & Production & """)" & _
            " FROM ReverUserIDsForAccessLogTQ;"
    db.Execute qry, dbFailOnError
    
    qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
            "VALUES (""" & MgrSerNum & """, """ & EmployeeID & """, """ & UserIDPK & """, """ & SystemID & """, """ & Production & """)" & _
            " FROM ReverUserIDsForAccessLogTQ;"
    db.Execute qry, dbFailOnError
    
    'DoCmd.OpenQuery "ReverUserIDsForAccessLogTQ"
    
ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description & vbCrLf & vbCrLf & "SQL:" & vbCrLf & qry, , "Error #" & Err.Number
    Resume ExitHandler
    
End Sub

[scor1pion]

If fields are number type, don't use delimiters. I prefer apostrophe to doubled quote marks - easier to read.

"VALUES ('" & MgrSerNum & "', " & EmployeeID & ", " & UserIDPK & ", " & SystemID & ", " & Production & ")" & _

Are these references to fields or controls of form? Why use VBA and not just data entry to bound form?

I usually use CurrentDb.Execute then don't need SetWarnings.

Why do you need Eval() function in query?

Are these references to fields or controls of form? Why use VBA and not just data entry to bound form?

System ID and Production are fields on the form. Yes form remains open.

MgSerNum = Text
EmployeeID = Number
UserIDPK = Number
SystemID = Number, however its from a text box on the form
Production = Number, however its from the radio button selection box on the form


I find that if I don't use Eval() function when getting data from a form in a query used in VBA they don't work. I have tried with and without, no difference in outcome.


I also just tried the CurrentDb.Execute Didn't work either, again, not even a warning or error message.

[scor1pion]

I don't immediatly spot any problems with your code. I've made some changes, can you run it and report what, if any, errors you get?

Private Sub GenerateGroupReports_Click()
On Error GoTo ErrHandler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim temp As String
    Dim sFolder As String
    Dim qry As String
    
    ' Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose your save location"
        .ButtonName = "OK"
        If .Show = -1 Then ' if OK is pressed assign folder name to sFolder variable
            sFolder = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT DISTINCT ManagerName, Prod From GroupManagerForReportQ", dbOpenDynaset)
    
    If Not (rs.BOF And rs.EOF) Then 'make sure your results actually returned something or you'll get an error
        rs.MoveFirst
        Do While Not rs.EOF
            temp = rs("ManagerName")
            MyFileName = "Group Access to " & rs("Prod") & " Resources by Manager Report - " & rs("ManagerName") & ".PDF"
            
            DoCmd.OpenReport "GroupManagerR", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
            DoCmd.OutputTo acOutputReport, "GroupManagerR", acFormatPDF, sFolder & "\" & MyFileName
            DoCmd.Close acReport, "GroupManagerR"
        
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    
    'DoCmd.SetWarnings False
    
    ' This works INSERT INTO ReverReportDetailsT
    
    'DoCmd.RunSQL "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
    '        "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
    '        "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
    '        "FROM GroupManagerForReportQ;"
            
            
    'use db.Execute rather than RunSQL so you don't have to risk turning warnings off and on
    qry = "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
            "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
            "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
            "FROM GroupManagerForReportQ;"
    db.Execute qry, dbFailOnError
            
    'DoCmd.SetWarnings True  ' I turned warnings back on to see if I would get any messages, nothing.
    
    ' Neither of the below work (Yes, I remove the comment tags)
    ' I've commented out the options I've tried that don't work.  Keeping here so I know what I've already tried
    
    qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
            "SELECT DISTINCT MgrSerNum, EmployeeID, UserIDPK, SystemID, Production " & _
            "FROM ReverUserIDsForAccessLogTQ;"
    db.Execute qry, dbFailOnError
    
    qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
            "VALUES (""" & MgrSerNum & """, EmployeeID, UserIDPK, """ & SystemID & """, """ & Production & """)" & _
            " FROM ReverUserIDsForAccessLogTQ;"
    db.Execute qry, dbFailOnError
    
    qry = "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
            "VALUES (""" & MgrSerNum & """, """ & EmployeeID & """, """ & UserIDPK & """, """ & SystemID & """, """ & Production & """)" & _
            " FROM ReverUserIDsForAccessLogTQ;"
    db.Execute qry, dbFailOnError
    
    'DoCmd.OpenQuery "ReverUserIDsForAccessLogTQ"
    
ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
ErrHandler:
    MsgBox Err.Description & vbCrLf & vbCrLf & "SQL:" & vbCrLf & qry, , "Error #" & Err.Number
    Resume ExitHandler
    
End Sub

All of this works, I have no errors with this section

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT DISTINCT ManagerName, Prod From GroupManagerForReportQ", dbOpenDynaset)

Do While Not rs.EOF
    temp = rs("ManagerName")
    MyFileName = "Group Access to " & rs("Prod") & " Resources by Manager Report - " & rs("ManagerName") & ".PDF"
    
    DoCmd.OpenReport "GroupManagerR", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
    DoCmd.OutputTo acOutputReport, "GroupManagerR", acFormatPDF, sFolder & "\" & MyFileName
    DoCmd.Close acReport, "GroupManagerR"

    rs.MoveNext
Loop

        DoCmd.SetWarnings False
        
        ' This works INSERT INTO ReverReportDetailsT
        
        DoCmd.RunSQL "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
                "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
                "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
                "FROM GroupManagerForReportQ;"

This is the code not working

        DoCmd.RunSQL "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
                "SELECT DISTINCT MgrSerNum, EmployeeID, UserIDPK, SystemID, Production " & _
                "FROM ReverUserIDsForAccessLogTQ;"

[kd2017]

I find that if I don't use Eval() function when getting data from a form in a query used in VBA they don't work. I have tried with and without, no difference in outcome.


I also just tried the CurrentDb.Execute Didn't work either, again, not even a warning or error message.

Before anything I would validate all the input forms. Something like:

If Not IsNumeric(Me.textbox) Then Err.Raise 1001, , "The textbox ain't right. Go back and try again... ya jabroni "

Test for all possible dumb user input, including empty textboxes.

Have you tried converting your textbox values to numbers (longs) like this instead of eval?:

CLng(Me.textbox)

[kd2017]

All of this works, I have no errors with this section

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT DISTINCT ManagerName, Prod From GroupManagerForReportQ", dbOpenDynaset)

Do While Not rs.EOF
    temp = rs("ManagerName")
    MyFileName = "Group Access to " & rs("Prod") & " Resources by Manager Report - " & rs("ManagerName") & ".PDF"
    
    DoCmd.OpenReport "GroupManagerR", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
    DoCmd.OutputTo acOutputReport, "GroupManagerR", acFormatPDF, sFolder & "\" & MyFileName
    DoCmd.Close acReport, "GroupManagerR"

    rs.MoveNext
Loop

        DoCmd.SetWarnings False
        
        ' This works INSERT INTO ReverReportDetailsT
        
        DoCmd.RunSQL "INSERT INTO ReverReportDetailsT ( MgrSerNum, SystemID, ProductionID, ReportSelectionID ) " & _
                "SELECT DISTINCT GroupManagerForReportQ.MgrSerNum, GroupManagerForReportQ.SystemID, " & _
                "GroupManagerForReportQ.Production, ""2"" AS Expr1 " & _
                "FROM GroupManagerForReportQ;"

This is the code not working

        DoCmd.RunSQL "INSERT INTO ReverUserAccessLogT ( MgrSerNum, EmployeeID, UserID, SystemID, ProductionID ) " & _
                "SELECT DISTINCT MgrSerNum, EmployeeID, UserIDPK, SystemID, Production " & _
                "FROM ReverUserIDsForAccessLogTQ;"

Okay. Try the code I posted and let me know what happens.

[scor1pion]

Okay. Try the code I posted and let me know what happens.

Just tried, same results as my code. Reports generate, but table does not get updated. No error or warning messages/pop ups.

[kd2017]

System ID and Production are fields on the form. Yes form remains open.

MgSerNum = Text
EmployeeID = Number
UserIDPK = Number
SystemID = Number, however its from a text box on the form
Production = Number, however its from the radio button selection box on the form.

When to referring to the controls on the form where the code is being called use Me.TextboxName like this:

qry = "SELECT * FROM sometable WHERE EmployeeID = " & Me.EmployeeID

As June mentions don't use quotes around number fields.

[Welshgasman]

What does the Select Distinct on its own produce?

[kd2017]

Just tried, same results as my code. Reports generate, but table does not get updated. No error or warning messages/pop ups.

You may want to post your database in a zip file for review.

[scor1pion]

Hi All, thank you for your assistance. It boils to down to my own stupidity...like most things.

I had the code in the wrong section...so wasn't even being called to run.

Once I noticed and put it in the correct section I did encounter an error. All I had to do was switch the actual query from and Append query to a Select query and now all works good.

Thank You All for your time!!!