Solved

Modify VBA Code to change Case Number Format

Posted on 2013-12-26
7
515 Views
Last Modified: 2013-12-27
The code below is used to assign a unique identify number to emergency services request record. It is currently formatted as 13-12-1234 representing the current year, the current month and incremental number. the incremental number resets to 0001 at midnight  on the last day of the month. The formatting needs to be changed to capture only the year and the incremental number value with the incremental number value (No Dashes) changing only at the first of each year EXAMPLE 131234 rather than 13-12-1234...i have beat my head against the wall..below is the current working code..
 Dim agaf, vvdg, wlsb, nnl, ttlld As String
    Dim agaf1, vvdg1, wlsb1, nnl1, ttlld1 As String

    

Case "HEADLAND FIRE AND RESCUE"
    
    If IsNull(Casenum) = True Then
    GoTo sandy1:
    Else

    Dim Gena1 As String
    Gena1 = MsgBox("Do you want to replace the case number currently assigned to this call?", vbYesNo, "REPLACE CASE NUMBER?")


    If Gena1 = vbYes Then

    Text7 = ReadIniFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")
    'setup year
    agaf1 = Mid(Text7, 1, 2)
    'setup month
    vvdg1 = Mid(Text7, 4, 2)
    'compare the year to systems year
    If agaf1 = Format(Now(), "yy") Then

sandy1:
        Text7 = ReadIniFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")
        'setup year
        agaf1 = Mid(Text7, 1, 2)
        'setup month
        vvdg1 = Mid(Text7, 4, 2)
        'compare the year to systems year
        If agaf1 = Format(Now(), "yy") Then
        Else

    WriteIniFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", Format(Now(), "yy") & "-" & Format(Now(), "mm") & "-" & "0000"
    End If
    'compare the month to the systems month
    If vvdg1 = Format(Now(), "mm") Then
    Else
    WriteIniFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", Format(Now(), "yy") & "-" & Format(Now(), "mm") & "-" & "0000"
    End If
 
    Text7 = ReadIniFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")
    agaf = Mid(Text7, 7, 4)
    vvdg = Val(agaf) + 1
    wlsb = Mid(Text7, 1, 6)
    ttlld = wlsb & Format(vvdg, "0###")
    Casenum = ttlld

    WriteIniFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", ttlld
    End If
    End If
    End If
        Exit Sub

Open in new window

0
Comment
Question by:mickeyshelley1
  • 3
  • 3
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
It is easier to just maintain two fields, CaseYear, CaseNumber and then concatenate them in a query when you want to display them.
0
 

Author Comment

by:mickeyshelley1
Comment Utility
I agree with you, this code is a portion of the original code that assigns a number of case numbers for a number of emergency services agencies, Having said that if I could modify this section of the code it would be a more piratical option for me
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
so, you are saying that the two field option is not an option.

Are you going to start this new naming technique on 1 Jan of 2014?  

Do you want to migrate all of the old case numbers to the new syntax or are you just interested in new cases?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mickeyshelley1
Comment Utility
This will start Jan1 and there will be no need for migration at all...Just new case numbers are all that will be needed in the new format..
0
 
LVL 21
Comment Utility
Be sure to make a make a backup copy of the database and the INI file  BEFORE thrying any code changes

TIP If possible set up a test environment.

WARNING: Do NOT test this on the live/production database and ini file.


*** You will need to edit the INI file to remove the dashes ( - ) from the stored case numbers before running this code.

Try this code changes:
Dim agaf, vvdg, wlsb, nnl, ttlld As String
    Dim agaf1, vvdg1, wlsb1, nnl1, ttlld1 As String

    

Case "HEADLAND FIRE AND RESCUE"
    
    If IsNull(Casenum) = True Then
    GoTo sandy1:
    Else

    Dim Gena1 As String
    Gena1 = MsgBox("Do you want to replace the case number currently assigned to this call?", vbYesNo, "REPLACE CASE NUMBER?")


    If Gena1 = vbYes Then

    Text7 = ReadIniFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")
    'setup year
    agaf1 = Mid(Text7, 1, 2)
    'setup month
    vvdg1 = Mid(Text7, 3, 2)
    'compare the year to systems year
    If agaf1 = Format(Now(), "yy") Then

