UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Next Member Number, Office 2000    
 
   
Alexander
post Mar 4 2012, 07:47 AM
Post #1

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Hello ... just loking for some help on automating my application.
When I create a new member is it possible to look at the last member number eg 345 and automatically put in the membership number box 346?
Ocreate a new member record by clicking a button (I have created this using the wizard) to blank the form.
Does it matter if membership number is Text or a number?
Many thanks
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 07:55 AM
Post #2

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



I assume your "memberID" field is not Autonumber.
If "memberID" is text (string)
=cLng(nz(Dmax("memberID","tableWhere_memberID_is"),"0"))+1
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 07:59 AM
Post #3

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Thanks Arnelgp ...
o .. my membership Number is a text field because in the past we used eg A234 B789 but now just a number although still a text field/
What would the code be if a number field?
Also I presume I add this as the Control Source and not as a macro etc.
Regards
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 08:01 AM
Post #4

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



I assume your "memberID" is not Autonumber field.
If "memberID" is string:
nextNumber = CLng(NZ(DMax("memberID", "TableNameWhere_memberID_Is"), "0"))+1
If numberic (must be long or integer):
nextNumber = NZ(DMax("memberID", "TableNameWhere_memberID_Is"), 0)+1
You should put this on the OnLoad Event of your New member form.
Me.txtNewMemberID = <the above code>
Note however that you cannot use this on a multi user DB.
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 08:07 AM
Post #5

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Thanks Arnelgp ...
uch appreciated.
Not a multi user database.. only my use.
Thanks again.
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 08:12 AM
Post #6

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



If that's the case, then I would suggest using AutoNumber and saving you the headache of maintaining the sequence of this number. If you elect to use Autonumber field, dont delete (on all your tables yet) the old "MemberID" field coz you will use them later to relate back to this old field when you change to ID's of the other tables using "MemberID" field.
hope you get what I mean coz I am not really well versed in English.
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 08:14 AM
Post #7

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Thanks Arnelgp ...
One more question ...
Just confused on Me.txtNewMemberID
My field name is Mem_No and my form is called Memebership as is the table. (maybe bad practice ?)
Should I leave control source as Mem_No ? and then add to the On Load event but not sure on the Me.txtNewMemberID
Regards
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 08:23 AM
Post #8

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



Me.txtNewMemberID is just an example, of course you use the actual field name, which in this case is Mem_No
If "memberID" is string:
Mem_No = CLng(NZ(DMax("Mem_No", "TableNameWhere_Mem_No_Is"), "0"))+1
If numberic (must be long or integer):
Mem_No = NZ(DMax("Mem_No", "TableNameWhere_Mem_No_Is"), 0)+1
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 08:32 AM
Post #9

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Many thanks for the detailed help Arnelpg
Working on it now and will report back
Alexander
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 09:02 AM
Post #10

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Hello Arnelgp
I have used this method before to good effect.
Can this code be used to add the next number to the membership number?
If Not Me.NewRecord Then Exit Sub
If Not (IsNull(Me![Mem_No].Tag) Or Me![Mem_No].Tag = "") Then
Me![Mem_No].Value = Me![Mem_No].Tag
End If
Thanks
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 09:31 AM
Post #11

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



Yes!
But you still need to initialized Me!Mem_No.Tag to the last mem_no value + 1 when your New Member for first Opens.
And onte more thing, you have to increment Me!Mem_No.Tag after saving the record:
On your form before update event will be something like this:
If Me.NewRecord then
Me!Mem_No.Tag = Me!Mem_No.Tag + 1
End If
Use this on Before Update of the form not on After Update, coz Me.NewRecord will be false After Update Event of the form fires.
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 09:34 AM
Post #12

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



Thank you Arnelgp
This I can understand and I have it working... great stuff and thanks again.
"Before Update of the form not on After Update" ... yes.. that is what I have and it works.
Regards
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 10:09 AM
Post #13

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



Happy coding humbup.gif" />
Go to the top of the page
 
+
argeedblu
post Mar 4 2012, 10:29 AM
Post #14

UA Forum + Wiki Administrator
Posts: 13,118
From: Sudbury, Ontario, Canada



If I may jump in here, using Autonumbers for anything other than internal data management is not recommended. See Autonumbers - what they Are and Are Not... for a detailed explanation. Briefly stated, gaps in the autonumber series are possible, so they can't be relied on to give the next member number with absolute reliability.
Glenn
Go to the top of the page
 
+
Alexander
post Mar 4 2012, 10:58 AM
Post #15

UtterAccess Ruler
Posts: 2,453
From: Glasgow, Scotland



No problem Glenn ... I never use them (autonumbers) if I can help it.
will read the link and thanks.
Alexander
Go to the top of the page
 
+
arnelgp
post Mar 4 2012, 11:12 AM
Post #16

UtterAccess Ruler
Posts: 1,216
From: somewhere out there...



Yes I read. Surely Microsoft must have done something about it.
Go to the top of the page
 
+
argeedblu
post Mar 4 2012, 12:52 PM
Post #17

UA Forum + Wiki Administrator
Posts: 13,118
From: Sudbury, Ontario, Canada



I don't believe they have. Keep in mind also that the autonumber is created for a new record before the record itself is created. If the user should happen to cancel record creation, the autonumber is not rolled back. Quite frankly, given that Access is intended to be a multi-user database, rolling back a 'cancelled' autonumber could become a rather complex algorithm at the expense of efficient performance.
Glenn
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 18th September 2014 - 06:44 PM