Why does this DMAX code fail

[Frederick Wilson]

The database I have gotten has an event table with EVENT_ID as the PK.
The formate of the information is EVTID# where #=the next highest number
in the list.

The object of this code is to strip "EVTID" from the info in that field,
find the highest number, add 1 to it then concatenate it back together
to get the next PK.

The error that is reported is below the code.

************* CODE BELOW *****************
Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo err_BeforeUpdate

Dim strSQL As String

strSQL = "SELECT
CLng(Right([tblEvent]![EVENT_ID],(Len([tblEvent]![EVENT_ID]))-5)) AS
NextID " & _

"FROM tblEvent;"

Debug.Print DMax("[NextID]", strSQL)



exit_BeforeUpdate:

Exit Sub



err_BeforeUpdate:

Call CommonError(Me.Form.Name, "Form_BeforeUpdate", Err.Number,
Err.Description)



End Sub



*******************************ERROR************************************************

Form/Module: frmADMIN_AddEvents



Event Code: Form_BeforeUpdate



Is reporting the following



Error Number = 3078



The Microsoft Jet database engine cannot find the input table or query
'SELECT
CLng(Right([tblEvent]![EVENT_ID],(Len([tblEvent]![EVENT_ID]))-5)) AS
NextID FROM tblEvent;'. Make sure it exists and that its name is
spelled correctly.

 

[Rob Oldfield]

Because the domain argument for a domain aggregate function needs to be the
name of a table or query, not an SQL statement.

 

[SteveS]

WELL, bummer.

I hate having a bunch of independent queries. In the event you move the
form and forget the query you're out.

I guess I could use a recordset

This should work:

In the header of your form, add an unbound text box.

NAME : NextID
VISIBLE : NO
CONTROL SOURCE :="EVTID" & Max(Right([event_id],Len([event_id])-5))+1

Then change the Form BeforeUpdate event code to:

'******** begin code *******
Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo err_BeforeUpdate

Me.event_id = Me.NextID

exit_BeforeUpdate:

Exit Sub

err_BeforeUpdate:

Call CommonError(Me.Form.Name, "Form_BeforeUpdate", Err.Number,
Err.Description)

End Sub
'******** end code *******

No records sets - no SQL ...

HTH