How to inject a common field into reports & forms

[Richard Harison]

I have created a database which requires the same heading in reports and in
forms. IOW different sites using it have different names--which the user
chooses. (Joe's store, Pete's store...etc.) This also requires me to allow
label changes in forms.
Now I know I did not do this properly---there has to be a superior way.
What I did was create a small table with the required information. Since all
the reports were based on queries, adding the fields for the report header from
that small table was not a big deal. I simply added the site name table to each
query that required it and pulled down the necessary fields.
(I realize the issue of data redundancy)
The problem is that I tried the same technique with a form. (I needed to make
some labels user adjustable--even in an .mde environment.)
Because the additional table I add to a form based on that query, Access
says--sorry can't add/goto that record. Understandable.
SO knowing I am using a cheat solution to begin with...what is the PROPER way to
allow any report or form to access data from a standalone table?

 

[Guest]

You are on the right track by creating a table to populate lables, titles,
etc on your forms and reports in the situation where those objects will be
different for different locations.

A better approach than including them in the record source for your forms
and reports would be to use the DLookup() function to load the values where
you want them. If a control has a control source, like a text box, you can
put the DLookup in the Control Source property:
=DLookup("[Message_Text]", "LabelTable", "[MsgId] = " & 37)

(The above is just an example where I am thinking you have a message, and a
Message ID so it knows which message to present).

You will find that some developers doing this will use a text box rather
than a label and set the enabled propert to False and the Locked property to
True and set the back color so it looks like a lable.

You can use a label, but then you have to populate the Caption property in
the Load event of forms or the open event of reports:

Me.SomeLabel.Caption = =DLookup("[Message_Text]", "LabelTable", "[MsgId] = "
& 37)

 

[Richard Harison]

I may have forgotten to mention that my database is actually 2 databases--a
front end (reports, forms, macros & code, etc.) and a back end (tables only.
The small "user fill it out" table is in the front end database. Thanks

 

[Richard Harison]

Thanks for the help... what I don't understand is your DLookup code for the
ControlSource property. I am using a text box as opposed to a label, figuring
it had to reference something somewhere else. Here is your function:
=DLookup("[Message_Text]", "LabelTable", "[MsgId] = " & 37)
The first 2 parameters seem obvious: name the field and name the table
the third parameter escapes me entirely! "[MsgId] = " & 37
Where do I use quotes in this puppy, what is '37' all about?== ASCII code?
Answer these only to confirm what I already know--- that you are most assuredly
on the right track!


--
All the Best,
Richard Harison

You are on the right track by creating a table to populate lables, titles,
etc on your forms and reports in the situation where those objects will be
different for different locations.

A better approach than including them in the record source for your forms
and reports would be to use the DLookup() function to load the values where
you want them. If a control has a control source, like a text box, you can
put the DLookup in the Control Source property:
=DLookup("[Message_Text]", "LabelTable", "[MsgId] = " & 37)

(The above is just an example where I am thinking you have a message, and a
Message ID so it knows which message to present).

You will find that some developers doing this will use a text box rather
than a label and set the enabled property to False and the Locked property to
True and set the back color so it looks like a label.

You can use a label, but then you have to populate the Caption property in
the Load event of forms or the open event of reports:

Me.SomeLabel.Caption = =DLookup("[Message_Text]", "LabelTable", "[MsgId] = "
& 37)

I have created a database which requires the same heading in reports and in
forms. IOW different sites using it have different names--which the user
chooses. (Joe's store, Pete's store...etc.) This also requires me to allow
label changes in forms.
Now I know I did not do this properly---there has to be a superior way.
What I did was create a small table with the required information. Since all
the reports were based on queries, adding the fields for the report header
from
that small table was not a big deal. I simply added the site name table to
each
query that required it and pulled down the necessary fields.
(I realize the issue of data redundancy)
The problem is that I tried the same technique with a form. (I needed to
make
some labels user adjustable--even in an .mde environment.)
Because the additional table I add to a form based on that query, Access
says--sorry can't add/goto that record. Understandable.
SO knowing I am using a cheat solution to begin with...what is the PROPER way
to
allow any report or form to access data from a standalone table?

 

[Richard Harison]

Good Morning, Klatuu!
Thanks for your help! Just thought I'd let you know your idea worked perfectly!
Here's the syntax I used:
=DLookUp("[VolShift4]","[tblSetup]","[SetupID]=1")
(The table has only 1 record, of course)
Sometimes I feel that Access's *what you can and cannot use as a control source*
is only matched by theology in complexity!
--
All the Best,
Richard Harison

You are on the right track by creating a table to populate lables, titles,
etc on your forms and reports in the situation where those objects will be
different for different locations.

A better approach than including them in the record source for your forms
and reports would be to use the DLookup() function to load the values where
you want them. If a control has a control source, like a text box, you can
put the DLookup in the Control Source property:
=DLookup("[Message_Text]", "LabelTable", "[MsgId] = " & 37)

(The above is just an example where I am thinking you have a message, and a
Message ID so it knows which message to present).

You will find that some developers doing this will use a text box rather
than a label and set the enabled propert to False and the Locked property to
True and set the back color so it looks like a lable.

You can use a label, but then you have to populate the Caption property in
the Load event of forms or the open event of reports:

Me.SomeLabel.Caption = =DLookup("[Message_Text]", "LabelTable", "[MsgId] = "
& 37)

I have created a database which requires the same heading in reports and in
forms. IOW different sites using it have different names--which the user
chooses. (Joe's store, Pete's store...etc.) This also requires me to allow
label changes in forms.
Now I know I did not do this properly---there has to be a superior way.
What I did was create a small table with the required information. Since all
the reports were based on queries, adding the fields for the report header
from
that small table was not a big deal. I simply added the site name table to
each
query that required it and pulled down the necessary fields.
(I realize the issue of data redundancy)
The problem is that I tried the same technique with a form. (I needed to
make
some labels user adjustable--even in an .mde environment.)
Because the additional table I add to a form based on that query, Access
says--sorry can't add/goto that record. Understandable.
SO knowing I am using a cheat solution to begin with...what is the PROPER way
to
allow any report or form to access data from a standalone table?