Kent:
In the set-up I described all resorts will be shown at once; in the list of
the combo box bound to the resort id column in the subform when its dropped
down. When the data has been entered each row in the subform will show a
resort on which the user in question has worked on the day in question. The
operation here is the insertion of a row into the Verifications table for
every user/resort/day. A continuous view subform shows rows from the table,
so each insertion is the creation of a new row in the subform, which can be
done very easily in the way I described.
The above would be the usual approach to this sort of task, but you could do
it in the way I think you want by including an unbound list box of resorts in
the parent form; this could be set up in exactly the same way as I described
for the bound combo box in the subform, or you could set the ColumnWidths
property to something like 1.5cm; 8cm (experiment to get the best fit) to
show both the id and resort name. The combo box in the subform can then be
omitted. The subform control's LinkMasterFields property would then include
the list box, e.g.
txtuser;txtDate;lstResorts
and the LinkChildFields property would be extended to:
[user];[date];[resort id]
Once the user and date had been entered, as a resort is selected in the list
box the subform would refresh to display existing row(s) or allow a new row
or rows for that user/date/resort combination to be entered.
I'm still not clear about your model, but I'm inclined to think that the
Verifications table is not well designed and is contravening the 'information
principle' by having the three Co1, Co2 and Co3 columns, which is what's
known in the trade as 'encoding data as column headings', and is 'a bad
thing'. I'd need to know more about the underlying business model to be on
firm ground, but my gut feeling is that this table should have just one
column to hold the number of items worked on and a qualifying column, e.g.
CompanyID or whatever suits. The CompanyID column would hold the data
currently encoded as the three column headings. All data would then be
correctly stored at column positions in rows in tables, which is what the
information principle states. It also means that the number of companies is
not limited to three per user/day/resort of course as its simply a question
of adding additional rows as necessary. Even if you only work with three at
present I doubt that's an immutable law of nature.
Ken Sheridan
Stafford, England
Bunky said:
Ken,
Thank you for your wealth of information. Unfortunately, I did not make my
self clear. My Bad!
I need to have all the resorts showing on the screen at once. One user
would pull down their name and scan the entire list of resorts and ids shown,
find the ones they worked on this day and enter the number of items they
worked on for that resort. Then they could drop 4 lines down and enter
another total of items they worked on for the same exchange company or a
different one (the 3 different groups).
I thought I had to use the Resort Table in the subform to utilize the
continuous form option. This way all of the resorts and ids display on the
page. I just can't figure out how to get the entry boxes to on the same form
or another subform, to pull in the data entered on each resort the user
touched that day
I hope this clarifies what I am trying to do. If it does not, please let
me know and I will try again.
Thanks for responding.
Kent
:
The subform should be based on the verification table, and have a combo box
bound to the [resort id] column set up as follows:
RowSource: SELECT [resort id], resort FROM Resorts ORDER BY resort;
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
The above will show the resort name, but the value of the control will be
the hidden resort id. Normally the id is not shown in the form in cases like
this, but if you do want to show it add a text box with a ControlSource of
[resort id] to the subform and set its Locked property to True (Yes).
You should set the LinkMasterFields property of the subform control to the
names of the user and date controls on the parent form, separating them with
a semi colon, e.g.
txtuser;txtDate
and the LinkChildFields property to the names of the corresponding columns
in the subform's underlying table, i.e.
[user];[date]
though I'd strongly advise against using date as a column name as it’s the
name of a built in function. Use something more specific like
transactiondate.
That should work, but your table design might be flawed. If the Co1 Co2
Co3 columns are distinct attribute types of the verifications entity type
than the design is fine. But if they are different values of the same
attribute type they should be stored as separate rows in a related table, not
as three columns in the verifications table. In which case you'd either use
a single view subform with a continuous view subform in it, or correlated
subforms, both in continuous form view. For an example of correlated
subforms tale a look at the Customer Orders form in the sample Northwind
database which comes with Access. You'll see how, as an order is selected in
the first subform, the second subform shows the details of that order.
Ken Sheridan
Stafford, England
:
I have a form that shows resort id, resort name and then three areas for the
user to enter a number for the number of verifications they have done for
that day by exchange company. I have two tables - a resort table that
contains all resorts and their ids, and a verification table that contains
the user, date, resort id, and the three areas for the verification numbers.
The current way I have things designed is I show the user and the date on the
main form. I show all resorts and Ids possible (continuous form) on a
subform. I want to have the area for entering the number of verifications
next to the resort but have not been able to get my keys, child/master info
set so everything shows like below.
ID Resort Co1 Co2 Co3
ID Resort Co1 Co2 Co3
etc, etc.
Ideas are certainly welcome!