Autonumber with a Suffix 5

[bobbster2000]

I've looked at other Threads for help with this but none of them had exactly what I am looking for. I have a Form called "Work Order". It generates "Job order numbers" by using "Autonumber". I need to add a "Suffix" field so I can group certain records together in order to print them out for a Report. For example, if autonumber gives me 20, I need to add a "A", or a "B" so I can group them together. The Suffix will not always be the same since a Work Order can have 2 to 4 parts to it, with the Suffix representing each part. I know that I can't put a alpha field in the Autonumber section but I've heard that It could be done with a Query. But I don't know where to start and my VB skills are poor. Can someone Please help me?

 

[Smarty]

Use not autonumber but text
On Insert of a new record use the following code:

Dim sqlstring As String
sqlstring = "select * from yourtable order by yourkeyfield desc"
Dim db As Variant, rst, rst2 As Recordset
Dim Value1 As Variant
Dim suffix as string
Set db = CurrentDb
Set rst = db.openrecordset(sqlstring)
Debug.Print rst.RecordCount
If rst.RecordCount = 0 Then
Value1 = 0
Else
Value1 = rst!yourkeyfield
End If
Value1 = Value1 + 1
suffix = InputBox("Give your suffix&quot
suffix = suffix & value1
yourformfieldwhereyouputyourkey = suffix


I hope you do understand all this a bit... I guess this should work, but maybe you should disable the yourformfieldwhereyouputyourkey. If you do so, set it to enabled before and disable it after the last line of code

 

[bobbster2000]

Thanks Smarty, but the "Job order numbers" must be Auto generated. Will this code do this for me? Thanks again.

 

[dneufarth]

Why not just add another column to hold the suffix and make it part of the key?

You can always concatenate the number and suffix for other purposes.

Dave

 

[bobbster2000]

Thanks Dave, but how do I make the new column a part of the key?

 

[Craig0201]

Got to table design and add your new column. Make it part of the key by highlighting the two rows and clicking on the primary key icon.

HTH

Craig

 

[bobbster2000]

Thanks a lot. But since the users won't always have to add a Suffix, won't I run into some problems if I make it a Primary Key?

 

[bobbster2000]

Also Dave, since the Job Number needs to be Auto generated, when I put in a new Suffix but keep the same job number I'm just editeding the same record instead of making a new one. And when I go to the next record its a new number but in some cases I'm going to have to keep the same number and change the suffix.

Does your code produce an Auto generated number Smarty?

 

[dneufarth]

bobbster2000, I forgot about the multiple recs perjob number.

Looks as if you must assume the role of autonumbering yourself. I've never done that, but have seen ways to accomplish this in this forum. Basically you create a one column, one row table (let's call it tblBASE) with the next available job number to be used.

For the first record of each work order, retrieve the number from tblBASE, plug it into the first record, and update the tblBASE number to number+1.

You may use the two column key approach and default the suffix to "" or you may concatenate base number and suffix. Ff you concatenate, format the result into a consistent length with leading zeroes, or whatever, to make sorting, etc. easier.

Now you may add subsequent records under the same job number as it's not necessary to retrive a new base job number.

Dave

 

[UncleJack]

Can't you set the field the Number and then when you want a new number that's one higher just assign it with:

JobOrderNumber = DMax("JobOrderNumber", "JobOrders&quot + 1

 

[bobbster2000]

The main problem is that my boss wants the Job number to be auto generated. He's afraid if we left it up to the users to select a Job number that they might skip. For example on user selects 55 and the next user selects 60.

 

[UncleJack]

DMax takes the highest number that's been entered and then you add one to it. Users don't get to enter numbers; the system generates the next available number. It's just a more positive way of invoking an Autonumber field without the possibilities of skipping numbers because of deletes. If your boss doesn't like that idea then tell him to program it himself.

Uncle Jack

 

[jebry]

Hi!

UncleJack may never be a diplomat but, in this case he is correct. Never, Never use an autonumber field for any sort of record you want to be permanent. Access has a way of not respecting autonumbers and if you compact or repair you could lose the autonumbers or Access may regenerate them. In the long run, you will probably find that your database and hard copies don't match, or worse, your database and your customers records no longer match. Take some time and figure out UncleJack's DMax method, you and your boss will be happier!

Jeff Bridgham

 

[bobbster2000]

Which Private Sub should I insert the Dmax code?

 

[jebry]

Hi!

Use the Form_Current event:

If IsNull(txtUserID) = True Then
Do the DMax thing and store the number in the textbox
End If

This will make sure that you don't overwrite any data. Considering your earlier post, you will probably want to set up your own buttons to add new records, either keeping the information in the current record or making a record from scratch so to speak. If you want more information on this let me know.

hth
Jeff Bridgham

 

[bobbster2000]

Thanks for offering more help Jeff. I don't know what the variables represent in the Dmax code that Uncle Jack provided. The fields I have are "Job #" and "Suffix". The table is called "Work order". How do they correspond?

 

[dneufarth]

I like UncleJack's method as well. Simpler than the separate table approach.

Although Access won't allow dupicate keys, you may have to deal with dup key errors if two users are adding at the same time. True with separate table approach as well, but less likely as the retrieve/increment/update are in rapid succession.

How have others dealt with this issue? Trap the error and get next max number?


Dave

 

[jebry]

Hi!

The domain aggregate functions (DMax, DSum, etc) have the same basic format:

Function(YourField, YourTableorQuery, YourCriteria)

Note, all three parameters should, in most cases, be strings.

If your willing to give me your e-mail address I will send you a sample of how I would implement what you are talking about. I'll try to get it together by tomorrow morning.

hth
Jeff Bridgham

 

[bobbster2000]

Thanks a Mil jebry! Its bobbster2000@excite.com Please keep in mind that my VB skills are poor. With the domain aggregate function Dmax, in the YourCriteria section, which one of my fields goes there?

 

[dneufarth]

Criteria may be omitted as you just want the max in [Job #].


Dave