Solved Open record after save click (1 Viewer)

vanzie

Member
Local time
Today, 15:38
Joined
Aug 23, 2020
Messages
42
Hi everyone

Need help with something...

I have an unbound form that saves records. What I want to know is if it's possible to be able to directly view the record in a report once I've clicked save? The reason for this is because I do daily reports and my database helps keep records should my managers request a quarterly report or specific date. Once I click save, I want to view the record and export it to pdf to email them the report.
My save button VBA gives me a confirmation message once the record is saved but if I can add something directly after for e.g. "Would you like to view the report?" (Yes/No) and if I click yes, it will open the report to that specific record.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:38
Joined
May 7, 2009
Messages
19,175
do you have Autonumber field (ID) in the table, add it if you don't.
after saving the record, you can look for the last ID.

private sub button_click()
'save to table code here
'...
'after saving ask
if msgbox ("Would you like to view the report?", vbQuestion+vbYesNo) = vbYes then
DoCmd.OpenReport "theReportName", acViewPreview,, "ID=" & Dmax("ID", "theTableName")
end if

end sub
 

vanzie

Member
Local time
Today, 15:38
Joined
Aug 23, 2020
Messages
42
I have auto number in the table yes but the form is unbound. If I follow your method, it shows all the records instead of the specific one I saved
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:38
Joined
May 7, 2009
Messages
19,175
i edited my post, sorry, see there is now "ID = " & DMax("id", "theTableName")

you replace ID with your Autonumber field name.
 

vanzie

Member
Local time
Today, 15:38
Joined
Aug 23, 2020
Messages
42
Don't know what I'm doing wrong but it still shows all the records instead of one.

I've set the cycle option in my reports properties to 'Current Record' instead of 'All Records'
 

vanzie

Member
Local time
Today, 15:38
Joined
Aug 23, 2020
Messages
42
nevermind... I found the problem. The "ID = " & DMax("id", "theTableName") was in the filter clause and not the where condition of my VBA.

Thank you, Arnel. Your code works
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:38
Joined
May 7, 2009
Messages
19,175
ok, you missed the other comma!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:38
Joined
May 7, 2009
Messages
19,175
instead of counting the commas, you can explicitly fill the parameters of OpenReport:

docmd.OpenReport ReportName:="thereport", View:=acViewPreview, WhereCondition:="[ID]=" & DMax("ID", "theTable")
 

vanzie

Member
Local time
Today, 15:38
Joined
Aug 23, 2020
Messages
42
One more question

Say I want to email the report as pdf. I add a button and under on click, I set the procedure as Macro Builder. Using the EmailDatabaseObject command, can I include the date of that record in the subject line?

e.g. the subject is "Ninja Report for Date: " and then add the date displayed of that record?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:38
Joined
May 7, 2009
Messages
19,175
i don't think macro can do it for you.

use Code Builder instead and google the correct syntax for:

DoCmd.SendObject
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:38
Joined
May 7, 2009
Messages
19,175
sample:
Code:
Private Sub commandEMail_Click()
    Dim id As Long
    Dim dte As Date
    '* email
    
    'check the report, if open close it
    If SysCmd(acSysCmdGetObjectState, acReport, "theReportNameHere") <> 0 Then
        DoCmd.Close acReport, "theReportNameHere"
    End If
    
    '* get the last ID
    id = DMax("id", "Thetable")
    
    '* get the date of that id
    dte = DLookup("[dateField]", "Thetable", "id=" & id)
    
    
    '* open the report hidden
    DoCmd.OpenReport ReportName:="theReportNameHere", View:=acViewPreview, WhereCondition:="id=" & id, WindowMode:=acHidden
    
    '* email the report
    DoCmd.SendObject acSendReport, "theReportNameHere", acFormatPDF, "receiver@gmail.com", , , "Ninja Report - " & Format(dte, "dd-mmm-yyyy"), "message here"
End Sub
 

vanzie

Member
Local time
Today, 15:38
Joined
Aug 23, 2020
Messages
42
You're the man Arnel.

Thank you for the sample. Was searching for this but couldn't find any. Will give it a try
 

Users who are viewing this thread

Top Bottom