finding last account (1 Viewer)

Winterwolf73

New member
Local time
Today, 08:15
Joined
Jul 3, 2020
Messages
26
I am creating a form where the user can add new customers to the table. As I was creating the form, I realized there is no was to know what the next available account number will be. I initially tried the "go to last record" macro. Well I quickly learned that is not going to work without the table being open (not what I want to do.) So I started the research to find a better method. I found everything from DLookUp to DMax to DMin. From what I have read these really don't fit the job.
What I would like to do is when the "Find Account" button is clicked a mgsbox appears with the last used account number. Unless there is a way to automatically create the next available account number when the "add new customer" button in clicked.

Any help would be GREATLY appreciated.
 

Isaac

Lifelong Learner
Local time
Today, 06:15
Joined
Mar 14, 2017
Messages
8,738
You haven't specified what would be the logic for creating a new account number?
You can create code to test if newrecord and if so, populate an account number in the form's bound control
 

Micron

AWF VIP
Local time
Today, 09:15
Joined
Oct 20, 2018
Messages
3,476
What you need depends on what the sequence and the data looks like. If it's a mix of alphanumeric that is one thing. If just numbers, that's different. If they must be sequential that requires a certain approach. And so on. You can also generate a numeric list (e.g. using Excel) and dump that into a table of available numbers, but you will have to maintain it.
More info would help. Maybe some sample data for a start.
 

Winterwolf73

New member
Local time
Today, 08:15
Joined
Jul 3, 2020
Messages
26
No no no. I will apologize for how I started this. I think I put to many thoughts into one thread. I have a field in my customer table called Account Number. For example, if number 20201 and 20202 are the last two used. When I click on the "Last Account Number Used" button I would like a msgbox to pop up saying "Last Account Number was 20202".

All of my account number are just numbers; no letters.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:15
Joined
May 7, 2009
Messages
19,175
you can ofcourse use DMax() to get the last (presumable) Account No.
or you can have a table that saved the "last" account number you used (on new record).
 

Attachments

  • lastUsed.zip
    33.7 KB · Views: 110

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:15
Joined
Feb 19, 2002
Messages
42,981
Something you probably haven't thought about yet is that when you concatenate two "numeric" values and either is not fixed in width, they will not sort the way you think they should. For example.
20201
202011
202012
202013
20202

I understand that people like human readable identifiers so creating one is file. However, I never use them as the primary key. I always use an autonumber as the PK and consequently as the FK in all relationships. I create a unique index on the human ID and the human ID is always multiple fields so that I don't have to parse the number when working with it. So, I would use two fields. One to hold IssueYear and the second to hold SequenceNum. This will also force the records to sort as you expect them to because they are numeric. You can print them concatenated if you want to.
 

Mike Krailo

Well-known member
Local time
Today, 09:15
Joined
Mar 28, 2020
Messages
1,030
If the Account Number is indeed based off of YYYY + 000 where 000 is the sequence number formatted to three digits, then here are some functions that can be placed in a module and later used in your form code. The NextSeq function gets the next account number in the sequence based off of a table that stores the current years sequence numbers called SEQ. Just keep updating the table each time a new account number is created.

Design view of the table SEQ:
1594732275199.png


Code:
'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
'
' Note: This assumes that there will be less than 1000 new accounts created in a year!
'
'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
Dim yyyy As String  'Put this at top of module
Public Function NextSeq()
   'Get the next Account Number in the sequence
   yyyy = Format(Now(), "yyyy")
 
   If DCount("SeqID", "Seq") = 0 Then
      NextSeq = yyyy & "001"
   Else
      'Get the max value of the SeqID column
      NextSeq = yyyy & Format(((DMax("SeqID", "Seq")) + 1), "000")
   End If
End Function

And the function to check the current year in the SEQ table and automatically delete the contents when the new year rolls around.
Code:
Public Function ChkYear(tblName As String)
   yyyy = Format(Now(), "yyyy")
   Dim StoredYYYY As String
   Dim StrSQL As String
   Dim db As Database
   Dim rst As Recordset
   Set db = CurrentDb
   StrSQL = "SELECT TOP 1 " & tblName & ".SeqID, " _
          & tblName & ".DateUsed FROM " & tblName _
          & " ORDER BY " & tblName & ".SeqID DESC;"
   Set rst = db.OpenRecordset(StrSQL)
 
   'Clear table if year in table less than current year
   StoredYYYY = Format(rst!DateUsed, "yyyy")
   If Int(yyyy) > Int(StoredYYYY) Then
      'There must be a new year so clear the table to restart the sequence
      StrSQL = "DELETE " & tblName & ".* FROM " & tblName
      DoCmd.RunSQL StrSQL
   End If
End Function
 

Users who are viewing this thread

Top Bottom