I am just starting a foray into VBA, and am stuck right at the beginning.

My goal is to total some fields in one table, for each employee, and to add
these totals to a different table, with some information from previous
records in the second table incorporated. How can I get access to select all
of the records in table one, the most recent for each employee in table two,
and then combine them? (some will write as is, others add, others subtract,
etc.)

Is there a way to loop through each record from a select statement? I'm at
a loss for what the syntax is, or where to find examples of the syntax.

If more details would be helpful, here's the basics of the tables:
tblHours
EmployeeID----Date----Hrs----Type
A-----------1/1/09-----6--------Regular
A-----------1/1/09-----2--------Vacation
A-----------1/2/09-----8--------Overtime
B-----------1/1/09-----8--------Regular

tblEmployees
EmployeeID--------Dept---------VacRate
A-------------------sales-----------2
B-------------------admin----------3

tblHistor
EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalanc
A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------1
B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12


I need to total each employees' hours in tblHours for a week, put these in
tblHistory fields, add them to LifetimeHrs, multiply some of them by their
VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
subtract VacationHrs to get the VacationBalance.

I have a good idea of how to write the code for each element, ie which field
is getting multiplied by each and which is getting entered where, but I do
not know how to set up the skeleton. The whole part of selecting these
records and doing something with each of them (or, you know, with the sum of
them).

Thanks for any help you have. Anything described here or a link to some
basics on this kind of VBA would be GREATLY appreciated!

~Claire

Hi Claire... welcome aboard


There's various different ways to do what you are looking for, and I'll give
one example here. It may not always be the *best* way, but a the Recordset
object is very universal when working with specific data. It may not be
quite as fast as different queries to do the these tasks, but Recordsets have
always been 'old reliables' for me, and they have a very broad scope of what
can be done with them. You will need to know some basic SQL (Select
statements) to pull the data that you want to work with.

'===============
Sub GetTotal()

Dim db As Database 'To reference what db we're in
Dim rs as DAO.Recordset 'This holds the records to loop
Dim strSQL As String 'Your SQL statement
Dim lCount As Long 'This will tally the hours

'Objects like databases & records need to be "set"
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
'Now rs is equal to your select statement

'Make sure there's at least one record
If rs.RecordCount <> 0 Then

'Move to the first record in the set
rs.MoveFirst

'Loop the recordset until it hits the end
While rs.EOF = False
'Add the record's Hrs field value to lCount...
lCount = lCount + rs.Fields("Hrs")

'Move to the next record
rs.MoveNext

Wend

End If

'Close the Recordset
rs.Close

'Unreference the objects
Set rs = Nothing
Set db = Nothing
'====================


There you go...

If you want to add a record, use this:

With rs
.AddNew
.Fields("fieldname") = Value
.Fields("fieldname") = Value
.Update
End With


Or if you want to edit the current record:

With rs
.Edit
.Fields("fieldname") = Value
.Update
End With


Or delete the current record

rs.Delete



Anyway, you get the idea. Use the Recordset Object... theres all sorts of
more in depth tutorials if you need more detailed info...

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


"Claire" wrote:

> I am just starting a foray into VBA, and am stuck right at the beginning.
>
> My goal is to total some fields in one table, for each employee, and to add
> these totals to a different table, with some information from previous
> records in the second table incorporated. How can I get access to select all
> of the records in table one, the most recent for each employee in table two,
> and then combine them? (some will write as is, others add, others subtract,
> etc.)
>
> Is there a way to loop through each record from a select statement? I'm at
> a loss for what the syntax is, or where to find examples of the syntax.
>
> If more details would be helpful, here's the basics of the tables:
> tblHours
> EmployeeID----Date----Hrs----Type
> A-----------1/1/09-----6--------Regular
> A-----------1/1/09-----2--------Vacation
> A-----------1/2/09-----8--------Overtime
> B-----------1/1/09-----8--------Regular
>
> tblEmployees
> EmployeeID--------Dept---------VacRate
> A-------------------sales-----------2
> B-------------------admin----------3
>
> tblHistory
> EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
>
>
> I need to total each employees' hours in tblHours for a week, put these in
> tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> subtract VacationHrs to get the VacationBalance.
>
> I have a good idea of how to write the code for each element, ie which field
> is getting multiplied by each and which is getting entered where, but I do
> not know how to set up the skeleton. The whole part of selecting these
> records and doing something with each of them (or, you know, with the sum of
> them).
>
> Thanks for any help you have. Anything described here or a link to some
> basics on this kind of VBA would be GREATLY appreciated!
>
> ~Claire

Thanks very much for your help! There's one more element I'm unclear on.
How do I refer to another table that is related to the first? I'm going to
be utilizing at least three tables: One with hour information, one with
information about the employees, and one where the new data is going to be
added. I'm quite comfortable with the SQL aspects, but how do you join
tables (or do you do something else?) is VBA?



"dymondjack" wrote:

> Hi Claire... welcome aboard
>
>
> There's various different ways to do what you are looking for, and I'll give
> one example here. It may not always be the *best* way, but a the Recordset
> object is very universal when working with specific data. It may not be
> quite as fast as different queries to do the these tasks, but Recordsets have
> always been 'old reliables' for me, and they have a very broad scope of what
> can be done with them. You will need to know some basic SQL (Select
> statements) to pull the data that you want to work with.
>
> '===============
> Sub GetTotal()
>
> Dim db As Database 'To reference what db we're in
> Dim rs as DAO.Recordset 'This holds the records to loop
> Dim strSQL As String 'Your SQL statement
> Dim lCount As Long 'This will tally the hours
>
> 'Objects like databases & records need to be "set"
> Set db = CurrentDB
> Set rs = db.OpenRecordset(strSQL)
> 'Now rs is equal to your select statement
>
> 'Make sure there's at least one record
> If rs.RecordCount <> 0 Then
>
> 'Move to the first record in the set
> rs.MoveFirst
>
> 'Loop the recordset until it hits the end
> While rs.EOF = False
> 'Add the record's Hrs field value to lCount...
> lCount = lCount + rs.Fields("Hrs")
>
> 'Move to the next record
> rs.MoveNext
>
> Wend
>
> End If
>
> 'Close the Recordset
> rs.Close
>
> 'Unreference the objects
> Set rs = Nothing
> Set db = Nothing
> '====================
>
>
> There you go...
>
> If you want to add a record, use this:
>
> With rs
> .AddNew
> .Fields("fieldname") = Value
> .Fields("fieldname") = Value
> .Update
> End With
>
>
> Or if you want to edit the current record:
>
> With rs
> .Edit
> .Fields("fieldname") = Value
> .Update
> End With
>
>
> Or delete the current record
>
> rs.Delete
>
>
>
> Anyway, you get the idea. Use the Recordset Object... theres all sorts of
> more in depth tutorials if you need more detailed info...
>
> hth
>
> --
> Jack Leach
> www.tristatemachine.com
>
> - "First, get your information. Then, you can distort it at your leisure."
> - Mark Twain
>
>
> "Claire" wrote:
>
> > I am just starting a foray into VBA, and am stuck right at the beginning.
> >
> > My goal is to total some fields in one table, for each employee, and to add
> > these totals to a different table, with some information from previous
> > records in the second table incorporated. How can I get access to select all
> > of the records in table one, the most recent for each employee in table two,
> > and then combine them? (some will write as is, others add, others subtract,
> > etc.)
> >
> > Is there a way to loop through each record from a select statement? I'm at
> > a loss for what the syntax is, or where to find examples of the syntax.
> >
> > If more details would be helpful, here's the basics of the tables:
> > tblHours
> > EmployeeID----Date----Hrs----Type
> > A-----------1/1/09-----6--------Regular
> > A-----------1/1/09-----2--------Vacation
> > A-----------1/2/09-----8--------Overtime
> > B-----------1/1/09-----8--------Regular
> >
> > tblEmployees
> > EmployeeID--------Dept---------VacRate
> > A-------------------sales-----------2
> > B-------------------admin----------3
> >
> > tblHistory
> > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
> >
> >
> > I need to total each employees' hours in tblHours for a week, put these in
> > tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> > VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> > subtract VacationHrs to get the VacationBalance.
> >
> > I have a good idea of how to write the code for each element, ie which field
> > is getting multiplied by each and which is getting entered where, but I do
> > not know how to set up the skeleton. The whole part of selecting these
> > records and doing something with each of them (or, you know, with the sum of
> > them).
> >
> > Thanks for any help you have. Anything described here or a link to some
> > basics on this kind of VBA would be GREATLY appreciated!
> >
> > ~Claire

