Silly question time (1 Viewer)

Is it possible to have the field names in a table be set by the values in another table? The desire being that for a database used in various locations, the local variations could be changed in one table which would then propagate that change throughout all the forms, reports, tables etc.

You need to expand on this.
Changing of field names it's not at all necessary in any occasion.

I agree with vbaInet, we need more inifo. Perhaps an example of what you have in mind might help also.

OK, I will try and explain. I have a series of "Run ID" which varies from location to location.
So, at site 1, the values might be 32,70,64,109 and 266; at site 2, the values might be 56, 264, J1, and J3 - - - sometimes, these runs might be moved from site to site, and they appear on forms and reports (mostly as labels), and it would be useful if all of these changes could be done quickly and easily should a run move from one location to another.

I have done this in the past, where the table simply had Run1, Run2, Run3, Run4, and then forms and reports simply had their label captions changed accordingly, whilst the data source remained the same - however, this threw up a problem when one of our clients demanded a report in Excel format, because the Excel headings came out as Run1, Run 2 etc regardless of the location.

So, is it possible that I could have a table that I can simply put in the run ID's, and have that change the related labels on all the forms and reports, and more importantly give the right column headings on the export of an Excel report?

Right, I have a knocked up a very quick version here

On the form, see the labels Run 1, Run 2 etc - I would like those to be the numbers that are in the field RunID that are in the table RunID.

Theory being that if all forms and reports were set up this way, changing the numbers in RunID would change every label on every form throughout the database.

And, would it be possible to get an excel report with those same numbers as column headings and change whenever the numbers in the table RunID are changed?

Bang on - there will be 8-10 copies in all, cannot be linked, and my goal is to be able to quickly modify the labels on reports and forms so they make sense to the site they are being used at.

The design is essentially one that I impose, but the labels on forms on reports have to reflect the Run ID's that are specific to the site.

Initially, I simply changed the labels on the forms to read as I wanted, leaving the underlying table alone - - nobody knew any different, and it was reasonably easy (change one set of labels, then copy and paste into each report in turn). However, doing this meant that the field names were left alone, and when the excel report was run it showed Run1, Run2, Run3 etc as the column headings - - and for this one report alone, I had to go back to the drawing board and when making a copy for another site, I have been changing the field names, all the forms, all the queries, all the reports etc..... and it is getting really laborious.

If I could have labels on each report or form that are set to show the values in a small table, regardless of the recordsource for the rest of the form/report, that would change to the values that were put into the table, it would be ideal. I wouldn't even need a form to do it, as I would be the sole person who would make the changes.

I also need to have this one excel report also show the altered values as column headings. I intend at some point to go back to the main client who wants this report and see if I can't persuade them to have it as PDF, which would make things a whole lot easier - - we have a fairly well founded suspicion that they don't even refer to this report (not least as one of the fields only contains 255 characters on the excel sheet, yet the source is a memo field with potentially a lot more); they are however notoriously pedantic about things, and will block any changes 'just because they can'

fat controller said:

.. and for this one report alone, I had to go back to the drawing board and when making a copy for another site ...

Click to expand...

You could (easy) change the column heading in Excel from Ms-Access, if it was the only problem you had with that setup.

JHB said:

You could (easy) change the column heading in Excel from Ms-Access, if it was the only problem you had with that setup.

Click to expand...

It is and it isn't. If I could do that, then it would prevent me having to change the actual table field names when making a copy of the database for another site.

However, I would still have to go and manually change label names throughout the database (forms and reports) - - would it be possible to have all the labels have their values set by the values contained in a separate table?

So for example, the field names would be:

RUN1
RUN2
RUN3
RUN4
RUN5
RUN6
RUN7

But on each report/form for Site A, I would want the labels to show:

32
64
70
109
215
266
280

And for site B,

8
9
12
93
219
264

So, what I am looking for is a way to go into a table, change the values in there, and then have all the labels change accordingly?

Change the labels to textboxes and treat them as data. They can be made to look and behave exactly like a label by setting the BorderStyle to Transparent, Enabled to False and Locked to True.

Join the LabelCaptions table into the recordsource query of the forms and reports.

Include records for all versions of the captions in the table and have a field to indicate which rendering of the database they are used in. Select using the value in this field.

A Users table can also be included with a field to indicate which rendition applies to that user making the entire labeling process user specific and transparent.

Galaxiom said:

Change the labels to textboxes and treat them as data. They can be made to look and behave exactly like a label by setting the BorderStyle to Transparent, Enabled to False and Locked to True.

Join the LabelCaptions table into the recordsource query of the forms and reports.

Include records for all versions of the captions in the table and have a field to indicate which rendering of the database they are used in. Select using the value in this field.

A Users table can also be included with a field to indicate which rendition applies to that user making the entire labeling process user specific and transparent.

Click to expand...

OK - I 'think' I get most of that, but please bear with me as I am still very much a learner at this.

So, create a table with label information contained within - presumably that would require fields similar to:

SiteName (this would be the field that would determine which version is rendered)
Run1Label
Run2Label
Run3Label

and so on, with the record for each site being the list that gets used?

Then I would need to use a query (would this be a union query?) and link the forms/reports to that as opposed to linking the form directly to the table?

Simple demo attached.

You might add another field to designate the labels for other forms and select based on that for each form but it is not really necessary.

Note that the "label" captions are not loaded by name but by ControlSource. You can use the same query arrangement on any form and the label with the matching ControlSource will get the designated caption.

Hence in my example you could use one language to name the fields so that it would be easier to put in the right translation during setup.

Champion! I have got it working to a point (and clearly still need to fiddle with it), however, there is one wee problem that I don't understand.

With the form set to the query as its record source, I cannot add any new records - I presume that is because it doesn't know which table to create the new record in?

Am I right in thinking that it should be a sub-form linked to the label table as its data source, and the main form remains linked to its own table?

fat controller said:

Am I right in thinking that it should be a sub-form linked to the label table as its data source, and the main form remains linked to its own table?

Click to expand...

That would be a good idea.

I didn't check if it could take new records though I thought about it afterwards. I have never used it myself. The idea came from another user who said he had done it. Endre used to frequent this form a few years ago.