Why does this DMAX code fail

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frederick Wilson - 07 Apr 2005 23:56 GMT

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.

Reply to this Message

Rob Oldfield - 08 Apr 2005 00:47 GMT

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

> 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
[quoted text clipped - 31 lines]
>
> End Sub

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

> Form/Module: frmADMIN_AddEvents
>
[quoted text clipped - 9 lines]
> NextID FROM tblEvent;'. Make sure it exists and that its name is
> spelled correctly.

Reply to this Message

Frederick Wilson - 08 Apr 2005 00:59 GMT

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

> Because the domain argument for a domain aggregate function needs to be the
> name of a table or query, not an SQL statement.
[quoted text clipped - 51 lines]
>>NextID FROM tblEvent;'. Make sure it exists and that its name is
>>spelled correctly.

Reply to this Message

SteveS - 08 Apr 2005 02:49 GMT

> 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
---
SteveS
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Reply to this Message