Solved DMax not working - wont update in form or table (1 Viewer)

Local time
Today, 10:44
Joined
Sep 23, 2021
Messages
32
Hi all,

I am trying to set up sequential numbering for my CustomerID field in tblCustomer (first and highest table in database). Much as I would love to let it stay as an autonumber, I have been directed by management that CustomerIDs must be sequential with no gaps. As such I have established a separate autonumber field as my primary key in tblCustomer. I want/need to use field CustomerID as my foreign key in subsequent tables.

I have tried multiple ways to set this up using DMax with no luck. What I am looking for is:

1. Use opens 'Add Customer' form. All fields are blank.
2. Person types surname/firstname/date of birth. On confirmation that this combination is not already in database, generate max user ID +1 in CustomerID field on form.
3. On click 'save' button, save all fields to tblCustomer.

Only one person at a time will be using this database, so I don't have any concerns with clashing data entry resulting in duplicates/error.

Any help is appreciated!
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:44
Joined
Sep 21, 2011
Messages
14,038
Show what you have tried? :(
 
Local time
Today, 10:44
Joined
Sep 23, 2021
Messages
32
I am regretting not documenting! I was just trial and erroring following through with other similar responses on the forums, most recent try was:

[on click 'save' button] Me.CustomerID = Nz(DMax("CustomerID", "tblCustomers", 0) + 1

It didnt update the table and the textbox in the form remained blank.

I'm quite new to VBA so apologies if I've made some errors, I am watching YouTube vids when I can and saving up for some courses!
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:44
Joined
Sep 21, 2011
Messages
14,038
That will not work, I would have thought?
Try
Code:
? Nz(DMax("CustomerID", "tblCustomers"), 0) + 1
in the immediate window.
Report back what that shows.

Start simple and then increase the complexity.
So I would start with just the DMax(), then use NZ().

That way you would not get the syntax wrong?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:44
Joined
May 7, 2009
Messages
19,169
you can also use a "pre-made" customer numbers (customerNos table).
you only need to get number not yet "used"
 

Attachments

  • Customerdb.accdb
    532 KB · Views: 291

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:44
Joined
Sep 12, 2006
Messages
15,613
If you already have an autonumber field and it's all working, I would not change that. Just fill the (new) sequential customerid in the customers table, and use that where the bosses expect to see it. If customerID is your autonumber field, then use a different field name for the sequential number. eg CustomerIDSeq

I don't understand why it isn't working though.
Can you step through - maybe the code isn't getting called at all because of a logic error.

I would store the value in the beforeupdate event of the record.

Code:
if nz(customeridseq,0)=0 then
    customeridseq = nz(dmax("customeridseq","tblcustomers"),0) +1
end if
 
Local time
Today, 10:44
Joined
Sep 23, 2021
Messages
32
Heres a deidentified copy. Please excuse errors, I had to strip a LOT of fields out from both tables and forms.

I do appreciate that data shouldnt be duplicated across tables HOWEVER please note that we are NOT approved to complete all data analysis in Access - this needs to be exported to excel. My concern is that if names at the very least arent included in each exported datasheet, there is a high risk that the data analyser in the team wont understand how these are linked to ProgramID, CaseID and EventID. I will endeavour to improve this over time and reduce data duplication but I am on a short contract.
 
Last edited:
Local time
Today, 10:44
Joined
Sep 23, 2021
Messages
32
If you already have an autonumber field and it's all working, I would not change that. Just fill the (new) sequential customerid in the customers table, and use that where the bosses expect to see it. If customerID is your autonumber field, then use a different field name for the sequential number. eg CustomerIDSeq

I don't understand why it isn't working though.
Can you step through - maybe the code isn't getting called at all because of a logic error.

I would store the value in the beforeupdate event of the record.

Code:
if nz(customeridseq,0)=0 then
    customeridseq = nz(dmax("customeridseq","tblcustomers"),0) +1
end if
IT WORKED IT WORKED THANK YOU THANK YOU!!!

It is currently displaying '0' until I click my save button. If I move this this to the AfterUpdate of the Surname textbox, will this have the same effect, or does it need to be in the form events?

EDIT: Tried it and answered my own question, thank you!!!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:44
Joined
Sep 12, 2006
Messages
15,613
I'm glad you got it working.

Because an ID number of zero is not valid, and a number >0 is valid, it will set the value once only.
The possible issue is at what point a new record becomes "permanent", and at what point you might abandon the entry.

If two users are trying to set up accounts at the same time, you want them both to get a valid number.

So if you obtain the next number for an uncommitted new record, would a second user get the same next number or a different nextnumber? He needs to get a different number. It depends on the time your new record gets fully committed to the table.

But, if you then decide to scrap your entry. your number gets wasted, and you get a gap in the sequence.

Hence you need to be sure the timing of the "new number" process works correctly. Generally the best way is to do this at a point after which the record will not or cannot be rejected by the user, and gets committed to the table. If you won't reject (and delete) a record after you update the surname then you should be OK, as long as the new record gets committed to the table at that point. I don't know without testing whether you need the whole record to be updated in order to achieve this.

Consistent updates is always an issue in multi-user systems. It's the same thing if two users try to update records simultaneously, as opposed to saving a new record. You can use locking strategies, but that introduces other problems. Access users a strategy called optimistic locking, which is really "no locking" at all. Access protects the update by re-reading the record to see if it changed since you started. The key point is that you can have unlimited users reading records, but you can only have a single user writing a record change, so users locking records or tables can often prevent other users working. If you have multiple writers, you need to consider the possibility of a clash, and an inconsistent update.

It's worth reading wikipedia on these topics.
 
Last edited:
Local time
Today, 10:44
Joined
Sep 23, 2021
Messages
32
I'm glad you got it working.

Because an ID number of zero is not valid, and a number >0 is valid, it will set the value once only.
The possible issue is at what point a new record becomes "permanent", and at what point you might abandon the entry.

If two users are trying to set up accounts at the same time, you want them both to get a valid number.

So if you obtain the next number for an uncommitted new record, would a second user get the same next number or a different nextnumber? He needs to get a different number. It depends on the time your new record gets fully committed to the table.

But, if you then decide to scrap your entry. your number gets wasted, and you get a gap in the sequence.

Hence you need to be sure the timing of the "new number" process works correctly. Generally the best way is to do this at a point after which the record will not or cannot be rejected by the user, and gets committed to the table. If you won't reject (and delete) a record after you update the surname then you should be OK, as long as the new record gets committed to the table at that point. I don't know without testing whether you need the whole record to be updated in order to achieve this.

Consistent updates is always an issue in multi-user systems. It's the same thing if two users try to update records simultaneously, as opposed to saving a new record. You can use locking strategies, but that introduces other problems. Access users a strategy called optimistic locking, which is really "no locking" at all. Access protects the update by re-reading the record to see if it changed since you started. The key point is that you can have unlimited users reading records, but you can only have a single user writing a record change, so users locking records or tables can often prevent other users working. If you have multiple writers, you need to consider the possibility of a clash, and an inconsistent update.

It's worth reading wikipedia on these topics.
Another big thankyou for such a detailed response Dave. Luckily in this case there is only ever one person at a time entering data but I have 100% taken this on board. Hopefully future projects will be ground up to allow me to implement these learnings a bit better!
 

Users who are viewing this thread

Top Bottom