sandy1:
        Text7 = ReadIniFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")
        'setup year
        agaf1 = Mid(Text7, 1, 2)
        'setup month
        vvdg1 = Mid(Text7, 3, 2)
        'compare the year to systems year
        If agaf1 = Format(Now(), "yy") Then
        Else

    WriteIniFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", Format(Now(), "yy") & Format(Now(), "mm") & "0000"
    End If
    'compare the month to the systems month
    If vvdg1 = Format(Now(), "mm") Then
    Else
    WriteIniFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", Format(Now(), "yy") & Format(Now(), "mm") & "0000"
    End If
 
    Text7 = ReadIniFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")
    agaf = Mid(Text7, 5, 4)
    vvdg = Val(agaf) + 1
    wlsb = Mid(Text7, 1, 4)
    ttlld = wlsb & Format(vvdg, "0###")
    Casenum = ttlld

    WriteIniFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", ttlld
    End If
    End If
    End If
        Exit Sub
          

Open in new window


*** You will need to edit the INI file to remove the dashes ( - ) from the stored case numbers before running this code.

Observation: This code has design issues and really needs cleaned up.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
Are you actually using Access for this?  If so, why are you storing the case number in an INI file rather than in a table in the database?

You understand that the two declaration lines:

Dim agaf, vvdg, wlsb, nnl, ttlld As String
Dim agaf1, vvdg1, wlsb1, nnl1, ttlld1 As String

are not declaring all those variables as I think you want to.  That syntax is valid in VB, where you list a series of variables on the line and declare the type for all of them at the end of the line, but it is not valid in Access.  In VBA, each variable has to have its own declaration:

Dim agaf as String, vvdg as String, wlsb as String, nnl as String, ttlld As String
Dim agaf1 as String, vvdg1 as String, wlsb1 as String, nnl1 as String, ttlld1 As String

I obviously don't have all of the code for this routine, since it starts with a "CASE" line and not a "SELECT CASE xxxx", and it does not have an END SELECT, but I'll try to make some sense of this.  It appears that what this code is doing is:

1.  Check to see whether the variable (Casenum) is NULL.  If so, create a new one
2.  If CaseNum is not null, then check to see whether the user wants to assign a new one.  If so, create a new one.  If not, don't do anything.

I would start out with:
'This will test to see if the CaseNum exists and if so, ask if the user wants to create a 
'new one.  If the user selects 'No' it exits the routine right away via the Exit Sub statement
IF IsNull(CaseNum) Then
    'don't do anything yet
Else
    Dim strMsg as string
    strMsg = "Do you want to replace the case number currently assigned to this call?"
    if msgbox(strMsg, vbYesNo, "Replace Case Number?") = vbNo then Exit Sub
endif

'If the code reaches this point, then you need to create a new case number.
'Start by reading the previous number
Text7 = ReadINIFile("H:\Generald\GDB.ini", "Ariton Police", "casenumber", "")

'setup year and lngYearCase # variables
Dim strYear as string, lngYearCase as long

'Determine the year of the previous caseNumber
'If it is the same as the current case number, then increase the value of the case number.
'Otherwise change the year and start the casenumber variable at 1
strYear = Mid(Text7, 1, 2)
if strYear = Format(Now(), "yy") Then 
    lngYearCase = Val(mid(Text7, 3)) + 1
Else
    strYear = format(Now(), "yy")
    lngYearCase = 1
EndIF

'Create the string for the new CaseNumber.  I've made that 6 characters long
'but you could make it 4 or 5 depending on what the maximum number of 
'expected cases would be for the year.
CaseNum = strYear & Format(lngYearCase, "000000")

'Write that value back to the INI file
WriteINIFile "H:\Generald\GDB.ini", "Ariton Police", "casenumber", CaseNum

End Sub

Open in new window

If you need the code to work for the time between now and 1 Jan, 14, you would need to throw in a test for the month as well.  Let me know if that is necessary.

If this is just one segment of the code and there are other INI files or you do this same step for other organizations like "Ariton Police", then I would probably put this in a function of its own and pass in the values of the INI file name and the name of the organization.
0
 

Author Closing Comment

by:mickeyshelley1
Comment Utility
Solved my issue thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now