Text box on form

J

Joan

I am designing a data entry form where there is one textbox called Early
Booking Bonus. The user has an early booking bonus structure based on the
following calculation :


=Nz(Switch([Enter Date]-[Whelped Date]<21,10,[Enter Date]-[Whelped Date]
Between 22 And 28,7,[Enter Date]-[Whelped Date] Between 29 And
35,5),0)*([Male Quantity]+[Female Quantity])



If booking is made (the enter date) in <= 3 weeks after the date the
animals were born, then they receive $10 per animal

If booking is made between 3 weeks and <= 4 weeks then they receive $7 per
animal

and if booking is made between 4 weeks and <= 5 weeks then they receive $5
per animal.



The user would like this amount to automatically appear in the textbox on
the form after the user has entered the [Enter Date] and the [Whelped Date]
which is no problem for me. However, they also want this amount to be
editable on the form for extenuating circumstances and this is where I am
having difficulty. I read where you cannot make a calculated field in a
query editable. So I tried setting the control source of the text box to
[Early Booking Bonus] and putting the above calculation as the Default
property of the textbox. Then I get a #Name? error where the amount should
be in the textbox. I have double checked spellings of field names and have
brackets around two word field names like I have above. How would be the
best way for me to handle this situation?



Joan
 
S

SteveS

Maybe this will work for you.

If you set the value of the control using VBA, then the
control will be editable. And you will be able to do error
checking as a bonus...


So here is some air code: (watch for line wrap)


First, set the [Early Booking Bonus] Tab Stop property to
NO, then add the following code to your form


' if the Enter Date is changed
Private Sub Enter_Date_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Whelped Dateis changed
Private Sub Whelped_Date_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Male Quantityis changed
Private Sub Male_Quantity_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Female Quantityis changed
Private Sub Female_Quantity_AfterUpdate()
EarlyBonusCalc
End Sub

' ========================================
' this Sub does the calculation
Private Sub EarlyBonusCalc()

Dim bytAmt as Byte
Dim bytDiffDate as Byte

' skip the calc if either date is null
If IsNull([Enter Date]) or IsNull([Whelped Date]) Then
Me.[Early Booking Bonus] = 0
Exit Sub
Else 'do the calc
' How many days???
bytDiffDate = [Enter Date]-[Whelped Date]

' select bonus based on num. of days
Select Case bytDiffDate
Case <21 ' less than 3 weeks
bytAmt = 10
Case 21 to 28 ' 3 to 4 weeks
bytAmt = 7
Case 29 to 35 ' 4 to 5 weeks
bytAmt = 5
Case Else ' greater than 5 weeks
bytAmt = 0
End Select

' Calc the total bonus and enter in the text box
Me.[Early Booking Bonus] = bytAmt * ([Male Quantity]+
[Female Quantity])

' This is where you could set the font color or back
color
' to denote the bonus was calculated, not manually
entered.
' You would might have to have a flag (field in the
table) to show if the
' amount was calculated or edited (T/F).
' You would have to have the [Early Booking Bonus]
afterUpdate event
' also set the flag and the font color or back color


End If

End Sub

You can click on the Early Booking Bonus and change the
amount, if you need to.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
I am designing a data entry form where there is one textbox called Early
Booking Bonus. The user has an early booking bonus structure based on the
following calculation :


=Nz(Switch([Enter Date]-[Whelped Date]<21,10,[Enter Date]- [Whelped Date]
Between 22 And 28,7,[Enter Date]-[Whelped Date] Between 29 And
35,5),0)*([Male Quantity]+[Female Quantity])



If booking is made (the enter date) in <= 3 weeks after the date the
animals were born, then they receive $10 per animal

If booking is made between 3 weeks and <= 4 weeks then they receive $7 per
animal

and if booking is made between 4 weeks and <= 5 weeks then they receive $5
per animal.



The user would like this amount to automatically appear in the textbox on
the form after the user has entered the [Enter Date] and the [Whelped Date]
which is no problem for me. However, they also want this amount to be
editable on the form for extenuating circumstances and this is where I am
having difficulty. I read where you cannot make a calculated field in a
query editable. So I tried setting the control source of the text box to
[Early Booking Bonus] and putting the above calculation as the Default
property of the textbox. Then I get a #Name? error where the amount should
be in the textbox. I have double checked spellings of field names and have
brackets around two word field names like I have above. How would be the
best way for me to handle this situation?



