Putting the last record from a table into a variable

scotribs

New member
Local time
Today, 14:32
Joined
Dec 16, 2007
Messages
8
I have a table with a text string. All I want to do is take the last record from this table in place it into a variable within a module called strfield i.e. strfield = {the last record in the table}
This is probably very basic but I have tried several commands and cannot get the data into the variable. Any help would be greatly appreciated :)
 
There is an action you can carry out on a recordset called GetRows. That might be able to help here, but I'm not sure it is the best alternative.

Why are you doing this? What is the goal here?
 
I have a table with a text string. All I want to do is take the last record from this table in place it into a variable within a module called strfield i.e. strfield = {the last record in the table}
This is probably very basic but I have tried several commands and cannot get the data into the variable. Any help would be greatly appreciated :)

To be pedantic an Access table has no specific order of records so you need to define what you mean by the "last record". Then write a query to obtain that record.
 
I have a customer field for our customer complaints that consist of a 2 digit yeas and 2 characters then an automatic number that when combined will give a unique number. This data is held in a table called RMA numbers.

When the last record in this table is located I have used a bit of code to take the data and make a new entry that will automatically give this format that I am looking for i.e. 07RMA0002. You will see from the code that it is the very 1st section that I can't get the code for.

Dim strfield, newfield, strNumber As String

strfield = ' This would be loaded from field in table
strNumber = Trim(Right(strfield, 3) + 1)
Do Until Len(strNumber) = 3
strNumber = "0" + strNumber
Loop

If Format(Date, "yy") = Left(strfield, 2) Then
newfield = Format(Date, "yy") + "RMA" + strNumber
Else
newfield = Format(Date, "yy") + "RMA" + "001"
End If
 
It's bad practice to store calculated data in a table, but I can see why you might want to when allocating unique reference numbers to complaints.

So, you want to know what the last reference number was so you can calculate what the next should be?

As Rabbie said, the 'last' record is a bit of a grey area. You mean the record that was last added to the table (it's not always going to be the record at the bottom of the table). I'm not sure if Access keeps track of that, you could add a date/time stamp to the records in the table as they are entered but that would be a little too complex I feel (might be worth considering though if, in the future, you might want to know when the complaint was generated).

An un-elegant way to do it would be to create a LookUpTable holding just one record. That record would be the last used number, then in place of

strfield = ' This would be loaded from field in table
strNumber = Trim(Right(strfield, 3) + 1)

Get the number from the LookUpTable, increment it, use the new number to generate your reference number then put the new number in the LookUpTable in place of the old one.
 
Last edited:
I understand the desire for "meaningful" identifiers but over time, they have a tendency to degrade and loose meaning as the various pieces reach overload. A more robust solution is to keep the pieces separate and combine them for display.

So, for example you would store the transaction date and the sequence number separately. Then extract the year and build the string as you need it.
 

Users who are viewing this thread

Back
Top Bottom