Prev: Opening a query from a command button
Next: Locked Subform still lets me delete a record
From: Maxwell on 4 May 2010 11:55
Hi all,

Just would like some of your views as to how to go about solving a problem.
I have two tables (A and B) that are linked through a form to create records
in a link table (C).
Lets say that (A) represents bedrooms and (B) represents tenants. Obviously
a tenant could stay in different rooms at different times, and obviously a
room can have different tenants at each time. So (C) tracks instances where
a tenant stays in a given room. Table (C) also contains the start dates and
end dates of that instance of stay. All straightforward so far…
Now, (C) manifests itself in a continuous subform in a tenant management
form. That way you can see all of the rooms they stayed in before. The
bottom form entry is blank, and new records are added by selecting a vacant
room from a combo box…
Until a silly user goes to enter a date before they have selected a bedroom
from the combo box that displays empty rooms. If you select anything before
you have set that relationship, the DB hangs and dies. Unfortunately someone
will eventually try this.
What I wanted to do was have the other fields turned off unless there was a
positive entry in the combo box. Alas, if I turn off the offending fields,
you can't change them in any of lower iterations of the form. Essentially I
just want to block fields that are not yet attached to the link table.
Beyond displaying records that can't be changed in the subform, and creating
a whole new 'edit' form where only one record is shown at a time, does anyone
have any suggestions as to how to solve this?

Many thanks,
Max

From: Marshall Barton on 4 May 2010 13:30
Maxwell wrote:
>Just would like some of your views as to how to go about solving a problem.
>I have two tables (A and B) that are linked through a form to create records
>in a link table (C).
>Lets say that (A) represents bedrooms and (B) represents tenants. Obviously
>a tenant could stay in different rooms at different times, and obviously a
>room can have different tenants at each time. So (C) tracks instances where
>a tenant stays in a given room. Table (C) also contains the start dates and
>end dates of that instance of stay. All straightforward so far�
>Now, (C) manifests itself in a continuous subform in a tenant management
>form. That way you can see all of the rooms they stayed in before. The
>bottom form entry is blank, and new records are added by selecting a vacant
>room from a combo box�
>Until a silly user goes to enter a date before they have selected a bedroom
>from the combo box that displays empty rooms. If you select anything before
>you have set that relationship, the DB hangs and dies. Unfortunately someone
>will eventually try this.
>What I wanted to do was have the other fields turned off unless there was a
>positive entry in the combo box. Alas, if I turn off the offending fields,
>you can�t change them in any of lower iterations of the form. Essentially I
>just want to block fields that are not yet attached to the link table.
>Beyond displaying records that can�t be changed in the subform, and creating
>a whole new �edit� form where only one record is shown at a time, does anyone
>have any suggestions as to how to solve this?
>

That should not happen, so, depending on what you mean by
"the DB hangs and dies", I conclude that either you have
something else going on that's getting in the way or your
form is corrupted.

I think the first thing to check is if there is any VBA code
for the date text box that tries to do something that
doesn't work if the combo box is not set.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Opening a query from a command button
Next: Locked Subform still lets me delete a record