Establishing Default Values in a Control of a Subform 3

[JOEYB99]

I am using Access 2007.

I have a subform with a bound control (Control A) and it wants an input value from the user based on two other controls on the Main Form, (Control X and Control Y). The user can select either value or enter a third/different value!

The user now wants Control A to show or provide a default value based on the greater of Control X and Control Y.

I have inserted the following code into the On Load property of the main form,

Dim x
Dim y

x = [Forms]![MainForm]!ControlX.Value
y = [Forms]![MainForm]!ControlY.Value

If x>y then

[Forms]![MainForm]![SubformName]!ControlA.DefaultValue = x

else

[Forms]![MainForm]![SubformName]!ControlA.DefaultValue = y

endif

Endsub



I am very new to VBA so please bear with me. After I entered the code into the VBA screen I then clicked on the Debug. I did not receive any error messages.

I am not getting my desired results! What am I doing worng, or what am I not doing?

Any input would be greatly appreciated.

 

[PHV]

I'd use the Current event procedure instead of the Load

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

 

[JOEYB99]

Thanks PHV.

I tried that and got the same results. I removed the code from the On Load property and made sure it was entered under the On Current property. No change!

When I open the main form the displayed value of Control A still shows zero, while there are amounts in Control X and Control Y.

Am I doing the VBA properly?

After I unput the necessary code I click on Debug, and then Compile. I am not getting any error messages though.

 

[PHV]

the displayed value of Control A still shows zero
Even when you create a new record ?

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

 

[JOEYB99]

No PHV. Let me clarify.

Everytime I open the main form there is a zero value for Control A when I select a new record, one that I have not selected previously.

If I open the main form and select the same record from a previous time then Control A will show its stored value.

In my testing I will always open the main form and choosing a record that has not been previously selected, so I know if the default value is working.

Does that make sense?

 

[MajP]

That is not your real code because that would not compile (endif, endsub) and I am guessing those are not really the control names. Post your real code if you want some help. Also debug your code with something like
...
end if
msgbox me.SubformName.form.ControlA.DefaultValue
End sub

If the msgbox opens then it tells you that your code is at least firing. If it provides the correct value then it tells you that you are setting the correct value.

 

[JOEYB99]

Thank you MajP, I appreciate the thorough response.


Here is the 'real code' and as per PHV it is in the On Current property.


Private Sub Form_Current()

Dim x
Dim y

y = [Forms]![Cost Code Select]!Text15.Value
x = [Forms]![Cost Code Select]![SumOfActual Cost].Value

If x > y Then
[Forms]![Cost Code Select]![Amounts Billed subform]!Text13.DefaultValue = x

Else
[Forms]![Cost Code Select]![Amounts Billed subform]!Text13.DefaultValue = y

End If

End Sub



Cost Code Select is the main form, and Amounts Billed subform is the subform name.



Also, I input your code and when I compiled it I did not receive any error messages. When I opened the form to test it out I received the message, "Run-time Error '2465' Microsoft Office Access can't find the field "|" referred to in your expression."


When I clicked on the Debug button the following was highlighted in yellow,

msgbox me.[cost code select].[amounts billed subform].text13.DefaultValue


What am I doing wrong?

 

[MajP]

Me is used to represent the current object if run within that class (form module). So you can substitute
Me
For
[Forms]![Cost Code Select]

But you wrote the name of the form again.
me.[cost code select].[amounts billed subform]
It would be simply be
me.[amounts billed subform]

So instead by repeating the name of the form it is looking on the current form (cost code select) for an object named [cost code select] which it therefore cannot find.

 

[JOEYB99]

Thank you again MajP for the response, and helping me out.


I have updated the code to

msgbox me.[amounts billed subform].text13.DefaultValue

When I click compile I get the message "Complile Error: Method or data member not found" and the text13 is highlighted!

What does this mean? I double-checked and this is the correct name of the control in the subform.

 

[lionelhill]

Here's a link giving the syntax for referring to controls on subforms in all sorts of combinations. It is the most useful access link ever in the universe:

http://access.mvps.org/access/forms/frm0031.htm

In your original post you spoke about putting your code in the OnLoad event of the main form. If I had code intended to fill controls in a subform, I'd put the code in the subform's events, not the main form's events.

 

[MajP]

Sorry that was my fault. I was mixing bang and dot notation.
Using dot notation it would have to be

me.[amounts billed subform].form.text13.DefaultValue
or
me.[amounts billed subform].controls("text13").DefaultValue

or in bang either works I believe
me![amounts billed subform]!text13.DefaultValue
me![amounts billed subform].form!text13.DefaultValue

If I had code intended to fill controls in a subform, I'd put the code in the subform's events, not the main form's events.

I would be very careful here. The subform loads before the main form does. If you reference the main form from the subform it will error. You can do this, but need to put error checking.

 

[JOEYB99]

Okay thank you MajP for your constant efforts and follow-ups. I appreciate this.

I have revised the code to

msgbox me![amounts billed subform]!text13.DefaultValue

