1,000,000 number bind

[Lathian]

I'm very new to access and have no programming background of any kind.

I have created a DB for a returned check program at work with 4 tables [Check Signers] [Merchants] [Returned Checks] [Banks and Misc] of which three are truly important ([Banks and Misc] has values that may or may not be needed but are values that repeat from case to case). I like that Access generates a unique Autonumber for each new case we have. That said my local government office uses a different format for their case numbering system.

It is perfectly fine that I use a random number but it has to be populated in terms of yy-BC-(case number).

Is it possible to modify the autonumber to display in this format Recycling every year (11-BC-0001, 12-BC-0001 etc.), or is there a away to create a field that will autopopulate in this format so that I can use that number as the reference for my local government office?

If this can't be done it's not the end of the world as we would have to process 1,000,000 cases before the Clerk would run out of placeholders in their system (Simply place a yy-BC-(autonumber's number) on the report).



Thank you for your time and yes I am a n00b at access.

P.

[June7]

Cannot modify the autonumber field. You can either save the prefix to another field or make it part of a formatting structure whenever you display the autonumber value. I presume you have a date field that could be used to determine the correct year prefix.

[sshepard]

Adapted from http://office.microsoft.com/en-us/ac...in=HA010131676

Combining the a date and an autonumber in one field isn't possible, to my knowledge. You can design an update query that will combine them into a string in order to accomplish that though.

Start with your autonumber field and add a field for the date. Make sure the date field is formatted as a Date/Time data type. Now make a third field. This will be where the formatted autonumber will be.

Now create a query. Make sure all three fields are present in the query. In the design ribbon, select update to make your query an update query. In the update to box for your third field, enter the following:

Format([DateFilled],"yy") & "-BC-" & Format([ID],"0000")

What this does is create a string that is the concatination of the last two years of the date, the string -BC- and your autonumber, with up to three leading zeroes. Save the query, run it, and you're good to go!


EDIT: June7, you never responded to me after your first response to my thread, "Trouble running a query in a form and copying results". I'm a bit hurt.

[sshepard]

If you're still confused, I build a database with a query that performs the task I described:

[Lathian]

I appreciate the replies to my post.

Though I could use the autonumber as the primary number I am wondering if there is a way to make Access recycle numbers for this yy-BC-x number. For example, last week we delivered say, 11-BC-32. by the end of the the year we may deliver up to 11-BC-301. I'm wondering if I can make Access auto generate that number and recycle it the following year ex. 12-BC-32 or 12-BC-301. Thoughts?

Again as I'm very new, if you've already answered the question and I simply didn't understand the answer then I accept responsibility for that and am sorry.

P.

[June7]

You could not use autonumber for this. Would have to use VBA code to generate this sequence. It is exactly what I do in my project. What I do is when user initiates new record entry I find the max value in the table, increment it for the new ID, save record immediately then open the 'new' record for user to input other data.

[sshepard]

It is exactly what I do in my project. What I do is when user initiates new record entry I find the max value in the table, increment it for the new ID, save record immediately then open the 'new' record for user to input other data.

Would you be willing to share your code for this? I'm trying to do a very similar thing in my database. In our lab, we generate a log number for each new sample we create, and I've been trying to figure out a way for the new entry form to either: a) display the most recently created log number; or b) increment the value of the most recently created log number and save that as the log number of the new sample (the preferred but more complicated choice).

[June7]

This is exactly as is in my project. The function is called from several places that can initiate a new record.

Public Function NewSample() As String
Dim strLabNum As String
DoCmd.SetWarnings False
'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
    DoCmd.RunSQL "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
    DoCmd.RunSQL "INSERT INTO Submit(LabNum, DateEnter, EnterWho) VALUES('" & strLabNum & "', #" & Date & "#, '" & Form_Menu.tbxUser & "')"
End If
Form_SampleManagement.ctrSampleList.Requery
NewSample = strLabNum
DoCmd.SetWarnings True
End Function

Then one example of calling the function from a button Click event:
Dim strLabNum As String
strLabNum = NewSample
Me.tbxLABNUM = strLabNum
DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum & "'", , , strLabNum & "," & "Login"