Custom Counter

S

SimonW

Re. Knowledgebase article Q210194 How to create a multi-user custom counter

Some help please. I am running a SQL INSERT statement that appends selected
invoice records to a table. I want to avoid using Autonum so that users can
choose their own iitiial ordinal number i.e. 10001
The code in the above mentioned article works for the first record in my
recordset but then terminates. I am enumerating a listbox that provides a
WHERE Clause for the SQL statement, as follows:

'...

'listbox of contracts to enumerate
Set ctlList = colist

'variable to store enumerated list of contract ids
coids = ""

For Each varitem In ctlList.ItemsSelected
If coids <> "" Then
coids = coids & " OR "
End If
coids = coids & "[coid]=" & ctlList.Column(0, varitem)
Next varitem

If coids <> "" Then
'clear the listbox
DoCmd.RunSQL "UPDATE Contracts SET Contracts.post = True WHERE"
& coids
Else
MsgBox "Please select at least one active contract in the left
list... ", vbExclamatiion, Me.Caption
Exit Sub
End If

'create invoices to debits table
DoCmd.RunSQL "INSERT INTO debits (drid, coid, [desc], post, due,
[user], dr )" _
& " SELECT Next_Custom_Counter(), qijoin.coid, qijoin.desc, True,
qijoin.due, GetUserName(), Sum(qijoin.dr)" _
& " FROM qijoin" _
& " WHERE" & coids & "" _
& " GROUP BY Next_Custom_Counter(), qijoin.coid, qijoin.desc, True,
due, GetUserName(), True;"

Set ctlList = Nothing

'...

Can anyone explain why the statement only calls the custom_counter once, or
recommend an alternative method to create an incrementing primary key from
within the SQL statement... TIA Simon
 
R

Rick Brandt

SimonW said:
Re. Knowledgebase article Q210194 How to create a multi-user custom
counter

Some help please. I am running a SQL INSERT statement that appends
selected invoice records to a table. I want to avoid using Autonum
so that users can choose their own iitiial ordinal number i.e. 10001
The code in the above mentioned article works for the first record in
my recordset but then terminates. I am enumerating a listbox that
provides a WHERE Clause for the SQL statement, as follows:

'...

'listbox of contracts to enumerate
Set ctlList = colist

'variable to store enumerated list of contract ids
coids = ""

For Each varitem In ctlList.ItemsSelected
If coids <> "" Then
coids = coids & " OR "
End If
coids = coids & "[coid]=" & ctlList.Column(0, varitem)
Next varitem

If coids <> "" Then
'clear the listbox
DoCmd.RunSQL "UPDATE Contracts SET Contracts.post = True
WHERE" & coids
Else
MsgBox "Please select at least one active contract in the
left list... ", vbExclamatiion, Me.Caption
Exit Sub
End If

'create invoices to debits table
DoCmd.RunSQL "INSERT INTO debits (drid, coid, [desc], post,
due, [user], dr )" _
& " SELECT Next_Custom_Counter(), qijoin.coid, qijoin.desc,
True, qijoin.due, GetUserName(), Sum(qijoin.dr)" _
& " FROM qijoin" _
& " WHERE" & coids & "" _
& " GROUP BY Next_Custom_Counter(), qijoin.coid, qijoin.desc,
True, due, GetUserName(), True;"

Set ctlList = Nothing

'...

Can anyone explain why the statement only calls the custom_counter
once, or recommend an alternative method to create an incrementing
primary key from within the SQL statement... TIA Simon
Click to expand...

Functions in Access queries are "optimized" so they only run once per-query
unless they include a field as an argument. Then they are executed once per-row
of the query.

Modify your function to accept an argument and then use a field as an argument
in your INSERT query. The function's internal code doesn't have to DO anything
with the passed in argument. It just needs to be there to force the function to
be executed per-row instead of only one time.

HOWEVER; without seeing the code inside your Next_Custom_Counter() function I
can't guarantee that you will get unique values for each insert row. Most of
the custom auto-increment methods that I know of simply can't be used in INSERT
queries and are intended only for one-at-a-time insertions from a form.
 
R

Rick Brandt

Rick said:
HOWEVER; without seeing the code inside your Next_Custom_Counter()
function I can't guarantee that you will get unique values for each
insert row. Most of the custom auto-increment methods that I know of
simply can't be used in INSERT queries and are intended only for
one-at-a-time insertions from a form.
Click to expand...

Actually, I just read the article for the custom counter method you are using
and I suspect that it shoud be fine in a query (though I've never used it that
way).
 
S

SimonW

Work brilliantly Rick ...



Rick Brandt said:
SimonW said:
Re. Knowledgebase article Q210194 How to create a multi-user custom
counter

Some help please. I am running a SQL INSERT statement that appends
selected invoice records to a table. I want to avoid using Autonum
so that users can choose their own iitiial ordinal number i.e. 10001
The code in the above mentioned article works for the first record in
my recordset but then terminates. I am enumerating a listbox that
provides a WHERE Clause for the SQL statement, as follows:

'...

'listbox of contracts to enumerate
Set ctlList = colist

'variable to store enumerated list of contract ids
coids = ""

For Each varitem In ctlList.ItemsSelected
If coids <> "" Then
coids = coids & " OR "
End If
coids = coids & "[coid]=" & ctlList.Column(0, varitem)
Next varitem

If coids <> "" Then
'clear the listbox
DoCmd.RunSQL "UPDATE Contracts SET Contracts.post = True
WHERE" & coids
Else
MsgBox "Please select at least one active contract in the
left list... ", vbExclamatiion, Me.Caption
Exit Sub
End If

'create invoices to debits table
DoCmd.RunSQL "INSERT INTO debits (drid, coid, [desc], post,
due, [user], dr )" _
& " SELECT Next_Custom_Counter(), qijoin.coid, qijoin.desc,
True, qijoin.due, GetUserName(), Sum(qijoin.dr)" _
& " FROM qijoin" _
& " WHERE" & coids & "" _
& " GROUP BY Next_Custom_Counter(), qijoin.coid, qijoin.desc,
True, due, GetUserName(), True;"

Set ctlList = Nothing

'...

Can anyone explain why the statement only calls the custom_counter
once, or recommend an alternative method to create an incrementing
primary key from within the SQL statement... TIA Simon
Click to expand...

Functions in Access queries are "optimized" so they only run once per-query
unless they include a field as an argument. Then they are executed once per-row
of the query.

Modify your function to accept an argument and then use a field as an argument
in your INSERT query. The function's internal code doesn't have to DO anything
with the passed in argument. It just needs to be there to force the function to
be executed per-row instead of only one time.

HOWEVER; without seeing the code inside your Next_Custom_Counter() function I
can't guarantee that you will get unique values for each insert row. Most of
the custom auto-increment methods that I know of simply can't be used in INSERT
queries and are intended only for one-at-a-time insertions from a form.
Click to expand...
 
Top