Q for Stephen LeBans or Allen Browne re: Row NumberVer2

[Opal]

Stephen or Allen,

I have been trying to recreate the row numbering query you have in
your example:

http://www.lebans.com/rownumber.htm

but the numbers in the query do not increment. The return value for
the query when
it is run, is zero for each record.... what am I missing?

 

[Opal]

Stephen or Allen,

I have been trying to recreate the row numbering query you have in
your example:

http://www.lebans.com/rownumber.htm

but the numbers in the query do not increment. The return value for
the query when
it is run, is zero for each record.... what am I missing?

Even if I try to run the query, I get the following error message:

Microsoft Access set 0 field(s) to Null due to a type conversion
failure, and
it didn't add 2 record(s) to the table due to key violations, 0
record(s) due to
lock violations, and 0 record(s) due to validation rule violations.
Do you
want to run the the action query anyway?

I am missing something but I can't figure it out....can you help me
please?

 

[Allen Browne]

The example applies to forms. You cannot use it in a query.

It will be slow and inefficient, but you can use a DCount() expression in a
query, provided no additional filtering or sorting is applied.

Otherwise use a temp table with an AutoNumber.

 

[Opal]

The example applies to forms. You cannot use it in a query.

It will be slow and inefficient, but you can use a DCount() expression in a
query, provided no additional filtering or sorting is applied.

Otherwise use a temp table with an AutoNumber.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.









- Show quoted text -

Hi Allen,

Thank you for your reply. I guess I misread the example, but there is
a query in there
with an expression pointing to:

Expr1: Serialize("query1","code",

)

and a Module:

'Stephen Lebans April 1999
'For use directly as a calculated expression in the query
'For demonstration purposes only
'See my posting comp.databases.ms-accessfor an adapted version of this
function
'to be used on a form in an unbound text box  www.Dejanews.com search
Lebans query
'Blatantly adapted from Microsoft source code
'ACC: How to Display Line Numbers on Subform Records  Article ID:
Q120913
'Sorry, recordset property of Screen.ActiveDatasheet is not available
'In query design view save any changes to your query to disk before
executing your query
'Only send an indexed field to the function

Function Serialize(qryname As String, keyname As String, keyvalue) As
Long
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)


On Error GoTo Err_Serialize

'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"

End Select

Serialize = Nz(rs.AbsolutePosition, 0) + 1


Err_Serialize:
'Add your own Error handler
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing

End Function

?????????

 

[Allen Browne]

Okay, so Peter Schroeder contributed a Serialize() function that returns the
AbsolutePosition of a record in a query.

I don't think that's a really useful generic solution because:
a) It handles a single criteria only.

b) It doesn't cope with additional filtering or sorting that the user may
apply to the query.

c) It may not yield the same results where a query's sort order is not
adequately defined.

d) It will be quite slow an inefficient for large queries, as it opens a
recordset for every record.

e) It will not work properly for parameter queries.

Write to a temp table with an AutoNumber if you want a serialized query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The example applies to forms. You cannot use it in a query.

It will be slow and inefficient, but you can use a DCount() expression in
a
query, provided no additional filtering or sorting is applied.

Otherwise use a temp table with an AutoNumber.










- Show quoted text -

Hi Allen,

Thank you for your reply. I guess I misread the example, but there is
a query in there
with an expression pointing to:

Expr1: Serialize("query1","code",

)

and a Module:

'Stephen Lebans April 1999
'For use directly as a calculated expression in the query
'For demonstration purposes only
'See my posting comp.databases.ms-accessfor an adapted version of this
function
'to be used on a form in an unbound text box  www.Dejanews.com search
Lebans query
'Blatantly adapted from Microsoft source code
'ACC: How to Display Line Numbers on Subform Records  Article ID:
Q120913
'Sorry, recordset property of Screen.ActiveDatasheet is not available
'In query design view save any changes to your query to disk before
executing your query
'Only send an indexed field to the function

Function Serialize(qryname As String, keyname As String, keyvalue) As
Long
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)


On Error GoTo Err_Serialize

'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"

End Select

Serialize = Nz(rs.AbsolutePosition, 0) + 1


Err_Serialize:
'Add your own Error handler
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing

End Function

?????????
[/QUOTE]

 

[Opal]