When working with Recordsets, you will only work with one table per
recordset. Use your SQL to get the related records


strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID

Set rs = CurrentDB.OpenRecordset(strSQL)

So if you wanted hours for a specific employee, build it in your SQL, and
the Recordset will open only those hours.

You can work with multiple recordset objects though:

Dim rsEmployees As DAO.Recordset
Dim rsHours As DAO.Recordset

Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
Set rsHours = CurrentDB.OpenRecordset(strSQL)

You can then reference either/or recordset in vba, and intertwine loops for
each recordset. A common example of using two recordsets for me is to add
data to a separate table based on the frist recordset.

While not rsEmployees.EOF
with rsHours
.Addnew
.Fields("field") = whatever
.Update
End With
rsEmployees.MoveNext
Wend

So in an example here, you loop your Employees recordset, and for each, and
add a record to the Hours recordset for each employee. Feel free to use any
conditional statements to further control your code within the loop, but also
be wary of performance issues when using loops like this: the more it has to
check for each record, the longer it will take.

Keep in mind also that there are queries that can do this as well, usually
more efficiently. Unfortunately I am not well enough versed with them to
feel comfortable offering advice to use them, and I gave the Recordset
example because it offers much more flexability and can be used more
universally (IMO).

If you want to see about accomplishing some of this using queries, theres a
number of sites that offer good information on them.

www.allenbrowne.com/tips.html

http://www.accessmvp.com/strive4peace/

http://www.mvps.org/access/


One final note on performance with recordsets: although it does take longer
than queries, I tend to use recordsets a lot more (it's what I first
learned), and I have a number of applications that run over a
frontend/backend on a network with a few thousand records, and have never
experienced and *major* performance issues. So, while queries may be more
efficient, it may not be enough to make a noticable difference.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


"Claire" wrote:

> Thanks very much for your help! There's one more element I'm unclear on.
> How do I refer to another table that is related to the first? I'm going to
> be utilizing at least three tables: One with hour information, one with
> information about the employees, and one where the new data is going to be
> added. I'm quite comfortable with the SQL aspects, but how do you join
> tables (or do you do something else?) is VBA?
>
>
>
> "dymondjack" wrote:
>
> > Hi Claire... welcome aboard
> >
> >
> > There's various different ways to do what you are looking for, and I'll give
> > one example here. It may not always be the *best* way, but a the Recordset
> > object is very universal when working with specific data. It may not be
> > quite as fast as different queries to do the these tasks, but Recordsets have
> > always been 'old reliables' for me, and they have a very broad scope of what
> > can be done with them. You will need to know some basic SQL (Select
> > statements) to pull the data that you want to work with.
> >
> > '===============
> > Sub GetTotal()
> >
> > Dim db As Database 'To reference what db we're in
> > Dim rs as DAO.Recordset 'This holds the records to loop
> > Dim strSQL As String 'Your SQL statement
> > Dim lCount As Long 'This will tally the hours
> >
> > 'Objects like databases & records need to be "set"
> > Set db = CurrentDB
> > Set rs = db.OpenRecordset(strSQL)
> > 'Now rs is equal to your select statement
> >
> > 'Make sure there's at least one record
> > If rs.RecordCount <> 0 Then
> >
> > 'Move to the first record in the set
> > rs.MoveFirst
> >
> > 'Loop the recordset until it hits the end
> > While rs.EOF = False
> > 'Add the record's Hrs field value to lCount...
> > lCount = lCount + rs.Fields("Hrs")
> >
> > 'Move to the next record
> > rs.MoveNext
> >
> > Wend
> >
> > End If
> >
> > 'Close the Recordset
> > rs.Close
> >
> > 'Unreference the objects
> > Set rs = Nothing
> > Set db = Nothing
> > '====================
> >
> >
> > There you go...
> >
> > If you want to add a record, use this:
> >
> > With rs
> > .AddNew
> > .Fields("fieldname") = Value
> > .Fields("fieldname") = Value
> > .Update
> > End With
> >
> >
> > Or if you want to edit the current record:
> >
> > With rs
> > .Edit
> > .Fields("fieldname") = Value
> > .Update
> > End With
> >
> >
> > Or delete the current record
> >
> > rs.Delete
> >
> >
> >
> > Anyway, you get the idea. Use the Recordset Object... theres all sorts of
> > more in depth tutorials if you need more detailed info...
> >
> > hth
> >
> > --
> > Jack Leach
> > www.tristatemachine.com
> >
> > - "First, get your information. Then, you can distort it at your leisure."
> > - Mark Twain
> >
> >
> > "Claire" wrote:
> >
> > > I am just starting a foray into VBA, and am stuck right at the beginning.
> > >
> > > My goal is to total some fields in one table, for each employee, and to add
> > > these totals to a different table, with some information from previous
> > > records in the second table incorporated. How can I get access to select all
> > > of the records in table one, the most recent for each employee in table two,
> > > and then combine them? (some will write as is, others add, others subtract,
> > > etc.)
> > >
> > > Is there a way to loop through each record from a select statement? I'm at
> > > a loss for what the syntax is, or where to find examples of the syntax.
> > >
> > > If more details would be helpful, here's the basics of the tables:
> > > tblHours
> > > EmployeeID----Date----Hrs----Type
> > > A-----------1/1/09-----6--------Regular
> > > A-----------1/1/09-----2--------Vacation
> > > A-----------1/2/09-----8--------Overtime
> > > B-----------1/1/09-----8--------Regular
> > >
> > > tblEmployees
> > > EmployeeID--------Dept---------VacRate
> > > A-------------------sales-----------2
> > > B-------------------admin----------3
> > >
> > > tblHistory
> > > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> > > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> > > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
> > >
> > >
> > > I need to total each employees' hours in tblHours for a week, put these in
> > > tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> > > VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> > > subtract VacationHrs to get the VacationBalance.
> > >
> > > I have a good idea of how to write the code for each element, ie which field
> > > is getting multiplied by each and which is getting entered where, but I do
> > > not know how to set up the skeleton. The whole part of selecting these
> > > records and doing something with each of them (or, you know, with the sum of
> > > them).
> > >
> > > Thanks for any help you have. Anything described here or a link to some
> > > basics on this kind of VBA would be GREATLY appreciated!
> > >
> > > ~Claire

Hi Claire,

I have a couple of comments and observations in addition to what Jack
posted....

In the table "tblHours", there are fields named "date" and "type". these are
reserved words in Access (and SQL). Using them as field names will cause you
major headaches down the road. In addition "date" is not very descriptive...
"date" of what??? "type" of what??? "WorkHrs" and "HrsType" might be better
names for the fields. Allen Browne has a list of reserved words at

http://allenbrowne.com/Ap****ueBadWord.html


I see in your history table you are storing the results (totals/ sums) of
hours. This is generally considered bad practice. Here is an example: Suppose
employee "A" worked all of January. On 1/25 he reported that the hours he
reported was wrong for 1/12 because he had to take 2 hours personal time. He
reported 8 hours worked... now you need to subtract 2 hours from the 1/12
entry. But you also need to subtract 2 hours from the LifetimeHrs field on
1/13. And the same for 1/14.... and for 1/15... and ..... what a chore!

And what happens is you have to start tracking Sick Days earned/used??? You
will have to redesign the table, queries, forms and reports.

I would think about adding the department field to the table "tblHours".
Then to get the lifetime hours, use a totals query to sum the regular and
overtime hours.

Same with the Vacation Balance. You would have an entry of -2 hours for
"VacationUsed" and have an positive entry for "VacationEarned".

my 2 pennies....

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Claire" wrote:

