Running Query that will return next highest number in table on form

[banpreet]

Hi guys,



I have a form with one field from a table, an id field named TR_ID . I need to run a query that will have the format "TR-1" then"TR-2" and so on. I suck at queries, any idea how to achieve this.

Thanks!

[ranman256]

in query design, add the table
pull down the field TR_ID
run query

if you need to specify TR , then in criteria put:

TR-*

[banpreet]

How would I then implent this on the form? so every time a new record is created it automatically does it or do I need to make a button or jus run the query button on the form?

[banpreet]

sorry forgot to specify but I want it do retrieve the highest value from the table which I think is 54 then continue from there. I guess like the DMax function and then add one

[John_G]

Or, if you just want to display the ID's with "TR-" as a prefix, use an Expression in the query:

ID:"TR-" & [TR_ID]

If you have more than 10, you might want to format with leading zeros, otherwise the sorting won't work if you use that calculated field to sort on.

ID:"TR-" & Format([TR_ID],"000")

[John_G]

You're on the right track with the DMax -

As the default value for the form field put =nz(Dmax("TR_ID","Tablename"),0) + 1

Using the Nz means it will work even if there are no records yet, and putting that expression as the default means it will only set the value in new records.

To display the ID with the TR- on the form, set the format to "TR-000"

[banpreet]

Where do I Enter this information? I need a step by step because im a noob sorry. Thanks for the reply!

[John_G]

You put this in the Properties list for the textbox containing ID on the form. There is a Default Value property and a Format property.


Note: Setting the format does not affect how the data is stored in the table - it only affects how you see it.

[banpreet]

I put the code in and it went to a new record but the TR_ID was at 10, which is a duplicate, the highest record is 58 in the table so I would assume it would go to 59. Any help is appreciated thanks!

[banpreet]

It seems that the tables last record for the TR_ID was 9 instead of 58. I don't know why its out of order but I deleted the record, lets hope it works.

[Bulzie]

If I read it right, you have a table with 1 numeric field where you want to increment that value to use in your ID for each new record? So basically you have to retrieve that value, use it in your TR-?? value, then increment it to use for the next record. Below might work for you. Put this in the OnCurrent on after you go to a new record. You need to change the names iof the field OrderIDMax and table dbo_tblOrderIDMax to yours.


Dim vMax Variant
vMax = DLookup("OrderIDMax", "dbo_tblOrderIDMax")
me.TR_ID = "TR-" & vMax
DoCmd.RunSQL "UPDATE dbo_tblOrderIDMax SET OrderIDMax = " & vMax + 1

[banpreet]

Sweet it works thanks, pretty simple I just cant get the format to work, I tried but how do I get "TR-" in the beginning? I went in the table and put "TR-000" as format but it does not show up. How do I get it to show up like this one the form field? Thanks!

[Bulzie]

= "TR-" & Field

Field is your incremental number wherever you are getting it from. The "&" sign concatenates values in Access so if your value was 55, doing the above would make it TR-55

[banpreet]

I put it the way u said and it puts quotes around & and Field automatically and it comes up on the form like that saying field and &, is there another way?

[Bulzie]

Not sure who's method you went with or where you are trying to put this value, on a control/field or in a table? Maybe = "TR-" [YourMaxField]. Change YourMaxField to your own field that has that max value