Incrementing a value in a form

[erbuchan]

I need to increment a value in a form field (and have it enter into a table). AutoNumber would work except I am using it for the Primary Key and need it for other things and you are only allowed one AutoNumber per table. Is there a way to do this? I also want to be able to override the incremented value by typing into the form.

[maximus]

The sample mdb that I have included has a Form called increment:
This appears as a Startup form:
Scope of this Code:

1) Generate Sequence Number
2) if You type a number and then Click generate button the code verifies the number typed and takes the following actions:

a) Typed number less that the last RegID number = Prompt to alert a possible Double entry
b) If more Gives you an option to alter the Sequence e.g. if last RegID =5 but you now want the sequence to start from 100 type 100 in RegID and Click on generate button. You will get Yes and No option. Click yes to change sequence and no to maintain sequence.


3) if you want to begin from a Higher number right from the begining say you want REGID to start from 100 type 100 the first time and you will be given the option to Change sequence. Click Yes sequence will be changed.

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim intIncermentalNumber As Integer
Dim strMsgbx As String
'intIncrementalNumber is the maximum RegID

intIncrementalNumber = IIf(IsNull(DMax("[RegID]", "increment")), 0, DMax("[RegID]", "increment"))

'To check if any Number typed by user
Select Case Me.RegID
Case Is = 0 ' if RegID = 0 then Number sequence if filled in
Me.RegID = intIncrementalNumber + 1
'When RegID>0 this will happen if you type a number

Case Is > 0
'if Typed RegID <=intIncrementalNumber then a possible duplicate entry can be created thus REGID is set to 0
If Me.RegID <= intIncrementalNumber Then
MsgBox "This Number may have been already entered", vbInformation
'if RegID typed is more the Clicking on Yes Keeps your typed ID and sequence is adjusted accordingly if no then original sequence is maintained

ElseIf Me.RegID > intIncrementalNumber Then

strMsgbx = MsgBox("The Last Registration Number is = " & intIncrementalNumber & " . Click Yes to input Number Typed and No to Maintain Sequence", vbInformation + vbYesNo)
Select Case strMsgbx
Case vbYes
Me.RegID = Me.RegID
Case vbNo
Me.RegID = intIncrementalNumber + 1
End Select
End If
End Select

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

[erbuchan]

Thank you for the information. I will take a close look at it to see if it will point me in the right direction.

I was looking for something a little bit different that would generate the next number automatically (similar to AutoNumber) from the value in the previous record when I move to the next screen of the Form but also allow me to override it on those occasions when I need to take more manual control.

[maximus]

Attach the code to the next record, or new record button or even better to the afterupdate event on a field or the form it self. Use your imagination. It is generating the value based on the previous record adding 1 to the Last RegID I guess i expalined it quite clearly.

[maximus]

remember you may have to make minor adjustment to the code. Do you have a save button on your form. Put the code in it.

[erbuchan]

Thanks a lot. This should point me in the right direction.

[maximus]

is your problem solved if yes mark this thread solved

[erbuchan]

I haven't had a chance to get back to this problem since I asked the question (unfortunately it's a low priority) but I think your suggestions should point me in the right direction so I will consider this thread solved. If I have any other problems I will post another question.

Thanks for all your help.

Eric.

[maximus]

then mark this thread solved go to thread tools and select solved.

[Wayne311]

Maximus,

Thanks for the code it has been very helpful...and confusing. When you declare variable "Dim intIncermentalNumber As Integer" and then you make the statement "intIncrementalNumber = " Incremental is spelled differently. In your example it works, in mine it doesn't. Hence the confusion.

Wayne

[maximus]

Well correct the spelling!!!!!!!!!!!!!!!! LOL

if u have declared
Dim intIncrementalNumber

then u sud keep it to intIncrementalNumber.


the spelling mistake doesn't have any effect on the running of the code in this example as u will notice that when I have assigned the value to the variable intIncrementalNumber I have then spelled the variable correctly thereafter:

intIncrementalNumber = IIf(IsNull(DMax("[RegID]", "increment")), 0, DMax("[RegID]", "increment"))

I hope your query has been answered.