Syntax for nested aggregate functions

[BrianDG01]

I have a problem with a nested arrangement of Right, DLookup and DMax functions.
The function is for a default value in a text control

=Right(DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))+1000001,6)

I am trying to retrieve the last record from a field SampleNumber which is alphanumeric (e.g. "AK005434") and then add a 1 to it as the next SampleNumber. I had previously used a default value in the txtSampleNumber control as

="AK" & Right(1000000+DMax("[Clip]","[tbldat14A_MasterSampleList]")+1,6)
where [Clip] was a field I have calculated in the table to chop off the preceding characters. Adding 1,000,000 and taking the 6 right hand values and concatenating with "AK" gave me the answer, though it is a but primitive.
This all worked until the SampleNumber value got out of order and blocks of SampleNumber values came in that were then followed by blocks of numbers with lower values (say AK005001-AK005050 followed by AK002001-AK002050).
Now I figure if I just recall the latest entry by DLookup and criteria of DMax on the SampleID (Autonumber Primary key) I could get at the value. I have done this to some success using default values in a series of unbound controls like
=DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))
to get the SampleNumber I require, then a Right function to trim in another unbound box and then use that last unbound box as the default value for the txtSampleNumber control that is the entry for the data table. However, the unbound control box is only valid for the first record and does not update. So, I added a macro that closes the form and reopens it.
This all works but is a bit agricultural. I would like to do a single nested function to the default value of the txtSampleNumber control box.
Any help with the syntax? Is it possible to nest Right, DLoopkup and DMax into one statement?
Thanks

 

[Mihail]

Is it possible to nest Right, DLoopkup and DMax into one statement?

Should be. But is very hard to manage nested aggregate functions.

A tip is to create the function from interior to exterior and to see, in any step, that the result is what you are expecting.

So, in your case, create many text boxes that have this control sources:

txt1: = DMax("[SampleID]","tbldat14A_MasterSampleList")

txt2: = DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))

txt3: = DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]="  & DMax("[SampleID]","tbldat14A_MasterSampleList"))+1000001

txt4: =Right(DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]="  & DMax("[SampleID]","tbldat14A_MasterSampleList"))+1000001,6)

A better way to manage this is to create an external, public function, in a regular module, that should return the expected result:

Public Function SourceForTextBoxName
Dim R1
  R1 = DMax("[SampleID]","tbldat14A_MasterSampleList")
Dim R2
 R2 = DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & R1)
Dim R3
 R3 = R2+1000001
Dim R4
 R4 = Right(R3,6)

 SourceForTextBoxName = R4

End Function

The Control Source for the textbox should be:
= SourceForTextBoxName

Good luck !

 

[Galaxiom]

Your problems start with storing the AK and the leading zeros in the field.

You could save a lot of trouble by simply storing the number and using the Format property of the control where it is displayed to show it as you require.

"AK"000000

Then all you need is a DMax + 1 to increment the value.

 

[BrianDG01]

Thanks Mihail,
I got the VBA code working on a text box on the form, but as with my earlier attempts, the text box does not update when I TAB to a new record

Any thoughts?

Brian

 

[BrianDG01]

Thanks Galaxiom,
I had thought earlier about this but the AK000... needs to be saved in a field as the actual SampleNumber. I will think about this some more. I did have a calculated field in the table at one point which I used to generate a new number. But I had calculated it from the SampleNumber text field by trimming. Maybe I should do it the other way around and calculate the final text result from a numeric field.

Brian

 

[Mihail]

Thanks Mihail,
I got the VBA code working on a text box on the form, but as with my earlier attempts, the text box does not update when I TAB to a new record

Any thoughts?

Brian

This sound as a calculate value that you intend to store in the table... Hm.
Give us more information about the environment:
What you have, How/where you intend to use this calculation etc.

 

[BrianDG01]

