Selecting next record in dynaset and assigning to variable

H

HeliCaptKirk

I'm trying to concatenate 2 fields from a table and multiple records in that
table. I can get the system to do everything correctly except move to the
next record in the dynaset before selecting the data. So it chooses the same
first record (correctly) N times.

situation: 1 table, 3 columns
PRLDot1 (a number) Citation Reference URL

Caveat:
There may be several citations and references (columns 2 and 3) for one
PRLDot1. So there could be between 1 and 5 references.
Example:

157 Berenson J, et al. Cancer, http://www3..... Berenson
157 Lacey, MQ, et al. http://www3..... Lacey
157 Multiple Myeloma. Mayo Clin http://www3..... Mayo Clinic

I would like it to look like this (all in one memo field)
Berenson J, et al. Cancer,
http://www3..... Berenson

Lacey, MQ, et al.
http://www3..... Lacey

Multiple Myeloma. Mayo Clin
http://www3..... Mayo Clinic

I have this working for the first record and formatting correct. However,
what I get is this:
Berenson J, et al. Cancer,
http://www3..... Berenson

Berenson J, et al. Cancer,
http://www3..... Berenson

Berenson J, et al. Cancer,
http://www3..... Berenson

As you can see it's the same first record repeated.

Here's my code:

Private Sub lblCitRefUpdate_DblClick(Cancel As Integer)


Dim TotalRefs As Integer 'Total number of references for this protocol
TotalRefs = "0"


'Experimental DCount function p.733 Access 2003 Bible
'TotalRefs = DCount("PRLDot1", "tblPRLReferences", "tblPRLReferences.PRLDot1
= '135'") ' This WORKs to put the number 4 in the field (there are currently
4 references)
' Me.PRLCitationRef = TotalRefs
' TotalRefs = DCount("PRLDot1", "tblPRLReferences",
"tblPRLReferences.PRLDot1 ='" & PRLID & "'")
' Exit Sub

'Assign results of query to TotalRefs
TotalRefs = DCount("PRLDot1", "tblPRLReferences",
"tblPRLReferences.PRLDot1 ='" & PRLID & "'")


'Combine all the elements into one field:
Dim X As Integer
Dim RecNumb As Integer
RecNumb = "0"

Dim RefDataSet As Recordset
Dim strDocName As String
strDocName = "qryPRLRefsUpdate"

'set refdataset = DoCmd.OpenQuery strDocName, acViewPivotTable, acReadOnly


For X = 1 To TotalRefs
Me.PRLCitationRef = Me.PRLCitationRef & _
DLookup("Citation", "tblPRLReferences", "tblPRLReferences.PRLDot1
='" & PRLID & "'") & vbCrLf & _
DLookup("ReferenceURL", "tblPRLReferences",
"tblPRLReferences.PRLDot1 ='" & PRLID & "'")
If X < TotalRefs Then
Me.PRLCitationRef = Me.PRLCitationRef & vbCrLf & vbCrLf
End If
' DoCmd.GoToRecord acDataTable, PRLID, acNext
' RefDataSet.MoveNext
' Recordset.MoveNext (this moves the whole database record forward,
not within the extracted table data.
Next X

'clear variables
TotalRefs = "0"
X = "0"

End Sub


Question: How to get the code to advance to the next line in the dataset to
take the next set of values.
Thanks.
 
S

Steve Sanford

You say you want the Citations & Reference URLs for the same PRLDot1
numbers in a memo field, but what table is the memo field in? More info
please.


You cannot use "DLookup()" to get multiple records; if there are multiple
records with the same criteria, DLookup() finds the first occurance of the
criteria. If there are multiple lookups for the same criteria, there is no
guarantee that the same record will be returned every time.

I would use a record set and loop thru it.

HTH
 
Top