Looking for null values without results

[Guest]

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Okay, lets continue with this code. When I sent it to you, I assumed you
were sure that either County or City would be Null. I would run the code in
debug and see what the values of the County and City are when it hits the
Select statement. I also see a possible bug. There is no check to see if
they are both Null.

Also, I don't understand the need for a default of 1. What is the purpose
of that requirement?

 

[Guest]

Sorry to be such a pain. This and one other field are giving me fits.
Anyway, if you see the bug and can fix it (and even if you can't) I greatly
appreciate the help. Creating a default value of "1" in [PopulationBracket]
would allow me to utilize the same price calculation fromula for
non-population based products while not distorting anthing critical for those
which are population dependent.
--
Tom

Okay, lets continue with this code. When I sent it to you, I assumed you
were sure that either County or City would be Null. I would run the code in
debug and see what the values of the County and City are when it hits the
Select statement. I also see a possible bug. There is no check to see if
they are both Null.

Also, I don't understand the need for a default of 1. What is the purpose
of that requirement?

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

.... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hi, Klatuu.

The only time the Case Else will execute is if the condition in the select
returns a null,

Tom wants the billing multiplier to be 1 when the neither the county nor
city populations have values, so this suits his purposes.

... a negative number,

Tom is a smart guy and knows that neither the county nor the city could
_ever_ have negative populations, so he has wisely assigned >=0 Or Is Null
for the Validation Rule Property for both of these fields in the table's
Design View to guarantee invalid values will never be entered accidently.

... or any positive number up to .9999.

Since the bound fields are Long data types, fractions will automatically be
rounded to a whole number when saved. But don't forget zero, which isn't a
positive number, either. In case of zero, the default Case Else logic will
still be executed, which suits Tom's purposes. The only problem is when the
user accidently types 0 for county population and a value for city
population, because the county population number will supersede the city
population in this calculation. Tom should add some business logic that
guarantees that when the county population has a value, the city population
doesn't.

For
every other number, one of the case statements will kick in first.

As is intended.

where is he changing a value typed in? I don't see it.

Tom's original code is assigning a value to the PopulationBracket field --
in the BeforeUpdate( ) event for the control of the same name.

What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

I wouldn't recommend it. Putting Tom's code in the BeforeUpdate( ) event of
the control whose value is being updated is like painting the floor
underneath your feet. Paint the floor underneath your feet _after_ the paint
around your feet has already dried, so you can stand on a dry spot while
painting where you used to be standing.

And why have the user type in a value that's going to immediately be
overridden with a calculated value? Why not just calcalate the value as soon
as a certain event happens, like when updating another control or even
clicking a button -- if there's no event guaranteed to occur -- to do the
calculation?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hope is incouraging.

1. text box [PopulationBracket] in frmSales bound to qrySales. All other
form results are evident in the underlying query and the respective tables.
In this case, the field: [PopulationBracket] is in tblSales.

2. Code is definately in the Before Update of the [PoupulationBracket]
textbox.
3. Not sure how I can determine if the text box is disabled or locked, but
when I manually enter a number into qrySales, I can then see it in the
editable frmSales.
4. I can see the actual population numbers on my form that are called from
a combobox selection. In the unpopulated population textboxes, I don't know
how to determine if the value is zero text string or null value. Can you
tell me how to tell what's actually there, and insure that I'm getting a null
value when the field is unpopulated?


--
Tom

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

I did not see where Tom said the bound data types are Long. I must have
missed that. The Case Else will execute anytime the value is Null, 0, or
over 200000. I don't see why you say only Null. I think the answer would be
to add a Case that would catch Nulls and Zeros and it should be the first
case.

Case is = "" or Null

I did not mean to say Tom was not smart. I don't know how smart his data
entry people are. Without seeing the entire picture, I was only covering
every possibility.

Hi, Klatuu.

The only time the Case Else will execute is if the condition in the select
returns a null,

Tom wants the billing multiplier to be 1 when the neither the county nor
city populations have values, so this suits his purposes.

... a negative number,

Tom is a smart guy and knows that neither the county nor the city could
_ever_ have negative populations, so he has wisely assigned >=0 Or Is Null
for the Validation Rule Property for both of these fields in the table's
Design View to guarantee invalid values will never be entered accidently.

... or any positive number up to .9999.

Since the bound fields are Long data types, fractions will automatically be
rounded to a whole number when saved. But don't forget zero, which isn't a
positive number, either. In case of zero, the default Case Else logic will
still be executed, which suits Tom's purposes. The only problem is when the
user accidently types 0 for county population and a value for city
population, because the county population number will supersede the city
population in this calculation. Tom should add some business logic that
guarantees that when the county population has a value, the city population
doesn't.

For
every other number, one of the case statements will kick in first.

As is intended.

where is he changing a value typed in? I don't see it.

Tom's original code is assigning a value to the PopulationBracket field --
in the BeforeUpdate( ) event for the control of the same name.

What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

I wouldn't recommend it. Putting Tom's code in the BeforeUpdate( ) event of
the control whose value is being updated is like painting the floor
underneath your feet. Paint the floor underneath your feet _after_ the paint
around your feet has already dried, so you can stand on a dry spot while
painting where you used to be standing.

And why have the user type in a value that's going to immediately be
overridden with a calculated value? Why not just calcalate the value as soon
as a certain event happens, like when updating another control or even
clicking a button -- if there's no event guaranteed to occur -- to do the
calculation?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hi, Tom.

In PopulationBracket_BeforeUpdate( ), your line of code:

Me.PopulationBracket = Result

.... changes the value that's about to be updated. Perhaps you intended to
place this code in another control's event procedure? I would have.

One more thing to check besides Klatuu's list is that the control's event
property has [Event Procedure] assigned in the Properties dialog window for
that particular event.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

I agree with the After Update logic, but in which txtbox on my form would I
use that event property to get the proper results in [txtPopulationBracket]
(I've added the "txt" preface to all frmSales textbox names while mulling
over yours and Klatuu's exchange), since either [txtCityPopulation] or
[txtCountyPopulation] might be used?

Also, if I change the the numeric field size property in PopulationBracket
to something that accomodates the necessary fraction (for example:
"2.5"--will that foul up your proposed code?

Not to worry about data input--I wear a lot o hats around here...but I'll
take your advice and build in some appropriate logic for the day when help
arrives.


I can't tell you how much I appreciate both you and Klatuu helping me with
this. Wish I could do something to return the favor.
--
Tom

Hi, Tom.

In PopulationBracket_BeforeUpdate( ), your line of code:

Me.PopulationBracket = Result

... changes the value that's about to be updated. Perhaps you intended to
place this code in another control's event procedure? I would have.

One more thing to check besides Klatuu's list is that the control's event
property has [Event Procedure] assigned in the Properties dialog window for
that particular event.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom


:

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

One other elaboration/question: the forms population numbers are populated
on frmSales from the [CityID] or [CountyID] combo boxrs -- pull down the city
or county name and the population shows up in txtCityPopulation or
txtCountyPopulation from which the Case choices makes the selection. Since
the combo boxes are limited to the underlying table choices (I hadn't set the
Validation Rule Property on their respective field's to Is Null), would I
actually be getting a null value into the unused txtCityPopulation or
txtCountyPopulation boxes? All this is way over my pay grade--hope I'm
making sense here.
--
Tom

Hi, Tom.

In PopulationBracket_BeforeUpdate( ), your line of code:

Me.PopulationBracket = Result

... changes the value that's about to be updated. Perhaps you intended to
place this code in another control's event procedure? I would have.

One more thing to check besides Klatuu's list is that the control's event
property has [Event Procedure] assigned in the Properties dialog window for
that particular event.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom


:

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hi, Klatuu.

I did not see where Tom said the bound data types are Long. I must have
missed that.

You didn't miss it. He didn't say. Sometimes one needs to infer
information from what's given. The word population generally connotes number
of people, but it can also mean number of organisms. I don't think I'm going
out on a limb here, but Tom can correct me if I'm wrong in that there aren't
going to be more than 2.14748 billion population of people or animals in any
city or county on this planet, even in the foreseeable future. Think about
the resources and infrastructure necessary to keep this many people or
animals alive in such a concentrated area. Geez! The Earth would tilt under
so much weight in one spot.

Tom can use a Long data type for these fields unless the populations
recorded are going to exceed 2.14748 billion, in which case the Single data
type can hold up to 3.402823E38 and the Double data type can hold up to
1.79769313486231E308. If the populations aren't going to exceed 2.14748
billion, then Tom should definitely be storing the values as Longs, which
only need four bytes, not the other data types.

The Case Else will execute anytime the value is Null, 0, or
over 200000.

In the alternative to Tom's original code that I offered below, Case Is >
200000 will execute if the value is over 200,000. When the value is NULL or
0, the Case Else statement will execute.

I don't see why you say only Null.

I didn't. I wrote the following in my earlier reply, which accounts for
when the value is zero:

I think the answer would be
to add a Case that would catch Nulls and Zeros

That makes sense when the code needs to do something different when the
value is either zero or NULL as opposed to when none of the earlier Case
statements are executed. In Tom's case, the same code will be executed:
Give me a billing multiplier of 1 if the county and city populations aren't 1
or more. Tom can use Case statements to separate the zero and NULL from the
"Or else," but it's not necessary unless Tom wants to put something special
in the Case Else block, like an error message to the user.

and it should be the first
case.

The Select Case statements can be listed in any order before the final
default Case Else statement. For code execution speed purposes, the most
likely Case statements to be executed are placed first in the Select Case
block. The least likely cases are placed last, just before the Case Else
block. However, for code maintenance purposes, the Case statements are
listed in an ordinal order to easily see at a glance that none were left out.

Case is = "" or Null

If the CountyPopulation and CityPopulation controls are bound to numerical
data types, this line of code will bomb with a type mismatch error. But even
if the line of code were changed to:

Case Is = NULL

.... this Case statement will never evaluate to TRUE.

I did not mean to say Tom was not smart. I don't know how smart his data
entry people are.

I.Q. doesn't much matter in data entry. Even geniuses make typos. Data
validation and business logic help keep the data entry errors to a minimum.

Without seeing the entire picture, I was only covering
every possibility.

That's all we can do.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I did not see where Tom said the bound data types are Long. I must have
missed that. The Case Else will execute anytime the value is Null, 0, or
over 200000. I don't see why you say only Null. I think the answer would be
to add a Case that would catch Nulls and Zeros and it should be the first
case.

Case is = "" or Null

I did not mean to say Tom was not smart. I don't know how smart his data
entry people are. Without seeing the entire picture, I was only covering
every possibility.

Hi, Klatuu.

The only time the Case Else will execute is if the condition in the select
returns a null,

Tom wants the billing multiplier to be 1 when the neither the county nor
city populations have values, so this suits his purposes.

... a negative number,

Tom is a smart guy and knows that neither the county nor the city could
_ever_ have negative populations, so he has wisely assigned >=0 Or Is Null
for the Validation Rule Property for both of these fields in the table's
Design View to guarantee invalid values will never be entered accidently.

... or any positive number up to .9999.

Since the bound fields are Long data types, fractions will automatically be
rounded to a whole number when saved. But don't forget zero, which isn't a
positive number, either. In case of zero, the default Case Else logic will
still be executed, which suits Tom's purposes. The only problem is when the
user accidently types 0 for county population and a value for city
population, because the county population number will supersede the city
population in this calculation. Tom should add some business logic that
guarantees that when the county population has a value, the city population
doesn't.

For
every other number, one of the case statements will kick in first.

As is intended.

where is he changing a value typed in? I don't see it.

Tom's original code is assigning a value to the PopulationBracket field --
in the BeforeUpdate( ) event for the control of the same name.

What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?

I wouldn't recommend it. Putting Tom's code in the BeforeUpdate( ) event of
the control whose value is being updated is like painting the floor
underneath your feet. Paint the floor underneath your feet _after_ the paint
around your feet has already dried, so you can stand on a dry spot while
painting where you used to be standing.

And why have the user type in a value that's going to immediately be
overridden with a calculated value? Why not just calcalate the value as soon
as a certain event happens, like when updating another control or even
clicking a button -- if there's no event guaranteed to occur -- to do the
calculation?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hi, Tom.

I agree with the After Update logic, but in which txtbox on my form would I
use that event property to get the proper results in [txtPopulationBracket]
(I've added the "txt" preface to all frmSales textbox names while mulling
over yours and Klatuu's exchange), since either [txtCityPopulation] or
[txtCountyPopulation] might be used?

.... And neither one used in the case of a non-population-based product.
Find some control on the form that _must_ be updated, and put the logic there
to update the txtPopulationBracket text box. If there's no control that is
always guaranteed to have an AfterUpdate( ) event, then the Form's
BeforeUpdate( ) or AfterUpdate( ) event procdedure can be used for the logic
(whichever makes more sense), or even a command button's OnClick( ) event
procedure. But in the latter case, the user _must_ click the button in order
for the calculated value to be assigned to the txtPopulationBracket text box,
so this might not be dependable enough.

Also, if I change the the numeric field size property in PopulationBracket
to something that accomodates the necessary fraction (for example:
"2.5"--will that foul up your proposed code?

No problem. That one can use the Single data type. It's the population
fields' data types that we're concerned with for the Select Case statement
logic in your procedure.

I can't tell you how much I appreciate both you and Klatuu helping me with
this. Wish I could do something to return the favor.

Any leads on a full-time job at a good company in the U.S. for a database
expert / software engineer would be very much appreciated. And signing in to
the Microsoft Online Community, then marking our replies as answers to your
question would be much appreciated, too.

Thanks!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I agree with the After Update logic, but in which txtbox on my form would I
use that event property to get the proper results in [txtPopulationBracket]
(I've added the "txt" preface to all frmSales textbox names while mulling
over yours and Klatuu's exchange), since either [txtCityPopulation] or
[txtCountyPopulation] might be used?

Also, if I change the the numeric field size property in PopulationBracket
to something that accomodates the necessary fraction (for example:
"2.5"--will that foul up your proposed code?

Not to worry about data input--I wear a lot o hats around here...but I'll
take your advice and build in some appropriate logic for the day when help
arrives.


I can't tell you how much I appreciate both you and Klatuu helping me with
this. Wish I could do something to return the favor.
--
Tom

Hi, Tom.

I'm not changing any typed in value; and I've been attempting to
run the code in Before Update.

In PopulationBracket_BeforeUpdate( ), your line of code:

Me.PopulationBracket = Result

... changes the value that's about to be updated. Perhaps you intended to
place this code in another control's event procedure? I would have.

One more thing to check besides Klatuu's list is that the control's event
property has [Event Procedure] assigned in the Properties dialog window for
that particular event.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

:

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom


:

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.

 

[Guest]

Hi, Tom.

Since
the combo boxes are limited to the underlying table choices (I hadn't set the
Validation Rule Property on their respective field's to Is Null)

The combo boxes don't need this Validation Rule in order for the suggested
code to work. It's the fields that store the populations that must be
non-negative or NULL in order for the code to work with the least amount of
additional business logic and programming maintenance.

would I
actually be getting a null value into the unused txtCityPopulation or
txtCountyPopulation boxes?

Both of these text boxes will hold NULL values until a value is assigned.
If these are both bound text boxes, then typing a number, saving it, then
removing the number will return to having a NULL value in the text box.
However, if these aren't bound text boxes then typing a number in the text
box, then removing the number will result in the text box holding an empty
string. And an empty string is _not_ the same as a NULL value.

All this is way over my pay grade--hope I'm
making sense here.

Don't worry. You make plenty of sense.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

One other elaboration/question: the forms population numbers are populated
on frmSales from the [CityID] or [CountyID] combo boxrs -- pull down the city
or county name and the population shows up in txtCityPopulation or
txtCountyPopulation from which the Case choices makes the selection. Since
the combo boxes are limited to the underlying table choices (I hadn't set the
Validation Rule Property on their respective field's to Is Null), would I
actually be getting a null value into the unused txtCityPopulation or
txtCountyPopulation boxes? All this is way over my pay grade--hope I'm
making sense here.
--
Tom

Hi, Tom.

I'm not changing any typed in value; and I've been attempting to
run the code in Before Update.

In PopulationBracket_BeforeUpdate( ), your line of code:

Me.PopulationBracket = Result

... changes the value that's about to be updated. Perhaps you intended to
place this code in another control's event procedure? I would have.

One more thing to check besides Klatuu's list is that the control's event
property has [Event Procedure] assigned in the Properties dialog window for
that particular event.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

There is Hope. Here are some items to check:
1. [Population Bracket] is a bound textbox and bound to the correct field in
the database.
2. The code is in the Before Update event of the text box [Population
Bracket]
3. [Population Bracket] is not diabled or locked.
4. Check the values of [CountyPopulation] and [CityPopulation], they may not
be null when you expect them to be.

:

Correct. I'm not changing any typed in value; and I've been attempting to
run the code in Before Update. Is their hope?
--
Tom


:

'69,
The only time the Case Else will execute is if the condition in the select
returns a null, a negative number, or any positive number up to .9999. For
every other number, one of the case statements will kick in first. Also,
where is he changing a value typed in? I don't see it. What I do see is the
code is the an After Update event. Shouldn't it be in the Before Update
event of the Population Bracket text box?


:

Hi, Tom.

I'm not getting a result. Can anybody figure out why?

Yes. Your code is attempting to change the value typed into the text box
before the bound text box is updated. Your code should be attempting to
change the value in another control, not the same control.

Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen?

Use the Case Else statement as the default value. Your current Case Else
value is probably only intended to apply if the population is over 200,000,
but that 3.5 multiplier will be applied as the default for all cases not
covered, which isn't what you want. To get the effects you want, I'd
recommend something like the following VBA code:


' * * * * Start Code * * * *

Private Sub txtSomething_AfterUpdate()

On Error GoTo ErrHandler

Dim Result As Single

Select Case Nz(Me!CountyPopulation.Value, Me!CityPopulation.Value)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Is > 200000
Result = 3.5
Case Else
Result = 1
End Select

Me!PopulationBracket.Value = Result

Exit Sub

ErrHandler:

MsgBox "Error in txtSomething_AfterUpdate( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

... where PopulationBracket is the name of the bound text box that holds the
assigned billing multiple for the current population, and txtSomething is the
name of the text box that is being updated with a value during the course of
filling in the form. With the above logic, if both the county and city
populations have values (boo boos do happen), then the county population will
be used for the billing multiplier.

It might be better to have a command button that the user can click on to
calculate the billing multiplier, but if users tend to forget steps, then the
code should be doing as much automation as possible and an AfterUpdate( )
event is fine for this.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I'm using this code to populate a bound textbox [PopulationBracket] that
yields a billing multiple based on population depending on which non-null
population is choosen (City or County but never both):

Private Sub PopulationBracket_BeforeUpdate(Cancel As Integer)
Select Case Nz(CountyPopulation, CityPopulation)
Case 1 To 20000
Result = 1
Case 20001 To 40000
Result = 1.5
Case 40001 To 100000
Result = 2.5
Case 100001 To 200000
Result = 3
Case Else
Result = 3.5
End Select
Me.PopulationBracket = Result

End Sub

I'm not getting a result. Can anybody figure out why? Also, can you
recommend how I can get a default value of 1, should neither city or county
population options be choosen? Many thanks.