UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Function To Assign Record Number, Access 2010    
 
   
shellp
post May 19 2019, 08:17 AM
Post#1



Posts: 76
Joined: 4-July 11



Hello

I am using Access 2010. I have a db where the main ID number is the client and I want to assign a secondary sequential number (1,2,3 etc.) to the visits per client. The code for the main ID number per client is working well:
QUOTE
Public Function NewCustID() as Long
On Error GoTo NextID_Error
Dim lngNextID as Long
lngNextID=nz(DMax("[ID_Number]","tblClients"))+1
NewCustID=lngNextID
Exit_NewCustID
Exit Function

NextID_Err:
MsgBox "Error " & Err & ": " & Error$
Resume Exit_NewCustID
End Function



Any assistance greatly appreciated. Thanks.
Go to the top of the page
 
theDBguy
post May 19 2019, 09:57 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,730
Joined: 19-June 07
From: SunnySandyEggo


Do you have a Visit_ID field?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post May 19 2019, 11:49 AM
Post#3


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


personally, I wouldn't bother.

if you store client Id, and visit data and time, then you can easily "count" the visits to a client in total, or by day, without needing to number them. The trouble with numbering stuff, is maintaining the number sequence if you need to edit (ie insert or delete entries), or filter the view in a different way.


It really is much easier if you can design the database without sequential numbering of this nature.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
shellp
post May 21 2019, 08:52 AM
Post#4



Posts: 76
Joined: 4-July 11



Thanks, but it is necessary that I number the visits. The plan would be to have a visit_IDNumber.
Go to the top of the page
 
gemmathehusky
post May 21 2019, 11:59 AM
Post#5


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


If you have to have numbering, then I would use a version of the dmax you have now, with additional criteria in the "where" part as required. But it does require that the records get added in the sequence you expect them to be numbered.

sort of
CODE
lngNextID=nz(DMax("[ID_Number]","tblAppointments","clientID = " & myclientID))+1


Otherwise, if you number records 1,2,3,4, and then want to insert a new record as number 2 - then you need to renumber all the numbers to end up with 1,2,3,4,5 correctly sequenced. You will have multiple numbering sequences, one for each client

for reasons of this nature my recommendation not to have numbers at all was not a throwaway observation. Databases don't really require sequential numbering. You can sort records in any order, meaning pre-numbering is somewhat irrelevant. "count" is more germane.

(As far as a report goes, then a report can carry sequential numbering - but there is no guarantee that number 4 today, is the same as number 4 tomorrow, because of the possibility of inserts/deletes.)



--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
shellp
post May 22 2019, 09:19 AM
Post#6



Posts: 76
Joined: 4-July 11



Thanks...but will this also recognize when it is the first visit and assign "1"?
Go to the top of the page
 
gemmathehusky
post May 22 2019, 12:45 PM
Post#7


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


I think you need to try it out and see what happens, so that it works in the way you want it to.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
orange999
post May 23 2019, 06:37 AM
Post#8



Posts: 1,926
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Have you considered recording Date or Date and Time for each Visit? You can sort these and get the correct sequence. You could even temporarily number these with a query.
Perhaps you could describe the need for sequential number assignment and storage.

--------------------
Good luck with your project!
Go to the top of the page
 
Minty
post May 23 2019, 07:41 AM
Post#9



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


I bet it's because "The Boss" likes it on an existing paper form or spreadsheet.

I've had some interesting discussions about this with people before, and it almost always boils down to "We've always done it this way", or "My Boss insists"
As we all know it's a pain in the backside, and adds nothing of any value except possibly in a report, where it would be calculated or generated on the fly.
This post has been edited by Minty: May 23 2019, 07:42 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd July 2019 - 01:07 AM