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

Setting control properties

P: n/a
I'm having trouble setting decimal place values in the results field
of a sample management database I'm building for an environmental
testing laboratory. The degree of sensitivity varies among test
methods; consequently, some results are reported to 2 decimal places,
some to 3, etc. The Results subform consists of Test Parameter,
Result, Report Unit, Analysis Date, Analyst and other fields. The
test parameter control is a drop-down box. I created a test parameter
after_update event using Select Case relating the test parameter ID
number and the Result.DecimalPlaces property. When I select a test
parameter and proceed to enter a test result, the result is formatted
to the proper decimal place value. When I add new records, however,
and choose a test parameter with a different decimal place value, all
the test result values change to the decimal place value assigned to
the most recent entry. Obviously my knowledge of VBA is minimal,
because I can't solve what I'm sure is an elementary problem. I would
appreciate , in addition to a simplified explanation of my problem,
any recommendations on an entry level book for VBA. Thanks in advance
for your response.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
John,

Your problem comes down to how forms work.

Single Form ----------
A single form contains one set of controls and as the form navigates from record
to record, the controls fill with the data from the recordsource of the form
from the fields the controls are bound to. Given that there is only one set of
controls, each control can have only one format and as the form navigates from
record to record, all the data displayed has the same format. In other words, if
you set the decimal places to two for a certain control (textbox), when you
navigate from record to record, all the data displayed in that control will have
two decimal places.

Continuous Form ---------------------
When you look at a continuous form, you see one set of controls in the detail
section. When the form opens in form view and displays all the records from the
recordsource, there is still only one set of controls. What you see are multiple
instances of the same set of controls. Again, given that there is only one set
of controls, each control can have only one format. Each instance mirrors that
same set of controls so the data displayed in each instance has the same format.
In other words, if you set the decimal places to two for a certain control
(textbox), the data displayed in that control in each instance will have two
decimal places.

Given how forms work, you can't display data for different records in the same
texbox with different decimal places without doing something extra. In you case,
that something extra is probably writing a function to format the result in the
textbox after it has been entered. The function would have to apply an
appropriate format depending upon the test that produced the result. You would
use the Format function within this function. The format function would look
like:
Format([Result],"##0.00") to get two decimal places, or
Format([Result],"##0.000") to get three decimal places.
# indicates there may or may not be a number at that placeholder while 0 fills
the placeholder with a 0 if nothing was entered for that placeholder.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"John Hargrove" <jr********@comcast.net> wrote in message
news:82**************************@posting.google.c om...
I'm having trouble setting decimal place values in the results field
of a sample management database I'm building for an environmental
testing laboratory. The degree of sensitivity varies among test
methods; consequently, some results are reported to 2 decimal places,
some to 3, etc. The Results subform consists of Test Parameter,
Result, Report Unit, Analysis Date, Analyst and other fields. The
test parameter control is a drop-down box. I created a test parameter
after_update event using Select Case relating the test parameter ID
number and the Result.DecimalPlaces property. When I select a test
parameter and proceed to enter a test result, the result is formatted
to the proper decimal place value. When I add new records, however,
and choose a test parameter with a different decimal place value, all
the test result values change to the decimal place value assigned to
the most recent entry. Obviously my knowledge of VBA is minimal,
because I can't solve what I'm sure is an elementary problem. I would
appreciate , in addition to a simplified explanation of my problem,
any recommendations on an entry level book for VBA. Thanks in advance
for your response.

Nov 12 '05 #2

P: n/a
You should really consider a separate VBA function to format the
result values for the test. As you have seen, you cannot use the
DecimalPlaces property on a control on a continuous form to show
results with different decimal places. Consider using a separate
unbound calculated control adjacent to the result field, which uses
your function to display the properly formatted result. The result
value field's AfterUpdate event should requery the calculated control
so it is updated automatically. You will also need to use the same
function on your reports. At a minimum, the parameters to your
formatting function are the result value and the number of decimal
places. All of your result value formatting can be performed in this
single function so you can enhance its features over time. For
example, when you discover you also need significant figure rounding,
which is not the same as decimal place rounding, you can add it to
your formatting function.

The Visual Basic Programmer's Guide that comes with Office is a good
place to start learning VBA. You should also consider the Access
(appropriate version) Developer's Handbook by Litwin, Getz, et al.
which has plenty of Access-specific VBA examples. HTH

Rick Collard
www.msc-lims.com

On 10 Dec 2003 14:58:01 -0800, jr********@comcast.net (John Hargrove)
wrote:
I'm having trouble setting decimal place values in the results field
of a sample management database I'm building for an environmental
testing laboratory. The degree of sensitivity varies among test
methods; consequently, some results are reported to 2 decimal places,
some to 3, etc. The Results subform consists of Test Parameter,
Result, Report Unit, Analysis Date, Analyst and other fields. The
test parameter control is a drop-down box. I created a test parameter
after_update event using Select Case relating the test parameter ID
number and the Result.DecimalPlaces property. When I select a test
parameter and proceed to enter a test result, the result is formatted
to the proper decimal place value. When I add new records, however,
and choose a test parameter with a different decimal place value, all
the test result values change to the decimal place value assigned to
the most recent entry. Obviously my knowledge of VBA is minimal,
because I can't solve what I'm sure is an elementary problem. I would
appreciate , in addition to a simplified explanation of my problem,
any recommendations on an entry level book for VBA. Thanks in advance
for your response.


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.