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?

I create 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

I assume your "memberID" field is not Autonumber.

if "memberID" is text (string)

=cLng(nz(Dmax("memberID","tableWhere_memberID_is"),"0"))+1

Thanks Arnelgp ...

No .. 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

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.

Thanks Arnelgp ...

Much appreciated.

Not a multi user database.. only my use.

Thanks again.

Alexander

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.

I hope you get what I mean coz I am not really well versed in English.

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

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

Many thanks for the detailed help Arnelpg

Working on it now and will report back

Alexander

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

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.

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

This post has been edited by Alexander: Mar 4 2012, 09:35 AM

Happy coding (IMG:style_emoticons/default/thumbup.gif)

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

No problem Glenn ... I never use them (autonumbers) if I can help it.

I will read the link and thanks.

Alexander

Yes I read. Surely Microsoft must have done something about it.

This post has been edited by arnelgp: Mar 4 2012, 11:43 AM

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