Mihail.
It is not a calculated field. The object txtSampleNumber is a bound text control on a form that reports back to a table. I am trying to get the field to default to the previous +1 for a new record. But it is an alphanumeric field so some fiddly things like Right function have to be done to achieve it. Also, while the values are sequential for one data entry session, they may or may not be sequential with respect to the last session at the next session. So this means I cannot use Autonumber.

The code you gave me gets me the value that I put into an unbound text box, which I would then use as the default value for the bound txtSampleNumber control. There are only 3-4 controls for cycling through for each record, the SampleNumber, SampleDate (which is defaulting to last value on Lookup as required), SampleType (a combo control) and a flag for whether or not an assay is required. I TAB through each control and it gets to a new record after the last TAB on the form. The problem is, the unbound text box I had used previously and with your new VBA code do not update the DLookup/DMax when the TAB cycle reaches a new record.

I have the form displayed as split view so that data entry can be reviewed during the session.

Thanks,

 

[Galaxiom]

I had thought earlier about this but the AK000... needs to be saved in a field as the actual SampleNumber.

Why does it need to be saved? How the data is held and how it is displayed are quite separate.

As long as the system can display what is required it should not matter. The Format property AK000000 will display it from the simple numeric value in the table.

Even if it needs to be exported you would do this through a query that converts to the required format with:

Format([fieldname], "AK000000")

 

[Mihail]

You don't need the unbound textbox.
Use the OnCurrent event of the form:

Private Sub FormName_Current
  If Me.NewRecord then
    Me.txtSampleNumber.DefaultValue = [FONT=Arial][SIZE=2]SourceForTextBoxName[/SIZE][/FONT]
  End If
End Sub

Note that the syntax is not checked.

 

[BrianDG01]

Mihail, I get a #Name? in the txtSampleNumber control

 

[Galaxiom]

Should not need any code at all.

Just use DMax("fieldname","tablename") + 1 in the Default of the control on the form.

(Substitute your preferred complicated expression if you insist on continuing to store the string instead of a number.)

 

[BrianDG01]

You have me thinking more about this Galaxiom. Like what happens when someone uses a different prefix or different number of numerals in the sample identifier

 

[Galaxiom]

You have me thinking more about this Galaxiom. Like what happens when someone uses a different prefix or different number of numerals in the sample identifier

Some developers will separate the prefix and the number into two separate fields if the prefix varies. They would be displayed by concatenating them into another control.

The DMax becomes:

Nz(DMax("numberfield", "tablename", "prefixfield='" & Forms!formname.prefixcontrol & "'"),0) + 1

This increments the number field based on the prefix entered on the form. The Nz handles previously unused prefixes.

A composite index is applied across the two fields in the table to ensure no duplicates.

The user should not be able to edit automatically assigned control on the form at all.

 

[BrianDG01]

Yes, I would use a combo box for the prefix however, so people don't go making up their own codes.

I would like to get to the bottom of this VBA coding though. It appears very simple and would seem to fix immediate problem. I would like to learn more on VBA also.

I think I will alter tables to separate prefixes and numerals sometime soon.

 

[Mihail]

I think I will alter tables to separate prefixes and numerals sometime soon.

Do this NOW.
Later you will need to change also the code, the forms, the reports and the queries.

More:
Think about creating a table where to store this prefixes.
tblPrefixes
ID_Prefix
Prefix
PrefixMeaning

If, at a time, someone need a new prefix, he/she should first to update the prefix's table.
This also will give you the possibility to select the persons that are allowed to make additions/changes to this table.

 

[BrianDG01]

restructuring the tables is the fix I agree. But, any thoughts on why the code returns a "#Name?" to the text control box. That was a very good small tutorial on how to use a public function and return it to a control. I am keen to get more knowledge on this side of things.

 

[Mihail]

restructuring the tables is the fix I agree. But, any thoughts on why the code returns a "#Name?" to the text control box. That was a very good small tutorial on how to use a public function and return it to a control. I am keen to get more knowledge on this side of things.

We can't answer to this while we don't know exactly your DB.
For now, post all code implied here.