Timer Interval Event Code

[Irshad Alam]

I have a form Named "DispForm1", with One Unbound Text Field. I want to put
code to Form Timer event to collect a Data from a Table "EmpployeeTab", Field
Name "EmpName" and to display on the Form Unbound Text1 field. Again after 10
seconds the next record - Employee name, and so on till it completes all the
records. And last a text message that it has completed.

I tried to do the coding by the help through DAO. But failed.

Please advise code to do this and if possible any sample database to get
this examples

Regards

Irshad

 

[OldPro]

I have a form Named "DispForm1", with One Unbound Text Field. I want to put
code to Form Timer event to collect a Data from a Table "EmpployeeTab", Field
Name "EmpName" and to display on the Form Unbound Text1 field. Again after 10
seconds the next record - Employee name, and so on till it completes all the
records. And last a text message that it has completed.

I tried to do the coding by the help through DAO. But failed.

Please advise code to do this and if possible any sample database to get
this examples

Regards

Irshad

It sounds like you are on the right track. Why did it fail? Did it
stop on some line where it encountered an error, or did it not do what
you expected it to do? When you are in VBA mode, or in other words,
when you have a code window up, select "Debug, Compile all modules"
from the menu. It will show you any syntactical errors.
You are unlikely to find existing code that does exactly what you
want; if you do it probably won't run without modification. In order
to get this to work, you need to learn how to debug software.
Can you show us your code thus far?

 

[Klatuu]

First, you need to establish a recordset object at the form level. The
recordset needs to be opened in the form's Load event and stay open until the
form Unload event. The at the timer interval, advance the current record by
one until you hit EOF.

So set the recordset variable at the top of the form's module just after the
Option statments.

Dim rstEmp as Recordset

Now, in the Form Load event, open it.

Set rstEmp = CurrentDb.OpenRecordset("EmpployeeTab")
If rstEmp.RecordCount = 0 Then
Me.Text1 = "No Records To Process"
Else
rstEmp.MoveLast
rstEmp.MoveFirst
Me.Text1 = rstEmp!EmpName
rstEmp.MoveNext
End If

Now, in the time event:

With rstEmp
If .EOF Then
Me.Text1 = "Completed"
Else
Me.Text1 = rstEmp!EmpName
.MoveNext
End If
End With

BTW, There is no option for Compile All Modules in Access. Maybe in an
older version, but not since 97 as I recall.

 

[OldPro]

BTW, There is no option for Compile All Modules in Access. Maybe in an
older version, but not since 97 as I recall.

That's true. One size does not fit all. I don't know what version he
is using.

 

[Irshad Alam]

Hi,
I tried your following advise, But its failed ..not working.

I put the following code on the FormLoadEvent

Private Sub Form_Load()
Dim rstEmp As Recordset
Set rstEmp = CurrentDb.OpenRecordset("EmpTabA")
' Error 13, And putting yellow on above line
If rstEmp.RecordCount = 0 Then
Me.Text0 = "No Records To Process"
Else
rstEmp.MoveLast
rstEmp.MoveFirst
Me.Text0 = rstEmp!EmpName
rstEmp.MoveNext
End If

End Sub

And on the Timer event I put the following code :

Private Sub Form_Timer()
With rstEmp
If .EOF Then
' Error 424, and the above line in highlighted

Me.Text0 = "Completed"
Else
Me.Text0 = rstEmp!EmpName
..MoveNext
End If
End With

End Sub

And I set the Timer Interval to 1000

BUt it produces error. on the both the above event code.
I am using Access 2000

Please advise

Irshad

 

[OldPro]

Hi,
I tried your following advise, But its failed ..not working.

I put the following code on the FormLoadEvent

Private Sub Form_Load()
Dim rstEmp As Recordset
Set rstEmp = CurrentDb.OpenRecordset("EmpTabA")
' Error 13, And putting yellow on above line
If rstEmp.RecordCount = 0 Then
Me.Text0 = "No Records To Process"
Else
rstEmp.MoveLast
rstEmp.MoveFirst
Me.Text0 = rstEmp!EmpName
rstEmp.MoveNext
End If

End Sub

And on the Timer event I put the following code :

Private Sub Form_Timer()
With rstEmp
If .EOF Then
' Error 424, and the above line in highlighted

Me.Text0 = "Completed"
Else
Me.Text0 = rstEmp!EmpName
.MoveNext
End If
End With

End Sub

And I set the Timer Interval to 1000

BUt it produces error. on the both the above event code.
I am using Access 2000

Please advise

Irshad













- Show quoted text -

Yeah, the openrecordset method takes two parameters; the name of the
table (or sql string) and the type of recordset;
dbOpenTable,dbOpenSnapshot or dbOpenDynaset.

 

[Douglas J. Steele]

Yeah, the openrecordset method takes two parameters; the name of the
table (or sql string) and the type of recordset;
dbOpenTable,dbOpenSnapshot or dbOpenDynaset.

For a DAO recordset, the second paramter is optional.

I suspect the issue is that a recordset object exists in both the DAO and
ADO models, and, unless you disambiguate, Access will choose the first
recordset object it can find, which is usually the ADO one.

Try changing

Dim rstEmp As Recordset

to

Dim rstEmp As DAO.Recordset

 

[Irshad Alam]

As advised I again tried the following code, please check. BUT the problem
now is it only show the first record, It should show the second record value
and keep on going till the end of the record.

Option Compare Database

Private Sub Form_Load()
Dim rstEmp As DAO.Recordset
Set rstEmp = CurrentDb.OpenRecordset("EmpTabA")
' Error 13, And putting yellow on above line
If rstEmp.RecordCount = 0 Then
Me.Text0 = "No Records To Process"
Else
rstEmp.MoveLast
rstEmp.MoveFirst
Me.Text0 = rstEmp!ENamee
rstEmp.MoveNext
End If
End Sub


Private Sub Form_Timer()
Dim rstEmp As DAO.Recordset
Set rstEmp = CurrentDb.OpenRecordset("EmpTabA")
With rstEmp
If .EOF Then
Me.Text0 = "Completed"
Else
Me.Text0 = rstEmp!ENamee
..MoveNext
End If
End With
End Sub


Please correct my code.

Regards.

Irshad

 

[Douglas J. Steele]

As Dave already suggested, you need to declare the recordset at the module
level: in other words, outside of all of the routines in the module.

Option Compare Database

Dim rstEmp As DAO.Recordset

Private Sub Form_Load()
Set rstEmp = CurrentDb.OpenRecordset("EmpTabA")
With rstEmp
.MoveLast
.MoveFirst
If .RecordCount = 0 Then
Me.Text0 = "No Records To Process"
Else
Me.Text0 = !ENamee
.MoveNext
End If
End With
End Sub


Private Sub Form_Timer()
With rstEmp
If .EOF Then
Me.Text0 = "Completed"
Else
Me.Text0 = !ENamee
.MoveNext
End If
End With
End Sub