Forms view v/v Table set up

[IKMD66]

Hi,

Just beginning on Access therefore am gratefull for any assistance provided.

Scenario
I want to record general availability of people on a daily basis - straigh
forward enough

Person ID, Day, Time From, Time To, Total Hours (calculated) and a check box
to indicate the record is active.
By making the Person ID and Day fields primary the person can only ever have
one entry per day which is exaclty what I want,

The issue arises when I get onto forms I want to dispay default entries for
each day and allow the user to select the checkbox (to make it active) and
correct any entries that require amendment.

If I repeat the fields on the form seven times (for seven days) and rename
them accordingly as soon as I change e.g a Time From on Monday the values
then all change to the same for each of the seven lines. If I do this via
seperate subforms the result is the same.

The only way I have been able to do this without getting the above reaction
is to create seven seperate tables (Monday, Tuesday etc) and then place them
in the form - this way if I change the entry for Monday the other fields do
not change to reflect the Monday record. This seems an inefficient way to do
things and would appreciate any advise on how to do this in a more efficient
way.

Thanks in advance.

Regards,
Kirk

 

[Tom van Stiphout]

On Mon, 19 May 2008 06:18:03 -0700, IKMD66

If you create a subform and bind the fields to the underlying table,
you will not have the problem you describe.
Take a look at any subform in any sample database, and observe a
single record can be edited without affecting the others.

-Tom.

 

[IKMD66]

Tom,

It may be simple but I thought the fields were already bound to the table
via control source. If this is not the case can you tell me how to do this?

Thanks,
Kirk

 

[John W. Vinson]

If I repeat the fields on the form seven times (for seven days)

Don't.

Instead, use a continuous Subform with one *row* (record) per day. Make it big
enough to display seven rows; you can use a filter to limit the records to one
week, or a scrollbar to display earlier days' data.

 

[Tom van Stiphout]

On Mon, 19 May 2008 08:40:04 -0700, IKMD66

The ControlSource *is* indeed the way to bind the controls to the data
source.
Can you compare your form with that of one in a sample application and
see how the two are different?

-Tom.

 

[IKMD66]

John,

Thanks for the response....I had pretty much arrived at the solution of a
continuous form or seven seperate tables - 1 for each day. the seperate
tables allows me to provide the weeks view with default times (differing per
day). the user can select a check box to activate the record. For ease of
query / reporting I can run a make a query to consolidate back in to one
table. The continuous form doesn't allow ne to set defaults etc.

Again thanks.

Regards,
Kirk

 

[John W. Vinson]

Thanks for the response....I had pretty much arrived at the solution of a
continuous form or seven seperate tables - 1 for each day. the seperate
tables allows me to provide the weeks view with default times (differing per
day). the user can select a check box to activate the record. For ease of
query / reporting I can run a make a query to consolidate back in to one
table. The continuous form doesn't allow ne to set defaults etc.

Seven tables is a BAD IDEA. You can certainly set defaults with a continuous
form but it's somewhat harder, and you may need to do it programmatically
rather than by just setting the Default Value property of a control on the
form.

It's a somewhat rocky and difficult part of the terrain, but I'd really
investigate other solutions than the seven tables. It'll be a real nightmare
down the line to consolidate the data.

 

[IKMD66]

John,

FYI - I got back to one table for the complete week and this works a treat
for defaulting.

Cheers,
Kirk