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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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
 
 
Start new topic
Replies
theDBguy
post May 19 2019, 09:57 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,290
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 05:18 PM