Solved

Modify VBA Code to change Case Number Format

Posted on 2013-12-26
7
537 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 48

Accepted Solution

by:
Dale Fye 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

628 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