Joan


.
Click to expand...
 
J

Joan

Hi Steve,

This works like a charm! The form automatically calculates the bonus amount
and yet it is editable... and as a data integrity safeguard, each control
that would affect the result in the calculation calls the calculation's sub
procedure after it is updated.
I also followed your suggestion and I have the background of the Early
Booking Bonus textbox change colors if the value is different from the
calculation. This is so slick.
Thanks so much.

Joan


SteveS said:
Maybe this will work for you.

If you set the value of the control using VBA, then the
control will be editable. And you will be able to do error
checking as a bonus...


So here is some air code: (watch for line wrap)


First, set the [Early Booking Bonus] Tab Stop property to
NO, then add the following code to your form


' if the Enter Date is changed
Private Sub Enter_Date_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Whelped Dateis changed
Private Sub Whelped_Date_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Male Quantityis changed
Private Sub Male_Quantity_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Female Quantityis changed
Private Sub Female_Quantity_AfterUpdate()
EarlyBonusCalc
End Sub

' ========================================
' this Sub does the calculation
Private Sub EarlyBonusCalc()

Dim bytAmt as Byte
Dim bytDiffDate as Byte

' skip the calc if either date is null
If IsNull([Enter Date]) or IsNull([Whelped Date]) Then
Me.[Early Booking Bonus] = 0
Exit Sub
Else 'do the calc
' How many days???
bytDiffDate = [Enter Date]-[Whelped Date]

' select bonus based on num. of days
Select Case bytDiffDate
Case <21 ' less than 3 weeks
bytAmt = 10
Case 21 to 28 ' 3 to 4 weeks
bytAmt = 7
Case 29 to 35 ' 4 to 5 weeks
bytAmt = 5
Case Else ' greater than 5 weeks
bytAmt = 0
End Select

' Calc the total bonus and enter in the text box
Me.[Early Booking Bonus] = bytAmt * ([Male Quantity]+
[Female Quantity])

' This is where you could set the font color or back
color
' to denote the bonus was calculated, not manually
entered.
' You would might have to have a flag (field in the
table) to show if the
' amount was calculated or edited (T/F).
' You would have to have the [Early Booking Bonus]
afterUpdate event
' also set the flag and the font color or back color


End If

End Sub

You can click on the Early Booking Bonus and change the
amount, if you need to.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
I am designing a data entry form where there is one textbox called Early
Booking Bonus. The user has an early booking bonus structure based on the
following calculation :


=Nz(Switch([Enter Date]-[Whelped Date]<21,10,[Enter Date]- [Whelped Date]
Between 22 And 28,7,[Enter Date]-[Whelped Date] Between 29 And
35,5),0)*([Male Quantity]+[Female Quantity])



If booking is made (the enter date) in <= 3 weeks after the date the
animals were born, then they receive $10 per animal

If booking is made between 3 weeks and <= 4 weeks then they receive $7 per
animal

and if booking is made between 4 weeks and <= 5 weeks then they receive $5
per animal.



The user would like this amount to automatically appear in the textbox on
the form after the user has entered the [Enter Date] and the [Whelped Date]
which is no problem for me. However, they also want this amount to be
editable on the form for extenuating circumstances and this is where I am
having difficulty. I read where you cannot make a calculated field in a
query editable. So I tried setting the control source of the text box to
[Early Booking Bonus] and putting the above calculation as the Default
property of the textbox. Then I get a #Name? error where the amount should
be in the textbox. I have double checked spellings of field names and have
brackets around two word field names like I have above. How would be the
best way for me to handle this situation?



Joan


.
Click to expand...
Click to expand...
 
S

SteveS

Wonderful!

One other thing. You shouldn't use spaces in field names,
table names, form names, report names, etc. It makes it
more difficult to write VBA code. And I use 3 letter
prefix identifiers for the objects.

So table names would start with tbl, rpt for reports, qry
for queries, frm for forms...

