Forms: Disable field(s) based on inputted value of another field

[rab26]

Hi,

It has been ten years since I used access - and that was on a very basic level at school!!! I am thus way way out of my depth and would appreciate some help...

I am designing a database that records the ins and outs of a bicycle recycling scheme and am a little stuck on the data input form...

For each bicycle that needs to be recorded on the database, there are two possibilities: either it has been received or it has been tagged and is awaiting retrieval.
For any bike that has been received, its source and date of arrival must be recorded.

My question, is therefore this: given that on my form the "received" field is a checkbox, how do I make the availability of the "source" and "arrival date" fields dependent upon the value of the checkbox?

Can the same be done for the "tagged" field, given it is a checkbox?

Please remember I have no experience of coding in access...I am, however, extremely grateful for any assistance that may be given...
If anything is unclear please ask and I will try and clarify.

Many thanks, rab26

[NeoPa]

No. Further questions will be unlikely as I see it. That's because you've explained it well. A rare occurrence for a first-timer.

One confusion I do have though, is why do you think you need a field/control for Tagged when you already have one for Received - since the logic clearly determines one from the other. Unless I'm missing something that would be logical tautology - a failure to follow the guidelines of Normalisation theory (See Database Normalisation and Table Structures).

First let's just take a minute to clarify some terminology. Reports and Forms have Controls that manage the Fields of the record source. Controls needn't have associated fields, but what we're working with here are controls. FYI. Those without associated Fields are termed Unbound Controls.

Controls have various properties, or attributes, that you can use to help manage the form, as well as the controls - and via them the fields - on it.

One point to consider is that there are two points at which the value of a control can be different from the previous value :

1. When the control is changed. EG. When clicked for a CheckBox.
2. When a different (or first) record is loaded.

In each situation the other controls need to be set to match the current value. This can be done in VBA code. I'll show an example of that here in a general procedure then discuss later how and when this procedure may be called. In my example code I will name the controls in a way that's standard for their control types. IE. [chkReceived], [txtSource] & [txtDateReceived] :

Private Sub ShowControls()
    With Me
        .txtSource.Visible = .chkReceived
        .txtDateReceived.Visible = .chkReceived
    End With
End Sub

Notice that this procedure will both show and hide the extra controls depending on whether [chkReceived] is set or clear.

Now we know what we need to do, we need to ensure it's done at the appropriate points. We've already seen the .Visible property of two controls being used to show and/or hide the controls themselves. Now we're going to look at two more properties related to the control itself (chkReceived], and to the form.

1. .AfterUpdate is a property that sets what happens when the control is updated. Setting this to the string value "[Event Procedure]" in the Properties pane for the control will ensure our VBA procedure is called. Click on the ellipsis (...) button to the right of the value for Access to create the procedure for you automatically.
2. .OnCurrent (on the Form) does a similar job for whenever a record is changed. This includes when the first record is loaded.

Below is some example code for these two event procedures.

Private Sub Form_Current()
    Call ShowControls
End Sub
 
Private Sub chkReceived_AfterUpdate()
    Call ShowControls
End Sub

Try this and see how you get on.

[zmbd]

Hopefully you have started out with normalizing your data-tables (Insights>Database Normalization and Table Structures)

Once you have that done, the rest should fall in to place fairly rapidly.

What concerns me here is that you have a checkbox related to your received date data somehow... why?

Here's kind of my thought,

Table to hold your locations - include one entry for "in transit"
Table to hold your bike inventory
Table to hold the events

So, the data entry would be something along this line

Event: Bike(ABC) arrives 2015-10-28@18H41GST at station 6
Event: Bike(ABC) In-Transit 2015-10-28@18H50GST
Event: Bike(ABC) arrives 2015-10-28@20H40GST at station 1

Entry in the event table

[PK ][DatetimeField   ][FK_BikeInvtry][FK_Location]
[203][2015-10-28 18:41][6            ][25]
(other events)
[203][2015-10-28 18:50][6            ][1]
(other events)
[987][2015-10-28 20:40][6            ][15]

...

Need to check the status of a bike then one would query on [FK_BikeInvtry] = 6, sort order on the date [DatetimeField] descending, return TOP 1... you can filter the records down more by WHERE [DatetimeField]> today's date starting at 00h00

I know that's not directly answering your question; however, I hope it gives you some working ideas...

[rab26]

NeoPa: Thank you for your great response - and for sorting out terminology.

To answer your question:

"why do you think you need a field/control for Tagged when you already have one for Received - since the logic clearly determines one from the other"

The tagged bikes do eventually reach us...furthermore, bikes come from several different sources and those received from security may or may not have been tagged. The tagged checkbox thus needs to be made available should security be selected as the source.

Although I don't think this breaks the guidelines of normalisation theory (please correct me if I am wrong), it is convoluted and complicates matters...my logic may therefore be failing me. Indeed - I seem to be on a pathway heading for duplicate records as I have neglected the fact that tagged bikes will already be in the database by the time they are received...hmmm :-)

I will have a bash at the event procedures using your example code and let you know how I get on.

ZMBD: Thank you also for your response.

I think I have misused some terminology in my original question and thus mislead you...the "received" checkbox is, learning from what NeoPa has written, an unbound control: it is not actually associated with a field in a table: I am simply attempting to use it to determine which controls are visible and need to be completed when a record is produced.

That said, however, your post has given me food for thought. Thank you.

[NeoPa]

Hi Rab.

At this point I would only point out the importance of getting your detailed understanding of the logic absolutely and completely understood before doing anything else. Anything you implement at this stage can be up for review and redoing if the basis it's created on is flawed or changes. Think of the understanding, then the design, as the lowest two tiers of a pyramid. Get those right and consistent and you have a solid base upon which to build the rest of the structure. Get them wrong and you're building on shifting sands.

Good luck :-)