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

calculation

P: n/a
here is my code

Sub test()
Dim Rst As New ADODB.Recordset
Dim SQLstmnt As String
Dim RQ0 As Integer
Dim CurrentListedItemsNUM As Long
Dim SumLQav0 As Integer
RQ0 = Forms![frmlisted_3].Form![RQ0NUM]

On Error GoTo ErrorHandler
'DoCmd.RunCommand acCmdSaveRecord

CurrentListedItemsNUM = Forms![frmlisted_3]![frmllisteditems
subform].Form![ListedItemsNUM]

SQLstmnt = "SELECT SUM(LQav0NUM) AS SumLQav0 FROM TblListedItems " &
"WHERE[ListedItemsNUM]= """ & CurrentListedItemsNUM & """"
Rst.Open SQLstmnt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

Forms![frmlisted_3]![frmllisteditems subform].Form![LQav0NUM] = RQ0 -
SumLQav0

Rst.Close
Set Rst = Nothing
GoTo done

ErrorHandler:
MsgBox Err.Description
done:
End Sub
for some feason i'm not able to get the value for SumLQav0, i think
something is wrong with my SQLstmnt
i get the vlaue for currentlisteditemsnum
but i cant get the sum of LQav0NUM

please help

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"gbb0330" <gb*****@gmail.com> wrote
Sub test()
Dim Rst As New ADODB.Recordset
Dim SQLstmnt As String
Dim RQ0 As Integer
Dim CurrentListedItemsNUM As Long
Dim SumLQav0 As Integer
RQ0 = Forms![frmlisted_3].Form![RQ0NUM]

On Error GoTo ErrorHandler
'DoCmd.RunCommand acCmdSaveRecord

CurrentListedItemsNUM = Forms![frmlisted_3]![frmllisteditems
subform].Form![ListedItemsNUM]

SQLstmnt = "SELECT SUM(LQav0NUM) AS SumLQav0 FROM TblListedItems " &
"WHERE[ListedItemsNUM]= """ & CurrentListedItemsNUM & """"
Rst.Open SQLstmnt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

Forms![frmlisted_3]![frmllisteditems subform].Form![LQav0NUM] = RQ0 -
SumLQav0

Rst.Close
Set Rst = Nothing
GoTo done

ErrorHandler:
MsgBox Err.Description
done:
End Sub
for some feason i'm not able to get the value for SumLQav0, i think
something is wrong with my SQLstmnt
i get the vlaue for currentlisteditemsnum
but i cant get the sum of LQav0NUM

please help
First, you have opened the recordset, but then done nothing with it. Your
code does not actually get a value from the recordet and use it anywwhere.
You need to address the Fields member of the recordset like this:

Rst.Fields("SumLQav0")
Your SQL string appears to have too many quote marks, but it is frankly hard
to tell outside the VBA Editor. Try this in the testing phase: just before
you open the recordset, print out the SQL string in a MsgBox, so that you
can examine how the string looks when it is all concatenated together with
the variable values filled in:

MsgBox SQLstmnt
Although it will not make your code have an error in this case, using two
separate statements to Dim and Set your object varaible is better practice.
So use the following two statemets

Dim Rst As ADODB.Recordset
Set Rst = New ADODB.Recordset

instead of:
Dim Rst As New ADODB.Recordset


Darryl Kerkeslager
Nov 13 '05 #2

P: n/a
Darryl Hi
thank you for your help

what i get from MsgBox SQLstmnt:

SELECT SUM (LQav0NUM) AS SumLQav0 FROM TblListedItems WHERE[ListedItemsNUM]="4"

i added the following lines
Dim Rst As New ADODB.Recordset
Set Rst = New ADODB.Recordset
with no problems

when i add
Rst.Fields ("SumLQav0")
i get Compile error: invalid use of property

if i run the code without it i get
Data type mismatch in criteria expression
Darryl Kerkeslager wrote:
"gbb0330" <gb*****@gmail.com> wrote
Sub test()
Dim Rst As New ADODB.Recordset
Dim SQLstmnt As Sring
Dim RQ0 As Integer
Dim CurrentListedItemsNUM As Long
Dim SumLQav0 As Integer
RQ0 = Forms![frmlisted_3].Form![RQ0NUM]

On Error GoTo ErrorHandler
'DoCmd.RunCommand acCmdSaveRecord

CurrentListedItemsNUM = Forms![frmlisted_3]![frmllisteditems
subform].Form![ListedItemsNUM]

SQLstmnt = "SELECT SUM(LQav0NUM) AS SumLQav0 FROM TblListedItems " & "WHERE[ListedItemsNUM]= """ & CurrentListedItemsNUM & """"
Rst.Open SQLstmnt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

Forms![frmlisted_3]![frmllisteditems subform].Form![LQav0NUM] = RQ0 - SumLQav0

Rst.Close
Set Rst = Nothing
GoTo done

ErrorHandler:
MsgBox Err.Description
done:
End Sub
for some feason i'm not able to get the value for SumLQav0, i think
something is wrong with my SQLstmnt
i get the vlaue for currentlisteditemsnum
but i cant get the sum of LQav0NUM

please help
First, you have opened the recordset, but then done nothing with it.

Your code does not actually get a value from the recordet and use it anywwhere. You need to address the Fields member of the recordset like this:

Rst.Fields ("SumLQav0")
Your SQL string appears to have too many quote marks, but it is frankly hard to tell outside the VBA Editor. Try this in the testing phase: just before you open the recordset, print out the SQL string in a MsgBox, so that you can examine how the string looks when it is all concatenated together with the variable values filled in:

MsgBox SQLstmnt
Although it will not make your code have an error in this case, using two separate statements to Dim and Set your object varaible is better practice. So use the following two statemets

Dim Rst As ADODB.Recordset
Set Rst = New ADODB.Recordset

instead of:
Dim Rst As New ADODB.Recordset


Darryl Kerkeslager


Nov 13 '05 #3

P: n/a
"gbb0330" <gb*****@gmail.com> wrote
what i get from MsgBox SQLstmnt:

SELECT SUM (LQav0NUM) AS SumLQav0 FROM TblListedItems WHERE
[ListedItemsNUM]="4"

i added the following lines
Dim Rst As New ADODB.Recordset
Set Rst = New ADODB.Recordset
with no problems

when i add
Rst.Fields ("SumLQav0")
i get Compile error: invalid use of property


In your msgBox text above, it shows you having double quotes around "4". The
field name is ListedItemsNUM, which looks to be a numeric type base on the
name. If it is numeric, there should be no quotes at all - so remove the
extras. If it is not numeric, than the quotes should be single quotes.

BTW, you have correctly named your fields without spaces, so there is no
need to use the square brackets. The square brackets would only be
necessary had you used spaces within your filed names
Perhaps the width of text in the ng posting caused the confusion. What I
meant was that you needed to change the line:

Forms![frmlisted_3]![frmllisteditems subform].Form![LQav0NUM] = RQ0 -
SumLQav0

to

Forms![frmlisted_3]![frmllisteditems subform].Form![LQav0NUM] = RQ0 -
Rst.Fields ("SumLQav0")

This should all be on one line, even if the newsreader puts it on 2 or more
lines.

I also note that there may be an incorrect subform name. Is
"frmllisteditems subform" correct?
If you still have problems, re-post the entire sub.
Darryl Kerkeslager

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.