I have a table that I need to add a field to and populate. I only need to do
this once, as I'm trying to append data to another table. My table has 12K
records with several fields which include Sample_ID, Reader, and Age. It is
a flat file which looks like:
Sample_ID Reader Age
S050001 jas 14
s050001 jas 15
S050001 jas 14
S050001 bbb 14
s050002 jas 25
s050002 jas 24
S050002 jas 24
s050002 bbb 25
Each Sample_ID can be read by different people up to 3 times and have up to
three ages
I need to create a new field called Reading_Number and populate it
sequentially for each unique combination of Sample_ID and Reader
I am working on a function called from a query to do this but am having
trouble. I keep getting into a henious loop which locks up Access. Can
anyone help?
My function is a follows:
Public Function RN(tblJm As String, Age As String, Optional WhereClause As
String = "") As Single
Dim DBRN As DAO.Database
Dim RSRN As DAO.Recordset
Dim rcount As Integer
Dim strsql As String
Set DBRN = CurrentDb()
strsql = "SELECT [" & Age & _
"] FROM [" & tblJm & "] "
strsql = strsql & "WHERE [" & Age & "] IS NOT NULL "
If Len(WhereClause) > 0 Then
strsql = strsql & "AND (" & WhereClause & ") "
End If
strsql = strsql & "ORDER BY [" & Age & "]"
Set RSRN = DBRN.OpenRecordset(strsql)
If RSRN.EOF = False Then
RSRN.MoveLast
End If
rcount = RSRN.RecordCount
If Not (RSRN.BOF And RSRN.EOF) Then
RSRN.MoveFirst
End If
'Loop through the recordset
Do Until RSRN.EOF
With RSRN
RSRN.MoveFirst
RSRN.Edit
RN = 1
RSRN.Update
RSRN.MoveNext
RSRN.Edit
RN = RN + 1
RSRN.Update
End With
Loop
RSRN.NextRecordset
If Not (RSRN.BOF And RSRN.EOF) Then
RSRN.MoveFirst
End If
Do Until RSRN.EOF
With RSRN
RSRN.MoveFirst
RSRN.Edit
RN = 1
RSRN.Update
RSRN.MoveNext
RSRN.Edit
RN = RN + 1
RSRN.Update
End With
Loop
RSRN.Close
Set RSRN = Nothing
End Function
my query to call the function is as follows:
SELECT tblJM.Sample_ID, tblJM.Reader, rn("tbljm","Age","Sample_ID = '" &
[Sample_ID] & "' AND Reader = '" & [Reader] & "'") AS reading_Number, tblJM.
Age
FROM tblJM;
Please help!!
Thanks