Okay, so Peter Schroeder contributed a Serialize() function that returns the
AbsolutePosition of a record in a query.

I don't think that's a really useful generic solution because:
a) It handles a single criteria only.

b) It doesn't cope with additional filtering or sorting that the user may
apply to the query.

c) It may not yield the same results where a query's sort order is not
adequately defined.

d) It will be quite slow an inefficient for large queries, as it opens a
recordset for every record.

e) It will not work properly for parameter queries.

Write to a temp table with an AutoNumber if you want a serialized query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thank you for your reply. I guess I misread the example, but there is
a query in there
with an expression pointing to:

Expr1: Serialize("query1","code",

)[/QUOTE]
[QUOTE]
and a Module:[/QUOTE]
[QUOTE]
'Stephen Lebans April 1999
'For use directly as a calculated expression in the query
'For demonstration purposes only
'See my posting comp.databases.ms-accessfor an adapted version of this
function
'to be used on a form in an unbound text box  www.Dejanews.comsearch
Lebans query
'Blatantly adapted from Microsoft source code
'ACC: How to Display Line Numbers on Subform Records  Article ID:
Q120913
'Sorry, recordset property of Screen.ActiveDatasheet is not available
'In query design view save any changes to your query to disk before
executing your query
'Only send an indexed field to the function[/QUOTE]
[QUOTE]
Function Serialize(qryname As String, keyname As String, keyvalue) As
Long
Dim dbs As Database
Dim rs As Recordset[/QUOTE]
[QUOTE]
Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)[/QUOTE]
[QUOTE]
On Error GoTo Err_Serialize[/QUOTE]
[QUOTE]
'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"[/QUOTE]
[QUOTE]
End Select[/QUOTE]
[QUOTE]
Serialize = Nz(rs.AbsolutePosition, 0) + 1[/QUOTE]
[QUOTE]
Err_Serialize:
'Add your own Error handler
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing[/QUOTE]
[QUOTE]
End Function[/QUOTE]
[QUOTE]
?????????- Hide quoted text -[/QUOTE]

- Show quoted text -[/QUOTE]

Thank you Allen, but since I have never written a "temp table", could
you provide some direction?

 

[Allen Browne]

By "temp" table, I mean a table where you hold the data temporarily. It's an
ordinary table, and typically it may hold only the primary key value for the
data you really need, and an AutoNumber to give you the serialized count.

When you wish to run this query, you clear the temp table out with code like
this:
Currentdb.Execute "DELETE FROM MyTable1;", dbFailOnError
and reset the Seed of the AutoNumber using code like this:
Function SetSeed(strTable As String, strAutoNum As String, _
lngID As Long) As Boolean
'Purpose: Set the Seed of an AutoNumber using ADOX.
Dim cat As New ADOX.Catalog

Set cat.ActiveConnection = CurrentProject.Connection
cat.Tables(strTable).Columns(strAutoNum).Properties("Seed") = lngID
Set cat = Nothing
SetSeed = True
End Function

You then populate it with an Append query.

The code will need a reference to this library:
Microsoft ADO Ext 6.0 for DLL and Security

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, so Peter Schroeder contributed a Serialize() function that returns
the
AbsolutePosition of a record in a query.

I don't think that's a really useful generic solution because:
a) It handles a single criteria only.

b) It doesn't cope with additional filtering or sorting that the user may
apply to the query.

c) It may not yield the same results where a query's sort order is not
adequately defined.

d) It will be quite slow an inefficient for large queries, as it opens a
recordset for every record.

e) It will not work properly for parameter queries.

Write to a temp table with an AutoNumber if you want a serialized query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

The example applies to forms. You cannot use it in a query.

It will be slow and inefficient, but you can use a DCount() expression
in
a
query, provided no additional filtering or sorting is applied.

Otherwise use a temp table with an AutoNumber.

Stephen or Allen,

I have been trying to recreate the row numbering query you have in
your example:

but the numbers in the query do not increment. The return value
for
the query when
it is run, is zero for each record.... what am I missing?

Even if I try to run the query, I get the following error message:

Microsoft Access set 0 field(s) to Null due to a type conversion
failure, and
it didn't add 2 record(s) to the table due to key violations, 0
record(s) due to
lock violations, and 0 record(s) due to validation rule violations.
Do you
want to run the the action query anyway?

I am missing something but I can't figure it out....can you help me
please?- Hide quoted text -

