Two questions -- Macro and disable all fields

[CS]

My Macro Question:
I have created a form in which the user can check an option group in order
to determine whether they charge the customer's callout fee by a standard
fee (value=1) or based on actual mileage (value=2).

Usually, the user will charge a standard base fee (1), and this is the
default of the option group.

I have a conditional macro which assures that, if the user checks "By
Mileage", there must be number entered in the Mileage control. When the
user checks "By Mileage" in the option group, the macro fires on Mouse Down,
and does the following:

If Mileage is null, msgbx says "You must enter mileage to use this option",
goes to the Mileage control, sets the value of the optiongroup to 2, and
stops the macro.

If Mileage is not null, nothing happens.

This is my problem -- it's possible for the user to go back delete the info
in the mileage box while leaving the "By Mileage" option checked. I want to
make sure that, if they are using the By Mileage option, there is mileage in
the mileage control. Where should I put this Macro?

My Disable all fields question:
Is there an easy way to disable all the other fields on the form until a
certain field is completed? (This field is required at the table level, but
I'd prefer to just have the other fields completely unavailable until this
primary field (Customer) is populated through the drop-down.) I realize
that I could do this one field at a time, but I'd like to find an easy
work-around if it's possible.

Thanks in advance for any help.
CS

 

[BruceM]

I'll jump in since nobody else has. Rest of responses are inline.

My Macro Question:
I have created a form in which the user can check an option group in order
to determine whether they charge the customer's callout fee by a standard
fee (value=1) or based on actual mileage (value=2).

Usually, the user will charge a standard base fee (1), and this is the
default of the option group.

I have a conditional macro which assures that, if the user checks "By
Mileage", there must be number entered in the Mileage control. When the
user checks "By Mileage" in the option group, the macro fires on Mouse
Down, and does the following:

I am not very familiar with macros, preferring VBA procedures in almost all
cases. I would use a VBA procedure in the After Update event for the option
group. If you are interested, I can offer some suggestions.

If Mileage is null, msgbx says "You must enter mileage to use this
option", goes to the Mileage control, sets the value of the optiongroup to
2, and stops the macro.

How about if you just set the value back to 1, then set the focus to the
Mileage text box? The problem may be that you are telling the user to enter
mileage, but since you have set the Option Group to 2, the user doesn't need
to change the option group value, and the Mouse Down (or Before Update)
event never runs. Maybe you could use the After Update event of the Mileage
box to set the Option Group value to 2. You could also use the form's
Before Update event:

If Me.optOptionGroup = 2 Then
If IsNull(Me.Mileage) Then
MsgBox "You need to enter mileage"
Me.txtMileage.SetFocus
Cancel = True
End If
End If

Mileage is the field, and txtMileage is the text box bound to the field.
optOptionGroup is the name of the Option Group control. Note the Cancel =
True line. That stops the code from running. In the case of the form's
Before Update event, the record is not saved. Update is to save the record,
so before that happens the Before Update event occurs. If that event is
cancelled, Update doesn't happen. You can use Before Update in a text box
or other control in the same way.

If Mileage is not null, nothing happens.

This is my problem -- it's possible for the user to go back delete the
info in the mileage box while leaving the "By Mileage" option checked. I
want to make sure that, if they are using the By Mileage option, there is
mileage in the mileage control. Where should I put this Macro?

After Update event for the Mileage box:

If IsNull(Me.Mileage) Or Me.Mileage = "" Then
Me.optOptionGroup = 1
Else
Me.optOptionGroup = 2
End If

My Disable all fields question:
Is there an easy way to disable all the other fields on the form until a
certain field is completed? (This field is required at the table level,
but I'd prefer to just have the other fields completely unavailable until
this primary field (Customer) is populated through the drop-down.) I
realize that I could do this one field at a time, but I'd like to find an
easy work-around if it's possible.

If you actually want to disable the controls you will need to loop through
them with code. A Google groups search should help you find something, or
you can post back if you need help with that, but here's another (simpler)
idea:
Put the Customer combo box into the header, and the rest of the controls
into the Detail section. Set the combo box After Update event to:
Me.Detail.Visible = True

You can use the form's Current event to set Detail.Visible = False, if you
like.

 

[Ken Sheridan]