> I am just starting a foray into VBA, and am stuck right at the beginning.
>
> My goal is to total some fields in one table, for each employee, and to add
> these totals to a different table, with some information from previous
> records in the second table incorporated. How can I get access to select all
> of the records in table one, the most recent for each employee in table two,
> and then combine them? (some will write as is, others add, others subtract,
> etc.)
>
> Is there a way to loop through each record from a select statement? I'm at
> a loss for what the syntax is, or where to find examples of the syntax.
>
> If more details would be helpful, here's the basics of the tables:
> tblHours
> EmployeeID----Date----Hrs----Type
> A-----------1/1/09-----6--------Regular
> A-----------1/1/09-----2--------Vacation
> A-----------1/2/09-----8--------Overtime
> B-----------1/1/09-----8--------Regular
>
> tblEmployees
> EmployeeID--------Dept---------VacRate
> A-------------------sales-----------2
> B-------------------admin----------3
>
> tblHistory
> EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
>
>
> I need to total each employees' hours in tblHours for a week, put these in
> tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> subtract VacationHrs to get the VacationBalance.
>
> I have a good idea of how to write the code for each element, ie which field
> is getting multiplied by each and which is getting entered where, but I do
> not know how to set up the skeleton. The whole part of selecting these
> records and doing something with each of them (or, you know, with the sum of
> them).
>
> Thanks for any help you have. Anything described here or a link to some
> basics on this kind of VBA would be GREATLY appreciated!
>
> ~Claire

Dymondjack: I also need help with looping multiple tables.

I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
go to the first record in HK_active and the first record in Reserv_active and
update each table from a value in the other table.
goto 1st record in t_HK
goto 1st record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
goto the next record in t_HK
goto the next record in t_Reserv
use field "HKNum" in t_HK and update a field in t_reserv
use field "TripsReservID" and update a field in t_HK
etc.

Right now, I'm just trying to move to the next record in each form. When
the procedure starts, I am on record 1 for both tables. Now I need to move
to the 2nd record for both tables.

DoCmd.OpenForm "f_HK"
DoCmd.OpenForm "f_Reserv"


Forms!f_HK!HKNum.SetFocus
longintHKNum = Forms.f_HK.HKNum
' at record 1 for t_HK

Forms!f_Reserv!TripsReservID.SetFocus
longintTripsReservID = Forms.f_Reserv.TripsReservID
' at record 1 for t_Reserv


Forms!f_HK!HKNum.SetFocus
DoCmd.GoToRecord , , acNext
longintHKNum = Forms.f_HK.HKNum
' stayed on record 1

Forms!f_Reserv!TripsReservID.SetFocus
DoCmd.GoToRecord , , acNext
longintTripsReservID = Forms.f_Reserv.TripsReservID
' Now at record 3 ???

Your help would be GREATLY appreciated.


"dymondjack" wrote:

> When working with Recordsets, you will only work with one table per
> recordset. Use your SQL to get the related records
>
>
> strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID
>
> Set rs = CurrentDB.OpenRecordset(strSQL)
>
> So if you wanted hours for a specific employee, build it in your SQL, and
> the Recordset will open only those hours.
>
> You can work with multiple recordset objects though:
>
> Dim rsEmployees As DAO.Recordset
> Dim rsHours As DAO.Recordset
>
> Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
> Set rsHours = CurrentDB.OpenRecordset(strSQL)
>
> You can then reference either/or recordset in vba, and intertwine loops for
> each recordset. A common example of using two recordsets for me is to add
> data to a separate table based on the frist recordset.
>
> While not rsEmployees.EOF
> with rsHours
> .Addnew
> .Fields("field") = whatever
> .Update
> End With
> rsEmployees.MoveNext
> Wend
>
> So in an example here, you loop your Employees recordset, and for each, and
> add a record to the Hours recordset for each employee. Feel free to use any
> conditional statements to further control your code within the loop, but also
> be wary of performance issues when using loops like this: the more it has to
> check for each record, the longer it will take.
>
> Keep in mind also that there are queries that can do this as well, usually
> more efficiently. Unfortunately I am not well enough versed with them to
> feel comfortable offering advice to use them, and I gave the Recordset
> example because it offers much more flexability and can be used more
> universally (IMO).
>
> If you want to see about accomplishing some of this using queries, theres a
> number of sites that offer good information on them.
>
> www.allenbrowne.com/tips.html
>
> http://www.accessmvp.com/strive4peace/
>
> http://www.mvps.org/access/
>
>
> One final note on performance with recordsets: although it does take longer
> than queries, I tend to use recordsets a lot more (it's what I first
> learned), and I have a number of applications that run over a
> frontend/backend on a network with a few thousand records, and have never
> experienced and *major* performance issues. So, while queries may be more
> efficient, it may not be enough to make a noticable difference.
>
> --
> Jack Leach
> www.tristatemachine.com
>
> - "First, get your information. Then, you can distort it at your leisure."
> - Mark Twain
>
>
> "Claire" wrote:
>
> > Thanks very much for your help! There's one more element I'm unclear on.
> > How do I refer to another table that is related to the first? I'm going to
> > be utilizing at least three tables: One with hour information, one with
> > information about the employees, and one where the new data is going to be
> > added. I'm quite comfortable with the SQL aspects, but how do you join
> > tables (or do you do something else?) is VBA?
> >
> >
> >
> > "dymondjack" wrote:
> >
> > > Hi Claire... welcome aboard
> > >
> > >
> > > There's various different ways to do what you are looking for, and I'll give
> > > one example here. It may not always be the *best* way, but a the Recordset
> > > object is very universal when working with specific data. It may not be
> > > quite as fast as different queries to do the these tasks, but Recordsets have
> > > always been 'old reliables' for me, and they have a very broad scope of what
> > > can be done with them. You will need to know some basic SQL (Select
> > > statements) to pull the data that you want to work with.
> > >
> > > '===============
> > > Sub GetTotal()
> > >
> > > Dim db As Database 'To reference what db we're in
> > > Dim rs as DAO.Recordset 'This holds the records to loop
> > > Dim strSQL As String 'Your SQL statement
> > > Dim lCount As Long 'This will tally the hours
> > >
> > > 'Objects like databases & records need to be "set"
> > > Set db = CurrentDB
> > > Set rs = db.OpenRecordset(strSQL)
> > > 'Now rs is equal to your select statement
> > >
> > > 'Make sure there's at least one record
> > > If rs.RecordCount <> 0 Then
> > >
> > > 'Move to the first record in the set
> > > rs.MoveFirst
> > >
> > > 'Loop the recordset until it hits the end
> > > While rs.EOF = False
> > > 'Add the record's Hrs field value to lCount...
> > > lCount = lCount + rs.Fields("Hrs")
> > >
> > > 'Move to the next record
> > > rs.MoveNext
> > >
> > > Wend
> > >
> > > End If
> > >
> > > 'Close the Recordset
> > > rs.Close
> > >
> > > 'Unreference the objects
> > > Set rs = Nothing
> > > Set db = Nothing
> > > '====================
> > >
> > >
> > > There you go...
> > >
> > > If you want to add a record, use this:
> > >
> > > With rs
> > > .AddNew
> > > .Fields("fieldname") = Value
> > > .Fields("fieldname") = Value
> > > .Update
> > > End With
> > >
> > >
> > > Or if you want to edit the current record:
> > >
> > > With rs
> > > .Edit
> > > .Fields("fieldname") = Value
> > > .Update
> > > End With
> > >
> > >
> > > Or delete the current record
> > >
> > > rs.Delete
> > >
> > >
> > >
> > > Anyway, you get the idea. Use the Recordset Object... theres all sorts of
> > > more in depth tutorials if you need more detailed info...
> > >
> > > hth
> > >
> > > --
> > > Jack Leach
> > > www.tristatemachine.com
> > >
> > > - "First, get your information. Then, you can distort it at your leisure."
> > > - Mark Twain
> > >
> > >
> > > "Claire" wrote:
> > >
> > > > I am just starting a foray into VBA, and am stuck right at the beginning.
> > > >
> > > > My goal is to total some fields in one table, for each employee, and to add
> > > > these totals to a different table, with some information from previous
> > > > records in the second table incorporated. How can I get access to select all
> > > > of the records in table one, the most recent for each employee in table two,
> > > > and then combine them? (some will write as is, others add, others subtract,
> > > > etc.)
> > > >
> > > > Is there a way to loop through each record from a select statement? I'm at
> > > > a loss for what the syntax is, or where to find examples of the syntax.
> > > >
> > > > If more details would be helpful, here's the basics of the tables:
> > > > tblHours
> > > > EmployeeID----Date----Hrs----Type
> > > > A-----------1/1/09-----6--------Regular
> > > > A-----------1/1/09-----2--------Vacation
> > > > A-----------1/2/09-----8--------Overtime
> > > > B-----------1/1/09-----8--------Regular
> > > >
> > > > tblEmployees
> > > > EmployeeID--------Dept---------VacRate
> > > > A-------------------sales-----------2
> > > > B-------------------admin----------3
> > > >
> > > > tblHistory
> > > > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> > > > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> > > > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
> > > >
> > > >
> > > > I need to total each employees' hours in tblHours for a week, put these in
> > > > tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> > > > VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> > > > subtract VacationHrs to get the VacationBalance.
> > > >
> > > > I have a good idea of how to write the code for each element, ie which field
> > > > is getting multiplied by each and which is getting entered where, but I do
> > > > not know how to set up the skeleton. The whole part of selecting these
> > > > records and doing something with each of them (or, you know, with the sum of
> > > > them).
> > > >
> > > > Thanks for any help you have. Anything described here or a link to some
> > > > basics on this kind of VBA would be GREATLY appreciated!
> > > >
> > > > ~Claire