- Show quoted text -

Hi Allen,

Thank you for your reply. I guess I misread the example, but there is
a query in there
with an expression pointing to:

Expr1: Serialize("query1","code",

)[/QUOTE]
[QUOTE]
and a Module:[/QUOTE]
[QUOTE]
'Stephen Lebans April 1999
'For use directly as a calculated expression in the query
'For demonstration purposes only
'See my posting comp.databases.ms-accessfor an adapted version of this
function
'to be used on a form in an unbound text box  www.Dejanews.comsearch
Lebans query
'Blatantly adapted from Microsoft source code
'ACC: How to Display Line Numbers on Subform Records  Article ID:
Q120913
'Sorry, recordset property of Screen.ActiveDatasheet is not available
'In query design view save any changes to your query to disk before
executing your query
'Only send an indexed field to the function[/QUOTE]
[QUOTE]
Function Serialize(qryname As String, keyname As String, keyvalue) As
Long
Dim dbs As Database
Dim rs As Recordset[/QUOTE]
[QUOTE]
Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)[/QUOTE]
[QUOTE]
On Error GoTo Err_Serialize[/QUOTE]
[QUOTE]
'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"[/QUOTE]
[QUOTE]
End Select[/QUOTE]
[QUOTE]
Serialize = Nz(rs.AbsolutePosition, 0) + 1[/QUOTE]
[QUOTE]
Err_Serialize:
'Add your own Error handler
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing[/QUOTE]
[QUOTE]
End Function[/QUOTE]
[QUOTE]
?????????- Hide quoted text -[/QUOTE]

- Show quoted text -[/QUOTE]

Thank you Allen, but since I have never written a "temp table", could
you provide some direction?
[/QUOTE]

 

[john ricci]

for those struggling, i found 2 problems with the rownumber Serialize function for SQL.

1. in order to properly number the rows, it seems to require a unique identifier for each row. i accomplished this via a composite hash key in the query

2. the numbering function works fine while a query is in development, but it blows up once saved. this is because once the query (referencing the serialize function) is saved, the function becomes a recursive call, that blows up when the max # of querydefs is opened. i got around this by creating a copy of the query and referencing it in the function call within the orginal sql. this is cumbersome but does work. a more elegant solution would be to make a copy of the query in VBA and use it within the function logic.

hope this helps

Stephen or Allen,

I have been trying to recreate the row numbering query you have in
your example:

http://www.lebans.com/rownumber.htm

but the numbers in the query do not increment. The return value for
the query when
it is run, is zero for each record.... what am I missing?

On Saturday, October 27, 2007 7:37 PM Opal wrote:

Even if I try to run the query, I get the following error message:

Microsoft Access set 0 field(s) to Null due to a type conversion
failure, and
it didn't add 2 record(s) to the table due to key violations, 0
record(s) due to
lock violations, and 0 record(s) due to validation rule violations.
Do you
want to run the the action query anyway?

I am missing something but I can't figure it out....can you help me
please?

On Saturday, October 27, 2007 10:56 PM Opal wrote:

Hi Allen,

Thank you for your reply. I guess I misread the example, but there is
a query in there
with an expression pointing to:

Expr1: Serialize("query1","code",

)

and a Module:

'Stephen Lebans April 1999
'For use directly as a calculated expression in the query
'For demonstration purposes only
'See my posting comp.databases.ms-accessfor an adapted version of this
function
'to be used on a form in an unbound text box  www.Dejanews.com search
Lebans query
'Blatantly adapted from Microsoft source code
'ACC: How to Display Line Numbers on Subform Records  Article ID:
Q120913
'Sorry, recordset property of Screen.ActiveDatasheet is not available
'In query design view save any changes to your query to disk before
executing your query
'Only send an indexed field to the function

Function Serialize(qryname As String, keyname As String, keyvalue) As
Long
Dim dbs As Database
Dim rs As Recordset

Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)


On Error GoTo Err_Serialize

'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"

End Select

Serialize = Nz(rs.AbsolutePosition, 0) + 1


Err_Serialize:
'Add your own Error handler
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing

End Function

?????????[/QUOTE][/QUOTE] 
[QUOTE][/QUOTE] 
[QUOTE][/QUOTE] 
[QUOTE][/QUOTE][/QUOTE]