Multiply Values

[Wayne Livingstone]

I have a sub-form that conatint the following fields:
PrintFeeID from the PrintingFees table
First
Copies
PrintFee

All of these fields are bound to the PrintReq_Sub table.
All are number fields in the table an text boxes on the form.

I need to use the PrintFeeID to find the values in fields
FeeForFirst and Copies in the PrintingFees table.
I need this to happen:

subform.PrintFee = (subform.First X
PrintingFees.FeeForFirst) + (subform.Copies X
PrintingFees.Copies)

I hope that is clear enough.
Any help will be greatly appreciated.

 

[Upendra Agnihotram]

Dear Wayne,

Add One more text Box on the form and set the
controlsource = (subform.First X PrintingFees.FeeForFirst) + (subform.Copies
X PrintingFees.Copies)

Regards,
Upendra

 

[Wayne Livingstone]

I tried that but I just get an error #Name?
And I need to record the value from the expression in the
PrintFee field in the PrintReq_Sub table.

 

[Marshall Barton]

I really do not understand what you want to do here, but if
the code you're using is the same as what you posted, then
you need to be aware that names of things in Access should
not use spcaes or other non-alphanumeric characters. It is
legal to use the funky characters in a name, but then the
name **must** be enclosed in square brackets.

If you insist on using the names you posted, then your
statement would be more like:

subform.PrintFee = subform.[First X
PrintingFees].FeeForFirst + subform.[Copies X
PrintingFees].Copies

However, I have no idea where you want to place that line of
code.

 

[Wayne Livingstone]

Hi

The code I typed was not meant to be be the exact code, but
a general representation of what I want to happen. It
sounds like I havent been clear enough.
I want to multiply subform.First by
PrintingFees.FeeForFirst and multiply subform.Copies by
PrintingFees.Copies, then add the two totals together.
I want this total to be entered into the PrintFee field on
my subform.
I am thinking that I would probably code this on the After
Update event of the First and Copies fields on the subform.

The reason I havent been more specific with the code is
that I really dont know how to code this. I'm hoping that
you can show me how this can be done.

-----Original Message-----
I really do not understand what you want to do here, but if
the code you're using is the same as what you posted, then
you need to be aware that names of things in Access should
not use spcaes or other non-alphanumeric characters. It is
legal to use the funky characters in a name, but then the
name **must** be enclosed in square brackets.

If you insist on using the names you posted, then your
statement would be more like:

subform.PrintFee = subform.[First X
PrintingFees].FeeForFirst + subform.[Copies X
PrintingFees].Copies

However, I have no idea where you want to place that line of
code.
--
Marsh
MVP [MS Access]

I tried that but I just get an error #Name?
And I need to record the value from the expression in the
PrintFee field in the PrintReq_Sub table.

.

 

[Marshall Barton]

The code I typed was not meant to be be the exact code, but
a general representation of what I want to happen. It
sounds like I havent been clear enough.
I want to multiply subform.First by
PrintingFees.FeeForFirst and multiply subform.Copies by
PrintingFees.Copies, then add the two totals together.
I want this total to be entered into the PrintFee field on
my subform.
I am thinking that I would probably code this on the After
Update event of the First and Copies fields on the subform.

What is PrintingFees? A form? A table?

I still can't tell what data we're working with here, but
maybe all you want is:

subbform.Fomr.PrintFee = FeeForFirst * subform.Form.Copies _
+ Copies * subform.Form.Copies

Make sure you get the names straight. Pay special attention
to the name of the subform ***control***, it might be
different from the name of the subform object it is
displaying.

 

[Wayne Livingstone]

OK...I've done some work on this now and I think I've got
most of the code right. This is what I have:

Private Sub First_AfterUpdate()

Dim strSqlFirst As String
Dim strSqlCopies As String
Dim strSqlWhere As String
Dim strSqlfeeForFirst As String
Dim strSqlFeeRemaining As String

strSqlFirst = "SELECT FeeForFirst " & _
"FROM [PrintingFees] "

strSqlCopies = "SELECT FeeRemaining " & _
"FROM [PrintingFees] "

strSqlWhere = " WHERE [PrintingFees].PrintFeeID=" _
&
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID]

strSqlfeeForFirst = strSqlFirst & strSqlWhere & ";"
strSqlFeeRemaining = strSqlCopies & strSqlWhere
& ";"


Me!PrintFee.RowSource = strSqlfeeForFirst *
[Forms]![PrintRequisition]![PrintReq_Subform]![First] _
+ strSqlFeeRemaining *
[Forms]![PrintRequisition]![PrintReq_Subform]![Copies]

End Sub

I believe I'm picking up all the values I want now.
The last line of code gives me a Runtime Error 13 Type
Mismatch.
How do I add and multiply these items together?

 

[Marshall Barton]

OK...I've done some work on this now and I think I've got
most of the code right. This is what I have:

Private Sub First_AfterUpdate()

Dim strSqlFirst As String
Dim strSqlCopies As String
Dim strSqlWhere As String
Dim strSqlfeeForFirst As String
Dim strSqlFeeRemaining As String

strSqlFirst = "SELECT FeeForFirst " & _
"FROM [PrintingFees] "

strSqlCopies = "SELECT FeeRemaining " & _
"FROM [PrintingFees] "

strSqlWhere = " WHERE [PrintingFees].PrintFeeID=" _
&
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID]

