Hi, quicknk.
So basically your question is about how to enumerate records.
To achieve this your table has to contain at least one unique field (primary key will serve well) - let us say its name is [keyID]. The logic of the solution is the following:
- use a value of [keyID] in the first record as mark to start enumeration
- use a function with static variable to hold record number
- each time the function is invoked and gets [keyID] value as argument it returns incremented static variable as record number or 1 if the value of [keyID] is the same as the value in the first record (thats why [keyID] field has to be unique)
You'll need two VBA function:
- the one mentioned above
- a function to get particular field value from the first record of sorted query - native SQL First() function or domain aggregate DFirst() work incorrectly
-
-
Public Function RecordNumber(varUniqueField As Variant, _
-
varFirstValue As Variant) As Long
-
-
Static lngRecordNumber As Long
-
-
If IsNull(varUniqueField) Or IsNull(varFirstValue) Then Exit Function
-
If varUniqueField = varFirstValue Then lngRecordNumber = 0
-
lngRecordNumber = lngRecordNumber + 1
-
RecordNumber = lngRecordNumber
-
-
End Function
-
-
Public Function GetFirstValue(strFieldName As String, _
-
strDataSetName As String) As Variant
-
-
Dim rs As New ADODB.Recordset
-
-
With rs
-
.CursorType = adOpenForwardOnly
-
.LockType = adLockReadOnly
-
.ActiveConnection = CurrentProject.Connection
-
.Open strDataSetName
-
GetFirstValue = .Fields(strFieldName)
-
.Close
-
End With
-
-
Set rs = Nothing
-
-
End Function
-
-
Let us say the table has name [tblTable].
The first query ([qrySorted]) just sorts the table by some field:
-
SELECT tblTable.*
-
FROM tblTable
-
ORDER BY tblTable.[Some field];
-
The second query ([qryEnumerated]) returns enumerated records of [qrySorted].
-
SELECT qrySorted.*, RecordNumber(qrySorted.keyID, GetFirstValue("keyID","qrySorted")) AS lngRecordNumber
-
FROM qrySorted;
-
Thus obtained dataset may be easily filtered by specific record numbers and exported to csv using DoCmd.TransferText method.
Regards,
Fish
3
