Setting the value of a TextBox on a Form

[RunTime91]

On to my next challenge...



I'm trying to set the value of a TextBox on a form in the Form Load event with the following code:

Private Sub Form_Load()
    If Me.List37.ListCount = 0 Then
        Me.Text77.Value = 0
    End If
End Sub

Experienced Access coders probably know I'm getting a "You can't assign a value..." error

The control source event for Text77 is: =
[List37].
[ListCount]-1

Thus, when the form loads the TextBox loads with a value of -1 and I would like it to show 0

Thank you so much for any help...

[ranman256]

you dont need the .VALUE
if List37.ListCount = -1 then text77 = 0

[pbaldy]

You can't assign a value to a control that has a calculation in the control source. Use an IIf() in the control source to set a different value when the count is zero.

[RunTime91]

Hey Ranman ~

Okay...So I tried the following and I receive a: "End If Without Block If" Error

Private Sub Form_Load()
    If Me.List37.ListCount = 0 Then Me.Text77 = 0
    End If
End Sub

If I set the code up this way I receive the: "You can't assign a Value..." Error

Private Sub Form_Load()
    If Me.List37.ListCount = 0 Then
        Me.Text77 = 0
    End If
End Sub

Ugh!

(And at one point while I was trying to figure this out the above Form Load event caused Access to spew a parameter error on a completely unrelated ComboBox (Combo46)

[June7]

As pbaldy said, cannot set value of testbox that has a calculated expression in ControlSource so use IIf() expression in the ControlSource, no VBA.

Or don't have anything in the ControlSource property and set value in Form OnCurrent event using If Then Else.

Recommend the IIf() in ControlSource.

=IIf(List37.ListCount = 0, 0, List37.ListCount - 1)

[pbaldy]

Did you see post 3? You can't do what you're trying to do. You could, if you take the formula out and set the value in code for both possibilities.

[pbaldy]

Never mind, I'll get out of the way.

[RunTime91]

Pbaldy ~

Like a dream...

=IIf(
[List37].
[ListCount]=0,0,
[List37].
[ListCount]-1)

Although with decades of experience coding in the Excel VBA environment it makes no sense why Access can't do this in the Form Load event as long as it works ~ I'm happy

Thank you again and thank you again Ranman as well...

[RunTime91]

June I didn't see your post...

You had it right as well...

Thank You to everyone!!

[RunTime91]

June... I would like to stay with your post for a moment...

In Excel I would use the Initialize Event to perform this function

You mention the OnCurrent event and I can't find this... I have OnConnect is that the same thing

And lastly, which would be the best way to do this, using ControlSource or VBA?

[pbaldy]

Pbaldy ~

Although with decades of experience coding in the Excel VBA environment it makes no sense why Access can't do this in the Form Load event as long as it works ~ I'm happy

It's not that it can't do it in the load event, it's that you can't set the value of a textbox that has a formula in the control source. If your textbox didn't have that formula, it would have worked.

[RunTime91]

Hey Paul...

I think our posts (3 & 4) may have crossed ~ I did use your suggestion and it worked...June added some context and that was very helpful as well.

I think Ranman may have missed in my initial post where I had place the formula in the ControlSource, otherwise I believe his suggestion would have worked also.

On an unrelated note, as I begin my new (employer well compensated suggested) life in Access I know some, perhaps many, of my Excel habits will prove frustrating for me.

But I promise to never waste anyone's time...even though many of my questions for now will be of the rookie variety - if I am asking them here ~ I truly need help.

So, if you or anyone here has any suggestions on how I can make the transition from Excel VBA to Access VBA easier...I'm listening...

Thanks again...

[June7]

The OnCurrent event is the first event listed on property sheet for Access form or report, just above OnLoad.

I suggested OnCurrent event so the value will recalculate when navigating records but if the combobox listcount is not variable then does not matter. Are combobox and textbox in form Header section?

Using VBA to set control value will show the same result for all records regardless of event used. Calculation in ControlSource can be record dependent.

[RunTime91]

June, Hello

The form is built in the Detail Section... I see now the OnCurrent Event in the properties list.

Based on your comments, I think I'm in the right place with using a calculation in the ControlSource as I do want this total to update depending on the # of records shown in the ListBox

Thanks June...

[June7]

This is a subform?

Curious, why do you need to show the count of combobox list?