Using DMax to reference subform 1

[jmbcreative]

I am using a DMax statement to add a sequential number to a record. The problem is I need the DMax statement to reference a loaded subform. All the documentation that I am finding shows the DMax statement referencing a table or query as such;

DMax(FieldName, TableName)

Now, does anybody know of a way to reference a subform with DMax?

 

[TheAceMan1]

How are ya jmbcreative . . .

The [blue]domain[/blue] of aggregate function [blue]DMax[/blue], can be a [blue]table or query name[/blue].

Is there any reason why you can't [blue]use the subforms underlying table[/blue] along with [blue]proper criteria[/blue] if necessary?

See Ya! . . . . . .

 

[jmbcreative]

Yeah, since the subform uses a query I could probably use criteria. It would have to be dynamic criteria though since the results that show up on the subform are based on the active record in the main form. My code looks like the following;

********
Private Sub Form_Load()
Me.System.Value = Form_ChangeLogEntry.System.Value
Me.Location.Value = Me.Combo26.Column(2)
DoEvents
Dim strAlpha, strMax
strAlpha = "abcdefghijklmnopqrstuvwxyz"
strMax = DMax("[ControlID]", "[HardwareList components Query]")
Me.ControlID = CStr(Val(strMax)) & Mid(strAlpha, InStr(strAlpha, Right(strMax, 1)) + 1, 1)


End Sub
**********

The criteria would be all records containing the system name as defined in the field "System" on the main form. As you can see by the statement above the DMax statement I am auto-filling that field from another active form.

 

[jmbcreative]

I thought the proper criteria would look something like this;

strMax = DMax("[ControlID]", "[HardwareList components Query]", "[System] = " & Forms!HardwareAddComponentsRequest!System & "")

But for some reason it doesn't take it.

 

[PHV]

Perhaps this ?
strMax = DMax("[ControlID]", "[HardwareList components Query]", "[System] = '" & Me.System.Value & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmbcreative]

Thanks for the insight PHV, but unfortuntely that's return a value that I have absolutely no idea came from.

I might have to restructer this statement entirely.

 

[jmbcreative]

BINGO!!!!

I checked my syntax again and had a character out of place.

strMax = DMax("[ControlID]", "[HardwareList components Query]", "[System] = '" & Me.System.Value & "'")

Worked!

Many thanks, Mate!