LeagueOneDale
New member
- Local time
- Today, 01:53
- Joined
- Jun 23, 2011
- Messages
- 2
Hi folks,
New poster here, thou I have found answers in the forum before.
I have an Access 2003 database where I need to take the output from a number of records, format that data into a string and add it to a table for use in a report.
I have attempted the code to do this and researched the web on the error I am getting, which is a '3201 No Current record'. I thought the 'Do While Not rst.EOF' would trap this for me, but it doesn't seem to work the way I thought it did.
I only get the error when the routine reaches the end of the source table.
Here is the code:
Can anyone take a look and advise what I am doing wrong?
New poster here, thou I have found answers in the forum before.
I have an Access 2003 database where I need to take the output from a number of records, format that data into a string and add it to a table for use in a report.
I have attempted the code to do this and researched the web on the error I am getting, which is a '3201 No Current record'. I thought the 'Do While Not rst.EOF' would trap this for me, but it doesn't seem to work the way I thought it did.
I only get the error when the routine reaches the end of the source table.
Here is the code:
Code:
Public Function RefLCNBuilder()
Dim db As Database
Set db = CurrentDb
Dim rst, rst1 As Recordset
Dim strSQL, strSQL1 As String
Dim strLCNSTART1, strLCN1, strREF, strLCNPart As String
'SOURCE DATA SET
strSQL = "SELECT QRY_reference_tasks_MPOL_unsched.REFLCNCA as REFLCN, QRY_reference_tasks_MPOL_unsched.LSACONXB AS LCN FROM QRY_reference_tasks_MPOL_unsched ORDER BY QRY_reference_tasks_MPOL_unsched.REFLCNCA, QRY_reference_tasks_MPOL_unsched.LSACONXB"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'COMPILED DATA SET
strSQL1 = "SELECT [REF LCNS].LCN, [REF LCNS].[REF LCNS] AS REFLCN, [REF LCNS].[REF LCNS1] AS REFLCN1 FROM [REF LCNS]ORDER BY [REF LCNS].LCN"
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
If ((Not rst.BOF) And (Not rst.EOF)) Then rst.MoveFirst
'ADD LCNs TOGETHER'
Do While Not rst.EOF
strLCNSTART1 = rst!REFLCN
strLCN1 = rst!LCN
strREF = rst!LCN
rst.MoveNext
Do While rst!REFLCN = strLCNSTART1 Or Not rst.EOF
strLCN1 = rst!LCN
strREF = strREF & ", " & strLCN1
rst.MoveNext
Loop
rst1.AddNew
rst1!LCN = strLCNSTART1
rst1!REFLCN1 = strREF
rst1.Update
Loop
rst.Close
rst1.Close
Set db = Nothing
End Function
Can anyone take a look and advise what I am doing wrong?
Last edited: