Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Modify VBA Code to change Case Number Format

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
mickeyshelley1
Asked:
mickeyshelley1
  • 3
  • 3
1 Solution
 
Dale FyeCommented:
It is easier to just maintain two fields, CaseYear, CaseNumber and then concatenate them in a query when you want to display them.
0
 
mickeyshelley1Author Commented:
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
 
Dale FyeCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
mickeyshelley1Author Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
Dale FyeCommented:
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
 
mickeyshelley1Author Commented:
Solved my issue thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now