Are you trying to add a new record to t_Reserve, or are the records already
there and you are trying to populate that particular field?

If the records are already there and you are trying to populate, there will
have to be the exact same amount of records, in the same order for both
tables.

I think you should be able to do this with an append query, if you are
adding a new record to t_Reserve. Anyway, I'm on the way out the door now
with a busy day ahead of me, but if you don't have something figured out
later tonight (about 12 hours anyway regardless of location) I'll give an
example of how to do it w/ a recordset.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


"laavista" wrote:

> Dymondjack: I also need help with looping multiple tables.
>
> I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
> go to the first record in HK_active and the first record in Reserv_active and
> update each table from a value in the other table.
> goto 1st record in t_HK
> goto 1st record in t_Reserv
> use field "HKNum" in t_HK and update a field in t_reserv
> use field "TripsReservID" and update a field in t_HK
> goto the next record in t_HK
> goto the next record in t_Reserv
> use field "HKNum" in t_HK and update a field in t_reserv
> use field "TripsReservID" and update a field in t_HK
> etc.
>
> Right now, I'm just trying to move to the next record in each form. When
> the procedure starts, I am on record 1 for both tables. Now I need to move
> to the 2nd record for both tables.
>
> DoCmd.OpenForm "f_HK"
> DoCmd.OpenForm "f_Reserv"
>
>
> Forms!f_HK!HKNum.SetFocus
> longintHKNum = Forms.f_HK.HKNum
> ' at record 1 for t_HK
>
> Forms!f_Reserv!TripsReservID.SetFocus
> longintTripsReservID = Forms.f_Reserv.TripsReservID
> ' at record 1 for t_Reserv
>
>
> Forms!f_HK!HKNum.SetFocus
> DoCmd.GoToRecord , , acNext
> longintHKNum = Forms.f_HK.HKNum
> ' stayed on record 1
>
> Forms!f_Reserv!TripsReservID.SetFocus
> DoCmd.GoToRecord , , acNext
> longintTripsReservID = Forms.f_Reserv.TripsReservID
> ' Now at record 3 ???
>
> Your help would be GREATLY appreciated.
>
>
> "dymondjack" wrote:
>
> > When working with Recordsets, you will only work with one table per
> > recordset. Use your SQL to get the related records
> >
> >
> > strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID
> >
> > Set rs = CurrentDB.OpenRecordset(strSQL)
> >
> > So if you wanted hours for a specific employee, build it in your SQL, and
> > the Recordset will open only those hours.
> >
> > You can work with multiple recordset objects though:
> >
> > Dim rsEmployees As DAO.Recordset
> > Dim rsHours As DAO.Recordset
> >
> > Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
> > Set rsHours = CurrentDB.OpenRecordset(strSQL)
> >
> > You can then reference either/or recordset in vba, and intertwine loops for
> > each recordset. A common example of using two recordsets for me is to add
> > data to a separate table based on the frist recordset.
> >
> > While not rsEmployees.EOF
> > with rsHours
> > .Addnew
> > .Fields("field") = whatever
> > .Update
> > End With
> > rsEmployees.MoveNext
> > Wend
> >
> > So in an example here, you loop your Employees recordset, and for each, and
> > add a record to the Hours recordset for each employee. Feel free to use any
> > conditional statements to further control your code within the loop, but also
> > be wary of performance issues when using loops like this: the more it has to
> > check for each record, the longer it will take.
> >
> > Keep in mind also that there are queries that can do this as well, usually
> > more efficiently. Unfortunately I am not well enough versed with them to
> > feel comfortable offering advice to use them, and I gave the Recordset
> > example because it offers much more flexability and can be used more
> > universally (IMO).
> >
> > If you want to see about accomplishing some of this using queries, theres a
> > number of sites that offer good information on them.
> >
> > www.allenbrowne.com/tips.html
> >
> > http://www.accessmvp.com/strive4peace/
> >
> > http://www.mvps.org/access/
> >
> >
> > One final note on performance with recordsets: although it does take longer
> > than queries, I tend to use recordsets a lot more (it's what I first
> > learned), and I have a number of applications that run over a
> > frontend/backend on a network with a few thousand records, and have never
> > experienced and *major* performance issues. So, while queries may be more
> > efficient, it may not be enough to make a noticable difference.
> >
> > --
> > Jack Leach
> > www.tristatemachine.com
> >
> > - "First, get your information. Then, you can distort it at your leisure."
> > - Mark Twain
> >
> >
> > "Claire" wrote:
> >
> > > Thanks very much for your help! There's one more element I'm unclear on.
> > > How do I refer to another table that is related to the first? I'm going to
> > > be utilizing at least three tables: One with hour information, one with
> > > information about the employees, and one where the new data is going to be
> > > added. I'm quite comfortable with the SQL aspects, but how do you join
> > > tables (or do you do something else?) is VBA?
> > >
> > >
> > >
> > > "dymondjack" wrote:
> > >
> > > > Hi Claire... welcome aboard
> > > >
> > > >
> > > > There's various different ways to do what you are looking for, and I'll give
> > > > one example here. It may not always be the *best* way, but a the Recordset
> > > > object is very universal when working with specific data. It may not be
> > > > quite as fast as different queries to do the these tasks, but Recordsets have
> > > > always been 'old reliables' for me, and they have a very broad scope of what
> > > > can be done with them. You will need to know some basic SQL (Select
> > > > statements) to pull the data that you want to work with.
> > > >
> > > > '===============
> > > > Sub GetTotal()
> > > >
> > > > Dim db As Database 'To reference what db we're in
> > > > Dim rs as DAO.Recordset 'This holds the records to loop
> > > > Dim strSQL As String 'Your SQL statement
> > > > Dim lCount As Long 'This will tally the hours
> > > >
> > > > 'Objects like databases & records need to be "set"
> > > > Set db = CurrentDB
> > > > Set rs = db.OpenRecordset(strSQL)
> > > > 'Now rs is equal to your select statement
> > > >
> > > > 'Make sure there's at least one record
> > > > If rs.RecordCount <> 0 Then
> > > >
> > > > 'Move to the first record in the set
> > > > rs.MoveFirst
> > > >
> > > > 'Loop the recordset until it hits the end
> > > > While rs.EOF = False
> > > > 'Add the record's Hrs field value to lCount...
> > > > lCount = lCount + rs.Fields("Hrs")
> > > >
> > > > 'Move to the next record
> > > > rs.MoveNext
> > > >
> > > > Wend
> > > >
> > > > End If
> > > >
> > > > 'Close the Recordset
> > > > rs.Close
> > > >
> > > > 'Unreference the objects
> > > > Set rs = Nothing
> > > > Set db = Nothing
> > > > '====================
> > > >
> > > >
> > > > There you go...
> > > >
> > > > If you want to add a record, use this:
> > > >
> > > > With rs
> > > > .AddNew
> > > > .Fields("fieldname") = Value
> > > > .Fields("fieldname") = Value
> > > > .Update
> > > > End With
> > > >
> > > >
> > > > Or if you want to edit the current record:
> > > >
> > > > With rs
> > > > .Edit
> > > > .Fields("fieldname") = Value
> > > > .Update
> > > > End With
> > > >
> > > >
> > > > Or delete the current record
> > > >
> > > > rs.Delete
> > > >
> > > >
> > > >
> > > > Anyway, you get the idea. Use the Recordset Object... theres all sorts of
> > > > more in depth tutorials if you need more detailed info...
> > > >
> > > > hth
> > > >
> > > > --
> > > > Jack Leach
> > > > www.tristatemachine.com
> > > >
> > > > - "First, get your information. Then, you can distort it at your leisure."
> > > > - Mark Twain
> > > >
> > > >
> > > > "Claire" wrote:
> > > >
> > > > > I am just starting a foray into VBA, and am stuck right at the beginning.
> > > > >
> > > > > My goal is to total some fields in one table, for each employee, and to add
> > > > > these totals to a different table, with some information from previous
> > > > > records in the second table incorporated. How can I get access to select all
> > > > > of the records in table one, the most recent for each employee in table two,
> > > > > and then combine them? (some will write as is, others add, others subtract,
> > > > > etc.)
> > > > >
> > > > > Is there a way to loop through each record from a select statement? I'm at
> > > > > a loss for what the syntax is, or where to find examples of the syntax.
> > > > >
> > > > > If more details would be helpful, here's the basics of the tables:
> > > > > tblHours
> > > > > EmployeeID----Date----Hrs----Type
> > > > > A-----------1/1/09-----6--------Regular
> > > > > A-----------1/1/09-----2--------Vacation
> > > > > A-----------1/2/09-----8--------Overtime
> > > > > B-----------1/1/09-----8--------Regular
> > > > >
> > > > > tblEmployees
> > > > > EmployeeID--------Dept---------VacRate
> > > > > A-------------------sales-----------2
> > > > > B-------------------admin----------3
> > > > >
> > > > > tblHistory
> > > > > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> > > > > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> > > > > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
> > > > >
> > > > >
> > > > > I need to total each employees' hours in tblHours for a week, put these in
> > > > > tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> > > > > VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> > > > > subtract VacationHrs to get the VacationBalance.
> > > > >
> > > > > I have a good idea of how to write the code for each element, ie which field
> > > > > is getting multiplied by each and which is getting entered where, but I do
> > > > > not know how to set up the skeleton. The whole part of selecting these
> > > > > records and doing something with each of them (or, you know, with the sum of
> > > > > them).
> > > > >
> > > > > Thanks for any help you have. Anything described here or a link to some
> > > > > basics on this kind of VBA would be GREATLY appreciated!
> > > > >
> > > > > ~Claire

