Updating a control based on other controls and user input 2

[JOEYB99]

I am using Access 2007.

I have a subform that has a control (ControlA) and its input value must populate a field in a table, TableA. There are two other controls (ControlX and ControlY) on the main form which provide possible values for the user to select from when inputting to ControlA.

The user wants the default for ControlA to be the greater of ControlX and ControlY. However, they could also enter a third value and over-ride the 'greater of' value.

How can I accomplish this as well as ensure that ControlA will properly populate a certain field in TableA?

Any input would be greatly appreciated.

 

[dan08]

For the control to update the proper table, just make sure the countrol is bound to that field. To set the default value of the control I would suggest some VBA code like the following. Used in the form on load event.

Dim x
Dim y

y = FormName.ControlY.Value
x = FormName.ControlX.Value

if x>y then
FormName.Subform.ControlA.DefaultValue = x

else if y>x then
FormName.Subform.ControlA.DefaultValue = y

 

[JOEYB99]

Thanks Dan08 but I could use some more help. I forgot to mention that I am not too familiar with VBA.

However, what do you mean by "make sure the control is bound to that field"? How can I check for this requirement? Is it in the property sheet, where the control source has the field name?

As for your VBA code I understand that I must input it into an event procedure. But there is no On Load property. Should I use the On Click property then?

 

[dan08]

"Is it in the property sheet, where the control source has the field name?" - Exactly

If you have the main form selected (this means that will say 'Form' in the dropdown list of objects in the property sheet) there should be either an 'on load' or 'on open' event. Click the ... button and select code builder (I think you have to be in design mode for this).

 

[JOEYB99]

Okay Dan08 thanks, but I have a problem with your code. Can you please help me out?


When I did a debug on your code - after I loaded in to the Main Form's property called On Load - I received the error message "Compile error: syntax error" and the line 'else if y>x then' is high-lighted. Why is that?

 

[PHV]

The proper code is:

If x > y Then
  FormName!Subform.Form!ControlA.DefaultValue = x
Else
  FormName!Subform.Form!ControlA.DefaultValue = y
End If

A more compact way:
FormName!Subform.Form!ControlA.DefaultValue = IIf(x > y, x, y)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thanks PHV, I will give that a try.

But just so I understand you. I can use either code for the event procedure under the On Load property of the Main Form. Correct?

 

[PHV]

Yes, either code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886