Using If in AfterUpdate to output text

[The Bey]

I have a bunch of text boxes in a form which show a number according to a choice, which is then fed into a text box bound to a sum of these boxes.

I want to have another text box display a string of text, dependent on the sum eg. display "message" when sum > value.

Now I think I can do this simply in the AfterUpdate event of the sum using If statements in the code but I can't get it to display my message.

Here's what i've written in the code:

Private Sub txtInsCalc_AfterUpdate()

Dim txtInsCalc As Integer
Dim txtInsAction As String

If txtInsCalc > 32 Then
txtInsAction = "Strip insulation and inspect"
End If


End Sub

I'm sure this can be done real simply so help would be most welcome..

 

[John Big Booty]

The problem is that nowhere in your code have you assigned a value to txtInsCalc so it will never be greater than 32

 

[The Bey]

The value I want to use is displayed in the textbox on the form, so how do I go about using the displayed value in the form as code to be used (if that makes sense) ?

I understand the concept of coding but I don't have experience so I'm limited to what I can do

 

[JANR]

Since txtInsAction should be a calculated control you can put something like this in it's controlsource:

=IIf([txtInsCalc]>32,"Strip insulation and inspect","")

JR

 

[The Bey]

Thankyou it worked!!

Can I use a similar statement to If-Else as its control source?

The calculated value can vary, so I want to say "If lower than one value, output one answer, if lower than another put this answer, else put this answer"

 

[missinglinq]

txtInsCalc is a calculated field and is populated through code. Events tied to Controls, such as your txtInsCalc_AfterUpdate event, are only executed if the Control is populated physically, which is to say through data entry or pasting data into the Control. When data is entered through code, the event has to be explicitly called.

JNAR's suggestion is one way to work around this problem. Another would be to add a line of code

Call txtInsCalc_AfterUpdate

immediately after the line of code that does the calculation.

Linq ;0)>

 

[JANR]

Yes you can use nested IIf's but for multible choices I would use the Switch() function:

=Switch([Control]=1,"Answer1",[Control]=2,"Answer2",....ect)

JR

 

[missinglinq]

BTW, I agree with JANR on this point! I'd rather get hit on the nose with a brick than to ever use nested IIfs!

The Switch() Function or a Select Case Statement is preferable, being easier to read and more reliable.

Linq ;0)>

 

[The Bey]

Thanks for all your help, I really appreciate it.

I went against your obviously better judgement and used IIf's (and it worked nicely) but just because I couldn't get the switch to work.

I'll copy what I wrote and the error it gave me:

=Switch([txtMatCalc]>=30, "Repair or change type", [txtMatCalc]<18, "No action",[txtMatCalc]>=18 AND <30, "Reinspect on later date or arrange NDT")

"The expression you entered contains invalid syntax - You may have entered a comma without a preceding value or identifier"

What I would like to do now is to set a boxes visibility to false when a certain value is selected. I'm guessing I can do this in the AfterUpdate event using something like:

If txtInsValue = 4, set.visible.txtInsCalc = True

How can I do this?

 

[missinglinq]

The error with your use of Switch() was in this part

[txtMatCalc]>=18 AND <30

You cannot do this in VBA, it has to be

[txtMatCalc]>=18 AND [txtMatCalc] <30

Assuming that this is not a Continuous View or Datasheet View Form, your syntax for the new problem would be

If Me.txtInsValue = 4 Then
  txtInsCalc.Visible = True
Else
 txtInsCalc.Visible = False
End If

This would need to be in the txtInsValue_AfterUpdate event and also in the Form_Current event.

Linq ;0)>

 

[JANR]

alternative to VBA:

=Switch([txtMathCalc]>=30,"Repair or change type",[txtMathCalc]<18,"No action",[txtMathCalc] Between 18 And 29,"Reinspect on later date or arrange NDT")

JR

 

[The Bey]

I added the code to the txtInsValue_AfterUpdate event and to the Form_Current event and it came up with

"Compile Error: Method or data member not found"

and the code looked like:

Private Sub Form_Current()
If Me.txtMatValue = 4 Then
Me.txtMatAction.Visible = True
Else
txt.MatAction.Visible = False
End Sub

If it helps, it had the "txtMatAction" highlighted when the error came up.

When I choose to add data to the Form_Current event, it adds it midway between previous AfterUpdate events within the form. Is this right or is there a problem with my ordering?

 

[missinglinq]

Is there a Textbox named txtMatValue, as you originally posted, or is it actually txtMat?

I'm betting on the latter, and you actually meant

If Me.txtMat.Value = 4 Then

with the period between Me.txtMat and Value.

The error message came because Access couldn't find a Control named txtMatValue. Unfortunately, the Access Gnomes have a bad habit of highlighting the line after the line actually causing the error! Meaning that the actual errant line of code was, as I've guessed,

If Me.txtMatValue = 4 Then.

The order of the Subs in the code module make absolutely no difference. The only trouble that this kind of thing can cause is if you were to copy a Sub/Procedure and then accidentally paste it into the middle of another Sub/Proceedure. But this will never happen letting Access generate a given event, such as Sub Form_Current.

Linq ;0)>

 

[The Bey]

Yeah the textbox is called txtCMatValue, so what I hadn't done was Me.txtCMatValue.Value = 4, but now it works almost like I want it to.

The problem I have now is that it will only load up the initial value and hide the box, but when I change the text in the combo box, the box which I want to be visible/invisible doesn't change.

I'm guessing I need something along the lines of a requery, but I don't actually have a query (as far as I'm aware) so what do I do to get this to update after each change?

 

[The Bey]

I've sorted it; I ran the same If statements in the AfterUpdate section of the combo boxes and it seems to be working fine!

Thanks for the help and I'll repost when I'm stuck