Incrementing with Dmax 1

[bobbster2000]

I have a table called "Work Order". It has a field called "Job #" in which I need a way to enable the users to enter a Suffix attacted to "Job #" in Some cases. For example, if one user gets the "Job #" 25 but there's 3 parts to it. He'll need to add a suffix so we'll have 25a, 25b, 25c, three seprate records, but related. The "Job #" needs to be Auto Generated so I'm using the Dmax function but my VB skills are very poor. I need to have "Job #" be incremented by one with using the Dmax function but I keep on getting errors. Here's the code

Private Sub Form_BeforeInsert(Cancel As Integer)

JOB # = DMax("JOB #", "Work Order&quot + 1

End Sub


It tells me that (Identifer under cursor not reckonized) and the cursor is pointing to the Private Sub section.

Can anyone please assist?

 

[rewdee]

Good morning,

I think you should look at your relationships. I think you are making a mistake trying to change your job # field to represent a one to many relationship. If you can redo the job table to include only master details. In other words, only specific jobs. Then create the detail table the of job parts. Thus job 25 is entered once in the master table and refers to many details such as a,b,c in the detail table. Even if DMax could return the maximum number (which it can't without doing some manipulations to convert from string, strip suffix, etc.) you are going to run into problems. Say for example, an user has entered job 25 parts a and b. What happens if a second user wants to create job 26 and then the first user wants to go back and add job 25 c. If you try using DMax it will end up being job 26 b or job # 27 instead of job # 25 C.

If you need more help with the VBA or the relationships, keep posting as this newsgroup rocks.

hope this helps,
Rewdee

 

[bobbster2000]

That sounds like a good idea but if I create a new table for the suffix the "Job #" will still be a primary key. So lets say if Job # 25 has 3 parts. When I update the first one and add the "A" 25a, no problem. But when I go the add the "B" I'll get an error message saying that access won't allow that because it will create duplicate values. Any suggestions? Thanks

 

[bjj]

Hmmm...why should Job # be a primary key? I agree with Rewdee that this sounds like a job for relationships – the database will be a lot smaller and easier to manage, especially if there’s a lot in common between job 25A, 25B, and 25C.

Having said that, there are a few issues. Incrementing a value means that it has to be an integer. The val statement takes a string and gives the closest integer representation. The DMax requires brackets if you have a space or number sign in the table/field name. Finally, I think you need to assign the value to the field or a control in the form that’s bound to the Job # field. So, the statement looks like this:

Me.[JOB #] = Val(DMax("[JOB #]", "[Work Order]&quot) + 1

This won’t increment the suffix, of course – you can use the Chr statement to auto-increment numbers and convert the numbers to letters.

Haven’t tested this, so use with care .