Rather than using a macro I'm going to suggest you dip your toes into the
murky waters of VBA code, which I think you'll find much easier than using a
macro. You can keep using your current macro, though.

Put some code in the AfterUpdate event procedure of the Mileage control To
do this select the control, in its properties sheet select the AfterUpdate
event property, click on the 'build' button (the one on the right with 3
dots), select Code Builder in the dialogue and OK out of it. The VBA window
will now open at the control's AfterUpdate event procedure with the first and
last lines in place. You enter the code as new lines between them. The code
is simply going to reset the option group to 1 if the user empties it, i.e.
makes it Null. For this an If….Else…End If construct is used. You can go
even further, however, and get it to set the option group to 2 if the control
is not Null. Good code should be commented , which you do by means of the '
character, so the code would be:

' if user enters a value then set value
' of option group to 'Standard Fee' option,
' otherwise, if user empties control
' set value to 'By Mileage' option
If Not IsNull(Me.Mileage) Then
Me.YourOptionGroup = 2
Else
Me.YourOptionGroup = 1
End If

You might have spotted that this demonstrates that the field to which the
option group is bound is in fact redundant as it gives you the same
information as the fact that the Mileage field is Null or not Null does. By
recognizing the potential problem you have actually put your finger on one of
the fundamental principles of relational database design, that redundancy
leaves the door open to 'update anomalies'. In the jargon the option group's
underlying field is said to be functionally dependent on the mileage field.
This is a non-key field however, and non-key fields should be functionally
dependent solely on the whole of the primary key. The table is thus not
properly 'normalized'.

OK, that's enough theory for now. Lets look at your second question. Again
I'm going to suggest using VBA. This time the code will need to go in two
places, in the Form's Current event procedure, and in the AfterUpdate event
procedure of the Customer control.

Before that, however, you need a way to tell the form which control's to
enable/disable. You could get the code to simply disable/enable all by
Customer but that would mean that unbound controls like command buttons etc
would also be disabled, which you probably wouldn't want. To do it
selectively set the Tag property of each control you want to disable/enable
to:

ToggleMe

You can use any term you like in fact, so long as you use the same one in
the code, which goes like this:

Dim ctrl As Control

' loop through Controls collection
' and enable/disable bound controls
' if value in customer control or not
For Each ctrl In Me.Controls
If Me.Tag = "ToggleMe" Then
ctrl.Enabled = Not IsNull(Me.Customer)
' if you want the disabled controls to look
' normal rather than greyed out then
' add the following line
ctrl.Locked = IsNull(Me.Customer)
' if you want to empty all the other controls
' if the user resets the Customer control to Null
' then add the following line, but you might find
' this conflicts with validation rules
If IsNull(Me.Customer) Then ctrl = Null
End If
Next ctrl

Put the above code in both the Form's Current event procedure and the
Customer control's AfterUpdate event procedure. A more efficient way would
be to put it in a separate function just the once and call the function as
both event properties, but repeating the code will work fine.

Ken Sheridan
Stafford, England

 

[CS]

Thank you both, Bruce and Ken. Those approaches have given me "new eyes"
and new approaches. I will jump into the VBA fire.

I do have a question, Ken, about the normalization issue. Let me explain
the problem that I'm dealing with.

The user sometimes charges for their on-site visits by one of two standard
prices, or by mileage, but they want to have the ability to also waive the
fee altogether, (but show the client what the fee would have been, had it
been charged). The user also wants to be able to compare the standard
callout to the mileage-based callout before making a choice about what to
charge.

I've handled it like this -- a drop-down populates the Callout field
(number) with one of two standard fees ($30 or $60) -- another bound text
field on the form populates a number to the Mileage field (and this is
calculated at a price-per-mile in the invoicing process -- the calculation
is not stored). They want to be sure to charge one or the other, but not
both. A third field tells the record which price basis is being used for
billing as a callout field (the option group, which populates CalloutBasis
with either a "1" or "2").

I had the sense that this was an inelegant way to handle it, but I couldn't
figure out how to design the table to store both possibilities (for data
comparison later), and only charge for one.

How would you suggest normalizing this?

Thanks for any help on that, and for all the fine suggestions (Bruce, I
thought the concept of putting the client in the header and hiding the rest
was tres elegant! -- not to mention simple for a dunderhead like me).

CS