comparing calculated values in a Subform

[fluid]

Hi,

I'm trying to compare 2 calculated numbers in a Subform to see if they are equal. If they are equal, I want to make a textbox visible. I cant get it to work. I'm running the code from the main form, but I've also tried from the subform, with the same result. There are no errors, and it seems to run the if statements, but it always results in a visible text box? If I change the = to a <> it never shows the text box.

The 2 calculated text boxes and the textbox I'm trying to hide/show are all on the footer of the subform.

The Form is called: RFP_FORM
The Subform is called: RFP_subform
The Calculated textboxes are called: QTYORDERED and QTYRECTOTAL
The hide/show textbox is called: completetext


Here is my code that isn't working:

Private Sub Form_current()
 
 
Me!RFP_subform.Form!completetext.Visible = IIf(Me!RFP_subform.Form!QTYORDERED = Me!RFP_subform.Form!QTYRECTOTAL, True, False)
 
End Sub

I've also tried....

If Me!RFP_subform.Form!QTYORDERED = Me!RFP_subform.Form!QTYRECTOTAL Then
     Me!RFP_subform.Form!completetext.Visible = True
       Else: Me!RFP_subform.Form!completetext.Visible = False
End if

 

[James Dudden]

Are they calculated textboxes? It may be that when the code runs there is nothing in them therefore they are always the same?

Try putting a breakpoint in your code and step through it so you can see what values Access thinks are in the textboxes at that point.

 

[fluid]

Is there an event or method I can use to ensure that its calculated before the comparison?

---edit

I figured out how to create a breakpoint. I ran with the breakpoint just before the above code. It did indeed show the values in the calculated textboxes. So I don't think it is an "sequence of operations" issue with the calculated numbers

 

[James Dudden]

Not sure if you tried this but what I meant was look at the values in Break mode - this means selecting the desired part of code and using QuickWatch to see the values.

So when the code breaks at the line below, highlight Me!RFP_subform.Form!QTYORDERED and then press SHIFT + F9 to see the value and then do the same for Me!RFP_subform.Form!QTYRECTOTAL :

Me!RFP_subform.Form!QTYORDERED = Me!RFP_subform.Form!QTYRECTOTAL

 

[fluid]

Ok,

I tried what you said. This is what I found. Where the astrix is in my code below is where I put the breakpoint. When I tested the values, I got the following....
QTYB = 0
QTYA = 0

but...

Me!RFP_subform.Form![QTYRECTOTAL] = null
Me!RFP_subform.Form![QTYORDERED] = 5

Why isn't the value of the control being written to the variable?

Private Sub Form_current()
Dim QTYA As Integer
Dim QTYB As Integer
Dim TF As Boolean
QTYA = Me!RFP_subform.Form![QTYRECTOTAL]
QTYB = Me!RFP_subform.Form![QTYORDERED]
***If IsNull(QTYA) Then
QTYA = 0
Else: QTYA = QTYA
End If
If IsNull(QTYB) Then
QTYB = 0
Else: QTYB = QTYB
End If
TF = IIf(QTYA = QTYB, -1, 0)
Me!RFP_subform.Form!completetext.Visible = TF

End Sub

 

[fluid]

Yes, i can now see that the value of the 2 controls won't write to the 2 integer variables. But I'm not sure why. any thoughts?

Is the syntax correct?

QTYA = Me!RFP_subform.Form![QTYRECTOTAL]

 

[James Dudden]

It's possibly a timeing issue - you could try running a recalc first as follows:

Private Sub Form_current()
Dim QTYA As Integer
Dim QTYB As Integer
Dim TF As Boolean
Me!RFP_subform.Form.Recalc
QTYA = Me!RFP_subform.Form![QTYRECTOTAL]
QTYB = Me!RFP_subform.Form![QTYORDERED]
***If IsNull(QTYA) Then
QTYA = 0
Else: QTYA = QTYA
End If
If IsNull(QTYB) Then
QTYB = 0
Else: QTYB = QTYB
End If
TF = IIf(QTYA = QTYB, -1, 0)
Me!RFP_subform.Form!completetext.Visible = TF
 
End Sub

 

[fluid]

I still can't seem to get it working.
It seems like it should be so simple. I must be missing something.


For those that may want to try to help me....
I've attached my DB. Look in the VBA for RFP_Form.

Thanks in advance..

 

[James Dudden]

I've downloaded the db and had a quick look and I believe it is a timing issue becuase when I step through the code it works. This means your code is fine - though somewhat convoluted - but I don't have time to investigate further at this point.

Below is simplified version of what you're doing:

Me!RFP_subform.Form!completetext.Visible = IIf(Me!RFP_subform.Form![QTYRECTOTAL]= Me!RFP_subform.Form![QTYORDERED], -1, 0)

 

[fluid]

Thanks for your input. I simplified my code as you indicated above. Still not working though.

I too, believe it is a timing issue of some sort, but I've tried several different methods to get rid of it to no avail.

Anybody have any thoughts?