and I think it is working!

When I open the main form I get a message box titled Microsoft Office Access and there is a zero in it. That's all. When I click OK for this the main form then opens properly. When I make a selection for a new record - one that I have not chosen before - everything comes up properly including that same message box. However, it has the proper calculated value inside it!

But, the control still shows zero. It is not being properly populated.

Why is that? What is going on here?

Should I switch the code over to the On Load property of the main form?

 

[MajP]

Debugging is a process of going from big to small. So the message box has narrowed some things down.
1) your code is firing
2) your code is assigning the correct default value to text13 on the subform

Any chance that the field you are looking at is not text13, but another field? Verify that.

Here is a good reason for naming convention to be descriptive. Normally I do something like
txtBxYourFieldName. Also for all objects in Access do not use any spaces, this causes a lot of extra work and problems. Use either camelCase or PascalCase or under scores

frmCostCodeSelect (FrmCostCodeSelect, Form_Cost_Code_Select)
subFrmAmountBilled
txtBxBillAmount

 

[JOEYB99]

Thank you again MajP.

I will rename my controls accordingly but will Access automatically update in other areas, such as VBA code?

Is this done by simply typing in the new name in the property called Name under the Other tab?


I have triple-checked the control name. It is text13.

Is it possible that there is another property that is causing a conflict or over-ride here?



Before I forge on with this I have a general question about the way default values work.

If I have a record that has been changed to a third/different value what happens when I call that record up on my form? Will the default value calculation over-ride it? I do not want my default value to behave in this manner.

I'm hoping that the default value will only 'do its thing' when it is a new record.

Does that make sense? Is there anything you can offer to me to help me ensure this effect?

 

[MajP]

But will Access automatically update in other areas, such as VBA code?
- Unfortuantely not the vba code. Certain things such as table names and fields names will auto update in sql. But imagine you build a real be application with thousands of line of code. If you come back and debug if you use good names the code will be understandable and able to fix.


Is this done by simply typing in the new name in the property called Name under the Other tab?
- Yes

Is it possible that there is another property that is causing a conflict or over-ride here?
- I doubt it. More likely it is not updating the property at the time you expect.

I'm hoping that the default value will only 'do its thing' when it is a new record.
- Your correct. Only adds a value on a new record. Will not effect it later.

 

[JOEYB99]

Once again, thank you MajP for your efforts and time, and knowledge!

Somehow I got it working! I had the same code in a property of the Control inside the subform! So I deleted it. Then I made another change. The selection of records is done by an unbound combo box. So I took your code and entered it under the On Click property of the combo box. I removed your code from the On Current property of the main form.

And now it is working...almost! I have done some extensive testing and I am discovering an unusual but consistent pattern.

When I select a new record the "maximum calculation" is wrong for the current record but applies to the previous selection!

How is that possible? What is wrong now? Any ideas/suggestions?

 

[MajP]

Do not put you code inside the events, but make a stand alone procedure. That way many events can call the same code. It sounds to me that you need another event to call the code again.

so instead of

Private sub Somecontrol_afterupdate()
  code here
end sub

do

Private sub Somecontrol_afterupdate()
  standAloneProcedureName
end sub

Private sub SomeOtherControl_SomeOtherEvent()
  standAloneProcedureName
end sub

Private sub StandAloneProcedureName()
  put code here
end sub

That way lots of controls and events can call the same procedure. Often you will not know which event or how many will need to call it. So I think in your case you need to call the code from your combo but also from the main forms on current event.

 

[JOEYB99]

Thanks again MajP. However, I forgot to mention another wrinkle.On the main form there is a similar situation that requires this defaulting application. So, I took it upon myself to modify the code accordingly.

Here is the code at the present time,



Private Sub Combo54_Click()

Dim x
Dim y

y = [Forms]![cost code select]!Text15.Value
x = [Forms]![cost code select]![SumOfActual Cost].Value


If x > y Then
[Forms]![cost code select]![amounts billed subform]!txtBxBillMaterial.DefaultValue = x

Else
[Forms]![cost code select]![amounts billed subform]!txtBxBillMaterial.DefaultValue = y

End If


'MsgBox Me![amounts billed subform]!Text13.DefaultValue



Dim a
Dim b

a = [Forms]![cost code select]![Equip Actual].Value
b = [Forms]![cost code select]![Equip Budget].Value


If a > b Then
[Forms]![cost code select]![amounts billed subform]![Bill Equipment].DefaultValue = a

Else
[Forms]![cost code select]![amounts billed subform]![Bill Equipment].DefaultValue = b

End If


End Sub


The weird thing here is that the Equipment defaulting is working properly! The first part, for materials, is the one we have been working on and it is the one with problem of recognizing the "maximum calculation" for the previous record selection.


Now, moving on as per your suggestion. You would like me to set up this code as a module. Is that correct?

Also, you feel that I should call it something like 'standaloneProcedureName'. Correct?

Should I use the code exactly as I have it presently? That is, do you see any further tweaking required to the code (above)?