Field names would be sngEarlyBookingBonus, dteEnterDate,
dteWhelpedDate, etc.

Do a search for access naming conventions.

Good luck.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
Hi Steve,

This works like a charm! The form automatically calculates the bonus amount
and yet it is editable... and as a data integrity safeguard, each control
that would affect the result in the calculation calls the calculation's sub
procedure after it is updated.
I also followed your suggestion and I have the background of the Early
Booking Bonus textbox change colors if the value is different from the
calculation. This is so slick.
Thanks so much.

Joan


SteveS said:
Maybe this will work for you.

If you set the value of the control using VBA, then the
control will be editable. And you will be able to do error
checking as a bonus...


So here is some air code: (watch for line wrap)


First, set the [Early Booking Bonus] Tab Stop property to
NO, then add the following code to your form


' if the Enter Date is changed
Private Sub Enter_Date_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Whelped Dateis changed
Private Sub Whelped_Date_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Male Quantityis changed
Private Sub Male_Quantity_AfterUpdate()
EarlyBonusCalc
End Sub

' if the Female Quantityis changed
Private Sub Female_Quantity_AfterUpdate()
EarlyBonusCalc
End Sub

' ========================================
' this Sub does the calculation
Private Sub EarlyBonusCalc()

Dim bytAmt as Byte
Dim bytDiffDate as Byte

' skip the calc if either date is null
If IsNull([Enter Date]) or IsNull([Whelped Date]) Then
Me.[Early Booking Bonus] = 0
Exit Sub
Else 'do the calc
' How many days???
bytDiffDate = [Enter Date]-[Whelped Date]

' select bonus based on num. of days
Select Case bytDiffDate
Case <21 ' less than 3 weeks
bytAmt = 10
Case 21 to 28 ' 3 to 4 weeks
bytAmt = 7
Case 29 to 35 ' 4 to 5 weeks
bytAmt = 5
Case Else ' greater than 5 weeks
bytAmt = 0
End Select

' Calc the total bonus and enter in the text box
Me.[Early Booking Bonus] = bytAmt * ([Male Quantity]+
[Female Quantity])

' This is where you could set the font color or back
color
' to denote the bonus was calculated, not manually
entered.
' You would might have to have a flag (field in the
table) to show if the
' amount was calculated or edited (T/F).
' You would have to have the [Early Booking Bonus]
afterUpdate event
' also set the flag and the font color or back color


End If

End Sub

You can click on the Early Booking Bonus and change the
amount, if you need to.

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

-----Original Message-----
I am designing a data entry form where there is one textbox called Early
Booking Bonus. The user has an early booking bonus structure based on the
following calculation :


=Nz(Switch([Enter Date]-[Whelped Date]<21,10,[Enter
Click to expand...
Date]-
[Whelped Date]
Between 22 And 28,7,[Enter Date]-[Whelped Date] Between 29 And
35,5),0)*([Male Quantity]+[Female Quantity])



If booking is made (the enter date) in <= 3 weeks
Click to expand...
after
the date the
animals were born, then they receive $10 per animal

If booking is made between 3 weeks and <= 4 weeks then they receive $7 per
animal

and if booking is made between 4 weeks and <= 5 weeks then they receive $5
per animal.



The user would like this amount to automatically appear in the textbox on
the form after the user has entered the [Enter Date]
Click to expand...
and
the [Whelped Date]
which is no problem for me. However, they also want
Click to expand...
this
amount to be
editable on the form for extenuating circumstances and this is where I am
having difficulty. I read where you cannot make a calculated field in a
query editable. So I tried setting the control source
Click to expand...
of
the text box to
[Early Booking Bonus] and putting the above calculation as the Default
property of the textbox. Then I get a #Name? error
Click to expand...
where
the amount should
be in the textbox. I have double checked spellings of field names and have
brackets around two word field names like I have above. How would be the
best way for me to handle this situation?



Joan


.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Textbox based on Calculation 6
Reoccurring Entries on a Form 3
subform in main form 2
Update Table with Form Instantantly 7
Need help to Simplify a Complex Calculation on a Form 0
Multiple-criteria search on form, to open form 2
Today's date to default in form text box 1
Text box un-editable on form 5

Top