Getting Dmax to properly work for custom autonumber

[ThatNewGuy]

I've been down every road at this point. I've read through the FAQ, I've researched prior posts, the whole nine yards and I still can't seem to figure out how to properly use Dmax.

I need to make a custom control number. Example ST30549, ST30550, ST30551. This number has to sequentially increase by one. Basic stuff. I know how to merge the two characters to create this custom number the only problem is Dmax adds +1 on the first transaction then stops. My problem might stem from the fact that I tried making a very simple table with the starting number of 30550 as a test. Once I plug everything into the Dmax function it increases my number to 30551. But at that point it stops, which makes sense bcuz I'm always referencing back to my starting number of 30550 in my table. Is this the wrong approach?

 

[PWise]

what is your dmax statement

 

[ThatNewGuy]

The basic statement is just Dmax("[Control_No]","Control Number")+1. This one works in a test query and the form itself, but like i said it never goes above the set number.

I was messing around with another basic line of code as a test:

cmd_Print_Click ()
If [Control_No] < 30046 Then
[Control_No] DMax("[Control_No]", "Control Number") + 1
End If
End Sub

Needless to say I'm not very good at writing code.

 

[PHV]

If you don't update your "Control Number" table then you'll always get the same number ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThatNewGuy]

Exactly PHV! That's why my basic approach to this is flawed.

What I would need then is to automate the process of adding a new record in the control number table based on every transaction in the form. So the one column in the control number table would read 30000,30001,30002,etc.

 

[lespaul]

so somewhere in your process you need an UPDATE query or an INSERT query if you want to put the control number in a table...

Leslie

Have you met Hardy Heron?

 

[JBinQLD]

Or, if you want to use code rather than Leslies SQL option you could add this to the forms AfterInsert() Event:

me![Control_No] = DMax("[Control_No]", "[Control Number]") + 1

assuming you have a cntrol on your form called control_no of course!

 

[ThatNewGuy]

I wanted to thank everyone for their input, especially Leslie and JBin. I ended up going with an update query which works just perfect!!!

Thanks for the direction. I was wondering down the wrong path for awhile there. The query suggestion was so simple I wish I would have thought of it earlier

Thanks!

Dave

 

[ThatNewGuy]

I spoke too soon! I finally had time to revisit the db after the above updates and I found out that the main query I'm using for the form locks up. When I try to fill in the appropriate fields, esp a date field, I keep getting an error msg "Run-time error '3326' Recordset is not updateable"

When I delete the update query I can change the required fileds in the form, but I'm back to square one regarding the control number. I tried JBin's line of code above to try and avoid working with an update query and the code isn't working as needed. It works, but it's the same problem I had before.

I know I can write a line of code to get rid of the error msg, but that doesn't solve the problem. All in all, it appears that my problems are rooted in using an update query. Your suggestions would be appreciated.

 

[fneily]

From my previous posts:
Let’s say you want to create your own autonumber field. Create a table with a field called ID, in your case Control_No. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

So for you:
=Dmax("[Control_No","Control Number") + 1
I tested it and it continues beyond 30550.

 

[ThatNewGuy]

Thanks fneily for the suggestion. I tried everything, step by step, but I'm a bit confused. You said "when you go to the next NEW record the value will increment by 1". In my test it doesn't increment past 2. Once again, bcuz it's pulling from the original number in the table

Is the purpose of inserting the Dmax function under default value meant to increment by 1 when you add a new record, and only when you add a new record into your database?

I'm looking for clarification because I need the control number to increment based on an action, like clicking a command button or printing a new document etc. The control number I'm using is based on the number of transactions a day which may vary from 25 to 75. The control number is viewed as a tracking number for a specific product that's ordered. For example, part A may have control number 30000 today, then Part A is ordered again on Thursday so the control number might be up to 30050 by then based upon how many overall items were ordered by this one customer.

 

[fneily]

I misinterpreted. I thought you meant to increment each NEW record by 1.
So you're just keeping a field to show quantity ordered?
In your first post, you state "This number has to sequentially increase by one."
Then in your last post you state, "part A may have control number 30000 today, then Part A is ordered again on Thursday so the control number might be up to 30050 by then based upon how many overall items were ordered by this one customer."

So are you counting a single item at a time or a bulk count? Unclear.

 

[ThatNewGuy]

Looking back I should have clarified.

I am keeping a field for quantity, but that's completely seperate from the control number that needs to increment.

My example above should read as follows "Part A is ordered today 5,000 qty, but it's control number is 30000. The customer ordered 50 different part's ranging from A,B,C,etc. in the same day therefore the control number would increment by 1 with each order. So Part B would have control no. 30001, Part C control no. 30002, etc." This control number is independent of the quantity ordered.

On Monday this customer ordered 50 different part #'s therefore the control number would sequentially increment from 30000 up to 30050, one by one. When we start the process over on Tuesday the starting control no. would be up to 30050. Therefore if Part A is ordered the next day w. qty of 1000 the control no. would be 30050.

Clear as mud, right?

I'm just counting a single item at a time.

 

[jsteph]

ThatNewGuy,
There are some faq's here somewhere that talk about incrementing these numbers.

In my opinion, it's best to use a 'seed' table. Have a function called something like GetNextNum(), with code that hit's the seed table, takes the number from the table, updates the table's number by 1, then returns the original number it found. So the table always contains the next number to be used. The seed table has only 1 record.

In the function, you open the recordset with the dbDenyRead Option and dbPessimistick LockEdits parameter. (Trap the 3260 error and resume after a delay if this is a multi-user app).

This gives you the functionality of autonumber but without the danger inherent in using autonumber for a field that may be a foreign key elswhere, plus you can prepend your character fields to this as you wish.
--Jim

 

[TheAceMan1]

How are ya ThatNewGuy . . .

Be aware ... what you have is an [blue]alpha-numeric[/blue] value! Meaning, values are [blue]ascii compared[/blue] ... rather than numeric, and numeric is what you seek!

The Idea is to seperate and the alpha-numeric part, convert to numeric, and get your answer. You will of course append "St" in the final. What I present is a function that returns the next Alpah-Numeric value as required.

In a module in the modules window, copy/paste the following function:

[blue]Public Function NextID() As String
   NextID = "ST" & CStr(DMax("Val(Right([purple][B][I][FieldName[/I][/B][/purple]], Len([[purple][B][I][FieldName[/I][/B][/purple]) - 2))", "[purple][B][I]TableName[/I][/B][/purple]") + 1)
End Function[/blue]

Call the function as you like, example:

[blue]   Me!TextboxName = NextID[/blue]

If you want to step thru the process, let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ThatNewGuy]

Jsteph and AceMan thanks for all your help! I combined both approaches. I went with a seperate seed table and ace's function. Perfect match!!!! Things are working just right.

Ace I had to adjust the parameters in the Len()function from your suggested -2 to 0. The -2 was cutting off the first two numeric characters of the overall value. I was getting something like ST001 instead of the desired ST30001.

I was curious why ascii compared values come into play here. I assume that creates more problems?

One last question. Is it possible for access to print dupliate forms, lables, etc yet sequentially number the labels? For example, if I needed to print 10 duplicates it would number the printed labels 1 of 1, 2 of 2, etc until the 10 desired labels are completed.

 

[TheAceMan1]

ThatNewGuy . . .

Although you've been here since [blue]Jan 19, 2007[/blue]:

Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]