user defined functions

[talytech]

OK .. I'm going to try to exlplain what I want to do first. Here goes ... I have a report that I want to send via email in a snapshot format using the sendObject method when the user clicks the button "Send Form".

.. the criteria for the report is based on a field on my form. ([requestId] = forms!myFrm![requestId]) ... So as long as the form is open, the report opens with the correct data.

Now ... what I have done works the first time I try it, but if I make a change to the form and click the "Send Form" button, it sends the report but the report doesn't reflect the changes. Now if I click on the report to open it from the database window, it opens with the correct data.

My button on my form is calling a user defined function.


Function submitRequest()
Dim frm As Form
Dim Name, strHrRep, recip As String
Dim numdays As Long


Set frm = form_frmUpdate

Name = "Tee Lewis"
strHrRep = "Fred.Flintstone@test.com"

If frm.NewRecord = True Then
If numdays <= 90 Then
recip = "Barney.Rubble@test.com"

'DoCmd.SendObject _
acSendReport, "rptBI_screening_request_form", _
acFormatSNP, recip, , , _
"CONFIDENTIAL: Background Screening Request For: " & Name, _
"Please find attached, the request cover sheet and the " & _
"employment application for " & Name & Chr(13) & Chr(13) & _
"Submitted by Human Resources: " & strHrRep, -1
'Call Read_HR

ElseIf numdays > 90 Then
recip = "Wilma.Flintstone@test.com"

'DoCmd.SendObject _
acSendReport, "rptBI_screening_request_form", acFormatSNP, _
recip, , , "CONFIDENTIAL: Background Screening Request For: " & Name, _
"Please find attached, the request cover sheet and the " & _
"employment application for " & Name & Chr(13) & Chr(13) & _
"Submitted by Human Resources: " & strHrRep, -1
'Call Read_HR
End If

Else
recip = "Betty.Rubble@test.com"

'DoCmd.SendObject _
acSendReport, "rptBI_screening_request_form", acFormatSNP, recip, , , _
"CONFIDENTIAL: Background Screening Request For: " & Name, _
"Please find attached, the request cover sheet and the " & _
"employment application for " & Name & Chr(13) & Chr(13) & _
"Submitted by Human Resources: " & strHrRep, -1

'Call Read_HR
End If
End Function



My button on my form calls the above function. If I put that code behind my button then it works fine. I can cancel the email and make changes to the record and send again and the report reflects the changes.

But if I call that function, the report shows the right data the first time but if I choose to cancel the email and make a change to the record and send again, the report shows the same data as the first time.

Any ideas ... I hope this is clear .. its the only way I can explain it.

Edited 17-Jul-07 by geekgirlau. Reason: insert line breaks

[geekgirlau]

I don't have a solution for your issue as yet, but you could restructure slightly as below:

Function submitRequest()
Dim frm As Form
Dim Name, strHrRep, recip As String
Dim numdays As Long


Set frm = form_frmUpdate

Name = "Tee Lewis"
strHrRep = "Fred.Flintstone@test.com"

If frm.NewRecord = True Then
If numdays <= 90 Then
recip = "Barney.Rubble@test.com"

ElseIf numdays > 90 Then
recip = "Wilma.Flintstone@test.com"
End If

Else
recip = "Betty.Rubble@test.com"
End If

' DoCmd.SendObject _
' acSendReport, "rptBI_screening_request_form", _
' acFormatSNP, recip, , , _
' "CONFIDENTIAL: Background Screening Request For: " & Name, _
' "Please find attached, the request cover sheet and the " & _
' "employment application for " & Name & Chr(13) & Chr(13) & _
' "Submitted by Human Resources: " & strHrRep, -1
' Call Read_HR
End Function


Untested, but perhaps one approach might be opening a preview of the report, send it, and then close the report. This may force Access to requery the data source.

By the way, I've taken the liberty of editing your post to add line breaks (very annoying to read the code without them) and replace the email addresses with dummy emails.

[talytech]

thank you for your reply. But what I did was put all the code in one procedure instead of calling functions and it worked. For some reason, the report act like it was stuck in que. But thanks again

[geekgirlau]

My pleasure - don't forget to mark this thread as "Solved" (use the Thread Tools option at the top of the page).