live update of of report or form

  • Thread starter Thread starter Bobk
  • Start date Start date
B

Bobk

I am attempting to setup a display screen on the production floor of a
manufacturing plant. The screen is intended to display, in real time, the
status of orders being worked on. As orders are completed, employees scan in
order information and report completed production quantities. I want to
immediately display this infromation. I tried to do this by entering the
production information into an Excel spreadsheet and linking the spreadsheet
to Access, but I found that once the spreadsheet is linked the spreadsheet
was locked and would not allow any new data to be entered. Does anyone know
another way to do this?
 
Linked Excel spreadsheets are always read only.
I would suggest an Access form in datasheet view.

Then when production data is entered, requery the form.
It will then show the updated information.

If there are multiple computers showing the data, then for those computers
to show the updated information, you could use the form Timer event and
requery the form when the Timer event fires.
 
After looking more closely at my problem, I think I just need to be able to
update a file linked to Access where I will be able to update a display
generated from a form or report. The data I need to see is initially coming
from a computer on the shop floor. It will periodically produce an updated
text file. Initially we sent the file to Excel, but once linked to Access
we can no longer update it. Is there way to have a file linked to Access
where we can continually update the data externally?
 
Not from a file linked directly to Access.
There is another option you should explore. You can create a query in Excel
that links to your Access data. I don't know enought about coding in Excel
to say whether or how you might be able to have the data refreshed frequently
in all the sheets. You might ask that question in an Excel group.

But, I still think using a datasheet form in Access would be the easiest way
to do it. I personally would not use a flexgrid control because it is an
ActiveX control and I have an aversion to them.

If each computer has an instance of your application running and all are
linked to the same back end mdb, it would not be difficult to keep them all
in sync. As I said before, use the form's Timer event. Set the timer
interval to one second if you want. That would be using a value of 1000.

Now in the timer event, you can requery the form and it will then show all
the records either added or changed by all users in the last second. Here is
an example of how you can do the requery and still stay on the same record
you were on before the requery:

This code assumes you have a field in your table named RecID and it is bound
to a control on the form named txtRecID

Dim lngRecID as Long

lngRecID = Me.txtRecID
Me.Requery
With Me.RecordsetClone
.FindFirst "[RecID] = " & lngRecID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
 
Thanks for the code. I think once I get the external data where Access can
get to it things will work. Getting the data into a file we can update and
Access can read is the problem. I just don't know what that file is. The plan
is to have a single display on the shop floor with data coming from a back
end that resides on a server. I was hoping to just continually update the
back end and run code as you have suggested to update the display.

Klatuu said:
Not from a file linked directly to Access.
There is another option you should explore. You can create a query in Excel
that links to your Access data. I don't know enought about coding in Excel
to say whether or how you might be able to have the data refreshed frequently
in all the sheets. You might ask that question in an Excel group.

But, I still think using a datasheet form in Access would be the easiest way
to do it. I personally would not use a flexgrid control because it is an
ActiveX control and I have an aversion to them.

If each computer has an instance of your application running and all are
linked to the same back end mdb, it would not be difficult to keep them all
in sync. As I said before, use the form's Timer event. Set the timer
interval to one second if you want. That would be using a value of 1000.

Now in the timer event, you can requery the form and it will then show all
the records either added or changed by all users in the last second. Here is
an example of how you can do the requery and still stay on the same record
you were on before the requery:

This code assumes you have a field in your table named RecID and it is bound
to a control on the form named txtRecID

Dim lngRecID as Long

lngRecID = Me.txtRecID
Me.Requery
With Me.RecordsetClone
.FindFirst "[RecID] = " & lngRecID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP


Bobk said:
After looking more closely at my problem, I think I just need to be able to
update a file linked to Access where I will be able to update a display
generated from a form or report. The data I need to see is initially coming
from a computer on the shop floor. It will periodically produce an updated
text file. Initially we sent the file to Excel, but once linked to Access
we can no longer update it. Is there way to have a file linked to Access
where we can continually update the data externally?
Click to expand...
Click to expand...
 
Back
Top