Auto assign text/number value to record

[spectraflame]

I have a form that allows my users to key in a service order. I need a unique letter/number combination assigned to each record or service order depending on the year, department, and numeric sequence.

In my table I have an ID column that is defined as an auto number just to keep track of all of the records. I have another column that is for the service order number itself.

The example below shows the format that I would like to have for my service order number. The first letter is the first letter of the department. It is then followed by 2 digits to note the current year, then it is followed by the next number in the sequence of service orders for that specific department. My departments are: Electric, Water, Sewer, Maintenance, Locate, & Other.

E04 - 00100
W04 - 00100
S04 - 00100
E04 - 00101
W04 - 00101
S04 - 00101
M04 - 00100
L04 - 00100

The reason that I have for keeping the numeric sequence for each department is for internal catalog and reporting.

I have thought about separate tables, but they may get a little tricky for me to figure out my reporting needs. I just thought that keeping all of the records in a single table would be better.

Any thoughts?
Matthew

 

[mdmilner]

Yes - a query.
If you already have the fields within the table that allow you to calculate the key value, there may not be a need to even create the key.

The key item that many people overlook is that queries have nearly exactly the same capabilities/uses as a regular table in Access.

The final answer depends on your exact needs. For a simple database that isn't very large (few thousand records) performance may not be impacted very much by just using a single table to store all your data. You will find, though, that creating multiple related tables (relational database) becomes necessary the more data you have.

Mike

 

[spectraflame]

Based on the reponse above, I just want to make sure my thinking is correct.

If I were to use a separate table for each department, that would solve my issue with keeping the numeric sequence of the service orders. THis would also allow me to use the next and previous record buttons to navigate through the service orders for a specific department.

In order to still keep the naming convention for my service orders (E04 - 00100), I could then create a text box that is formatted to always placed the department letter followed by the current year and then have another box just for the auto number. I would then just have to create a specific service order entry form for each department.

Does this sound somewhat logical? I realize that it is a lot of work to accomplish something that seems easy, but that is about the only way I can think of.

Matthew

 

[mdmilner]

I think I provided a bad answer - at least, it wasn't very clear.

You said that the index field was actually contained within two or more other fields already present in the table. What I am trying to get at is this: Tables and Queries are nearly 100% interchangeable within Access. Anywhere that you think you have to have a table, you can use a query.

For example, go make a query based on the table. In the QBE wizard, add something like IndexField: [fieldname1] & [fieldname2] then run it and you'll see something that concatenates the two fieldnames you chose. From here you can do more complex things like IndexField: Left([fieldname1],3) & Left([fieldname2],3) which would only grab the first three characters of each of the given fieldnames.

Check out things like: len, instr, left, right, mid

Now, here's a question:
Do you have to have the service order numbers in consecutive order? Would it be good enough for them to be in ascending order?

And a comment:
One issue with Autonumber is if you add a record and delete it; then add another field, you'll see a gap in the numbering. Access remembers the last number used, even though that number may have been deleted. Autonumber can be very useful, but you have to pay attention to it's limitations.

And finally, on to what I think might be an answer for you.

What I think you should probably do is use a little custom VB programming behind the form to concatenate the values you need. At the same time, the routine can go out and look at the existing relevant (within department) records and set the numbering for you.

Going to make the assumption that you've used your table as the recordsource for your form. This allows you to reference the current record with syntax like Me.fieldname1

Also going to make the assumption that you either just hit a button that says something like "add new record" and throws the form onto your screen...or, it puts the form into add mode. If you use the command button wizard, it gives you the ability to select Forms then Add Record...which inserts code similar to this:

Private Sub AddRecordMain_Click()
On Error GoTo Err_AddRecordMain_Click

    DoCmd.GoToRecord , , acNewRec

Exit_AddRecordMain_Click:
    Exit Sub

Err_AddRecordMain_Click:
    MsgBox Err.Description
    Resume Exit_AddRecordMain_Click
    
End Sub

One approach might be to build a quick query containing only the records you want and find the highest value within a given field.

Private Sub AddRecordMain_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strVarName, strYourIndex as String

Set dbs = CurrentDb()

On Error GoTo Err_AddRecordMain_Click

    DoCmd.GoToRecord , , acNewRec
    

    ‘ quick proof of concept, this creates a query limiting the results
    ‘ it returns a single value, the highest and prints it to the intermediate window and then
    ‘ deletes the query.
    ‘ There’s probably a better way to do this, but Dmax requires a table/query name
    strSQL = "SELECT * FROM tblData " _
              & "WHERE [B/O]=#1/20/2003#"
    Set qdf = dbs.CreateQueryDef("tempqry", strSQL)

    Debug.Print Nz(DMax("[Number]", "tempqry"), 0)
    DoCmd.DeleteObject acQuery, "tempqry"
    ‘ End fiddling with creating/deleting queries

    ‘ Instead of printing, you could put something like this in:
    ‘strVarName =  Nz(DMax("[Number]", "tempqry"), 0)

    ‘ And then do this:
    strYourIndex = Me.fieldname1 & Me.fieldname2 & strVarName

Exit_AddRecordMain_Click:
    Exit Sub

Err_AddRecordMain_Click:
    MsgBox Err.Description
    Resume Exit_AddRecordMain_Click
    
End Sub

This isn't a complete answer, just a suggestion and limited demo of what types of things you will end up doing. Also, there is no requirement that you leave the above all within a _Click event.

Mike

 

[spectraflame]

Mike,

Thanks for the suggestions. I thought about your suggestion about combining the data from multiple fields. Currently I have a field called EntryDate. That field automatically captures the current date and time when the record was created with the Now() function. The table contains a column called ID# which is an auto number that just numbers each row.

I was thinking that I could combine the first letter of the department with the year(yy) of the EntryDate field and with the ID# field to produce the desired service order number.

Currently I have" ="E" & [EntryDate] & " - " & [ID#]

I just need to figure out how to extract just the year from the EntryDate and how to set my control source so that the combined result will be stored in the WorkOrder# field in my table.

If it were in Excel, I could figure it out, but I have found out that the methods in Excel do not carry over well into Access.

To answer your question about having the service orders in sequential order, the answer is yes. After thinking it over, I elected to create a separate table for each department. That way each record for a specific department would be in numeric order. No one that uses the database has the ability to delete records so I do not think that the auto number will be that big of an issue.

Thanks again for your suggestions,
Matthew

 

[Norie]

to extract just the year from the EntryDate

Just

Year([EntryDate])

should do this bit.

 

[spectraflame]

Thanks Norie,

That gives me the year in YYYY format which is fine.

Matthew

 

[Norie]

That gives me the year in YYYY format which is fine.

Or you could try this

Format([EntryDate], "yy")

Which whould give the 2-digit year.

 

[spectraflame]

That works great also.

Now that I have my syntax correct and it is displaying exactly what I want, is there a way for me to have the result of the formula below stored in a specific field in my database?

This is the code that is in my control source for this text box:

="E" & Format([EntryDate],"yy") & " - " & [ID#]

Thanks again,
Matthew