Can you not just use an Update query?

It's seldom efficient to use VBA when updating every record in a table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"laavista" <(E-Mail Removed)> wrote in message
news:FAFAA477-EBAA-4C45-A6EE-(E-Mail Removed)...
> Dymondjack: I also need help with looping multiple tables.
>
> I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want
> to
> go to the first record in HK_active and the first record in Reserv_active
> and
> update each table from a value in the other table.
> goto 1st record in t_HK
> goto 1st record in t_Reserv
> use field "HKNum" in t_HK and update a field in t_reserv
> use field "TripsReservID" and update a field in t_HK
> goto the next record in t_HK
> goto the next record in t_Reserv
> use field "HKNum" in t_HK and update a field in t_reserv
> use field "TripsReservID" and update a field in t_HK
> etc.
>
> Right now, I'm just trying to move to the next record in each form. When
> the procedure starts, I am on record 1 for both tables. Now I need to
> move
> to the 2nd record for both tables.
>
> DoCmd.OpenForm "f_HK"
> DoCmd.OpenForm "f_Reserv"
>
>
> Forms!f_HK!HKNum.SetFocus
> longintHKNum = Forms.f_HK.HKNum
> ' at record 1 for t_HK
>
> Forms!f_Reserv!TripsReservID.SetFocus
> longintTripsReservID = Forms.f_Reserv.TripsReservID
> ' at record 1 for t_Reserv
>
>
> Forms!f_HK!HKNum.SetFocus
> DoCmd.GoToRecord , , acNext
> longintHKNum = Forms.f_HK.HKNum
> ' stayed on record 1
>
> Forms!f_Reserv!TripsReservID.SetFocus
> DoCmd.GoToRecord , , acNext
> longintTripsReservID = Forms.f_Reserv.TripsReservID
> ' Now at record 3 ???
>
> Your help would be GREATLY appreciated.
>
>
> "dymondjack" wrote:
>
>> When working with Recordsets, you will only work with one table per
>> recordset. Use your SQL to get the related records
>>
>>
>> strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID
>>
>> Set rs = CurrentDB.OpenRecordset(strSQL)
>>
>> So if you wanted hours for a specific employee, build it in your SQL, and
>> the Recordset will open only those hours.
>>
>> You can work with multiple recordset objects though:
>>
>> Dim rsEmployees As DAO.Recordset
>> Dim rsHours As DAO.Recordset
>>
>> Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
>> Set rsHours = CurrentDB.OpenRecordset(strSQL)
>>
>> You can then reference either/or recordset in vba, and intertwine loops
>> for
>> each recordset. A common example of using two recordsets for me is to
>> add
>> data to a separate table based on the frist recordset.
>>
>> While not rsEmployees.EOF
>> with rsHours
>> .Addnew
>> .Fields("field") = whatever
>> .Update
>> End With
>> rsEmployees.MoveNext
>> Wend
>>
>> So in an example here, you loop your Employees recordset, and for each,
>> and
>> add a record to the Hours recordset for each employee. Feel free to use
>> any
>> conditional statements to further control your code within the loop, but
>> also
>> be wary of performance issues when using loops like this: the more it has
>> to
>> check for each record, the longer it will take.
>>
>> Keep in mind also that there are queries that can do this as well,
>> usually
>> more efficiently. Unfortunately I am not well enough versed with them to
>> feel comfortable offering advice to use them, and I gave the Recordset
>> example because it offers much more flexability and can be used more
>> universally (IMO).
>>
>> If you want to see about accomplishing some of this using queries, theres
>> a
>> number of sites that offer good information on them.
>>
>> www.allenbrowne.com/tips.html
>>
>> http://www.accessmvp.com/strive4peace/
>>
>> http://www.mvps.org/access/
>>
>>
>> One final note on performance with recordsets: although it does take
>> longer
>> than queries, I tend to use recordsets a lot more (it's what I first
>> learned), and I have a number of applications that run over a
>> frontend/backend on a network with a few thousand records, and have never
>> experienced and *major* performance issues. So, while queries may be
>> more
>> efficient, it may not be enough to make a noticable difference.
>>
>> --
>> Jack Leach
>> www.tristatemachine.com
>>
>> - "First, get your information. Then, you can distort it at your
>> leisure."
>> - Mark Twain
>>
>>
>> "Claire" wrote:
>>
>> > Thanks very much for your help! There's one more element I'm unclear
>> > on.
>> > How do I refer to another table that is related to the first? I'm
>> > going to
>> > be utilizing at least three tables: One with hour information, one with
>> > information about the employees, and one where the new data is going to
>> > be
>> > added. I'm quite comfortable with the SQL aspects, but how do you join
>> > tables (or do you do something else?) is VBA?
>> >
>> >
>> >
>> > "dymondjack" wrote:
>> >
>> > > Hi Claire... welcome aboard
>> > >
>> > >
>> > > There's various different ways to do what you are looking for, and
>> > > I'll give
>> > > one example here. It may not always be the *best* way, but a the
>> > > Recordset
>> > > object is very universal when working with specific data. It may not
>> > > be
>> > > quite as fast as different queries to do the these tasks, but
>> > > Recordsets have
>> > > always been 'old reliables' for me, and they have a very broad scope
>> > > of what
>> > > can be done with them. You will need to know some basic SQL (Select
>> > > statements) to pull the data that you want to work with.
>> > >
>> > > '===============
>> > > Sub GetTotal()
>> > >
>> > > Dim db As Database 'To reference what db we're in
>> > > Dim rs as DAO.Recordset 'This holds the records to loop
>> > > Dim strSQL As String 'Your SQL statement
>> > > Dim lCount As Long 'This will tally the hours
>> > >
>> > > 'Objects like databases & records need to be "set"
>> > > Set db = CurrentDB
>> > > Set rs = db.OpenRecordset(strSQL)
>> > > 'Now rs is equal to your select statement
>> > >
>> > > 'Make sure there's at least one record
>> > > If rs.RecordCount <> 0 Then
>> > >
>> > > 'Move to the first record in the set
>> > > rs.MoveFirst
>> > >
>> > > 'Loop the recordset until it hits the end
>> > > While rs.EOF = False
>> > > 'Add the record's Hrs field value to lCount...
>> > > lCount = lCount + rs.Fields("Hrs")
>> > >
>> > > 'Move to the next record
>> > > rs.MoveNext
>> > >
>> > > Wend
>> > >
>> > > End If
>> > >
>> > > 'Close the Recordset
>> > > rs.Close
>> > >
>> > > 'Unreference the objects
>> > > Set rs = Nothing
>> > > Set db = Nothing
>> > > '====================
>> > >
>> > >
>> > > There you go...
>> > >
>> > > If you want to add a record, use this:
>> > >
>> > > With rs
>> > > .AddNew
>> > > .Fields("fieldname") = Value
>> > > .Fields("fieldname") = Value
>> > > .Update
>> > > End With
>> > >
>> > >
>> > > Or if you want to edit the current record:
>> > >
>> > > With rs
>> > > .Edit
>> > > .Fields("fieldname") = Value
>> > > .Update
>> > > End With
>> > >
>> > >
>> > > Or delete the current record
>> > >
>> > > rs.Delete
>> > >
>> > >
>> > >
>> > > Anyway, you get the idea. Use the Recordset Object... theres all
>> > > sorts of
>> > > more in depth tutorials if you need more detailed info...
>> > >
>> > > hth
>> > >
>> > > --
>> > > Jack Leach
>> > > www.tristatemachine.com
>> > >
>> > > - "First, get your information. Then, you can distort it at your
>> > > leisure."
>> > > - Mark Twain
>> > >
>> > >
>> > > "Claire" wrote:
>> > >
>> > > > I am just starting a foray into VBA, and am stuck right at the
>> > > > beginning.
>> > > >
>> > > > My goal is to total some fields in one table, for each employee,
>> > > > and to add
>> > > > these totals to a different table, with some information from
>> > > > previous
>> > > > records in the second table incorporated. How can I get access to
>> > > > select all
>> > > > of the records in table one, the most recent for each employee in
>> > > > table two,
>> > > > and then combine them? (some will write as is, others add, others
>> > > > subtract,
>> > > > etc.)
>> > > >
>> > > > Is there a way to loop through each record from a select statement?
>> > > > I'm at
>> > > > a loss for what the syntax is, or where to find examples of the
>> > > > syntax.
>> > > >
>> > > > If more details would be helpful, here's the basics of the tables:
>> > > > tblHours
>> > > > EmployeeID----Date----Hrs----Type
>> > > > A-----------1/1/09-----6--------Regular
>> > > > A-----------1/1/09-----2--------Vacation
>> > > > A-----------1/2/09-----8--------Overtime
>> > > > B-----------1/1/09-----8--------Regular
>> > > >
>> > > > tblEmployees
>> > > > EmployeeID--------Dept---------VacRate
>> > > > A-------------------sales-----------2
>> > > > B-------------------admin----------3
>> > > >
>> > > > tblHistory
>> > > > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
>> > > > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
>> > > > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
>> > > >
>> > > >
>> > > > I need to total each employees' hours in tblHours for a week, put
>> > > > these in
>> > > > tblHistory fields, add them to LifetimeHrs, multiply some of them
>> > > > by their
>> > > > VacRate(from tblEmployees) to get VacationEarned, and add
>> > > > VacationEarned and
>> > > > subtract VacationHrs to get the VacationBalance.
>> > > >
>> > > > I have a good idea of how to write the code for each element, ie
>> > > > which field
>> > > > is getting multiplied by each and which is getting entered where,
>> > > > but I do
>> > > > not know how to set up the skeleton. The whole part of selecting
>> > > > these
>> > > > records and doing something with each of them (or, you know, with
>> > > > the sum of
>> > > > them).
>> > > >
>> > > > Thanks for any help you have. Anything described here or a link to
>> > > > some
>> > > > basics on this kind of VBA would be GREATLY appreciated!
>> > > >
>> > > > ~Claire

