UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Update Query When Using Loop, Access 2007    
 
   
choli0090
post Mar 2 2018, 05:57 PM
Post#1



Posts: 141
Joined: 18-June 05



Hi Everyone,

I'm running a query that export reports into a PDF. What I'm trying to do is after it creates the PDF (based on the employee id), it will run an update query to put a "Y" in a field called "Rpt_Created", where the employee id matches the same id that the report was just created for

When I try to create the SQL, it brings up a input parameter box with vRepID instead of updating the particular record

Below is the code

Set db = CurrentDB
Set Recset = db.OpenRecordset ("tblRecords")
DoCmd.SetWarnings False
recset.MoveFirst
Do Until recset.EOF

vEmp = recset!EmployeeID
vNm = recset!EmployeeName

DoCmd.outputto acOutputReport "SalesResults", acFormatPDF, "C:\Output\" & vEmp & " " & vNm & ".pdf", False

MySql ="Update [tblRecords] Set [Rpt_Created] = 'Y' Where [EmployeeID] = vEmp"
DoCmd.RunSQL MySql
recset.MoveNext
Loop
recset.Close

End Sub


What am I doing wrong?

Thanks in advance
This post has been edited by choli0090: Mar 2 2018, 06:13 PM
Go to the top of the page
 
DanielPineault
post Mar 2 2018, 06:36 PM
Post#2


UtterAccess VIP
Posts: 6,274
Joined: 30-June 11



Is Rpt_Created a Yes/No data type? Then try




CODE
MySql ="Update [tblRecords] Set [Rpt_Created] = True Where [EmployeeID] = " & vEmp
  db.Execute MySQL, dbFailOnError


Also, the best way to troubleshoot such issues is to first build the query in the query editor and then use the generated SQL statement as the starting point for creating the VBA version. Furthermore, you can also Debug.Print your MySQL variable and copy/paste it into the query editor and test it out to see where the problem lies exactly. Perfect it there.
Go to the top of the page
 
zaxbat
post Mar 2 2018, 06:55 PM
Post#3



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Are you sure that it asks for vRepID. I do not even see ref to vRepID anywhere in the SQL. I'm sure you understand the it asks for a value in a dialog box only when it cannot resolve a variable withing the scope in which it is running.
Go to the top of the page
 
orange999
post Mar 2 2018, 07:06 PM
Post#4



Posts: 1,868
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


As zaxbat said - there doesn't seem to be a vRepID??
Was it vEmp?
Is there a vRepID dimmed somewhere?
Go to the top of the page
 
choli0090
post Mar 2 2018, 07:12 PM
Post#5



Posts: 141
Joined: 18-June 05



The vRepid should be vEmpID. That being said, I did some changing

CODE
Set db = CurrentDB
Set Recset = db.OpenRecordset ("tblRecords")
DoCmd.SetWarnings False
recset.MoveFirst
Do Until recset.EOF

vEmp = recset!EmployeeID
vNm = recset!EmployeeName

DoCmd.outputto acOutputReport "SalesResults", acFormatPDF, "C:\Output\" & vEmp & " " & vNm & ".pdf", False
MySql ="Update [tblRecords] Set [Rpt_Created] = TRUE Where [EmployeeID] = vEmp"
Debug.Print MySql
DoCmd.RunSQL MySql

recset.MoveNext
Loop
recset.Close

End Sub


When I look at the immediate Window it says "Update [tblRecords] Set [Rpt_Created] = Where Employee ID = 5521", which would be correct/valid

However now, it giving me a RunTime 3464 - Data type Mismatch in criteria expression.

The Rpt_Created field is set as Yes/No

Go to the top of the page
 
DanielPineault
post Mar 2 2018, 07:30 PM
Post#6


UtterAccess VIP
Posts: 6,274
Joined: 30-June 11



Have you tried build the same query in the query editor to figure out the proper syntax and field names to compare with your VBA code?




Also, in your code your set the SetWarnings to False, be sure to set them back to True at the end of your procedure! This is one of the reason why you are better off using db.Execute/currentdb.Execute, you don't need to jump through the hoops of truning them off and on.




If your field names are accurate then I'd be trying something along the lines of

CODE
Sub YourSubName()
       On Error GoTo Error_Handler
       Dim db                    As DAO.Database
       Dim recset                As DAO.Recordset
       Dim MySql                 As String
  
       Set db = CurrentDb
       Set recset = db.OpenRecordset("tblRecords", dbOpenSnapshot)
       With recset
           If .RecordCount <> 0 Then
               Do While Not .EOF
                   vEmp = !EmployeeID
                   vNm = !EmployeeName
  
                   DoCmd.OutputTo acOutputReport, "SalesResults", acFormatPDF, "C:\Output\" & vEmp & " " & vNm & ".pdf", False
  
                   MySql = "Update [tblRecords] Set [Rpt_Created]=True Where ([EmployeeID]=" & vEmp & ");"
                   db.Execute MySql, dbFailOnError
                   recset.MoveNext
               Loop
           End If
       End With
  
   Error_Handler_Exit:
       On Error Resume Next
       If Not recset Is Nothing Then
           recset.Close
           Set recset = Nothing
       End If
       If Not db Is Nothing Then Set db = Nothing
       Exit Sub
  
   Error_Handler:
       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
              "Error Number: " & Err.Number & vbCrLf & _
              "Error Source: YourSubName" & vbCrLf & _
              "Error Description: " & Err.Description & _
              Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
              , vbOKOnly + vbCritical, "An Error has Occured!"
       Resume Error_Handler_Exit
   End Sub





