Setting defaults from a table entry?

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Hi,

I have a DB in which I store info on various greenhouse crops. Each of
these crops are handled sequentially, and I'd like to use the same forms,
reports, etc. for different crops at different times. To this end the
forms, etc. are designed to use crop specific defaults in several locations
around the app., such as type of crop, shipping dates & times and such.
Rather than to make these defaults settings changes in a variety of
locations around the program, I made a form to enter the settings for each
default and store them in a table with the intent of having each place in
the program that needs a default to be able to just pull it from this one
central repository of settings so the changes only need to be made in one
place. The only way I've been able to make this work with my forms is to
base the main form's recordsource on a query that includes this table of
settings along with the other table for the forms data and then put hidden
text boxes on the form tied to this query so that the subforms can pull from
these textboxes. This works fine for the subforms, but it has the side
effect of making it impossible to edit records in the main form's table.

Does anyone here know a better way to do this? Is there a way to refer to a
field in this defaults table without including it in the main form's
recordsource?
 
Have you considered any of the following?

(1) using an/some subforms to contain the default
data -- you can design them so that it is not
obvious they are subforms
(2) working with the query you use for the main
form, to make it updateable. If you research
the things that make queries un-updateable
and create your queries carefully, it is some-
times surprising how "complex" a query can
be and still be updateable.
(3) using the DLOOKUP domain aggregate
function in the Control Source of the default
values, so that they don't interfere with your
query
(4) using VBA code in an appropriate event
(Open if all the defaults will be the same on
each record; Current if they may be different
from record to record) to retrieve the defaults
and populate unbound Controls
(5) using ComboBoxes for the default values
with bound column being the unique id (of the
crop, I guess) but displaying the information
from the defaults table.

Seems to me that all would be possibilities.

Larry Linson
Microsoft Access MVP
 
I'm sure I'll find a solution in one of those suggestions.
I <thought> there must be a way, had no idea there were so many ways.
 
Back
Top