Adding multiple records to a table?

  • Thread starter Thread starter Travis
  • Start date Start date
T

Travis

Hi

On a form i would love a way to have a command button onclick that
would create a record for every employee from the employee table to
the timesheet table and attribute them 8 hours of work for a specific
date on the form where the button is.

The created record would look something like this:

DATE, DEPARTMENT, TEAMLEADER, EMPLOYEE, START TIME, FINISH TIME, TOTAL
HOURS ETC

Obviously the start and finish times could be arbitary.
The department & teamleader are attributed to the employee via the
employee table.

I would use this button when it was a public holiday as everyone gets
paid 8 hours, no sense in filling in the form 80 times with the same
info.

Any help would be appreciated

Thanks
 
try this:


Sub test
dim dtHoliday as date
dim dtStart as date
dim dtEnd as date

dtHoliday = #2004/12/31#

dtStart = #03/10/2004 8:00:00 AM #
dtEnd = #03/10/2004 5:00:00 PM #

if CanDoHoliday(dtholiday,dtstart,dtend)
msgbox "All employes were updated"
else
msgbox "Error trying to update"
end if
End sub

Public function CanDoHoliday(dtHoliday as date,dtStart as date, dtEnd as
date) as boolean
on error goto errorhandler
dim wsp as dao.workspace
dim bolTran as boolean
dim db as dao.database
dim rsSrc as dao.recordset
dim rsDst as dao.recordset

set db = dbengine(0)(0)
set rsSrc = db.openrecordset("tblemployees")
set rsDst = db.openrecordset("tblTimeSheet")

boltran = true
wsp.begintrans

while not rsSrc.eof
rsDst.addnew
rsdst![DATE] = dtHoliday
rsdst![DEPARTMENT] = rsSrc![DEPARTMENT]
rsdst![TEAMLEADER] = rsSrc![TEAMLEADER]
rsdst![EMPLOYEE] = rsSrc![EMPLOYEE]
rsdst![START TIME] = dtStart
rsdst![FINISH TIME] = dtEnd
rsDst.update
rsSrc.movenext
wend
wsp.CommitTrans
boltran=false
set rsDst = nothing
set rsSrc = nothing
set db = nothing
CanDoHoliday = true
exit function

Errorhandler:
if boltran then
wps.Rollback
end if
exit function
end Function

Rodrigo.
 
by the way, all the dates should have been for the same date (2004/12/31),
that was just a typo.

Rodrigo.
Rodrigo said:
try this:


Sub test
dim dtHoliday as date
dim dtStart as date
dim dtEnd as date

dtHoliday = #2004/12/31#

dtStart = #03/10/2004 8:00:00 AM #
dtEnd = #03/10/2004 5:00:00 PM #

if CanDoHoliday(dtholiday,dtstart,dtend)
msgbox "All employes were updated"
else
msgbox "Error trying to update"
end if
End sub

Public function CanDoHoliday(dtHoliday as date,dtStart as date, dtEnd as
date) as boolean
on error goto errorhandler
dim wsp as dao.workspace
dim bolTran as boolean
dim db as dao.database
dim rsSrc as dao.recordset
dim rsDst as dao.recordset

set db = dbengine(0)(0)
set rsSrc = db.openrecordset("tblemployees")
set rsDst = db.openrecordset("tblTimeSheet")

boltran = true
wsp.begintrans

while not rsSrc.eof
rsDst.addnew
rsdst![DATE] = dtHoliday
rsdst![DEPARTMENT] = rsSrc![DEPARTMENT]
rsdst![TEAMLEADER] = rsSrc![TEAMLEADER]
rsdst![EMPLOYEE] = rsSrc![EMPLOYEE]
rsdst![START TIME] = dtStart
rsdst![FINISH TIME] = dtEnd
rsDst.update
rsSrc.movenext
wend
wsp.CommitTrans
boltran=false
set rsDst = nothing
set rsSrc = nothing
set db = nothing
CanDoHoliday = true
exit function

Errorhandler:
if boltran then
wps.Rollback
end if
exit function
end Function

Rodrigo.

Travis said:
Hi

On a form i would love a way to have a command button onclick that
would create a record for every employee from the employee table to
the timesheet table and attribute them 8 hours of work for a specific
date on the form where the button is.

The created record would look something like this:

DATE, DEPARTMENT, TEAMLEADER, EMPLOYEE, START TIME, FINISH TIME, TOTAL
HOURS ETC

Obviously the start and finish times could be arbitary.
The department & teamleader are attributed to the employee via the
employee table.

I would use this button when it was a public holiday as everyone gets
paid 8 hours, no sense in filling in the form 80 times with the same
info.

Any help would be appreciated

Thanks
Click to expand...
Click to expand...
 
Back
Top