When I did what you suggested nothing happened. When I
click on Ctrl G a VB window popped up called Immediate.
I placed the code in, which I can't compile. I went to my
form a for the reason textbox I selected type for Control
Source. Each record shows without the concatenation. I
should have 5 records with concatenation. What I get is
10 records each with just one reason. I believe I am
getting 10 due to fact of the query which has a
relationship of tblVisit and tblVisitReason. If I get rid
of tblVisitReason I get 5 records, but I lose the relation
to tblClinicType to tblAppointmentType. Any suggestions?
-----Original Message-----
Can't you add the concatenate() function to your form's
record source query?
When you stated earlier "This doesn't work." to my
suggestion about the
debug window, what do you mean?
--
Duane Hookom
MS Access MVP
I see your point, but here is my problem, maybe you can
help solve. This works fine to place in a textbox that
I
use on a continuous form. However, when the
TimerInterval
event happens then the textbox has to requery, which
causes the information in the textbox to flash. The
information the other fields doesn't do this since they
are related to fields from the underlying query. So my
thought was maybe I can place all this information in a
listbox for the user to see all the customers and what
they are there for and pick the person who is
visiting. I
hope you see where I am going with this? Do you have
any
solutions or ideas that I might use to make the form
more
enticing for the user to be happy with?
Someone mentioned ECHO to turn off the flash however
once
you turn it back on it still causes the flash.
Thanks for any input.
-----Original Message-----
The query containing the Concatenate() function should
have only one record
per VisitID.
--
Duane Hookom
MS Access MVP
This doesn't work. I think the problem is where xx
is a
legitimate VisitID from your data. When I run the
query I
just get a column of all the VisitID and Type. Is
there a
way to have the query show just each VisitID and all
the
associated Type for each visit? I feel this can be
done,
but how? Stay with me on this one. It is a little
difficult, but hopefully with your help I can clear
some
of the trees in the forest.
Thanks
-----Original Message-----
I would create a single query out of all you selects
==qselVisitReasons================
SELECT tblVisit.VisitID,
tblAppointmentType.Type
FROM (tblCustomer INNER JOIN tblVisit ON
tblCustomer.CustomerID=tblVisit.CustomerID) INNER
JOIN
((tblClinicType INNER JOIN tblAppointmentType ON
tblClinicType.TypeID=tblAppointmentType.TypeID)
INNER
JOIN
tblVisitReason ON
tblClinicType.ClinicTypeID=tblVisitReason.ClinicType)
ON
tblVisit.VisitID=tblVisitReason.VisitID
Then try open the debug window (press Ctrl+G) and
then
enter the following
?Concatenate("Select Type from qselVisitReasons
WHERE
VisitID =" & xx)
where xx is a legitimate VisitID from your data.
--
Duane Hookom
MS Access MVP
Thanks for the response.
I have the 5 or so tables that are related so it
is a
long
string. Do I need to do something different?
With
your
recommendations this is what I came up with, but I
keep
getting an ERROR Run Time Error 3061, To few
Paramters.
Expected 1.
Then my columns show
34 #Error
35 #Error
36 #Error
etc.
SELECT tblVisit.VisitID, Concatenate("SELECT
tblAppointmentType.Type
FROM (tblCustomer INNER JOIN tblVisit ON
tblCustomer.CustomerID=tblVisit.CustomerID) INNER
JOIN
((tblClinicType INNER JOIN tblAppointmentType ON
tblClinicType.TypeID=tblAppointmentType.TypeID)
INNER
JOIN
tblVisitReason ON
tblClinicType.ClinicTypeID=tblVisitReason.ClinicType) ON
tblVisit.VisitID=tblVisitReason.VisitID
WHERE tblVisitID =" & [VisitID]) AS Reason
FROM tblVisit;
-----Original Message-----
Placing this is a query requires a table with
VisitID
in
it.
SELECT VisitID, Concatenate("SELECT....WHERE
tblVisitID
=" & [VisitID])
FROM tblWithVisitID;
--
Duane Hookom
MS Access MVP
Duane here is my SQL that I used for a Row
Source
of
a
textbox, but how can I put it in a query to be
used
later?
=Concatenate("SELECT tblAppointmentType.Type
FROM (tblCustomer INNER JOIN tblVisit ON
tblCustomer.CustomerID=tblVisit.CustomerID)
INNER
JOIN
((tblClinicType INNER JOIN tblAppointmentType
ON
tblClinicType.TypeID=tblAppointmentType.TypeID)
INNER
JOIN
tblVisitReason ON
tblClinicType.ClinicTypeID=tblVisitReason.ClinicType) ON
tblVisit.VisitID=tblVisitReason.VisitID
WHERE ((tblVisit.VisitID))=" & [txtVisitID])
thanks
-----Original Message-----
I have almost always used it in a query. The
code
has
comments on its usage:
'SELECT FamID,
'Concatenate("SELECT FirstName FROM
tblFamMem
' WHERE FamID =" & [FamID]) as
FirstNames
'FROM tblFamily;
If you have other questions, come on back.
--
Duane Hookom
MS Access MVP
[email protected]...
Duane I am using your Concatenate function
and
it
works
fine. My question is can I use the results
in
a
query?
If
so, how? I want to be able to create a
listbox
with
the
customers name along with the concatenate
that
was
created
and some other info. If this function
can't be
used
for
what I need, do you have any other
suggestions
that I
may
use? Thanks for any input.
.
.
.
.
.
