Results to the switchboard

[pgoulet]

I am using this code to get the results of the last time a bar code scan was performed.

SELECT Max([Date]+[timeMS]) AS lastScanEvent, Format$(Now()-Max([Date]+[Timems]),"hh:nn:ss") AS Ago
FROM tbl_FSACDATA;

How can I get these results to display on the switchboard, and continually update? The switchboard and query do not have a common field.

I have tried to use a subform. That did work, but did not update the subform.

I currently have a clock displayed on the switchboard in a label and would like to incorporate something similar to that, or even better into it.
The clock display code is:
Private Sub Form_Timer()
Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
End Sub

Private Sub cmdClockStart_Click()
Me.TimerInterval = 1000
End Sub

Private Sub cmdClockEnd_Click()
Me.TimerInterval = 0
End Sub


Thanks in Advance.

 

[PHV]

I have tried to use a subform. That did work, but did not update the subform.
Have you tried to requery like this ?
Private Sub Form_Timer()
Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
Me!frmSubForm.Form.ReQuery
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cgarts]

One possibility - Put an unbound subform on your switchboard. Feed your subform with a recordsource that brings up your most recent scan time and date, and then keep it up to date by having it requery on the timer event as PHV suggests.

Best

C

 

[pgoulet]

I must not have been clear in my description. The clock portion of the swithboard is working fine. I am looking at how to bring the results of the query to the swithboard, to tell me the time of the last scan.

SELECT Max([Date]+[timeMS]) AS lastScanEvent, Format$(Now()-Max([Date]+[Timems]),"hh:nn:ss") AS Ago
FROM tbl_FSACDATA;

I am avoiding use of the requery of the subform, but I would think that would be more of a distraction "because of the delay in the requery process" than I would be accomplishing.

Paul

 

[PHV]

To speed up the query you may consider to have the scan event as a single DateTime indexed column in your table.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[pgoulet]

I will give it another try and post the results.

 

[cgarts]

Paul,

Maybe I am off track or misunderstanding your needs, but if I understand your question, you want to display the Date/Time of the last time a scan was done, and how long it has been since that time till now, and you want that information updated regularly.

I suspect PHV is right - save your scan Time/Date as simply as possible.

Then, as I suggested before, try a subform that brings up the most recent scan time as it's current record. Your "Ago" would be the difference between the LastScanEvent and now() sgould be your ago.

Again, perhaps I am missing something but I don't believe requerying should be an unacceptable "distraction" unless you have a huge recordset or unless you need the display to update super fast and fractions of a second count big, or unless your db has loggy performance.

Depending upon how long is acceptable or desirable between updates of the "Ago" datum, your form timer driven requery can be more or less frequent.

Perhaps someone else may offer an elegant solution - but what I've noted would be where I'd start.

C

 

[pgoulet]

Thanks for the help. I took the advice and brought in the lastscan time in to a sub form. I created an unbound object (Ago) on the form that pointed to the time of the lastscan (Subform) and calculated the difference between that time and now(). Made the subform invisible and now we know the time of the last scan.

Private Sub Form_Timer()
Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
Me.Ago.Requery
End Sub

Thanks again for the help.