Hi Sue,
I'm having a problem with the first 2 lines of code you gave me:
Option Compare Database
Option Explicit
As John has mentioned earlier, these two lines of code should appear only
once, at the top of each module. If you have not already configured your VBA
editor to automatically include Option Explicit as the second line of code in
all new modules, I highly suggest that you do so now. Here's why:
Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions
...but now I'm getting compile errors for a lot of other methods that
used to work.
This indicates that you are using variables that have not been declared, as
explained in the "gem tip" shown above. You should always declare the
variables that you use in VBA code.
Should I put the code in a new module instead of the class objects' code?
No. The code I provide goes in the form's class module for the applicable
form. As a matter of fact, one cannot use the Me keyword in VBA code that is
in a stand-alone module (ie. a module that is not associated with a form or a
report).
Also, I can see how just having 2 buttons instead of 3 will create a
problem. The Create Report button generates a report with the LAST record's
data the user just entered.
Not really. The code for creating the report generates a report for the
current record. It really has no relation to the last record entered.
In order for this to work, the input needs to be added to the table
before the report is generated.
Correct. The record, whether new or existing, must be in a saved state (ie.
Me.Dirty = False), in order for the report to reflect a change entered by the
user. That's why there is code just prior to opening the report that saves
the record first. It does not matter whether this is a new record, or an
existing record that you were editing.
... but it won't seem intuitive to click on the Get Report button at this
point since there's no data entered on this new record yet.
That's true. So, your print report button should could include the following
modification:
Option Compare Database
Option Explicit
Private Sub cmdPrintReport_Click()
On Error GoTo ProcError
' Save the record first if it has been edited
If Me.Dirty = True Then
Me.Dirty = False
End If
' Print report for current record
DoCmd.OpenReport "YourReportName", View:=acViewNormal, _
WhereCondition:="[RecordID] = " & Me!YourRecordIDControlName
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdPrintReport_Click..."
Resume ExitProc
End Sub
_________________________________
Here is an example that you can insert into the Customers form in the sample
Northwind database. You likely already have a copy of Northwind on your hard
drive. My recommendation is to do a search for this file (Northwind.mdb).
Once you find it, make a copy of the original and place it in your normal
working directory. That way, you leave a pristine copy of the original sample
available.
Option Compare Database
Option Explicit
Private Sub cmdPrintReport_Click()
On Error GoTo ProcError
' Save the record first if it has been edited
If Me.Dirty = True Then
Me.Dirty = False
End If
' Print report for current record
DoCmd.OpenReport "Customer Labels", View:=acViewPreview, _
WhereCondition:="[CustomerID] = '" & Me!CustomerID & "'"
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdPrintReport_Click..."
Resume ExitProc
End Sub
You might notice that I added a bit more to the WhereCondition statement in
this code, versus the code I had previously given you. Notice how I am
surrounding the Me!CustomerID with a pair of single quotes, one just after
the = sign, and one at the end, enclosed in double quotes. The reason this is
necessary is because the primary key, CustomerID, is text. If the primary key
was numeric, then the WhereCondition statement would be like this:
WhereCondition:="[CustomerID] = " & Me!CustomerID
Okay, so when you run this code in Northwind, you should see that it works
for whatever record is your current record. If you make a change to the
customer's address information, the report shows the latest change, because
the record was first saved. However, if you navigate to a new record, and
then attempt to click on the button, you will see the following:
#Error
#Error
#Error
#Error
There is one error for each line of the address info. The reason for this is
that VBA Trim function is being used in the Control Source of each text box
on the report. Trim generates an error when the underlying field is null. We
can fix that with a little bit of code added to the report. Open the report
in design view. Then click on View > Code to open a class module associated
with the report. (You should be seeing Option Explicit as your second line of
code by now). Copy the following code and paste it into this module:
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError
MsgBox "There is no data for the selected criteria.", _
vbInformation, "No Data Available..."
Cancel = True
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Report_NoData..."
Resume ExitProc
End Sub
Now, when you click on the Print Report command button, you should see a
message box informing you that there is no data to print. However....were not
quite done yet. As soon as you click on the OK button of the message box, you
will see the following error:
Error 2501: The OpenReport action was canceled.
To handle this situation, we trap for error number 2501 and ignore it. We
can do this by adding a Select Case .... End Select construct into the error
handler, like this:
Option Compare Database
Option Explicit
Private Sub cmdPrintReport_Click()
On Error GoTo ProcError
' Save the record first if it has been edited
If Me.Dirty = True Then
Me.Dirty = False
End If
' Print report for current record
DoCmd.OpenReport "Customer Labels", View:=acViewPreview, _
WhereCondition:="[CustomerID] = '" & Me!CustomerID & "'"
ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report open cancelled
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPrint_Click event procedure ..."
End Select
Resume ExitProc
End Sub
______________________________________
There is an alternate method of checking for no data without adding a code
module to each and every report. I'll let you investigate this method for
yourself:
Tip #45: Add NoData Event to Access Reports
http://www.fmsinc.com/free/NewTips/Access/accesstip45.asp
(See the downloadable demo associated with this tip).
Currently, everytime the user clicks on the Add Record to Table button, the
record is date and time stamped so the report "knows" which record is the
most recently entered one to create a report for. I want the report to be
generated for all records in descending order.
Are you saying that you want to print a report that includes *all* records,
whenever you click on your Print Report button? That's certainly possible,
but the code that I've been discussing is for printing only the current
record.
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