I also notice you are updating a field in the same table for which you already open the recordset, so why not update it right then and there by doing something like

CODE
Sub YourSubName()
      On Error GoTo Error_Handler
      Dim db                    As DAO.Database
      Dim recset                As DAO.Recordset
  
      Set db = CurrentDb
      Set recset = db.OpenRecordset("tblRecords")
      With recset
          If .RecordCount <> 0 Then
              Do While Not .EOF
                  vEmp = !EmployeeID
                  vNm = !EmployeeName
  
                  DoCmd.OutputTo acOutputReport, "SalesResults", acFormatPDF, "C:\Output\" & vEmp & " " & vNm & ".pdf", False
  
                  .Edit
                  ![Rpt_Created] = True
                  .Update
  
                  recset.MoveNext
              Loop
          End If
      End With
  
  Error_Handler_Exit:
      On Error Resume Next
      If Not recset Is Nothing Then
          recset.Close
          Set recset = Nothing
      End If
      If Not db Is Nothing Then Set db = Nothing
      Exit Sub
  
  Error_Handler:
      MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
             "Error Number: " & Err.Number & vbCrLf & _
             "Error Source: YourSubName" & vbCrLf & _
             "Error Description: " & Err.Description & _
             Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
             , vbOKOnly + vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit
  End Sub








Lastly, instead of referencing the entire table, you should limit the fields returns to just those you are actually using in some manner, so

CODE
Set recset = db.OpenRecordset("tblRecords")


should become

CODE
Set recset = db.OpenRecordset("SELECT EmployeeID, EmployeeName, Rpt_Created FROM tblRecords;")





Go to the top of the page
 
zaxbat
post Mar 2 2018, 08:48 PM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


It looks like vEmp is a string.....but how about EmployeeID....is it data type number(long) ????? possibly the problem....
Go to the top of the page
 
choli0090
post Mar 3 2018, 04:04 AM
Post#8



Posts: 141
Joined: 18-June 05



I took your advice & built the query & looked at the code & while the format didn't fully work because of the multiple ((( & ))), but the one thing I noticed was my previous code didn't contain quotes around the employee id.

So I changed the following from:

CODE
MySql ="Update [tblRecords] Set [Rpt_Created] = TRUE Where [EmployeeID] = vEmp"


To

CODE
MySql ="Update [tblRecords] Set [Rpt_Created] = TRUE Where [EmployeeID] = """ & vEmp & """""


which did end up working.

I did end up adding the DoCmd.SetWarnings True to the end.

I'll try your code & see how that works. I did have a question on the "Error Handler" section, are those separate lines or all of that typed on one line?

Thanks for the help & responses!

Go to the top of the page
 
zaxbat
post Mar 3 2018, 05:30 AM
Post#9



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: YourSubName" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"




the _ symbol is a line continuation mark , so, yes all one line
Go to the top of the page
 
DanielPineault
post Mar 3 2018, 06:12 AM
Post#10


UtterAccess VIP
Posts: 6,274
Joined: 30-June 11



QUOTE
So I changed the following from:

CODEMySql ="Update [tblRecords] Set [Rpt_Created] = TRUE Where [EmployeeID] = vEmp"

To

CODEMySql ="Update [tblRecords] Set [Rpt_Created] = TRUE Where [EmployeeID] = """ & vEmp & """""

which did end up working.


So that indicates that EmployeeID is a text data field not numeric.


Numeric, Text and Dates all get treated differently.
  • Numeric require no special treatment -> EmployeeID = 4
  • Text require you to surround the value with quotes -> EmployeeID = 'A43T'
  • Dates require you surround the values with hashes -> StartDate = #3/1/2018#

Which bring the question as to why you are using a text data typre for EmployeeId? Typically, this would be an AutoNumber (which is numeric). It's not the end of the world, but not a typical setup.


QUOTE
I'll try your code & see how that works. I did have a question on the "Error Handler" section, are those separate lines or all of that typed on one line?

Yes and no. In a general sense it is all one line and could be rewritten
CODE
MsgBox "The following error has occured" & vbCrLf & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Error Source: YourSubName" & vbCrLf & "Error Description: " & Err.Description & Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl), vbOKOnly + vbCritical, "An Error has Occured!"

but that become unreadable in the VBA editor, so to make it easier to read and work with I choose to break it up and that where the ' _' comes in. It is simply a way of breaking a line up and continue it on the next line in the VBE.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2018 - 06:20 AM