Access imcrement

[SHABZO]

i have created a form in access that has an autonumber for id and selects the first letter of the last name . I then concatenate the two to gime me example A2.
the problem i have is that ineed to increment all the A as A1,A2 and the all the B as B1,B2 etc.
So if a lastname is Denver it take D4. and the next capture is Smith and i get S5. i want Smith to start with S1

any help will be appreciated.

 

[Galaxiom]

Autonumber won't work.

You need to use a DMax + 1 technique. Search the forum for that term. It has been covered many times in the past.

 

[Pat Hartman]

Think long and hard before committing to an ID that is not an autonumber. In addition to the autonumber primary key, you might want a unique identifier that is more user friendly. If that is what you are talking about then Galaxiom's advice is spot on. Just don't make the mistake of using a user-defined string as a PK.

 

[SHABZO]

Autonumber won't work.

You need to use a DMax + 1 technique. Search the forum for that term. It has been covered many times in the past.

Thank you. I have done the following:

Private Sub btnGenerateRiOD_Click()
Dim strAbbrName As String
strAbbrName = Left(Me.FirstName, 1) + Left(Me.LastName, 3)

Me.GenPersonID = Nz(DMax("GenPersonID", "Contacts*", "Left(FirstName, 1) & Left(LastName, 3) = '" & strAbbrName & "'"), 0) + 1

DoCmd.RunCommand acCmdSaveRecord
End Sub

And in the textbox called test : control source
=Left([FirstName],1)+Left([LastName],1) & [GenPersonID]

What it does it takes the next number . If i want it to specifically add a new number to ab1 and the next client is smith it should give me sm1. now it gives sm2

 

[Pat Hartman]

You are saving the ID as soon as you generate it. The control source should be bound to GenPersonID so it shows the existing record rather than generating a new one.

I would normally not generate the ID until the Form's BeforeUpdate event. If the user needs to see the generated value, you'll need to do it sooner or give the user a save button to force a save and generate the ID without moving to a new record.

 

[SHABZO]

You are saving the ID as soon as you generate it. The control source should be bound to GenPersonID so it shows the existing record rather than generating a new one.

I would normally not generate the ID until the Form's BeforeUpdate event. If the user needs to see the generated value, you'll need to do it sooner or give the user a save button to force a save and generate the ID without moving to a new record.

Sir

I cannot get it to work. I have a button on my form with the follwing
Me.Increment = Nz(DMax("[Increment]", "Contacts", "Left([LastName],1) = '" & Left(Me.LastName, 1) & "'"), 0) + 1
Me.Dirty = False
End Sub

It does nothing. But if i take the follwing
=Nz(DMax("[Increment]","Contacts"),0)+1

It works but obviously it is not taking the next initial and then incrementing. It just increments to the next number irrespective of the Alpha.

Just to recap
1. I have a field in my table called Increment (number longer integer0
2.I have a control on my form ( =Left([LastName],1) & [Increment])
3. I also have a hidden control named Increment bound to Increment in my table.

So i think the last part is causing me stress for two days.
Can i not use the dmax function somewhere else.

I even tried the following
Dim strAbbrName As String
strAbbrName = Left(Me.FirstName, 1) + Left(Me.LastName, 3)

Me.GenPersonID = Nz(DMax("GenPersonID", "Contacts*", "Left(FirstName, 1) & Left(LastName, 3) = '" & strAbbrName & "'"), 0) + 1

DoCmd.RunCommand acCmdSaveRecord
End Sub

Still no increment. just the first letter in ClientCode.

 

[jdraw]

Can you tell us why you want/need this structure?

 

[SHABZO]

Hallo

Basically I need this for patients at the surgery. We are busy doing a complete refilling system and need to allocate a file no to each patient. The files will then be filled by alpha-numeric order. The updates will then be scan to these file nos and can be called up when patient visits again.

At the moment the closest we got is the =Nz(DMax("[Increment]","Contacts"),0)+1

This increments but not to A1,A2,B1,C1 but A1,A2,B3,C4


So if a new patient eg Mr Smith comes in we cannot continue from the last S+INCREMENT. It takes the next increment which is 250 and the last S patient was 99.

 

[Galaxiom]

The DMax need the optional third argument.

DMax("[Increment]","Contacts", "Left([Surname],1)='" & Left(Me.surname,1) & "'")

 

[SHABZO]

This is what i have at the moment but it still does not increment

Private Sub GenerateCode_Click()

Me.Increment = Nz(DMax("[Increment]", "Contacts", "Left([LastName],3)='" & Left(Me.LastName, 3) & "'"), 0) + 1



End Sub

I have a hidden control on my form named Increment bound to the Increment field on my Patients Table. If i check the increment on my table , nothing.

On my form i have a control named ClientCode and the source code is =Left([LastName],3) & Format([Increment],"00")

Only the first part eg SHA is working . Not SHA001

 

[Galaxiom]

Post a cut down database with the form and table and a few sample records.

Compact the database to remove the deleted stuff. Then zip it so you can attach it (because you don't yet have ten posts).

 

[CazB]

try this?

I've left all the fields visible but obviously you can hide them once you've seen what it does

If this works for you.... then I'd also suggest you put in some code so that if someone clicks the button and the patient already has a 'reference', then it doesn't try to generate a new one!

Something like...

Private Sub cmdGenerateCode_Click()
Dim NewID As Integer
    
    Select Case Me.PatientREF
    Case Null
    NewID = Nz(DMax("[IncNo]", "qryPatients", "[IncName]='" & Left(Me.PatientLName, 3) & "'"), 0) + 1
    
    PatientREF = UCase(Left(Me.PatientLName, 3)) & Format(NewID, "000")
    
    Case Else
        MsgBox "This Patient already has a Reference number. Cannot proceed"
    End Select
End Sub

 

[SHABZO]

Thank you and my apologies for the late response. Will give it a try and confirm .

 

[SHABZO]

Thank you all for the help. This is what i eventually did and it works.

There is a warning that is displayed on the top:
Certain contents in the databse has been disabled. option enable.

Once enabled it works perfectly.
My apologies to all.

Is there a way to to get rid of that message. cos now everytime you open the form you have to enable.

 

[Galaxiom]

Is there a way to to get rid of that message. cos now everytime you open the form you have to enable.

Best solution. Databases in the mdb and mde formats can have their code digitally signed. The dialog then asks if the publisher should be trusted. Answering yes ends all warnings for signed documents bearing that signature.

On a domain the signature approval can be distributed through Group Policy so the warning never appears. Additionally, for complete protection a GP can also be set so that any other code won't even offer the option to run.

For Access 2007, put the database into a Trusted Location. Additionally, in Access 2010 the database itself can be trusted in the dialog.

The worst solution is to turn off the warning in the settings.