Dymondjack:

I need to update records already in the table.

No, the two tables will not have the same number of records. There would be
30 records maximum in either of the tables, though.

Thanks in advance for any help you can provide.


"dymondjack" wrote:

> Are you trying to add a new record to t_Reserve, or are the records already
> there and you are trying to populate that particular field?
>
> If the records are already there and you are trying to populate, there will
> have to be the exact same amount of records, in the same order for both
> tables.
>
> I think you should be able to do this with an append query, if you are
> adding a new record to t_Reserve. Anyway, I'm on the way out the door now
> with a busy day ahead of me, but if you don't have something figured out
> later tonight (about 12 hours anyway regardless of location) I'll give an
> example of how to do it w/ a recordset.
>
>
> --
> Jack Leach
> www.tristatemachine.com
>
> - "First, get your information. Then, you can distort it at your leisure."
> - Mark Twain
>
>
> "laavista" wrote:
>
> > Dymondjack: I also need help with looping multiple tables.
> >
> > I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want to
> > go to the first record in HK_active and the first record in Reserv_active and
> > update each table from a value in the other table.
> > goto 1st record in t_HK
> > goto 1st record in t_Reserv
> > use field "HKNum" in t_HK and update a field in t_reserv
> > use field "TripsReservID" and update a field in t_HK
> > goto the next record in t_HK
> > goto the next record in t_Reserv
> > use field "HKNum" in t_HK and update a field in t_reserv
> > use field "TripsReservID" and update a field in t_HK
> > etc.
> >
> > Right now, I'm just trying to move to the next record in each form. When
> > the procedure starts, I am on record 1 for both tables. Now I need to move
> > to the 2nd record for both tables.
> >
> > DoCmd.OpenForm "f_HK"
> > DoCmd.OpenForm "f_Reserv"
> >
> >
> > Forms!f_HK!HKNum.SetFocus
> > longintHKNum = Forms.f_HK.HKNum
> > ' at record 1 for t_HK
> >
> > Forms!f_Reserv!TripsReservID.SetFocus
> > longintTripsReservID = Forms.f_Reserv.TripsReservID
> > ' at record 1 for t_Reserv
> >
> >
> > Forms!f_HK!HKNum.SetFocus
> > DoCmd.GoToRecord , , acNext
> > longintHKNum = Forms.f_HK.HKNum
> > ' stayed on record 1
> >
> > Forms!f_Reserv!TripsReservID.SetFocus
> > DoCmd.GoToRecord , , acNext
> > longintTripsReservID = Forms.f_Reserv.TripsReservID
> > ' Now at record 3 ???
> >
> > Your help would be GREATLY appreciated.
> >
> >
> > "dymondjack" wrote:
> >
> > > When working with Recordsets, you will only work with one table per
> > > recordset. Use your SQL to get the related records
> > >
> > >
> > > strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID
> > >
> > > Set rs = CurrentDB.OpenRecordset(strSQL)
> > >
> > > So if you wanted hours for a specific employee, build it in your SQL, and
> > > the Recordset will open only those hours.
> > >
> > > You can work with multiple recordset objects though:
> > >
> > > Dim rsEmployees As DAO.Recordset
> > > Dim rsHours As DAO.Recordset
> > >
> > > Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
> > > Set rsHours = CurrentDB.OpenRecordset(strSQL)
> > >
> > > You can then reference either/or recordset in vba, and intertwine loops for
> > > each recordset. A common example of using two recordsets for me is to add
> > > data to a separate table based on the frist recordset.
> > >
> > > While not rsEmployees.EOF
> > > with rsHours
> > > .Addnew
> > > .Fields("field") = whatever
> > > .Update
> > > End With
> > > rsEmployees.MoveNext
> > > Wend
> > >
> > > So in an example here, you loop your Employees recordset, and for each, and
> > > add a record to the Hours recordset for each employee. Feel free to use any
> > > conditional statements to further control your code within the loop, but also
> > > be wary of performance issues when using loops like this: the more it has to
> > > check for each record, the longer it will take.
> > >
> > > Keep in mind also that there are queries that can do this as well, usually
> > > more efficiently. Unfortunately I am not well enough versed with them to
> > > feel comfortable offering advice to use them, and I gave the Recordset
> > > example because it offers much more flexability and can be used more
> > > universally (IMO).
> > >
> > > If you want to see about accomplishing some of this using queries, theres a
> > > number of sites that offer good information on them.
> > >
> > > www.allenbrowne.com/tips.html
> > >
> > > http://www.accessmvp.com/strive4peace/
> > >
> > > http://www.mvps.org/access/
> > >
> > >
> > > One final note on performance with recordsets: although it does take longer
> > > than queries, I tend to use recordsets a lot more (it's what I first
> > > learned), and I have a number of applications that run over a
> > > frontend/backend on a network with a few thousand records, and have never
> > > experienced and *major* performance issues. So, while queries may be more
> > > efficient, it may not be enough to make a noticable difference.
> > >
> > > --
> > > Jack Leach
> > > www.tristatemachine.com
> > >
> > > - "First, get your information. Then, you can distort it at your leisure."
> > > - Mark Twain
> > >
> > >
> > > "Claire" wrote:
> > >
> > > > Thanks very much for your help! There's one more element I'm unclear on.
> > > > How do I refer to another table that is related to the first? I'm going to
> > > > be utilizing at least three tables: One with hour information, one with
> > > > information about the employees, and one where the new data is going to be
> > > > added. I'm quite comfortable with the SQL aspects, but how do you join
> > > > tables (or do you do something else?) is VBA?
> > > >
> > > >
> > > >
> > > > "dymondjack" wrote:
> > > >
> > > > > Hi Claire... welcome aboard
> > > > >
> > > > >
> > > > > There's various different ways to do what you are looking for, and I'll give
> > > > > one example here. It may not always be the *best* way, but a the Recordset
> > > > > object is very universal when working with specific data. It may not be
> > > > > quite as fast as different queries to do the these tasks, but Recordsets have
> > > > > always been 'old reliables' for me, and they have a very broad scope of what
> > > > > can be done with them. You will need to know some basic SQL (Select
> > > > > statements) to pull the data that you want to work with.
> > > > >
> > > > > '===============
> > > > > Sub GetTotal()
> > > > >
> > > > > Dim db As Database 'To reference what db we're in
> > > > > Dim rs as DAO.Recordset 'This holds the records to loop
> > > > > Dim strSQL As String 'Your SQL statement
> > > > > Dim lCount As Long 'This will tally the hours
> > > > >
> > > > > 'Objects like databases & records need to be "set"
> > > > > Set db = CurrentDB
> > > > > Set rs = db.OpenRecordset(strSQL)
> > > > > 'Now rs is equal to your select statement
> > > > >
> > > > > 'Make sure there's at least one record
> > > > > If rs.RecordCount <> 0 Then
> > > > >
> > > > > 'Move to the first record in the set
> > > > > rs.MoveFirst
> > > > >
> > > > > 'Loop the recordset until it hits the end
> > > > > While rs.EOF = False
> > > > > 'Add the record's Hrs field value to lCount...
> > > > > lCount = lCount + rs.Fields("Hrs")
> > > > >
> > > > > 'Move to the next record
> > > > > rs.MoveNext
> > > > >
> > > > > Wend
> > > > >
> > > > > End If
> > > > >
> > > > > 'Close the Recordset
> > > > > rs.Close
> > > > >
> > > > > 'Unreference the objects
> > > > > Set rs = Nothing
> > > > > Set db = Nothing
> > > > > '====================
> > > > >
> > > > >
> > > > > There you go...
> > > > >
> > > > > If you want to add a record, use this:
> > > > >
> > > > > With rs
> > > > > .AddNew
> > > > > .Fields("fieldname") = Value
> > > > > .Fields("fieldname") = Value
> > > > > .Update
> > > > > End With
> > > > >
> > > > >
> > > > > Or if you want to edit the current record:
> > > > >
> > > > > With rs
> > > > > .Edit
> > > > > .Fields("fieldname") = Value
> > > > > .Update
> > > > > End With
> > > > >
> > > > >
> > > > > Or delete the current record
> > > > >
> > > > > rs.Delete
> > > > >
> > > > >
> > > > >
> > > > > Anyway, you get the idea. Use the Recordset Object... theres all sorts of
> > > > > more in depth tutorials if you need more detailed info...
> > > > >
> > > > > hth
> > > > >
> > > > > --
> > > > > Jack Leach
> > > > > www.tristatemachine.com
> > > > >
> > > > > - "First, get your information. Then, you can distort it at your leisure."
> > > > > - Mark Twain
> > > > >
> > > > >
> > > > > "Claire" wrote:
> > > > >
> > > > > > I am just starting a foray into VBA, and am stuck right at the beginning.
> > > > > >
> > > > > > My goal is to total some fields in one table, for each employee, and to add
> > > > > > these totals to a different table, with some information from previous
> > > > > > records in the second table incorporated. How can I get access to select all
> > > > > > of the records in table one, the most recent for each employee in table two,
> > > > > > and then combine them? (some will write as is, others add, others subtract,
> > > > > > etc.)
> > > > > >
> > > > > > Is there a way to loop through each record from a select statement? I'm at
> > > > > > a loss for what the syntax is, or where to find examples of the syntax.
> > > > > >
> > > > > > If more details would be helpful, here's the basics of the tables:
> > > > > > tblHours
> > > > > > EmployeeID----Date----Hrs----Type
> > > > > > A-----------1/1/09-----6--------Regular
> > > > > > A-----------1/1/09-----2--------Vacation
> > > > > > A-----------1/2/09-----8--------Overtime
> > > > > > B-----------1/1/09-----8--------Regular
> > > > > >
> > > > > > tblEmployees
> > > > > > EmployeeID--------Dept---------VacRate
> > > > > > A-------------------sales-----------2
> > > > > > B-------------------admin----------3
> > > > > >
> > > > > > tblHistory
> > > > > > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> > > > > > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> > > > > > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
> > > > > >
> > > > > >
> > > > > > I need to total each employees' hours in tblHours for a week, put these in
> > > > > > tblHistory fields, add them to LifetimeHrs, multiply some of them by their
> > > > > > VacRate(from tblEmployees) to get VacationEarned, and add VacationEarned and
> > > > > > subtract VacationHrs to get the VacationBalance.
> > > > > >
> > > > > > I have a good idea of how to write the code for each element, ie which field
> > > > > > is getting multiplied by each and which is getting entered where, but I do
> > > > > > not know how to set up the skeleton. The whole part of selecting these
> > > > > > records and doing something with each of them (or, you know, with the sum of
> > > > > > them).
> > > > > >
> > > > > > Thanks for any help you have. Anything described here or a link to some
> > > > > > basics on this kind of VBA would be GREATLY appreciated!
> > > > > >
> > > > > > ~Claire

