Default value

[Guest]

I need several form fileds to change the default value based on selecting a
state.

So If I choose FL or TX or MA from the drop down box several other fields
prefill based on this selection.

 

[Guest]

Your going to have to code the after_update event to change the value in the
other controls.

 

[Guest]

ok I am not much of a programmer

I am pretty good with Excell but ran into a limit of IF statements - had to
create a workbook for each state - but when other values changed it is very
cumbersome to change each sheet.

Do you have an example?

 

[John W. Vinson]

I need several form fileds to change the default value based on selecting a
state.

So If I choose FL or TX or MA from the drop down box several other fields
prefill based on this selection.

The need to do this suggests that your table design isn't properly normalized.
Are these truly *default* values - subject to editing after selection? Or are
they properly attributes of the state?

If you really do need to do this you'll want some VBA code in the AfterUpdate
event of the state combo box: something like

Private Sub cboState_AfterUpdate()
Select Case Me!cboState
Case "FL"
Me!txtTemp = "Warm"
Me!txtHumidity = "Soggy"
Case "AZ"
Me!txtTemp = "Hot"
Me!txtHumidity = "Arid"
Case "AK"
Me!txtTemp = "Frigid"
Me!txtHumidity = "Dry"
End Select
End Sub


John W. Vinson [MVP]

 

[Guest]

John code is a good solution. You'll also notice he uses the Case statement
and not the If.

Another alternative would be to have a table with all these default values
state,defalut1,default2, default3,...

and in your code use a dlookup to retrieve the appropriate default values.
The benefit here would be you could create an admin form for updates and new
entries.
--
Hope this helps,

Daniel P

 

[John W. Vinson]

Another alternative would be to have a table with all these default values
state,defalut1,default2, default3,...

and in your code use a dlookup to retrieve the appropriate default values.
The benefit here would be you could create an admin form for updates and new
entries.

Excellent idea! Any time you can put data in a table rather than burying it in
code it becomes easier to maintain (and your code is simpler).

John W. Vinson [MVP]

 

[Guest]

John, - I don't want to overwhelm you. Just maybe a little direction.

I have a working database that stores Customer data, Sales People data, an
Invoice procedure. It has been working flawlessly for 10 years now -
However I lost my programmer. I am trying to add some additional
functionality. I know word and excell like the back of my hand and can make
them work for me. But when changes are needed I end up changing 50 documents.

I just don't know the best way to acomplish how I want the outcome to be.
My dilima is I need to transfer the data from Access into word. I found a
great piece of code on here that does exactally what I need. Click a button
get a window that stores word mege documents, pick your doc hit the button
and one record sent to your word doc - and the best part they are editable.
the constants are names address, etc.

The part that changes are the calculations that also transfer to the word
docs. Different for each Customer.

My main calculation is as follows:

Value * Rate / 100 = Premium there will be several of those calculation and
a total and then other calculation need to run to get the end figure. I can
make them work by using the expresson builder but can not figure out how to
store the all the figures. The code you typed will help ALOT because lets
say I pick Florida as the state - I need a text box to fill in with the same
figure used for calculating the tax and it is differnt for each state.

Should I store the value? Rate? Premium? all of thoes figures change for
each customer and I need to run reports to group them together such as how
much total of that type value do I have. I read another post that says don't
store the calculation do this by query? Not sure how to do that.

Maybe I have bitten off more than I can chew. But I am learning

 

[John W. Vinson]

Value * Rate / 100 = Premium there will be several of those calculation and
a total and then other calculation need to run to get the end figure. I can
make them work by using the expresson builder but can not figure out how to
store the all the figures. The code you typed will help ALOT because lets
say I pick Florida as the state - I need a text box to fill in with the same
figure used for calculating the tax and it is differnt for each state.

You don't NEED to store the result. In fact you *shouldn't* store the result!
Calculate it in a Query and export it to Word, as a calculated field.

Should I store the value? Rate? Premium? all of thoes figures change for
each customer and I need to run reports to group them together such as how
much total of that type value do I have. I read another post that says don't
store the calculation do this by query? Not sure how to do that.

