Copy the same record to all employees 3

[nja5150]

I've been stuck on this for a week now! I have a form and a subform:

-Mainform-
EmployID(PriKey- from Employees Table)
controlling
-subform-
DayPK(PriKey HIDDEN),DayID(dates), EmployID,Day(names)

This is for a timecard database. I want to be able to add days (records) to each employee. Very similar to adding invoices to employees. I want all the records I create in code to be copied to ALL employees when I push the button. The end result is I can add a whole week of records to each employee for data entry.

The code I've got so far takes the last DayID (Date) entered increases it's value by one day and creates the new record. The next bit of code moves to the next employee and replicates the created record (without increasing the date).

I KNOW you can loop this so it will create new records for each of my employees till the EOF but I can't get the debugger to take it. I thought of using a query to count the number of employees and run the loop x many times but I'm getting an undefined record error.

Please help...first time doing this kind of VB stuff.

Thanks a TON,

Nick

My code follows:

Private Sub AddWeek_Click()


Dim rsta As Recordset
Dim rstb As Recordset
Dim str As String
Dim i As Long
Dim e As Long


Set rsta = CurrentDb.OpenRecordset("Employees", dbOpenDynaset)
Set rstb = CurrentDb.OpenRecordset("Days", dbOpenDynaset)


rstb.MoveLast
i = rstb![DayID] + 1
str = rsta![EmployID]


rstb.AddNew
rstb![DayID] = i
rstb![EmployID] = str
rstb![Day] = "Wednesday"
rstb.Update

rsta.MoveNext
str = rsta![EmployID]
rstb.MoveLast
i = rstb![DayID]

rstb.AddNew
rstb![DayID] = i
rstb![EmployID] = str
rstb![Day] = "Wednesday"
rstb.Update

Me.[Days subform].Requery

End Sub

 

[Rick39]

Try something like this:


Dim rsta As Recordset
Dim rstb As Recordset
Dim str As String
Dim i As Date
Dim e As Long


Set rsta = CurrentDb.OpenRecordset("Employees", dbOpenDynaset)
Set rstb = CurrentDb.OpenRecordset("Days", dbOpenDynaset)

rsta.Movelast
rsta.MoveFirst


rstb.MoveLast
i = rstb![DayID] + 1
For e = 0 to rsta.RecordCount -1
str = rsta![EmployID]


rstb.AddNew
rstb![DayID] = i
rstb![EmployID] = str
rstb![Day] = Format(i, "dddd&quot
rstb.Update
rsta.MoveNext
Next e

 

[nja5150]

Rick39....THANK YOU SOOOOO much worked like a charm. I was able to add code to loop your statement 7 times to complete the week. FANTASTIC!!!

I have alot to learn, let me know if this completed code for the loop is right...it seems to work just fine. If anyone is following this thread the completed code with the loop inserted is as follows:

Private Sub AddWeek_Click()

Dim rsta As Recordset
Dim rstb As Recordset
Dim str As String
Dim i As Date
Dim e As Long
Dim count As Integer

Set rsta = CurrentDb.OpenRecordset("Employees", dbOpenDynaset)
Set rstb = CurrentDb.OpenRecordset("Days", dbOpenDynaset)

For count = 0 To 7 Step 1

rsta.MoveLast
rsta.MoveFirst

rstb.MoveLast
i = rstb![DayID] + 1
For e = 0 To rsta.RecordCount - 1
str = rsta![EmployID]


rstb.AddNew
rstb![DayID] = i
rstb![EmployID] = str
rstb![Day] = Format(i, "dddd&quot
rstb.Update
rsta.MoveNext

Next e
Next count

Me.[Days subform].Requery

End Sub

 

[Rick39]

Looks good....glad it helped

 

[Trendsetter]

Could I just add a finishing touch to this excellent coding - the recordsets have been left open and are still occupying memory.
You need to:
rsta.Close
rstb.close
Set rsta = Nothing
Set rstb = Nothing

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[Rick39]

Good point...my bad

 

[nja5150]

Thanks Frank I was curious about that when I was experimenting and values weren't getting cleared!