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.
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.