Custom Autonumber Conditional Formatting

  • Thread starter Thread starter drrajnishpatel
  • Start date Start date
D

drrajnishpatel

To,
The EXPERTS, ( Access Dept.)

I am New to access but have managed to make a database for my patients
records , my trouble is the Registration Number of the Patient,( a cloumn in
my database). i want it to generate the unique ID just llike AUTONUMBER, with
the format i need that should be like YYMMnn( YY= present year,MM= present
month and nn = numbers 01 to 99, with reset to 01 at the start of the next
month)the event to trigger the event is SAVE Form button. please can anybody
help? Thanking you in advance
 
I create a table, and store the next available value. In a single user
environment, you can easily retrieve the next number, and update the value
back to 0 at the start of the month.

If you have multiple users, you will need to add more precaution to the
retrieval to prevent dups.
 
Use the Form Current event.

Dim strHighNumber as String

If Me.NewRecord Then
strHighNumber = Nz(DMax("[RegistrationNumber]", "tblPatient", _
"Left([RegistrationNumber, 4) = '" & Format(Date, "yymm") &
"'"),"")
If strHighNumber = vbNullString Then 'No numbers for this year/month
strHighNumber = Format(Date, "yymm01")
Else
strHighNumber = Left(strHighNumber,4) & _
Format(Right(strHighNumber, 2) + 1, "00")
End If
Me.txtRegNumber = strHighNumber
End If
 
To,
Mr.Klatuu
Thanks for your Promt help... I will try to put this into use in my forms,
but hwever, thanks again for the help.... Since i am new i still find
slightly difficult , on my Main form page in access where excatly do i place
this script, is it to be [placed in the properties of the registration field ,
?
rajnish

wrote:
Use the Form Current event.

Dim strHighNumber as String

If Me.NewRecord Then
strHighNumber = Nz(DMax("[RegistrationNumber]", "tblPatient", _
"Left([RegistrationNumber, 4) = '" & Format(Date, "yymm") &
"'"),"")
If strHighNumber = vbNullString Then 'No numbers for this year/month
strHighNumber = Format(Date, "yymm01")
Else
strHighNumber = Left(strHighNumber,4) & _
Format(Right(strHighNumber, 2) + 1, "00")
End If
Me.txtRegNumber = strHighNumber
End If
To,
The EXPERTS, ( Access Dept.)
Click to expand...
[quoted text clipped - 6 lines]
month)the event to trigger the event is SAVE Form button. please can anybody
help? Thanking you in advance
Click to expand...
Click to expand...
 
It goes in the Current Event of the form.
Open the form in design view
select the properties dialog and be sure the form is selected
Go to the Events tab
select the Current event and click the command button to the right of it
with the 3 dots
Select Code Builder
The VBA editor will open with the cursor in the event sub
Put the code in there
--
Dave Hargis, Microsoft Access MVP


drrajnishpatel via AccessMonster.com said:
To,
Mr.Klatuu
Thanks for your Promt help... I will try to put this into use in my forms,
but hwever, thanks again for the help.... Since i am new i still find
slightly difficult , on my Main form page in access where excatly do i place
this script, is it to be [placed in the properties of the registration field ,
?
rajnish

wrote:
Use the Form Current event.

Dim strHighNumber as String

If Me.NewRecord Then
strHighNumber = Nz(DMax("[RegistrationNumber]", "tblPatient", _
"Left([RegistrationNumber, 4) = '" & Format(Date, "yymm") &
"'"),"")
If strHighNumber = vbNullString Then 'No numbers for this year/month
strHighNumber = Format(Date, "yymm01")
Else
strHighNumber = Left(strHighNumber,4) & _
Format(Right(strHighNumber, 2) + 1, "00")
End If
Me.txtRegNumber = strHighNumber
End If
To,
The EXPERTS, ( Access Dept.)
Click to expand...
[quoted text clipped - 6 lines]
month)the event to trigger the event is SAVE Form button. please can anybody
help? Thanking you in advance
Click to expand...
Click to expand...
Click to expand...
 
To,
Mr.Dave Hargis, Microsoft Access MVP,

Thanks i am thankful to you for your kind support.....this will definately
help....

Dr.Rajnish Patel
 
Hi,
I have a similar dilemma that I am working on whereby my company uses a case id number that is alphanumeric wherein the first character will always be a “P”, the second character will be conditional depending on the geographic area, the next two characters will represent the current year, the next character will be a “-“, followed by a four digit number starting with “0001”. (so the result would like something like PE15-0001, next record might be PW15-0002, a third record might be PS15-0003 and so on...)At present we manually enter the case number into a form in our db, but we are in the process of hiring some new employees and we would like to automate this process for ease of operation. I was wondering if a similar solution would be feasible? Or would I need to tackle this type of situation using a form/subform? Any help would be greatly appreciated!
 
Back
Top