By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Help on If statment function using a range to input a value in to a text box

P: n/a
Hello,

This is my second post to the any usernet group and the first one was
posted to the wrong one.
I am currently working on creating an order form for sales associates
at my work to be used at conventions. I have a main form with two
subforms. On the main form, there is a text box that displays the sum
of total orders entered in the subform, [SUBTOTAL]. I'm trying to
create another text box on the main form, [GIFTS] that looks at the
subtotal text box and
depending on the value, updates the filed with appropriate value.

I have set the control source of the Gifts text box to be the same as
in the Subtotal text box. Which is =[Orders Subform].Form!
OrderSubtotal.

I have entered the following code in the "event after update" of the
GIFTS tex box.

Private Sub Gifts_AfterUpdate()
If [Subtotal] >= 0 And [Subtotal] <= 2499 Then
Me.gifts = 0
ElseIf [Subtotal] >= 2500 And [Subtotal] <= 4999 Then
Me.gifts = 250
ElseIf [Subtotal] >= 5000 Then
Me.gifts = 600
End If

End Sub

The reply I got from original post of this message said that I need to
refer to the value propety of the textbox and not the textbox itself.
I did that by changing the above code to all instances of [subtotal]
in the above code with [subtotal].value

Please help me,

Thank you,

Victoria

Mar 1 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Mar 1, 2:20 pm, vsteshe...@dentsply.com wrote:
Hello,

This is my second post to the any usernet group and the first one was
posted to the wrong one.
I am currently working on creating an order form for sales associates
at my work to be used at conventions. I have a main form with two
subforms. On the main form, there is a text box that displays the sum
of total orders entered in the subform, [SUBTOTAL]. I'm trying to
create another text box on the main form, [GIFTS] that looks at the
subtotal text box and
depending on the value, updates the filed with appropriate value.

I have set the control source of the Gifts text box to be the same as
in the Subtotal text box. Which is =[Orders Subform].Form!
OrderSubtotal.

I have entered the following code in the "event after update" of the
GIFTS tex box.

Private Sub Gifts_AfterUpdate()
If [Subtotal] >= 0 And [Subtotal] <= 2499 Then
Me.gifts = 0
ElseIf [Subtotal] >= 2500 And [Subtotal] <= 4999 Then
Me.gifts = 250
ElseIf [Subtotal] >= 5000 Then
Me.gifts = 600
End If

End Sub

The reply I got from original post of this message said that I need to
refer to the value propety of the textbox and not the textbox itself.
I did that by changing the above code to all instances of [subtotal]
in the above code with [subtotal].value

Please help me,

Thank you,

Victoria

I'll assume you have the value in [SubTotal] coming in correctly.
The reply you previously got is not entirely correct in that the Value
property is the default of a textbox - so when you refer to it like
Variable = [Subtotal] you are actually refering to it's value. The
first thing I see you doing wrong in your code, is that you must
include the form reference when refering to the control, I use either
Forms("FormName") or Me depending on where the code is. In your case
it should be Me!Subtotal.

Second you may find it more manageable to use a Select Case
statement...

Select Case Me!Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case >=5000
Me!Gifts = 600
Case Else
Me!Gifts=0
End If

Mar 1 '07 #2

P: n/a
On Mar 1, 3:16 pm, "storrboy" <storr...@sympatico.cawrote:
On Mar 1, 2:20 pm, vsteshe...@dentsply.com wrote:


Hello,
This is my second post to the any usernet group and the first one was
posted to the wrong one.
I am currently working on creating an order form for sales associates
at my work to be used at conventions. I have a main form with two
subforms. On the main form, there is a text box that displays the sum
of total orders entered in the subform, [SUBTOTAL]. I'm trying to
create another text box on the main form, [GIFTS] that looks at the
subtotal text box and
depending on the value, updates the filed with appropriate value.
I have set the control source of the Gifts text box to be the same as
in the Subtotal text box. Which is =[Orders Subform].Form!
OrderSubtotal.
I have entered the following code in the "event after update" of the
GIFTS tex box.
Private Sub Gifts_AfterUpdate()
If [Subtotal] >= 0 And [Subtotal] <= 2499 Then
Me.gifts = 0
ElseIf [Subtotal] >= 2500 And [Subtotal] <= 4999 Then
Me.gifts = 250
ElseIf [Subtotal] >= 5000 Then
Me.gifts = 600
End If
End Sub
The reply I got from original post of this message said that I need to
refer to the value propety of the textbox and not the textbox itself.
I did that by changing the above code to all instances of [subtotal]
in the above code with [subtotal].value
Please help me,
Thank you,
Victoria

