G
Guest
Hi, hope this isn't a dog's breakfast to follow but I'm banging my head
against a wall here. I've read & tried tons of your posts for the last 2
days on concatenating but am not finding my way yet (learned a heck of a lot,
though!!).
2 questions:
(1) I'm using Duane Hookom's excellent Generic Concatenate (DAO) to list all
the names associated with a case, separated by commas.
tblApplic has all the names & personal details
tblCaseAppl is the join tbl (many-many)
tblCase1 has all the case info that's not person-specific
normalized, ref integrity, etc.
OK, mainform frmCase1 has a couple of subforms, (1) sfCaseAppl1 to show
personal info for each name and (2) sfAppt to show specialists appointed to
the case. Because I'm having major trouble with referencing & merging this
info to letters I'm using a txtbox on the main form to display the names from
subform.
The control source for txtCaseKids is:
=Concatenate("SELECT DISTINCT [Child] FROM qryApptKidSf WHERE
[qryApptKidSf].[CaseID] =" & [CaseID])
--[Child] is text, not ID number--
and it works great. However, when the frmCase1 first opens, or on a new
record where the recordset isn't yet populated/saved, I get an error msg:
Run-time error '3075':
Syntax error (missing operator) in query expression
'[qryApptKidSf].[CaseID]='
Debug highlights the line:
Set rs = db.OpenRecordset(pstrSQL)
but trying it in Immediate window with hardcoded CaseID works fine.
Obviously I need some sort of event to fill the txtCaseKids only when
there's a value to show, but can't figure out which event(s) to use.
(2) I would much rather use the tables directly instead of qryApptKidSf but
can't manage to get Duane's recent many-many suggestion to work:
SELECT CaseID, CaseNo, Concatenate("SELECT ChFirst &' ' & ChLast FROM
tblApplic INNER JOIN
tblCaseAppl on tblApplic.CaseApplID = tblCaseAppl.CaseApplID
WHERE CaseID =" & CaseID) as CaseKids
FROM tblCase1;
What event should I use for populating the textbox and how would I modify
the many-many code so it will work?
Any help you can suggest on either of these would be super.
Thanks,
against a wall here. I've read & tried tons of your posts for the last 2
days on concatenating but am not finding my way yet (learned a heck of a lot,
though!!).
2 questions:
(1) I'm using Duane Hookom's excellent Generic Concatenate (DAO) to list all
the names associated with a case, separated by commas.
tblApplic has all the names & personal details
tblCaseAppl is the join tbl (many-many)
tblCase1 has all the case info that's not person-specific
normalized, ref integrity, etc.
OK, mainform frmCase1 has a couple of subforms, (1) sfCaseAppl1 to show
personal info for each name and (2) sfAppt to show specialists appointed to
the case. Because I'm having major trouble with referencing & merging this
info to letters I'm using a txtbox on the main form to display the names from
subform.
The control source for txtCaseKids is:
=Concatenate("SELECT DISTINCT [Child] FROM qryApptKidSf WHERE
[qryApptKidSf].[CaseID] =" & [CaseID])
--[Child] is text, not ID number--
and it works great. However, when the frmCase1 first opens, or on a new
record where the recordset isn't yet populated/saved, I get an error msg:
Run-time error '3075':
Syntax error (missing operator) in query expression
'[qryApptKidSf].[CaseID]='
Debug highlights the line:
Set rs = db.OpenRecordset(pstrSQL)
but trying it in Immediate window with hardcoded CaseID works fine.
Obviously I need some sort of event to fill the txtCaseKids only when
there's a value to show, but can't figure out which event(s) to use.
(2) I would much rather use the tables directly instead of qryApptKidSf but
can't manage to get Duane's recent many-many suggestion to work:
SELECT CaseID, CaseNo, Concatenate("SELECT ChFirst &' ' & ChLast FROM
tblApplic INNER JOIN
tblCaseAppl on tblApplic.CaseApplID = tblCaseAppl.CaseApplID
WHERE CaseID =" & CaseID) as CaseKids
FROM tblCase1;
What event should I use for populating the textbox and how would I modify
the many-many code so it will work?
Any help you can suggest on either of these would be super.
Thanks,