Using table for control source in report expression builder

[adrift]

Is it possible without using a subform to refer to the expression builder / table for a control source?

When I try writing an expression "=

![variable]" as a control source, it asks for a parameter value and displays an #error?

Basically I need to refer to many tables in one report. I cannot use a subreport because I am writing a narrative that textualizes my data but the data items come from different tables in a non sequential fashion. I'm basically writing a paragraph that concatenates strings and variable values.

They are many to many tables that I've tried creating a query for but it doesn't work. Ie: the value for one variable in table 1 is repeated a number of times if there are more rows in table 2.

Ultimately, I'd like to write a narrative as follows in a report

"Hi my name is " & [Nametable]![name] & "I am " & [agetable]![age] & "years old" etc. etc.

 

[Tash]

Hi,

I'm not sure if I understand your problem correctly, but would it be possible to reference your "name", "age" tables in a lookup field on a main table? It sounded like none of your tables were related (so that made it difficult for a query for your report), however, your narrative appears to be referencing a single or double column table that might be able to be referenced through a lookup field. When you refered to it on the report it would look something like the following:

[TableName].[Column](1) or [TableName].[column](2), etc.

TASH

 

[adrift]

Ok, for example.. I have a table for medications, one for labs and one for feeds. they are many to many tables (i think).. here is an example

Medications Table

id med1 med2 med3 date
1 amikacin bles caffiene 10/02
1 bles 10/03
1 amikacin bles 10/04

Labs

id lab date
1 xray 10/02
1 ct scan 10/02
1 xray 10/02
1 mri 10/03
1 surgery 10/03
1 ekg 10/03

Feeds

id type date
1 breastmilk 10/02
1 isomil 10/02
1 isomil 10/02
1 breastmilk 10/03

NOW, say I want to create a narrative based on these 3 tables for the date 10/02..

Baby [id] had [med1], [med2], [med3] with the following labs: [lab], and the following feeds: [type]

I hope that is a bit clearer..

thanks , shawn

 

[adrift]

someone please?

cheers, shawn

 

[braindead2]

various ways to handle this would be to use dlookup() or create a query that contains all the fields you want or VBA.
lookup will be slowest, query the easiest and VBA would be the most powerful

VBA example using DAO but will need serious editing;
dim rst,rst1,rst2 as recordset
dim db as database
dim srtid ,strmed,strlab,strfeed, as string
dim criteria as string
criteria = ("id = " & me.id &" and date = " & me.date)
set db = currentdb
set rst = db.openrecordset("medstable)
set rst1 = db.openrecordset("labs&quot
set rst2 = db.openrecordset("feed&quot
rst.findfirst(criteria)
do until rst.nomatch
strid = rst!id
strmed = strmed & rst!med1 &","&rst!med2&","&rst!med3
rst.findnext(criteria)
loop
rst1.findfirst(criteria)
do until rst1.nomatch
strlab = strlab & rst!lab & ","
rst1.findnext(criteria)
loop
rst2.findfirst(criteria)
do until rst2.nomatch
strfeed = strfeed & rst!feed & ","
rst2.findnext(criteria)
loop
rst.close
rst1.close
rst2.close
set db = nothing

me.text1 = "Patient " & strid &" used " & strmeds &" and " & " labs were " & strlab & " and ate " & strfeed

 

[adrift]

thank you brain.. I tried a query.. but it didn't seem to work because there were not an equal number of records per table.. ie: a patient may have 6 meds, 4 feeds, and 8 labs.. when selected for a query it generates 8 records, but for the med and feeds variables, it duplicates the previous entries.. which of course won't be sufficient for summary..

 

[Tash]

Sorry it took so long to respond, I have been out of work for a while...

I was wondering if you could do this a simple way... How about only having 2 tables? One table would be the main table which would turn into the main form. The other would be a subtable converting into your subform. It could go something like this.

MAINTABLE

ID BabyName (Other fields could be added about subject)
1 Tiger Date of birth, gender,etc.

SUBTABLE

BabyID Date Type Description
1 10/19/01 Meds amikacin
1 10/19/01 Meds bles
1 10/19/01 Meds caffiene
1 10/20/01 Feeds Isomil
1 10/20/01 Labs Xray

Your Type field could be a drop down list with the different types of actions taken, while the description field would hold the different types of treatment/feeds/labs detail. I'm not sure how complicated your database is, but I made a similar one based upon our resort animals for tracking their physician checkups. You could have a main form with the animals specs, with a subform showing all of the actions taken on that particular animal. To ease data entry of the daily repetitive stuff, you could create another form with just the animal ID's (in a drop down list), the date, type and descriptions that would link up to the main form.

Your text would look more like a list that could be sorted by "Type":
[BabyName]&" Recieved the following treatments:"
[Subform].[Date] [Subform].[Type] [Subform].[Description]

Hope this helps!

TASH

 

[adrift]

braindead2..

took a while to wrap my head around your example but after going through it, it was VERY easy.. thank you endlessly

cheers, shawn