strSqlfeeForFirst = strSqlFirst & strSqlWhere & ";"
strSqlFeeRemaining = strSqlCopies & strSqlWhere
& ";"


Me!PrintFee.RowSource = strSqlfeeForFirst *
[Forms]![PrintRequisition]![PrintReq_Subform]![First] _
+ strSqlFeeRemaining *
[Forms]![PrintRequisition]![PrintReq_Subform]![Copies]

End Sub

I believe I'm picking up all the values I want now.

Sorry, but you're not going to execute those SQL statements
by using them in an arithmetic expression (the VBA
environment is unaware of the database engine environment
that runs SQL statements).

Instead of the SQL, use the DLookup function to retrieve the
values (or else open a recordset on an SQL query). Try
this:

Dim varFirst As Variant
Dim varCopies As Variant

varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" & Me!PrintReq_Subform.PrintFeeID)

varCopies = DLookup("FeeRemaining", "PrintingFees", _
"PrintFeeID=" & Me!PrintReq_Subform.PrintFeeID)


Me!PrintFee = varFirst * Me!PrintReq_Subform.First _
+ varCopies * Me!PrintReq_Subform.Copies

I've shortened those form references, but what you had would
also work.

 

[Wayne Livingstone]

It works some of the time now.
sometimes when I edit the values in existing records I get:
Syntax error (missing operator) in query expression
'PrintFeeID='
The line of code its pointing at is:
varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" &
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID])

And here's the entire code:
Private Sub First_AfterUpdate()

Dim varFirst As Variant
Dim varCopies As Variant

varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" &
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID])

varCopies = DLookup("FeeRemaining", "PrintingFees", _
"PrintFeeID=" &
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID])


Me!PrintFee = (varFirst *
[Forms]![PrintRequisition]![PrintReq_Subform]![First]) _
+ (varCopies *
[Forms]![PrintRequisition]![PrintReq_Subform]![Copies])

End Sub

I really appreciate your help with this. Thankyou VERY much
for help in figuring this out.

-----Original Message-----

OK...I've done some work on this now and I think I've got
most of the code right. This is what I have:

Private Sub First_AfterUpdate()

Dim strSqlFirst As String
Dim strSqlCopies As String
Dim strSqlWhere As String
Dim strSqlfeeForFirst As String
Dim strSqlFeeRemaining As String

strSqlFirst = "SELECT FeeForFirst " & _
"FROM [PrintingFees] "

strSqlCopies = "SELECT FeeRemaining " & _
"FROM [PrintingFees] "

strSqlWhere = " WHERE [PrintingFees].PrintFeeID=" _
&
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID]

strSqlfeeForFirst = strSqlFirst & strSqlWhere & ";"
strSqlFeeRemaining = strSqlCopies & strSqlWhere
& ";"


Me!PrintFee.RowSource = strSqlfeeForFirst *
[Forms]![PrintRequisition]![PrintReq_Subform]![First] _
+ strSqlFeeRemaining *
[Forms]![PrintRequisition]![PrintReq_Subform]![Copies]

End Sub

I believe I'm picking up all the values I want now.

Sorry, but you're not going to execute those SQL statements
by using them in an arithmetic expression (the VBA
environment is unaware of the database engine environment
that runs SQL statements).

Instead of the SQL, use the DLookup function to retrieve the
values (or else open a recordset on an SQL query). Try
this:

Dim varFirst As Variant
Dim varCopies As Variant

varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" & Me!PrintReq_Subform.PrintFeeID)

varCopies = DLookup("FeeRemaining", "PrintingFees", _
"PrintFeeID=" & Me!PrintReq_Subform.PrintFeeID)


Me!PrintFee = varFirst * Me!PrintReq_Subform.First _
+ varCopies * Me!PrintReq_Subform.Copies

I've shortened those form references, but what you had would
also work.

 

[Marshall Barton]

It works some of the time now.
sometimes when I edit the values in existing records I get:
Syntax error (missing operator) in query expression
'PrintFeeID='
The line of code its pointing at is:
varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" &
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID])

And here's the entire code:
Private Sub First_AfterUpdate()

Dim varFirst As Variant
Dim varCopies As Variant

varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" &
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID])

varCopies = DLookup("FeeRemaining", "PrintingFees", _
"PrintFeeID=" &
[Forms]![PrintRequisition]![PrintReq_Subform]![PrintFeeID])


Me!PrintFee = (varFirst *
[Forms]![PrintRequisition]![PrintReq_Subform]![First]) _
+ (varCopies *
[Forms]![PrintRequisition]![PrintReq_Subform]![Copies])

End Sub

That will happen when there is no value specified for
PrintFeeID.

You may want to check for that situation by using something
like:

Private Sub First_AfterUpdate()
Dim varFirst As Variant
Dim varCopies As Variant
Dim varID As Variant

varID = Forms!PrintRequisition!PrintReq_Subform!PrintFeeID
If Not IsNull(varID) Then
varFirst = DLookup("FeeForFirst", "PrintingFees", _
"PrintFeeID=" & varID)

varCopies = DLookup("FeeRemaining", "PrintingFees", _
"PrintFeeID=" & varID)

Me!PrintFee = (varFirst *
[Forms]![PrintRequisition]![PrintReq_Subform]![First]) _
+ (varCopies *
[Forms]![PrintRequisition]![PrintReq_Subform]![Copies])
End If
End Sub