S
Steve
Greetings:
I am attempting to concatenate the many side of a one-to-many relationship
in a textbox using the following control source for the textbox:
=Concatenate("SELECT tblPostAblationTesting.chrPostAblationTesting FROM
tblPostAblationTesting WHERE tblPostAblationTesting.intTargetID = " &
Forms!frmMaster!fsubOpenEPS!fsubTargetDetails!txtTargetID & ";")
Where intTargetID is the foreign key in the many side of the relationship.
This works fine once a TargetID exists. But prior to this, when the
Concatenate function is triggered as the subform containing the textbox is
opened, I get the following error:
Run-time error '3075': Syntax error (missing operator) in query expression
'tblPostAblationTesting.intTargetID = '.
I figured that the problem was that intTargetID was null before the target
ID was created so I modified the query to include the NZ function as follows:
=Concatenate("SELECT tblPostAblationTesting.chrPostAblationTesting FROM
tblPostAblationTesting WHERE nz([intTargetID],0) = " &
Forms!frmMaster!fsubOpenEPS!fsubTargetDetails!txtTargetID & ";")
But this did not fix the problem and the error message reads:
Run-time error '3075': Syntax error (missing operator) in query expression
'nz([intTargetID],0) = '.
I need to make it so the concatenate function does not run until there is a
TargetID created or have the concatenate function produce a blank result. I
have reproduced the concatenate function that I am using from Duane Hookom
(2003) below. Thanks for any help in this matter.
Function Concatenate(pstrSQL As String, Optional pstrDelim As String = "; ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Public Function Concat(id As Long) As String
'this function concatenates the many side of a 1:Many relationship
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim tmpStr As String
tmpStr = ""
Set d = CurrentDb
sSQL = "SELECT tblRhythm.chrRhythmName"
sSQL = sSQL & " FROM tblRhythm INNER JOIN tblRhythmTarget_link ON
tblRhythm.idsRhythmID = tblRhythmTarget_link.intRhythmID"
sSQL = sSQL & " WHERE tblRhythmTarget_link.intTargetID = " & id
sSQL = sSQL & " ORDER BY tblRhythmTarget_link.intRhythmID;"
'open recordset
Set r = d.OpenRecordset(sSQL)
' check for records
If Not r.BOF And Not r.EOF Then
r.MoveFirst
'loop thru recordset
Do While Not r.EOF
tmpStr = tmpStr & r.Fields(0) & "; "
r.MoveNext
Loop
tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
End If
'return value
Concat = tmpStr
'clean up
r.Close
Set r = Nothing
Set d = Nothing
End Function
I am attempting to concatenate the many side of a one-to-many relationship
in a textbox using the following control source for the textbox:
=Concatenate("SELECT tblPostAblationTesting.chrPostAblationTesting FROM
tblPostAblationTesting WHERE tblPostAblationTesting.intTargetID = " &
Forms!frmMaster!fsubOpenEPS!fsubTargetDetails!txtTargetID & ";")
Where intTargetID is the foreign key in the many side of the relationship.
This works fine once a TargetID exists. But prior to this, when the
Concatenate function is triggered as the subform containing the textbox is
opened, I get the following error:
Run-time error '3075': Syntax error (missing operator) in query expression
'tblPostAblationTesting.intTargetID = '.
I figured that the problem was that intTargetID was null before the target
ID was created so I modified the query to include the NZ function as follows:
=Concatenate("SELECT tblPostAblationTesting.chrPostAblationTesting FROM
tblPostAblationTesting WHERE nz([intTargetID],0) = " &
Forms!frmMaster!fsubOpenEPS!fsubTargetDetails!txtTargetID & ";")
But this did not fix the problem and the error message reads:
Run-time error '3075': Syntax error (missing operator) in query expression
'nz([intTargetID],0) = '.
I need to make it so the concatenate function does not run until there is a
TargetID created or have the concatenate function produce a blank result. I
have reproduced the concatenate function that I am using from Duane Hookom
(2003) below. Thanks for any help in this matter.
Function Concatenate(pstrSQL As String, Optional pstrDelim As String = "; ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Public Function Concat(id As Long) As String
'this function concatenates the many side of a 1:Many relationship
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim tmpStr As String
tmpStr = ""
Set d = CurrentDb
sSQL = "SELECT tblRhythm.chrRhythmName"
sSQL = sSQL & " FROM tblRhythm INNER JOIN tblRhythmTarget_link ON
tblRhythm.idsRhythmID = tblRhythmTarget_link.intRhythmID"
sSQL = sSQL & " WHERE tblRhythmTarget_link.intTargetID = " & id
sSQL = sSQL & " ORDER BY tblRhythmTarget_link.intRhythmID;"
'open recordset
Set r = d.OpenRecordset(sSQL)
' check for records
If Not r.BOF And Not r.EOF Then
r.MoveFirst
'loop thru recordset
Do While Not r.EOF
tmpStr = tmpStr & r.Fields(0) & "; "
r.MoveNext
Loop
tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
End If
'return value
Concat = tmpStr
'clean up
r.Close
Set r = Nothing
Set d = Nothing
End Function