recordset question

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I'm trying to do the following to get total for textbox on
a formfoot of a continious form:

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strCode As String ' Form is linked with strCode table
to be able to edit data there.

Set rst1 = OpenRecordset("SELECT ([StaffDay]*[ShiftDay]+
[StaffAFT]*[ShiftAFT])*[Rate] AS LabourDol" _
& " FROM " & strCode & " INNER JOIN tblRate ON [" &
strCode & "].[Rate Code] = tblRate.[Rate Code]")
It's working. But, I need a sum of these records.

Is it possible to get Sum of LabourDol to assign it to
textbox on the formfoot?
Something as Set rst2 = OpenRecordset(Sum(rst1.LabourDol))

Thanks
 
Hi Alex

Add the Sum function to your query:

SELECT Sum(([StaffDay]* ... )*[Rate]) AS SumLaborDol FROM ..."

You can then open the recordset and put the first (and only) field value
into the textbox:

Set rst1 = CurrentDb.OpenRecordset("Select...")
txtTotalLaborDol = rst1![SumLaborDol]
rst1.Close

Note that you cannot bind a textbox to a field in a recordset you have
created in VBA, so this must be done manually.

I am more that somewhat suspicious though, that you appear to have several
tables with identical structure. This is an indication of poor database
design. Would you like to explain some more about your structure and what
you want to achieve, because there is probably a better way.
 
Thanks a lot, Graham.
It's working.

Alex
-----Original Message-----
Hi Alex

Add the Sum function to your query:

SELECT Sum(([StaffDay]* ... )*[Rate]) AS SumLaborDol FROM ..."

You can then open the recordset and put the first (and only) field value
into the textbox:

Set rst1 = CurrentDb.OpenRecordset("Select...")
txtTotalLaborDol = rst1![SumLaborDol]
rst1.Close

Note that you cannot bind a textbox to a field in a recordset you have
created in VBA, so this must be done manually.

I am more that somewhat suspicious though, that you appear to have several
tables with identical structure. This is an indication of poor database
design. Would you like to explain some more about your structure and what
you want to achieve, because there is probably a better way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to do the following to get total for textbox on
a formfoot of a continious form:

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strCode As String ' Form is linked with strCode table
to be able to edit data there.

Set rst1 = OpenRecordset("SELECT ([StaffDay]*[ShiftDay]+
[StaffAFT]*[ShiftAFT])*[Rate] AS LabourDol" _
& " FROM " & strCode & " INNER JOIN tblRate ON [" &
strCode & "].[Rate Code] = tblRate.[Rate Code]")
It's working. But, I need a sum of these records.

Is it possible to get Sum of LabourDol to assign it to
textbox on the formfoot?
Something as Set rst2 = OpenRecordset(Sum (rst1.LabourDol))

Thanks
Click to expand...


.
Click to expand...
 
Back
Top