I'm not sure how to use the update query in this case...



"Douglas J. Steele" wrote:

> Can you not just use an Update query?
>
> It's seldom efficient to use VBA when updating every record in a table.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "laavista" <(E-Mail Removed)> wrote in message
> news:FAFAA477-EBAA-4C45-A6EE-(E-Mail Removed)...
> > Dymondjack: I also need help with looping multiple tables.
> >
> > I have two tables; t_HK and t_Reserv. Using a "do while not EOF", I want
> > to
> > go to the first record in HK_active and the first record in Reserv_active
> > and
> > update each table from a value in the other table.
> > goto 1st record in t_HK
> > goto 1st record in t_Reserv
> > use field "HKNum" in t_HK and update a field in t_reserv
> > use field "TripsReservID" and update a field in t_HK
> > goto the next record in t_HK
> > goto the next record in t_Reserv
> > use field "HKNum" in t_HK and update a field in t_reserv
> > use field "TripsReservID" and update a field in t_HK
> > etc.
> >
> > Right now, I'm just trying to move to the next record in each form. When
> > the procedure starts, I am on record 1 for both tables. Now I need to
> > move
> > to the 2nd record for both tables.
> >
> > DoCmd.OpenForm "f_HK"
> > DoCmd.OpenForm "f_Reserv"
> >
> >
> > Forms!f_HK!HKNum.SetFocus
> > longintHKNum = Forms.f_HK.HKNum
> > ' at record 1 for t_HK
> >
> > Forms!f_Reserv!TripsReservID.SetFocus
> > longintTripsReservID = Forms.f_Reserv.TripsReservID
> > ' at record 1 for t_Reserv
> >
> >
> > Forms!f_HK!HKNum.SetFocus
> > DoCmd.GoToRecord , , acNext
> > longintHKNum = Forms.f_HK.HKNum
> > ' stayed on record 1
> >
> > Forms!f_Reserv!TripsReservID.SetFocus
> > DoCmd.GoToRecord , , acNext
> > longintTripsReservID = Forms.f_Reserv.TripsReservID
> > ' Now at record 3 ???
> >
> > Your help would be GREATLY appreciated.
> >
> >
> > "dymondjack" wrote:
> >
> >> When working with Recordsets, you will only work with one table per
> >> recordset. Use your SQL to get the related records
> >>
> >>
> >> strSQL = "SELECT * FROM table WHERE [forgienkey] = " & foreignkeyID
> >>
> >> Set rs = CurrentDB.OpenRecordset(strSQL)
> >>
> >> So if you wanted hours for a specific employee, build it in your SQL, and
> >> the Recordset will open only those hours.
> >>
> >> You can work with multiple recordset objects though:
> >>
> >> Dim rsEmployees As DAO.Recordset
> >> Dim rsHours As DAO.Recordset
> >>
> >> Set rsEmployees = CurrentDB.OpenRecordset("tblEmployees")
> >> Set rsHours = CurrentDB.OpenRecordset(strSQL)
> >>
> >> You can then reference either/or recordset in vba, and intertwine loops
> >> for
> >> each recordset. A common example of using two recordsets for me is to
> >> add
> >> data to a separate table based on the frist recordset.
> >>
> >> While not rsEmployees.EOF
> >> with rsHours
> >> .Addnew
> >> .Fields("field") = whatever
> >> .Update
> >> End With
> >> rsEmployees.MoveNext
> >> Wend
> >>
> >> So in an example here, you loop your Employees recordset, and for each,
> >> and
> >> add a record to the Hours recordset for each employee. Feel free to use
> >> any
> >> conditional statements to further control your code within the loop, but
> >> also
> >> be wary of performance issues when using loops like this: the more it has
> >> to
> >> check for each record, the longer it will take.
> >>
> >> Keep in mind also that there are queries that can do this as well,
> >> usually
> >> more efficiently. Unfortunately I am not well enough versed with them to
> >> feel comfortable offering advice to use them, and I gave the Recordset
> >> example because it offers much more flexability and can be used more
> >> universally (IMO).
> >>
> >> If you want to see about accomplishing some of this using queries, theres
> >> a
> >> number of sites that offer good information on them.
> >>
> >> www.allenbrowne.com/tips.html
> >>
> >> http://www.accessmvp.com/strive4peace/
> >>
> >> http://www.mvps.org/access/
> >>
> >>
> >> One final note on performance with recordsets: although it does take
> >> longer
> >> than queries, I tend to use recordsets a lot more (it's what I first
> >> learned), and I have a number of applications that run over a
> >> frontend/backend on a network with a few thousand records, and have never
> >> experienced and *major* performance issues. So, while queries may be
> >> more
> >> efficient, it may not be enough to make a noticable difference.
> >>
> >> --
> >> Jack Leach
> >> www.tristatemachine.com
> >>
> >> - "First, get your information. Then, you can distort it at your
> >> leisure."
> >> - Mark Twain
> >>
> >>
> >> "Claire" wrote:
> >>
> >> > Thanks very much for your help! There's one more element I'm unclear
> >> > on.
> >> > How do I refer to another table that is related to the first? I'm
> >> > going to
> >> > be utilizing at least three tables: One with hour information, one with
> >> > information about the employees, and one where the new data is going to
> >> > be
> >> > added. I'm quite comfortable with the SQL aspects, but how do you join
> >> > tables (or do you do something else?) is VBA?
> >> >
> >> >
> >> >
> >> > "dymondjack" wrote:
> >> >
> >> > > Hi Claire... welcome aboard
> >> > >
> >> > >
> >> > > There's various different ways to do what you are looking for, and
> >> > > I'll give
> >> > > one example here. It may not always be the *best* way, but a the
> >> > > Recordset
> >> > > object is very universal when working with specific data. It may not
> >> > > be
> >> > > quite as fast as different queries to do the these tasks, but
> >> > > Recordsets have
> >> > > always been 'old reliables' for me, and they have a very broad scope
> >> > > of what
> >> > > can be done with them. You will need to know some basic SQL (Select
> >> > > statements) to pull the data that you want to work with.
> >> > >
> >> > > '===============
> >> > > Sub GetTotal()
> >> > >
> >> > > Dim db As Database 'To reference what db we're in
> >> > > Dim rs as DAO.Recordset 'This holds the records to loop
> >> > > Dim strSQL As String 'Your SQL statement
> >> > > Dim lCount As Long 'This will tally the hours
> >> > >
> >> > > 'Objects like databases & records need to be "set"
> >> > > Set db = CurrentDB
> >> > > Set rs = db.OpenRecordset(strSQL)
> >> > > 'Now rs is equal to your select statement
> >> > >
> >> > > 'Make sure there's at least one record
> >> > > If rs.RecordCount <> 0 Then
> >> > >
> >> > > 'Move to the first record in the set
> >> > > rs.MoveFirst
> >> > >
> >> > > 'Loop the recordset until it hits the end
> >> > > While rs.EOF = False
> >> > > 'Add the record's Hrs field value to lCount...
> >> > > lCount = lCount + rs.Fields("Hrs")
> >> > >
> >> > > 'Move to the next record
> >> > > rs.MoveNext
> >> > >
> >> > > Wend
> >> > >
> >> > > End If
> >> > >
> >> > > 'Close the Recordset
> >> > > rs.Close
> >> > >
> >> > > 'Unreference the objects
> >> > > Set rs = Nothing
> >> > > Set db = Nothing
> >> > > '====================
> >> > >
> >> > >
> >> > > There you go...
> >> > >
> >> > > If you want to add a record, use this:
> >> > >
> >> > > With rs
> >> > > .AddNew
> >> > > .Fields("fieldname") = Value
> >> > > .Fields("fieldname") = Value
> >> > > .Update
> >> > > End With
> >> > >
> >> > >
> >> > > Or if you want to edit the current record:
> >> > >
> >> > > With rs
> >> > > .Edit
> >> > > .Fields("fieldname") = Value
> >> > > .Update
> >> > > End With
> >> > >
> >> > >
> >> > > Or delete the current record
> >> > >
> >> > > rs.Delete
> >> > >
> >> > >
> >> > >
> >> > > Anyway, you get the idea. Use the Recordset Object... theres all
> >> > > sorts of
> >> > > more in depth tutorials if you need more detailed info...
> >> > >
> >> > > hth
> >> > >
> >> > > --
> >> > > Jack Leach
> >> > > www.tristatemachine.com
> >> > >
> >> > > - "First, get your information. Then, you can distort it at your
> >> > > leisure."
> >> > > - Mark Twain
> >> > >
> >> > >
> >> > > "Claire" wrote:
> >> > >
> >> > > > I am just starting a foray into VBA, and am stuck right at the
> >> > > > beginning.
> >> > > >
> >> > > > My goal is to total some fields in one table, for each employee,
> >> > > > and to add
> >> > > > these totals to a different table, with some information from
> >> > > > previous
> >> > > > records in the second table incorporated. How can I get access to
> >> > > > select all
> >> > > > of the records in table one, the most recent for each employee in
> >> > > > table two,
> >> > > > and then combine them? (some will write as is, others add, others
> >> > > > subtract,
> >> > > > etc.)
> >> > > >
> >> > > > Is there a way to loop through each record from a select statement?
> >> > > > I'm at
> >> > > > a loss for what the syntax is, or where to find examples of the
> >> > > > syntax.
> >> > > >
> >> > > > If more details would be helpful, here's the basics of the tables:
> >> > > > tblHours
> >> > > > EmployeeID----Date----Hrs----Type
> >> > > > A-----------1/1/09-----6--------Regular
> >> > > > A-----------1/1/09-----2--------Vacation
> >> > > > A-----------1/2/09-----8--------Overtime
> >> > > > B-----------1/1/09-----8--------Regular
> >> > > >
> >> > > > tblEmployees
> >> > > > EmployeeID--------Dept---------VacRate
> >> > > > A-------------------sales-----------2
> >> > > > B-------------------admin----------3
> >> > > >
> >> > > > tblHistory
> >> > > > EmployeeID---Dept---PayrollEnding-------RegularHrs------OvertimeHrs-----VacationHrs------LifetimeHrs------VacationEarned---------VacationBalance
> >> > > > A--------------sales-------1/2/09-------------6------------------8-------------------2------------------150--------------2-----------------------15
> >> > > > B--------------admin------1/2/09-------------8------------------0-------------------0-------------------100-------------1-----------------------12
> >> > > >
> >> > > >
> >> > > > I need to total each employees' hours in tblHours for a week, put
> >> > > > these in
> >> > > > tblHistory fields, add them to LifetimeHrs, multiply some of them