Well... taxes are *tough*. Consider New Mexico. There's a state sales tax, and
each municipality can choose to add its own sales tax - payable to the state,
not the community directly - so the state doesn't have "a tax rate", it has
maybe forty of them depending on whether the transaction occurred in Taos,
Albuquerque or Silver City. Lots of states do this.

You need a fairly complicated table structure to model this at ALL. If you do
so, you would not use IF or SELECT CASE at all; instead you'ld have a tax rate
table and join it to your table by state code (or more likely state and city
or some more complex design). The calculation would be done in the query, not
in code, and you could export to Word directly from the Query with no need to
"store the figures".

John W. Vinson [MVP]

 

[Guest]

John,

This mis very usefull information.

Do you think I need to store the value and the rate, just not the
calculation outcome?

OK this is ifor nsurance rating - so the "value" would be for Frame Bldg,
Joisted Masonry Bldg, Fire Resistive Bldg. The "Rate" would change for each
and even change for each type of value.

I would need to runa report to see howm much Frame value etc I have.

 

[John W. Vinson]

John,

This mis very usefull information.

Do you think I need to store the value and the rate, just not the
calculation outcome?

Yes, absolutely. Here's my blurb on storing calculated outcomes:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

The exception - and it may well apply here! - is if you need to capture the
calculation result as of a point in time. If the rates change but the
transaction should store the value using the rates in force at the time, then
you *can* store the result (or alternatively use a rate-history table with a
range of effective dates). To store the calculated value use VBA code in the
Form's BeforeUpdate event to copy the value in the calculated textbox into a
bound textbox.

OK this is ifor nsurance rating - so the "value" would be for Frame Bldg,
Joisted Masonry Bldg, Fire Resistive Bldg. The "Rate" would change for each
and even change for each type of value.

I would need to runa report to see howm much Frame value etc I have.

Then you'll need a table with fields for all of the factors which go into
doing the calculation - e.g. state, BuildingType, fire district, distance to
firehouse, etc. etc. and the rate. I don't know enough about the insurance
business to know, but I suspect this will be a big, hairy calculation... :-{(


John W. Vinson [MVP]

 

[Guest]

John,

Thank you so much for the help.

I can now se the end of the project. DON'"T Store the calculated value -
but I can strore the factors needed to preform the calculation.

Thanks again

 

[Guest]

John,

One more dumb question - I have an unbound text box that I have formated as
a standard number. For the control source I have the following expression.

=[FrameBldgValue]+[JMBldgValue]+[MNCBldgValue]+[FireBldgValue]+[FrameContentsValue]+[JMContentsValue]+[MNCContentsValue]+[FireContentsValue]

This text box in on a subform the only recod it works for is the one that
opens up first. It will not calculate on any other record.

Funny thing - I have several other text boxes setup in the same manner on
the same subform and when you switch from recod to record they seem to work
independantly on each record.

Did I miss something?

 

[John W. Vinson]

This text box in on a subform the only recod it works for is the one that
opens up first. It will not calculate on any other record.

Funny thing - I have several other text boxes setup in the same manner on
the same subform and when you switch from recod to record they seem to work
independantly on each record.

Did I miss something?

If *any* of the fields that go into the expression is NULL, the entire result
will be NULL. Wrap each field in the NZ() function to convert Nulls to Zeros:

=NZ([FrameBldgValue]) + NZ([JMBldgValue]) + NZ([MNCBldgValue]) + ...


John W. Vinson [MVP]

 

[Guest]

John,

Thank you. That works. Not sure why the other calculation work for each
record when some of them are not used in other records.

Thanks again.

This text box in on a subform the only recod it works for is the one that
opens up first. It will not calculate on any other record.

Funny thing - I have several other text boxes setup in the same manner on
the same subform and when you switch from recod to record they seem to work
independantly on each record.

Did I miss something?

If *any* of the fields that go into the expression is NULL, the entire result
will be NULL. Wrap each field in the NZ() function to convert Nulls to Zeros:

=NZ([FrameBldgValue]) + NZ([JMBldgValue]) + NZ([MNCBldgValue]) + ...


John W. Vinson [MVP]