Results 1 to 6 of 6
  1. #1
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15

    Assigning a default value to a text box based on existing records

    I have Table1 with 2 fields:



    ID - text field whose format is X123 (1 alphabet followed by 3 digits).
    Date - Date format

    I have a form whose control source is Table1.

    I have assigned a default value = Date() to my textbox for "Date" control.

    I want to assign a default value to the text box control for "ID" field on this form as follows:

    If "Date" of the last record (order is determined by ascending order of the field "ID") is from last year (as compared to today's date), then
    "ID" alphabet should advance by 1 and the "ID" number should be reset to "001"
    Else
    "ID" alphabet should be same as last record and the "ID" number should be last "ID" number + 1"


    How to do this?

    I know that this system wouldn't work after 26 years - since I only have 1 alphabet character - but that is ok.

    Example:

    Table1:

    ID Date
    A001 1/1/2011
    A002 7/19/2011
    A003 12/18/2011
    B001 1/4/2012
    B002 4/6/2012

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Here is code I use to create custom unique ID that reinitializes each year. Your condition for the change of year will be different.
    Code:
    Public Function NewSample() As String
    Dim strLabNum As String
    'search for aborted lab number and use that record, else if none then create new record
    strLabNum = Nz(DLookup("LabNum", "Submit", "IsNull(DateEnter)"), "")
    If strLabNum <> "" Then
        CurrentDb.Execute "UPDATE Submit SET DateEnter=#" & Date & "# WHERE LabNum='" & strLabNum & "'"
    Else
        strLabNum = Nz(DMax("LabNum", "Submit"), "")
        If strLabNum = "" Then
            'this accommodates very first generated number of blank database
            strLabNum = Year(Date) & "A-0001"
        Else
            'this accommodates change in year
            If Left(strLabNum, 4) = CStr(Year(Date)) Then
                strLabNum = Left(strLabNum, 6) & Format(Right(strLabNum, 4) + 1, "0000")
            Else
                strLabNum = Year(Date) & "A-0001"
            End If
        End If
        CurrentDb.Execute "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
    End If
    Form_SampleManagement.ctrSampleList.Requery
    NewSample = strLabNum
    End Function
    I have never tried to increment an alpha. Think will have to use Asc and Chr functions. Asc will return the ASCII code of character, increment the code, then convert to letter with Chr.
    Example: Chr(Asc("A")+1)

    Chr(Asc(Left([ID],1))+1)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15
    Thanks! I am trying to use this with making appropriate changes for my case and am stuck at something quite seemingly stupid...

    I used DMax to get the "Date" and am comparing the year of this max date with today's year:

    strDateMax = Nz(DMax("Date", "Table1"), "")
    If YEAR(strDateMax) < YEAR(Date) Then
    <my code here>

    I am getting type mismatch error at the IF statement. I checked that my "Date" field format is Date/Time. From the watch window I see that YEAR(strDateMax) is returning type mismatch even when strDateMax = 3/26/2012.

    Also, i tried multiple options of converting strDateMax to Date type by using Datevalue function but I still get the type-mismatch... Any suggestions??

  4. #4
    reema is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    15
    got it.. this was because i had a hidden textbox in my form with name "year".

  5. #5
    elhale is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    14
    June7, I have modified your code for my use since I need a sequential number that functions the way it looks like yours does above, except I don't need alpha. I thought I removed any code that generates the alpha character, but I'm still getting the "A" character in the number it generates. I only need the year+0000 number it generates. I'm including the code as I've modified it. I still don't have a full grasp of VBA.

    Code:
    Private Sub AssignDate_AfterUpdate()Dim strCaseNum As String
    'search for aborted case number and use that record, else if none then create new record
    strCaseNum = Nz(DLookup("CaseNum", "tracker", "IsNull(AssignDate)"), "")
    If strCaseNum <> "" Then
        CurrentDb.Execute "UPDATE tracker SET AssignDate=#" & Date & "# WHERE CaseNum='" & strCaseNum & "'"
    Else
        strCaseNum = Nz(DMax("CaseNum", "tracker"), "")
        If strCaseNum = "" Then
            'this accommodates very first generated number of blank database
            strCaseNum = Year(Date) & "-0001"
        Else
            'this accommodates change in year
            If Left(strCaseNum, 4) = CStr(Year(Date)) Then
                strCaseNum = Left(strCaseNum, 6) & Format(Right(strCaseNum, 4) + 1, "0000")
            Else
                strCaseNum = Year(Date) & "-0001"
            End If
        End If
        CurrentDb.Execute "INSERT INTO tracker(CaseNum, AssignDate) VALUES('" & strCaseNum & "', #" & Date & "#)"
    End If
    End Sub
    Appreciate any help. Thanks in advance.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    @elhale, was just looking at this thread again in relation to another and noticed your post. Don't know how I missed it. If you had started your own thread with reference to this one, probably would have gotten an answer long time ago.

    I see nothing in your code that will cause letter "A" to be included in string. However, need to change the 6 to 5.

    Set breakpoint and step through code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Default Information for an existing field HELP
    By opinionsrfun in forum Access
    Replies: 1
    Last Post: 06-19-2013, 06:30 AM
  2. Replies: 7
    Last Post: 02-06-2012, 11:54 AM
  3. Access 2007 - Creating New Records Based on Existing Records
    By GeorgeAugustinePhillips in forum Forms
    Replies: 9
    Last Post: 10-05-2011, 05:58 PM
  4. Assigning values to Text Boxes
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 12-11-2010, 01:26 AM
  5. Replies: 2
    Last Post: 08-09-2010, 06:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums