Creating unique calculation form pop-up

[Opal]

I am using Access 2003 and am trying to create a form
for my users to calculate percentages based on values
that they input. I have the form working just the way
I want, but am having trouble interfacing it back to the
main form.

The pop-up form calculates 2 values in fields called:

[NTDR] and [NADR] in frmDRPopup

I now want these two values to appear in frmQtrUpt
in the fields [TDR] and [ADR] - this form is a bound
form. The frmDRPopup form is unbound.

The fields in both forms are formated as percent with
one decimal place. I formatted the fields on the bound
form as:
Field Size: Decimal
Format: Percent

I am trying the following:

Dim HoldTDR As Integer
Dim HoldADR As Integer
Dim dbObject As DAO.Database
Dim DRCalcRS As DAO.Recordset
Dim strQuery As String

HoldTDR = Forms!frmDRPopup!NTDR.Value
HoldADR = Forms!frmDRPopup!NADR.Value

Set dbObject = CurrentDb
strQuery = "SELECT * FROM AMQtr;"
Set DRCalcRS = dbObject.OpenRecordset(strQuery)

DRCalcRS.AddNew
DRCalcRS!ADR = HoldADR
DRCalcRS!TDR = HoldTDR
DRCalcRS.Update

and I get a type mismatch error.

I tried this instead:

' HoldTDR = Format(Forms!frmDRPopup!NTDR, "Percent")
' HoldADR = Format(Forms!frmDRPopup!NADR, "Percent")

and still get a type mismatch error. I am missing something
so would appreciate any assistance / direction you can give.

Thank you.

 

[John W. Vinson]

The fields in both forms are formated as percent with
one decimal place. I formatted the fields on the bound
form as:
Field Size: Decimal
Format: Percent

Don't confuse the Format of a control with its value. The Format property of a
textbox just controls how the value is displayed, not what's stored. If it's a
Number field you need to store a Number value (and that number's format is
irrelevant).

The Format() function returns a text string, which will indeed give an error
if you try to store it in a Number field. Try just storing the value of the
textbox itself.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Opal]

Thank you, John. That cleared up the type mismatch...

But I've still got a problem.... When I run this the values,
for example, that I am trying to write to the table
are 94.7% and 98.5% and when the code runs, it
saves them as 100% and 100%. What am I missing
with my field formatting or field size...? Help!

 

[Bob Quintal]

Thank you, John. That cleared up the type mismatch...

But I've still got a problem.... When I run this the values,
for example, that I am trying to write to the table
are 94.7% and 98.5% and when the code runs, it
saves them as 100% and 100%. What am I missing
with my field formatting or field size...? Help!

From your original post, you have defined
Dim HoldTDR As Integer
Dim HoldADR As Integer

The numbers 94.7% and 98.5% are actually 0.947 and 0.985.
When you try to save them as integers, they get rounded to 1.00, which
the Format Percent shows as 100% and 100%

 

[Opal]

Oh.... d'oh!

Sorry for the blond moment ;-)