Printing Form Record Number on a Report (Access 20

Topic

New Reply

Access 2003:

My users want to print the record number of a record as shown in the navigation area on the bottom of a form on a report. I explained to them that this number will vary as records are inserted and deleted in the table. They understand this but want to see the current record number on the report.

I set up VBA code in the Detail | Print event of the report as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim lngrecordnum As Long

lngrecordnum = Forms![Donor Form].CurrentRecord
Text29 = lngrecordnum

End Sub

I’m getting the message “Microsoft Access can’t find the form Donor Form referred to in a macro expression or Visual Basic code” when the form is not open. When the form is open, I don’t get that message but, instead, I get the number 1 for all records.

I don’t want the form to be open when they run this report. The form name is spelled correctly. What am I doing wrong?

TIA.

Reply | Quote

Replies

The expression Forms![Donor Form].CurrentRecord requires that the form be open. “Forms” is the forms collection which is the collection of all open forms.

The current record as shown in the navigation buttons, is not actually a property of the record itself, but shows its place in the current recordset behind the open form.
So if you reopen the form with a filter in place, the record number will be different then. If you display the records in a different order, the numbers will be different again.

So if you want to print a number on the report that means anything at all, add an autonumber field to the table and print that. That will never change.

Reply | Quote

Hey, this looks like a friendly place. I’m thinking this is a follow-up. If it would be better in a new thread, jet tell me so.

The Access 2000 report I’m concerned about has an Autonumbered field which is protected. (reverse engineering: is not marked in the display as one users can edit and cursor never goes there – I haven’t figured out yet how this is done).

Presently, the macro that OpenReport on a Report does not include the protected fields.
I need the AutoNumber to be searched and reported.

So, I’m looking for hints, links, or good advice.

Reply | Quote

Welcome to Woody’s Lounge!

An AutoNumber field can never be edited. When designing a form, you can decide not to display the AutoNumber field at all, or you can include a text box bound to the AutoNumber field. If you set the Tab Stop property of the text box to No, the user cannot go to the text box using the Tab key.
You can also include a text box bound to the AutoNumber field on the report.

What exactly do you mean by “I need the AutoNumber to be searched”?

Reply | Quote

On the Nicely Behaving Access database, I can put my cursor in the field (bound to the AutoNumber) labelled Task Number and use the binoculars icon. The Find dialog comes up ready to search in that field.
When I search in the whole database I get good results.

In the Weird database, I can’t put my cursor in the field, so the Find dialog doesn’t list that field.
Even searching the whole database does not find the Task Number.

Thanks for the question. I need to put aside my frustration (was told the Weird one was cloned from the Nicely Behaving one, and only cosmetic changes were made).

Reply | Quote

Open the form in the “Weird” database in design view.
Is there a text box bound to Task Number? If so, make sure that its Visible property is set to Yes, its Locked property to No and its Enabled property to Yes.
If there is no text box bound to Task Number, activate the Field List (View | Field List) and drag Task Number from the list to a convenient location on the form.
When you switch to form view, you should now be able to click in Task Number and use the Find button (the binoculars).

Reply | Quote

You were so right. The Enabled Property needs to be Yes for the text box.

I’ve checked the change and saved it – the folks trying to use this are happy.

THANKS.

Reply | Quote

If they just want a sequential number for each record in the report, place a text box in the detail section of the report with the following properties::
Control Source: =1
Running Sum: Over All

Reply | Quote

Thanks for the replies. I’ve decided to go with the autonumber approach. The database was not designed optimally (my fault) and I now need a unique identifier for each record. I’m sure the autonum approach will work for my requirements.

John, your explanation of why the record numbers are not a record property makes perfect sense. Thanks for clarifying that.

Reply | Quote