Cycle of records

  • Thread starter Edan Mimran via AccessMonster.com
  • Start date
E

Edan Mimran via AccessMonster.com

i have a form that should add a note to a table for each Employee that was
marked.

The code look like this:

Dim I As Variant
DoCmd.GoToRecord , , acFirst
For I = 1 To Form.RecordsetClone.RecordCount
If [CheckWillPrint] = True Then
Call AddNoteToEmployee
[CheckWillPrint] = False
End If
If Me.Recordset.AbsolutePosition + 1 < Me.Recordset.RecordCount Then
DoCmd.GoToRecord , , acNext
End If
Next I


Public Function AddNoteToEmployee()
'Add Note to Employee History
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Employyes-ExtraHours")
rst.AddNew
rst![ID] = Forms![Employees_MultiDeduct_Main]![ID]
rst![WorkDate] = Forms![Employees_MultiDeduct_Main]![Whatdate]

rst![WorkedWhere] = Forms![Employees_MultiDeduct_Main]!
[AdditionDescription]
rst![TotalHours] = Forms![Employees_MultiDeduct_Main]![TotalHours]
rst![PricePerHour] = Forms![Employees_MultiDeduct_Main]![StartSalary1]
/ 40 * Forms![Employees_MultiDeduct_Main]![TotalHours]
rst![HourlyPresantage] = "100"
rst![PricePerHourPaid] = Forms![Employees_MultiDeduct_Main]!
[StartSalary1] / 40 * Forms![Employees_MultiDeduct_Main]![TotalHours]
rst![WasPaid] = False
rst![CreatedOn] = Now()
rst![CreatedBy] = CurrentUser()

rst.Update
rst.Close
dbs.Close
End Function


the problem is:
When i compile the Database to an MDE file, The form doesn't cycle all the
records! its check only the first one. But when i run the database as MDB -
its working fine.

can someone help me with this?


thanks.
 
R

RuralGuy

From VBA Help:

The RecordCount property doesn't indicate how many records are contained in a
dynaset-, snapshot-, or forward-only–type Recordset object until all records
have been accessed. Once the last record has been accessed, the RecordCount
property indicates the total number of undeleted records in the Recordset or
TableDef object. To force the last record to be accessed, use the MoveLast
method on the Recordset object.

I would stick with just the RecordSetClone.RecordCount and start by making sure
it is accurate with the MoveLast then MoveFirst method.


i have a form that should add a note to a table for each Employee that was
marked.

The code look like this:

Dim I As Variant
DoCmd.GoToRecord , , acFirst
For I = 1 To Form.RecordsetClone.RecordCount
If [CheckWillPrint] = True Then
Call AddNoteToEmployee
[CheckWillPrint] = False
End If
If Me.Recordset.AbsolutePosition + 1 < Me.Recordset.RecordCount Then
DoCmd.GoToRecord , , acNext
End If
Next I


Public Function AddNoteToEmployee()
'Add Note to Employee History
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Employyes-ExtraHours")
rst.AddNew
rst![ID] = Forms![Employees_MultiDeduct_Main]![ID]
rst![WorkDate] = Forms![Employees_MultiDeduct_Main]![Whatdate]

rst![WorkedWhere] = Forms![Employees_MultiDeduct_Main]!
[AdditionDescription]
rst![TotalHours] = Forms![Employees_MultiDeduct_Main]![TotalHours]
rst![PricePerHour] = Forms![Employees_MultiDeduct_Main]![StartSalary1]
/ 40 * Forms![Employees_MultiDeduct_Main]![TotalHours]
rst![HourlyPresantage] = "100"
rst![PricePerHourPaid] = Forms![Employees_MultiDeduct_Main]!
[StartSalary1] / 40 * Forms![Employees_MultiDeduct_Main]![TotalHours]
rst![WasPaid] = False
rst![CreatedOn] = Now()
rst![CreatedBy] = CurrentUser()

rst.Update
rst.Close
dbs.Close
End Function


the problem is:
When i compile the Database to an MDE file, The form doesn't cycle all the
records! its check only the first one. But when i run the database as MDB -
its working fine.

can someone help me with this?


thanks.
Click to expand...

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
J

John Vinson

i have a form that should add a note to a table for each Employee that was
marked.
Click to expand...

Ummm...

You're going to a LOT of extra work to do what could be done (if I'm
reading the code aright) in a single, simple Append query, referencing
the form for parameters.

That said, rather than looping up to the RecordsetCount, I'd use the
loop syntax

rs.MoveFirst
Do Until rs.EOF
<your append code>
rs.MoveNext
Loop

This will traverse all the records without your needing to get the
count at all.

John W. Vinson[MVP]
 
E

Edan Mimran via AccessMonster.com

thanks for the info...

i tried your solution but it doesn't work!


here is my another code:


Dim dbs As Database
Dim rs As Recordset
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("SELECT Employees.JobSubWork, Employees.
FullName, Employees.ID, Employees.StartSalary1, Employees.Position, Employees.
CheckWillPrint FROM Employees WHERE (((Employees.Termination) = False)) ORDER
BY Employees.JobSubWork, Employees.FullName; ")

rs.MoveFirst
Do Until rs.EOF

if [CheckWillPrint] = true then call AddNotetoEmployee
rs.MoveNext
Loop
 
E

Edan Mimran via AccessMonster.com

thanks for the info...

i tried your solution but it doesn't work!


here is my another code:


Dim dbs As Database
Dim rs As Recordset
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("SELECT Employees.JobSubWork, Employees.
FullName, Employees.ID, Employees.StartSalary1, Employees.Position, Employees.
CheckWillPrint FROM Employees WHERE (((Employees.Termination) = False)) ORDER
BY Employees.JobSubWork, Employees.FullName; ")

rs.MoveFirst
Do Until rs.EOF

if [CheckWillPrint] = true then call AddNotetoEmployee
rs.MoveNext

Loop


and it's still not working why is that?
 
J

John Vinson

thanks for the info...

i tried your solution but it doesn't work!


here is my another code:


Dim dbs As Database
Dim rs As Recordset
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("SELECT Employees.JobSubWork, Employees.
FullName, Employees.ID, Employees.StartSalary1, Employees.Position, Employees.
CheckWillPrint FROM Employees WHERE (((Employees.Termination) = False)) ORDER
BY Employees.JobSubWork, Employees.FullName; ")

rs.MoveFirst
Do Until rs.EOF

if [CheckWillPrint] = true then call AddNotetoEmployee
rs.MoveNext
Loop
Click to expand...

Make it rs![CheckWillPrint] and you should be OK. You're using a
recordset; but unless you tell Access that [CheckWillPrint] is part of
the process, you don't get anywhere.

Question: why not just add AND [CheckWillPrint] = True as an
additional criterion? And even more pertinent question: WHY are you
using VBA code to run through a recordset step by step, when (as noted
previously) a single, simple Append query will do the same job?

John W. Vinson[MVP]
 
E

Edan Mimran via AccessMonster.com

Mr. John W. Vinson

I want to thank you so much.

it is working very good.
 
Top