Lock field on form after update ??? & generate autonumber

[fibayne]

Hi...I am using the following code to generate an auto number (taken from previous thread on this forum as always ) problem is the button whcih genartes the autonumber will generate a new number each time clicked within the same record I would like the field to lock after it has been updated for the first time, I have found many threads to enable disable and lock fields but non seem to be working the field with grey aout but can still be updated, would anyone be able to point me in the right direction if it is possible


Private Sub cmdGetNumber_Click()
On Error GoTo Err_cmdGetNumber_Click


Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM tblnextnum")
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.close
Set rs = Nothing

Me.CRNum = NextNo


Exit_cmdGetNumber_Click:
Exit Sub

Err_cmdGetNumber_Click:
MsgBox Err.Description
Resume Exit_cmdGetNumber_Click

End Sub

cheers...............Fi

 

[Rabbie]

Why are you using code to generate an Autonumber? If there is an Autonumber filed defined for a table it will be automatically updated as soon as you enter any value into a new record.

 

[fibayne]

Hi Rabbie...the reason I wanted an autonumber is because my DB is to produce documents to raise payments, currently the numbering system of these docs is a 5 digit number starting with 5 ie 50000 etc i wanted the users to be able to create a record and once it is ready to print they would click the getnumber button allowing them to enter the data for the payment and once complete generate its pmt number.....however after your comment I am thinking now perhaps I could use the record ID autonumber eg by adding a text box with a control source of =[DataStoreID]+50000...what do you think ? cheers Fi

 

[Rabbie]

Hi Rabbie...the reason I wanted an autonumber is because my DB is to produce documents to raise payments, currently the numbering system of these docs is a 5 digit number starting with 5 ie 50000 etc i wanted the users to be able to create a record and once it is ready to print they would click the getnumber button allowing them to enter the data for the payment and once complete generate its pmt number.....however after your comment I am thinking now perhaps I could use the record ID autonumber eg by adding a text box with a control source of =[DataStoreID]+50000...what do you think ? cheers Fi

In that case because you can't guarantee that Autonumbers will be contiguous (There can be gaps) I would use something like

Newpaymentnumber = DMAx("PaymentNumber","PaymentsTable") + 1 to get get the next number in the sequence.

 

[fibayne]

Hi Rabbie...I have put this on the OnClick event of the cmd button

Private Sub cmdGetNumber_Click()
Me.CRNum = DMax("NextNum", "tblNextNum") + 1
End Sub

I started the NextNum at 49999 in tblNextNum it works for the first record created then all other it stays at 50000 ?? would you know what is wrong with what I have done thanks again for your help...cheers Fi

 

[fibayne]

Hi Rabbie...been playing about with the code and am guessing that most of the original code should have stayed and that I should have inserted your Dmax solution into it, I have tried but am not sure how and where to add it to make it work...would you be able to help furhter ?? thanks again fi

 

[Rabbie]

Hi Fi

I would use the DMAX expression directly on the Payment table because that way I know I am always getting the next number in the sequence. if you are using your method of having a separate table for next number then you must store the latest number every time you use the existing one. In that case you won't need the DMAX function.

The advantage of what I am suggesting is that you are only looking at numbers actually used in the Payments Table so if for any reason you don't add a record to the table then the next time you will get the correct result.

 

[fibayne]

Hi Rabbie...thanks for your reply, where you say to put the expression directly in the payment table would I put it in for example Validation rule??.. apologies my knowledge is fairly limited I had tried putting it on the query behind the form but that didnt work (the way I had done it anyway) would you be able to help further ??? cheers Fi

 

[Rabbie]

Hi Rabbie...thanks for your reply, where you say to put the expression directly in the payment table would I put it in for example Validation rule??.. apologies my knowledge is fairly limited I had tried putting it on the query behind the form but that didnt work (the way I had done it anyway) would you be able to help further ??? cheers Fi

Hi Fi
How exactly are you adding a record to the Payments table? If you can post that code I will see what I can do.

If the user is typing the new record directly into the form and thence to to the table you could try setting the default value of the field to

DMAx("PaymentNumber","PaymentsTable") + 1

Haven't tested that so I am not sure if it works.

 

[fibayne]

hi Rabbie..data is entered directly to the form, at the moment I have DataStoreID primary key of the underlying table used to give a sequential number by adding a text box which adds 50000 to the ID number, which works well and cant be changed by the user and i have a number that is generated by the code I started with but that isnt locked to that record so each time the cmdGetNumber button is clicked it takes the next consecutive number on the same record, I think taking AutoID number and adding 50000 to it works best but if it can skip numbers...does this happen often ????
I tried DMax as the default value of the field, it takes the last number of the table NextNum but doesnt do +1 to it ...think I am getting in a muddle

 

[Rabbie]

.....does this happen often ????
I tried DMax as the default value of the field, it takes the last number of the table NextNum but doesnt do +1 to it ...think I am getting in a muddle

Not often but it does. Only you can tell if this would be an issue for your system.

Have you tried DMAX()+1 as the field default number?

 

[fibayne]

Hi Rabbie...tried Dmax as the default value of the field, couldnt get it to work I'm afraid, I am going to stick with AutoID +50000 as the easy option, its never going to be a huge DB perhaps 20 new records a week so hopefully the AutoID number will behave thanks again for all your help ..cheers Fi

 

[Rabbie]

Whatever you feel happy with. Good luck. BTW I built a little form to test this and DMAX() +1 worked perfectly. You get jumps in autonumbers if for some reason the record does not get added properly - system crash or such like so you shouldn't have too many problems

 

[fibayne]

Hi Rabbie...any chance you could post the form or do a screen print so i can see what you did and where you put it ... as it does sound like the best way to do it cheers Fi (In Exile in Gibraltar )

 

[Rabbie]

Data table is called Table1
the Form is called Form1 (imaginative aren't I?)

By entering data into the text box you will see the field TestNumber getting bigger.

 

[fibayne]

Yours sure does work.. not getting it to work on mine yet.. but will work on and post back thanks a lot for posting the demo...cheers Fi

 

[Rabbie]

Just post any questions you may have and I'll do my best to help.

 

[fibayne]

Morning Rabbie...works a treat many, many thanks for your help, and on to my next brain teaser cheers Fi