need help with sql statement

[burrina]

I need to turn this: & "#, tblEvent.newid = " & NextEventIDCounter & ", " _




Into this: = Nz(DMax("[NextCustomEventIDCounter]", "EventCounterTable"), 0) + 1

[pbaldy]

Presuming you mean the NextEventIDCounter value, just replace it.

[burrina]

YES, that's what I need. I am lost with this sql, I do good to try and hang in there with vba.

[alcapps]

ok.. write a function.. I thought we had this settled..

Public Function GetNextID(tblName as string, idfieldName as string) as Long
dim db as DAO.database
dim rst as DAO.recordset
dim lngOut as Long

LngOut = 0
set db = currentdb()

Set rst = db.OpenRecordset("select Max([" & idfieldName & "]) as ID from [" & tblName & "]", dbOpenSnapshot)

if rst.recordcount > 0 then
LngOut = rst!ID + 1
end if
rst.Close

GetNextID = LngOut

end function

haven't tested this code.. but that should be good..

[burrina]

Is that something I put in a module? If so, how do I call it? Or add to the existing code already behind the command button on the form?

Here is my final edited version of what you sent. I put this is a module named ModNextEventCounter
Public Function GetNextID(EventCounterTable As String, NextCustomEventIDCounter As String) As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngOut As Long

lngOut = 0
Set db = CurrentDb()

Set rst = db.OpenRecordset("select Max([" & NextCustomEventIDCounter & "]) as ID from [" & EventCounterTable & "]", dbOpenSnapshot)

If rst.RecordCount > 0 Then
lngOut = rst!id + 1
End If
rst.Close

GetNextID = lngOut

End Function

_________________________ OR ADD TO THIS:
Public Function GetNextID(EventCounterTable As String, NextCustomEventIDCounter As String) As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngOut As Long

lngOut = 0
Set db = CurrentDb()

Set rst = db.OpenRecordset("select Max([" & NextCustomEventIDCounter & "]) as ID from [" & EventCounterTable & "]", dbOpenSnapshot)

If rst.RecordCount > 0 Then
lngOut = rst!id + 1
End If
rst.Close

GetNextID = lngOut

End Function

[alcapps]

ok in a sql statement you can

Select GetnextID("EventCounterTable","NextCustomEventIDCounter") as ID, "Hello" as field1 from table

or in an update query..
in the set ID = GetNextID("EventCounterTable","NextCustomEventIDCounter")

in vba

X = GetNextID("EventCounterTable","NextCustomEventIDCounter")

or

rst!ID = GetNextID("EventCounterTable","NextCustomEventIDCounter")

in the immediate window..try

?GetNextID("EventCounterTable","NextCustomEventIDCounter")

the code was setup so you could pass any table name and it's id field and it would return the max ID value back + 1. So you can use this for more than just one table.

hope this helps

[burrina]

Thanks, but I am afraid that did not help. I have code already in place that I need to edit or add to and this only confuses me.

[alcapps]

ok..

if you are in a query graphically..

in the name field try..

NextID: GetNextID("EventCounterTable","NextCustomerEventID Counter")

run it and see if doesn't bring back the next id..

assuming that you have a table named EventCounterTable and an Id field names nextCustomerEventIDCounter.. which has the id's in it your are trying to increment.

if you looked at the sql statement it would be

select GetnextID("EvnetCounterTable","NextCustomerEventID Counter") as NextID

if you don't get this then I guess you will stay confused.. Sorry..

[burrina]

Ok, I tried to implement what you wrote: Here is what I got! Revision, was able to run query, here is the end result.

[burrina]

I give up. Thanks Anyway! Mark as Solved!

[alcapps]

you give up to easy..

been busy..

but you have the case messed up

Case = GetnextID(".......... like you have it) remove the as nextid thats for a query.. not a call
is NextCustomerEventID counter you field name?

this only works for a real table entry not a query..

this update a table...

what I gave you is for a table..
and you wanted to add a new record using an select sql.

what you are doing is a batch. so you would need to have a base number maybe from a table using the code I sent you can use. but in a query you will need to start with that number and increment from there.. then send that output to a table.

if you want a query to work.. you have to do this..
SELECT t.[idfield], GetNextID("YourTableNameHere","yourtableIDfieldnam ehere") AS BaseNextID,((select count([idfield]) from [Master Cable Code Table (TRI)] where [idfield] <= t.[idfield]) + GetNextID("YourTableNameHere","yourtableIDfieldnam ehere") ) as newIDnumber from [YourTableNameHere] t

anyway it is frustrating because it is a lot of code ..

understand if you give up

[burrina]

Here is what I am trying right now. Me.newid = DMax("EventCounterTable", "NextCustomEventIDCounter") + 1
If Me.Dirty Then Me.Dirty = False 'Force save record.
EventCounterTable is my table. and NextCustomEventIDCounter is the field in the table set to number, no PK but no duplicates.
It says it cant find NextustomEventIDCounter

[alcapps]

if you are going to use Dmax(expression , domain)

so it would be dmax("[field]","[Table]") + 1

[alcapps]

this will work..
change the "table name" and "field name" in this to point to the table and the field you are trying to get a max value from.
me.newid = getNextID("table name", "field name")


put this in module.. don't change anything in this function...its taking variables so you can name the table and field..
Public Function GetNextID(tblName As String, idfieldName As String) As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngOut As Long


lngOut = 0
Set db = CurrentDb()


Set rst = db.OpenRecordset("select Max([" & idfieldName & "]) as ID from [" & tblName & "]", dbOpenSnapshot)


If rst.RecordCount > 0 Then
lngOut = rst!ID + 1
End If
rst.Close


GetNextID = lngOut


End Function

[burrina]

Here is what happens with that code. It appends the already edited record that has a newid of 1001 back to 1000 since the table EventCounterTable and it's field NextCustomEventIDCounter do not get updated, it stays the same, NextCustomEventIDCounter has a value of 999 so all items added afterwards get a value of 1000. However it stopped adding records after the first one?