Creating a new Max() value for a new record

[bftsg]

I am trying to create a new maximum number for a new record I am creating. It consists of three numbers plus the unique new identifier.
The first number can be one of 15 values, as can the second and third numbers. Once the user has selected the first three options, I need to create a new unique fourth number to prevent duplicates.
The code I use is working in SQL
SELECT max(a.Req_No)
FROM tbl_Requirements a
where a.Req_Type = forms!frm_Requirements!Req_Type and
a.Req_Area = forms!frm_Requirements!Req_Area and
a.Req_SubArea = forms!frm_Requirements!Req_SubArea;

How can I call that from a form using the "After Update" on the third number? My brain hurts...

I have set it up as a query from the form but does not show the new value in the field box on the form. DoCmd RunSQL cannot be used.

[Gicu]

Wouldn't a dMax work (as the control source of the text box of your frm_Requirements)?

Dmax("[a.Req_No]","[tbl_Requirements]", "[a.Req_Type] = " & [Req_Type] & " And [a.Req_Area] = " & [Req_Area] & " And [a.Req_SubArea] = " & [Req_SubArea])

The above assumes Reg_Type,Reg_Area and Reg_Subarea are all numbers. You might need to wrap it in some IIF statements to check if the three fields are populated:
=IIf(Isnull([Req_Type]),Null,IIf(isnull([Req_Area]),Null,IIf(Isnull([Req_SubArea]),Null,Dmax("[a.Req_No]","[tbl_Requirements]", "[a.Req_Type] = " & [Req_Type] & " And [a.Req_Area] = " & [Req_Area] & " And [a.Req_SubArea] = " & [Req_SubArea]))))
Cheers,

[bftsg]

Thanks Vlad - much appreciated. I have tried lots and lots of ways to try and get it to work but without success, mostly down to my misunderstand the syntax of the cDMax command. The latest non-working version is

Req_No = DMax(["Req_No"], "tbl_Requirements",( "Req_Type = " & Forms!frm_Requirements!Req_Type & " AND 'Req_Area' = " & Forms!frm_Requirements!Req_Area & "AND 'Req_SubArea = '" & Forms!frm_Requirements!Req_SubArea))


I get an error "Run Time Error 2465 Database can't find the field '|1' referred to in your expression."

[orange]

bftsg,
Can you tell us a little about the environment? What do Req_Type, Req_Area and Req_SubArea represent?
May also be helpful if you show readers your tables and relationships.

Since you're dealing with numbers, can you show us some representative data?

[bftsg]

The fields are all in one table called Requirements.
Within the table there is a field calld Area, one called SubArea and one called Type.
Each field holds an id_

number from a

look-up table holding text descriptions

The user picks an Area, a SubArea and a Type to create a requirement and the system is supposed to create a unique number, one higher than the last requirement for that set of numbers.

Running an SQL query I can do it easily, but I cannot get the DMax process to work. I tried running the DoCmd.RunSQL to create it but that didn't work either.

I understand that it is my lack of knowledge of setting the three criteria with DMax that is stopping me but I cannot find any clear instructions on the syntax anywhere. All help is very appreciated.

[bftsg]

All the id_numbers are two digit but the req_no cqan be 5 digits long.

[davegri]

If the DMax code is on form frmRequirements try this:

Dim nNextReg as long


nNextReg = DMax("Req_No", "tbl_Requirements", "Req_Type = " & Me.Req_Type & " AND Req_Area = " & Me.Req_Area & " AND Req_SubArea = " & Me.Req_SubArea) + 1

[orange]

I agree with davegri that his code sample will get you a new unique number. However, I'm still curious what the application is about.

[bftsg]

Thanks Guys - I will give it a try and let you know.

The app is for sme businesses going through a new or changed application or system process. Allows them to identify what they need to prove before they can build, test and accept that they are getting what they asked for. Makes it easier for both users, developers and testers during the whole SDLC.

[orange]

Sounds like a good idea. My only concern is the single table.

[bftsg]

Hi Orange. What is your concern about a single table? There are a number of tables which are linked by primary keys. The sub-tables contain an auo-number id and a text field. The main table links to these tables using the id key, as normal.

[orange]

My concern was that you only had 1 table.

[bftsg]

Ok - now I see. Thanks for your help

[bftsg]

Apologies for my lack of understanding of the DMax command but the statement below is not returning anything for nNextReq. Any ideas would be appreciated

There was nothing wrong with the command - apart from a stupid spelling mistake on the name! Only spotted using the larger text in this form. Specsavers here I come...

[orange]

Suggest you show us some real data values for the fields in this
Dmax("Req_No", "tbl_Requirements", "Req_Type = " & Me.Req_Type & " AND Req_Area = " & Me.Req_Area & " AND Req_SubArea = " & Me.Req_SubArea)