Solved

Modify VBA Code to change Case Number Format

Posted on 2013-12-26
7
519 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)
ID: 39740464
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
ID: 39740517
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)
ID: 39740548
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:mickeyshelley1
ID: 39740558
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
ID: 39740706
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
ID: 39740731
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
ID: 39742430
Solved my issue thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

867 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

17 Experts available now in Live!

Get 1:1 Help Now