Update table record after each report prints

[Jarekb]

I have a list of employees that need to be sent a letter if they meet a certain criteria. The report looks at a query and prints out a letter for each employee that needs one. I want to be able to update their record with the date the letter was printed on. I think I have the code to get this to work, but I'm not sure what event to put it under since when I run the report only one record updates out of the 3 that should have updated (as an example).

Dim sqlRP As String

sqlRP = "UPDATE PROCESS SET Process.DT_NOTICE_LETTER = Date() WHERE EMPLID ='" & [EMPLID] & "'"
CurrentDb.Execute sqlRP

Thanks

 

[Remou]

You can use the SQL from the report and run the update after the code that opens the report.

 

[Jarekb]

Thanks, that was an easier solution then what I was trying to do. Right now this is how the code works.
1. click on Print Letter button
2. report is opened and prints out all records based on a query.
3. After open report code, the sql is executed that updates the tables with the same where condition as the report.

I used this for my other letters and one other button. The only thing I don't like is that each button and report has a separate query that does the same thing. For example the notice letter print button has update sql thats has the same where clause as the report query. So if I have to change something I've got to go to several places and make sure all the where clauses are the same.

 

[Remou]

You can build the Where clause in its own procedure.

 

[Jarekb]

Can you give me an example of how that would be done using where clause from my first post?

 

[Remou]

Very roughly:

Function GetSQL(blnUpdate, EmplID) As String
If blnUpdate Then
  GetSQL= "UPDATE PROCESS SET Process.DT_NOTICE_LETTER = Date() WHERE EMPLID ='" & EmplID & "'"
Else
  GetSQL= "Select PROCESS.DT_NOTICE_LETTER  From Process Where PROCESS.DT_NOTICE_LETTER=Date() And EMPLID ='" & EmplID & "'"
End If

 

[Jarekb]

Ah, I see what you mean now. I'll try to change over to this, but one question. How would I call a function within a sql query and/or code?

 

[Remou]

You can call functions with SQL, but not such a function as the sketch above. With code, it is simple enough, for example:

[tt]sqlRP = GetSQL(False, Me.EmplID)[/tt]

 

[Jarekb]

Can I send the function to a report with DoCmd.OpenReport? If so I can have select all queries for the reports and part of the click button code could send the where clause.

 

[Remou]

Yes, you can. Why not put something together and post it here, if you have any problems?

 

[Jarekb]

Ok I just started to change over my code to use functions and getting an error when trying to pass a function to DoCmd.OpenReport. The error is:

Run-Time error '2212':
Microsoft Office Access coudln't print your object.

Here's part of the function

Public Function GetSQL(category As String, queryType As String) As String

Select Case category
Case "Notice"
    If queryType = "Button" Then
        GetSQL = "UPDATE Results SET Results.DT_NOTICE_LETTER = Date() WHERE"
    ElseIf queryType = "Letter" Then
        GetSQL = "WHERE "
    End If
    GetSQL = GetSQL + "[DT_HIRE] > #5/31/2006# and (DateDiff('d',[DT_LAST_WORKED],Date()))<35"
    GetSQL = GetSQL + "and (DateDiff('d',[DT_HIRE],Date()))>30 and [DT_Notice_Letter] is null"

And here is the button code

Private Sub NoticeLetter_Click()
    Dim stDocName As String
    
    Select Case MsgBox("Print Notice Letters?", vbYesNo)
    Case vbYes
        stDocName = "NOTICE_LETTER_REPORT"
        DoCmd.OpenReport stDocName, acNormal, , GetSQL("Notice", "Letter")
    
        CurrentDb.Execute GetSQL("Notice", "Button")
        MsgBox ("Noitice Letters Successfuly Printed")
    End Select
End Sub

 

[Remou]

A Where argument used in opening a form or report should not include the word "Where".

You are short of a space here:
[tt] GetSQL = GetSQL + "[COLOR=red yellow] [/color]and (DateDiff('d',[DT_HIRE],Date()))>30 and [DT_Notice_Letter] is null"[/tt]

It is oftern worth while to Debug.Print such statements or to try the function in the immediate window:

?GetSQL("Notice", "Letter")

As it will show up such errors.

 

[Jarekb]

Thanks, after taking out the where part in the function it works fine. I'm having another problem with sending the SQL through the function to DoCmd.TransferText, but I started another thread for that.