I have created a 'Work Instruction' report that will have a contatenate field which pulls together different information from different tables and fields. 4 tables involved 'Workshop_Job_Details', 'SV_Pressure Unit', 'Manufacturers' and 'Type'. This is to give a better description of the valve to be worked on. I will use the same field on a test certificate for the customer, again to make some sense of the valve worked on - so in effect it is a desription line coming from the data held in those tables/fields.

I first tried this using a create a table query as follows:-

Detail: [Workshop_Job_Details]![Size] & " " & [Manufacturers]![Manufacturer] & " " & [Workshop_Job_Details]![Pressure] & " " & [SV_Pressure_Unit]![Pressure Unit] & " " & [Type]![Type_of_Valve] & " Valve"

This works fine and creates my table with how I want the information to look in the newly created 'Detail' field.

However I cannot find a way of keeping that contatenate field up to date witout re-running the make table query every time - something users should not need to do when this becomes operational, particularly as it deletes and then creates a new table every time.

I have tried, without success, to use various options as a controlsource for that field on the report - [Size] brings back the size on the report but as soon as I start to add other tables/fields I only get errors.

I've had no joy with update query for this either.

In essence I want to return one description for the one record I am creating.

Can anyone help please?

Not quite sure why you're creating a new table?

Have you thought about creating a QUERY to include all this fields and then use your code to concatenate the field's values?

Yes I have without any success! I have even tried putting code directly into the Control Source for the field as I read somewhere else that was the way to do it i.e. don't create another field just use the current data directly related to the field.

I only gave the new table example to show that I was able to concatenate successfully, and I originally thought I could use the Details field, but this doesn't updata as I mentioned.

How many tables are you pulling data from?

Are they all linked?

Instead of using a make table query, just add that same column into a QUERY that you base your report on (substitute the query as the report's recordsource instead of the table).

I'm pulling data from 4 tables.


Workshop_Job_Details is the main table with Manufacturers, SV_Pressure_Unit and Type all containing one ID field and one data field.

The last 3 three are all linked with the first. They are all set up as 'Only include rows .....' and 'Enforce Referential Integrity' is not selected.

As for setting up a Query - I've gone round in circles with that one without success.

I am obvoiusly doing something fundamenatly wrong!

As for setting up a Query - I've gone round in circles with that one without success.

So, you managed to set one up for the make table, so what's the difference in using the same thing but as a select query and not a make table query?

So, you managed to set one up for the make table, so what's the difference in using the same thing but as a select query and not a make table query?
OK - just tried it again - I've created a description query exactly like the make table query and set it as the Control Source for the field on the report.

I get an 'Enter Parameter Value' box popping up when I run the report.

Any thoughts?

Either you have an actual parameter that it is looking for or you have something misspelled or referring to an object that is not currently open.

Or there's a control on the report referring to a field that is not a field in the report's record source.

OK - just tried it again - I've created a description query exactly like the make table query and set it as the Control Source for the field on the report.
I get an 'Enter Parameter Value' box popping up when I run the report.

Any thoughts?


The query should be the Reports source not an individual field's atleast i think it should, or have I lost the plot?

Brian

The query should be the Reports source not an individual field's atleast i think it should.

Brian
Yes, EXACTLY.

You need to REPLACE the Report's Record Source, not a single control's control source.

Yes, EXACTLY.

You need to REPLACE the Report's Record Source, not a single control's control source.

Good, I haven't gone senile yet then. :)

Brian

Good, I haven't gone senile yet then. :)

Brian

Well, let's not get too hasty there :D You might have just gotten lucky. ;)

Guys - No senility, no lost plots and certainly not luck!! Thanks a million, everything sorted.
I already had a Report Query and had tried adding it there before without success and that was why I tried the Control Source for the field - saw that as a solution somewhere on the web!
Finally worked out that I had the wrong Join the first time I tried it!! I did suggest I was doing something fundamentaly wrong!!

Well, let's not get too hasty there :D You might have just gotten lucky. ;)

LOL
I'll settle for lucky anytime.

Glad its sorted alsimbo.

Brian