I'll assume you have the value in [SubTotal] coming in correctly.
The reply you previously got is not entirely correct in that the Value
property is the default of a textbox - so when you refer to it like
Variable = [Subtotal] you are actually refering to it's value. The
first thing I see you doing wrong in your code, is that you must
include the form reference when refering to the control, I use either
Forms("FormName") or Me depending on where the code is. In your case
it should be Me!Subtotal.

Second you may find it more manageable to use a Select Case
statement...

Select Case Me!Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case >=5000
Me!Gifts = 600
Case Else
Me!Gifts=0
End If- Hide quoted text -

- Show quoted text -
Hello and thank you for your response.

I have first tried editing the code by replacing subtotal with Me!
[subtotal] and then by replacing the if statment with the select case
statment that you wrote with the same results. When I go back to the
form, all I see in the gifts text box is the amount that appears in
the subtotal box.

Private Sub gifts_AfterUpdate()
Select Case Me.Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case Is >= 5000
Me!Gifts = 600
Case Else
Me!Gifts = 0

End Select
End Sub

Any ideas?

Mar 2 '07 #3

P: n/a
On Mar 1, 9:51 pm, "Victoria" <vsteshe...@dentsply.comwrote:
On Mar 1, 3:16 pm, "storrboy" <storr...@sympatico.cawrote:
On Mar 1, 2:20 pm, vsteshe...@dentsply.com wrote:
Hello,
This is my second post to the any usernet group and the first one was
posted to the wrong one.
I am currently working on creating an order form for sales associates
at my work to be used at conventions. I have a main form with two
subforms. On the main form, there is a text box that displays the sum
of total orders entered in the subform, [SUBTOTAL]. I'm trying to
create another text box on the main form, [GIFTS] that looks at the
subtotal text box and
depending on the value, updates the filed with appropriate value.
I have set the control source of the Gifts text box to be the same as
in the Subtotal text box. Which is =[Orders Subform].Form!
OrderSubtotal.
I have entered the following code in the "event after update" of the
GIFTS tex box.
Private Sub Gifts_AfterUpdate()
If [Subtotal] >= 0 And [Subtotal] <= 2499 Then
Me.gifts = 0
ElseIf [Subtotal] >= 2500 And [Subtotal] <= 4999 Then
Me.gifts = 250
ElseIf [Subtotal] >= 5000 Then
Me.gifts = 600
End If
End Sub
The reply I got from original post of this message said that I need to
refer to the value propety of the textbox and not the textbox itself.
I did that by changing the above code to all instances of [subtotal]
in the above code with [subtotal].value
Please help me,
Thank you,
Victoria
I'll assume you have the value in [SubTotal] coming in correctly.
The reply you previously got is not entirely correct in that the Value
property is the default of a textbox - so when you refer to it like
Variable = [Subtotal] you are actually refering to it's value. The
first thing I see you doing wrong in your code, is that you must
include the form reference when refering to the control, I use either
Forms("FormName") or Me depending on where the code is. In your case
it should be Me!Subtotal.
Second you may find it more manageable to use a Select Case
statement...
Select Case Me!Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case >=5000
Me!Gifts = 600
Case Else
Me!Gifts=0
End If- Hide quoted text -
- Show quoted text -

Hello and thank you for your response.

I have first tried editing the code by replacing subtotal with Me!
[subtotal] and then by replacing the if statment with the select case
statment that you wrote with the same results. When I go back to the
form, all I see in the gifts text box is the amount that appears in
the subtotal box.

Private Sub gifts_AfterUpdate()
Select Case Me.Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case Is >= 5000
Me!Gifts = 600
Case Else
Me!Gifts = 0

End Select
End Sub

Any ideas?

Are you aware that the AfterUpdate event only occurs when the user
changes the textbox's value. If your intention is to update the Gifts
textbox when the subform changes then this approach does not work. I
think you may need to remove the ControlSource from Gifts and use the
AfterUpdate event of the subform to change it's value. Otherwise I may
not quite understand what you are trying to do.

Private Sub Orders_ SubformActualName_AfterUpdate()
Select Case Me!OrderSubtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me.Parent!Gifts = 0
Case 2500 To 4999
Me.Parent!Gifts = 250
Case Is >= 5000
Me.Parent!Gifts = 600
Case Else
Me.Parent!Gifts = 0
End Select
End Sub

Mar 2 '07 #4

P: n/a
On Mar 1, 11:13 pm, "storrboy" <storr...@sympatico.cawrote:
On Mar 1, 9:51 pm, "Victoria" <vsteshe...@dentsply.comwrote:


