Querying for the last record of a table

[Reaper]

I have a form that the user will fill in, then click a command button to print a report (to a create a label)based on the entered and calculated fields.

The report is sourced from a query that needs to select only the last record of the base table. I've been successful at getting it to filter based on the primary key that I pick up from the form. However, this will require several different queries to cover all the forms that will print similar information.

1. Is there are way to use a variable in the Criteria field in the query? The variable can be set in each of the forms in vb.
2. If not, is there a way to get the query to simply return the last record in the table? This might be the simplest solution.

[June7]

Yes, you can use variable (also referred to as input parameter) in Criteria field. Input to the parameter can be by a prompt in the query or reference to a form data control.

Trying to return the 'last' record of a table is tricky and not 100% reliable if there a multiple users adding records.

[Reaper]

That's what I thought. The variable would be the most reliable way to get the record that I want every time. A

I am lost on how to use the variable in the Criteria field of the query. It will be a variable that I set in vb as I leave the form itself to go print the report.

How do I reference a variable in the Criteria field?

Can you give me an example?

[June7]

Can't. Access can't capture VBA variables. VBA can set a textbox on form to the value of the variable. Then the query can refer to the textbox but form must remain open until the query runs. Why do you need query to read this value? What is really happening? Is this query the RecordSource for a form or report and you are really wanting to open form/report filtered to this value?

[Reaper]

Its a simple form that allows the user enter data about a pallet of materials, Weight, Commodity type, Date, etc. After the form is filled out he will click a command button to print the data to a label that will then be applied to the pallet. The data is kept in a table for inventory transaction tracking.

There are 4 departments that need the functionality, so I was trying to make the form generic so I wouldn't have to build 4 different forms. I'll try your suggestion.

[June7]

I avoid setting dynamic parameters in queries. I use WHERE CONDITION of DoCmd.OpenForm (or OpenReport) to pass variable criteria to filter form/report when it opens. Like:

DoCmd.OpenReport "report name", , , "DeptID=" & variable

Variable can be a variable in the code or reference to a field of the open form's RecordSource.

[Reaper]

That worked perfectly. Thanks.

[boblarson]

Just an FYI-

Using DLAST will get the last record that has been added to the database table. You can also get the last record added based on some additional criteria such that, if you have the username stored in the record of the table you could find the last record entered for a particular user. Now IMPORTANT TO NOTE - Using LAST (not DLAST) in a grouping query will not work as it returns a random record. But DLAST will work and I've subjected it to many tests and it has not failed me yet.

If using a DAO recordset object you can query out @@IDENTITY from the table just after a record is added too, to get the ID of the record that was just added using the recordset (helpful if you don't know what the autonumber will be but need it right away afterwards).