Autonumber for Queries

[Porsche996GT2]

Hello All,

Is there a way to create some kind of an autonumber for queries? I automated a query to run several criteria and I would like to have the records numbered each time the query is run.

Any help will be greatly appreciated.

Thanks!

 

[abaldwin]

I can only think of one way to do this in Access.

Would require 1 form (I assume you have a form that is kicking off the queries?????)

One module and one query.

Module contents

Option Compare Database

Global dblCounter As Double


Public Function SequenceNo(vRecd) As Double
SequenceNo = dblCounter
dblCounter = dblCounter + 1
End Function


In this example I have simply put a button on a simple form and using the wizard (for speed more than anything) put the following code

Private Sub Command0_Click()
Dim stDocName As String
dblCounter = 1
stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

The table1 is
field type
cus_no number (integer)
cus_name text
I simply manally input about 6 numbers (randomly created) and their names.

Query
SELECT (SequenceNo([cus_no])) AS [Counter], Table1.Cus_no, Table1.Cus_name
FROM Table1
ORDER BY Table1.Cus_no DESC;


Using the sequenceNo function and simply pass it any one of the fields


That should get you individual "Line Numbers"

The key is to reset the global variable prior to executing any of the queries including a refresh of a query.

Hope that helps

Andy





Andy Baldwin

 

[dRahme]

Hi, have a look at this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1107818

drahme

 

[Porsche996GT2]

Hello abaldwin and dRahme,

I'm just trying to do it on a query. I automated the creation of these queries on a loop and each time it gets created, it runs with a different criteria and gets deleted. I basically want a numbering on each of the query results, as the numbering will be used in a formula. Let me know if you have any other suggestions.

Thanks!

 

[abaldwin]

Where is the code for your loop?

I would suggest that at the beginning of the loop you set the global variable I suggested to = 0 and modify your query to add the one field calling the function. Should work but post your looping code here and I can show you where to insert the mods to.

Andy

Andy Baldwin

 

[Porsche996GT2]

Hello All,

I tried this code and it works ok:

(declarations)
Option Explicit
Dim mlngCounter As Long

Function ResetCounter()
mlngCounter = 0
End Function

Function GetNextCounter(pvar As Variant) As Long
mlngCounter = mlngCounter + 1
GetNextCounter = mlngCounter
End Function

In the Query:
Select [feilds...], ResetCounter(), GetNextCounter([a valid column]) From table....

How this works:
ResetCounter() takes no parameters so Access (being as efficient as ever only calls the function once before returning any rows as it thinks the return value will never change, hence the counter only gets reset once.
GetNextCounter() must be sent a valid column as a parameter, Access will realise that it must call this function for every row as each value sent can be different, so it increases the counter on each row.

Please note: This won't work very well for queries & forms where you intend to update data or scroll up and down as the calculated fields gets calculated again and the sequence number will keep going up and up.



Unfortunately though, it does not start with 1. Any suggestions?

 

[abaldwin]

Change your reset function from

Function ResetCounter()
mlngCounter = 0
End Function

to

Function ResetCounter()
mlngCounter = 1
End Function



Andy Baldwin