On Mar 1, 3:16 pm, "storrboy" <storr...@sympatico.cawrote:
On Mar 1, 2:20 pm, vsteshe...@dentsply.com wrote:
Hello,
This is my second post to the any usernet group and the first one was
posted to the wrong one.
I am currently working on creating an order form for sales associates
at my work to be used at conventions. I have a main form with two
subforms. On the main form, there is a text box that displays the sum
of total orders entered in the subform, [SUBTOTAL]. I'm trying to
create another text box on the main form, [GIFTS] that looks at the
subtotal text box and
depending on the value, updates the filed with appropriate value.
I have set the control source of the Gifts text box to be the same as
in the Subtotal text box. Which is =[Orders Subform].Form!
OrderSubtotal.
I have entered the following code in the "event after update" of the
GIFTS tex box.
Private Sub Gifts_AfterUpdate()
If [Subtotal] >= 0 And [Subtotal] <= 2499 Then
Me.gifts = 0
ElseIf [Subtotal] >= 2500 And [Subtotal] <= 4999 Then
Me.gifts = 250
ElseIf [Subtotal] >= 5000 Then
Me.gifts = 600
End If
End Sub
The reply I got from original post of this message said that I need to
refer to the value propety of the textbox and not the textbox itself.
I did that by changing the above code to all instances of [subtotal]
in the above code with [subtotal].value
Please help me,
Thank you,
Victoria
I'll assume you have the value in [SubTotal] coming in correctly.
The reply you previously got is not entirely correct in that the Value
property is the default of a textbox - so when you refer to it like
Variable = [Subtotal] you are actually refering to it's value. The
first thing I see you doing wrong in your code, is that you must
include the form reference when refering to the control, I use either
Forms("FormName") or Me depending on where the code is. In your case
it should be Me!Subtotal.
Second you may find it more manageable to use a Select Case
statement...
Select Case Me!Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case >=5000
Me!Gifts = 600
Case Else
Me!Gifts=0
End If- Hide quoted text -
- Show quoted text -
Hello and thank you for your response.
I have first tried editing the code by replacing subtotal with Me!
[subtotal] and then by replacing the if statment with the select case
statment that you wrote with the same results. When I go back to the
form, all I see in the gifts text box is the amount that appears in
the subtotal box.
Private Sub gifts_AfterUpdate()
Select Case Me.Subtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me!Gifts = 0
Case 2500 To 4999
Me!Gifts = 250
Case Is >= 5000
Me!Gifts = 600
Case Else
Me!Gifts = 0
End Select
End Sub
Any ideas?

Are you aware that the AfterUpdate event only occurs when the user
changes the textbox's value. If your intention is to update the Gifts
textbox when the subform changes then this approach does not work. I
think you may need to remove the ControlSource from Gifts and use the
AfterUpdate event of the subform to change it's value. Otherwise I may
not quite understand what you are trying to do.

Private Sub Orders_ SubformActualName_AfterUpdate()
Select Case Me!OrderSubtotal
Case 0 To 2499 '(Or <=2499 to account for negatives)
Me.Parent!Gifts = 0
Case 2500 To 4999
Me.Parent!Gifts = 250
Case Is >= 5000
Me.Parent!Gifts = 600
Case Else
Me.Parent!Gifts = 0
End Select
End Sub- Hide quoted text -

- Show quoted text -
I have tried the code and it is still not working. Below is what I
would like to happen.
1. user opens main order form which also has a product subform
2. user enters customer information in the main form and product
information in the subform
The order information gets recorded in the orders table and product
information in orders detail table
3. there is a [OrderSubtotal] in the footer of the product subform
that gets updated with every additional product entered in the
subform. This subtotal is also displayed on the main form.
4.** I would like to add a field in the main form that looks up the
value in [OrderSubtotal] and based on a predefined array mentioned
earlier displays 0, 250 or 600. This number will also be stored in
the main orders table.
This forth step is what I'm trying to create. I think that perhaps
I'm not referencing correct fields.

This is the list of names involved:
Main table is called "Orders"
A field to which I want to append the calculated field value is called
"Gifts"
Main form is called "Orders'' and it is based on query called
OrdersQry that uses Orders table.
Subform is called "OrdersSubform"

Looking forward to your response.

Mar 2 '07 #5

P: n/a
>
I have tried the code and it is still not working.
Is there an error message or something? Still just showing the
Subtotal box? I can't see what's wrong with it if you replaced the
control names accordingly. I may have to try and re-create something
small to figure it out.

Mar 3 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.