Doesn't it occur to you that PersonA might be doing something when PersonB is trying to requery their form? And with three users there is twice the likelihood that you would clobber someone's work.
This is simply not a reasonable process. I gave you a solution that I implemented and which worked fine for EIGHT very busy people but you don't seem to think it works for even two people.
Correct. If I find a way to implement Isaac's suggestion, I think I know how it will be possible to control the form Requery when the form is being used.
Regarding your solution in the description in post #45... If a person is working on the form on a certain PC, other forms are somehow locked until the users finish the session. Is that correct understanding?
Not tried it in your type of environment but if the message 'box' is a single memo/long text field in the back end which users can append to - that should refresh based on the refresh interval set in File>Options>Client Settings.
Put it on your main form as a bound subform. if the data changes, the form control is refreshed immediately but it does not generate an event, so you might still need a timer event to draw the other users attention to it. Store the message in a form public variable in the timer event (perhaps 30 secs), compare with the current message and if different draw the users attention however you see fit (msgbox, flashing banner, whatever). If the user has noticed the update, they could simply click on the memo control as acknowledgement which would update the form public variable.
Pat agrees with me (post #33) about requerying the form. Use a user generated event which is outside of data entry/editing (other than the current event perhaps). Could be a mouse move event for example (even the click event on the message box above)- again the code would check if anything has changed before requerying.
Thanks Isaac! It would be great to create what you suggested. Please look at my response.
The flickering is solved as described in post #37, but the bad side of using the timer is a setback. Never used Tableau. Not sure how difficult would be to learn Tableau from scratch.
Sounds very interesting. Tried to make a non-stop loop in the subform containing the tasks, but it did not work. I guess it needs to be done in a way different from what I understand.
I made it like this:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim varNumber as Long
varNumber = 0
Do While varNumber = 1
me.Requery
Loop
End Sub
What should be done differently to make this work? I would be very happy to test this suggestion.
Hi @Acke
An infinite loop would be more along the lines of "Do Until 1=2" or something like that. I use them any time I want a script to run indefinitely. (Not referring to Access).
Attached is a zip file containing 2 extremely simple/rough files just to show what I mean. After unzipping them & saving them both somewhere, then change .txt to .vbs, if you open the database first, then open the Form. Then double click the VBS file to start running it. Every 5 seconds it will refresh the db form. If at any time it either can't find Access running or the form not displayed, it will display error and quit. All it does is a re-query, (which messes up which record you're working on on the form), but of course, this is just to demonstrate the concept that an external script can run invisibly and continuously and do something to an Access form...How exactly you want to resolve doing something to the user's Form while not negatively impacting their work is another story, but this script could be tweaked to do any number of different things just like Access VBA can.
What I think is "nifty" to have this in one's toolbelt is that you can run continuous code WITHOUT actually locking up Access VBA, which of course is what happens if you run a continuous loop inside Access vba. (Except for, notably, the Timer event).
Of course you can always adjust the script to stop running on some other cue, or to keep running but only try to do its thing to the form on certain conditions (like if it finds the form loaded), who knows, whatever you want.
PS .. it's surprisingly easy to learn Tableau from scratch. It's based on the premise of being a very dumbed-down, no-code drag and drop platform. Still, of course, there is simplistic usage and then more advanced usage that requires some study, but the internet is saturated with enthusiasm, support & examples. However, the license is not cheap. A big advantage it has? Hosting reports & dashboards online on Tableau Public site, for free.
Last note, Pat said something that sounded similar to an idea I often suggest to people--the simple notion of making your application involve features for assigning work and working queues. This is one of those things that your client/business partners may not actually suggest or require it, you have to suggest it to them. They're doubtless already wondering "how will we separate and assign work in this new tool?"...I usually just let them know, Hey, what we really need to do is have the database allow people to select work (in batches, even), and "assign" or "reassign" them to a user. So that work is always separated, always assigned, never conflicting. Suggesting it in contexts that seem reasonable, I've never had a response other than total enthusiasm for the idea, and away we went. Just my experience.
Hi @Acke
An infinite loop would be more along the lines of "Do Until 1=2" or something like that. I use them any time I want a script to run indefinitely. (Not referring to Access).
Attached is a zip file containing 2 extremely simple/rough files...
Last note, Pat said something that sounded similar to an idea I often suggest to people--the simple notion of making your application involve features for assigning work and working queues. This is one of those things that your client/business partners may not actually suggest or require it, you have to suggest it to them. They're doubtless already wondering "how will we separate and assign work in this new tool?"...I usually just let them know, Hey, what we really need to do is have the database allow people to select work (in batches, even), and "assign" or "reassign" them to a user. So that work is always separated, always assigned, never conflicting. Suggesting it in contexts that seem reasonable, I've never had a response other than total enthusiasm for the idea, and away we went. Just my experience.
The option to assign a task to a person is already available. There are options per person, person+person as a team and everybody (includes all the people). It that is what you meant...
I've described two solutions I created for what I think are similar applications. You don't like either of them. Maybe someday you will figure out how to get your code to somehow run in memory on some number of other computers. Good luck.
On the contrary. I am interested in your solution, but don't understand it from the description in your post #45. Once I resolve the issue of refreshing the form, I will have to deal with the issue of overlapping automatic requery with entering the task. It would be great if you would explain how you solved this issue. Again, if that is what you meant...
As mentioned, English is not my first language and computer English is an additional difficulty. Frases you guys use and understand on the spot, I need to read several times before I understand fully what you were trying to say.
Awesome. I should have mentioned, that's another wonderful aspect of the whole thing...not only can vba easily start a vbs file, it can create the file too (by the same methods I'd use to create any text file - scripting filesystemobject + textstream object) and save it. Nicely encapsulated.
The option to assign a task to a person is already available. There are options per person, person+person as a team and everybody (includes all the people). It that is what you meant...
Awesome. I should have mentioned, that's another wonderful aspect of the whole thing...not only can vba easily start a vbs file, it can create the file too (by the same methods I'd use to create any text file - scripting filesystemobject + textstream object) and save it. Nicely encapsulated.
Thanks Isaac for pointing out the VBScript option. I was not aver it existed. It is a great trick. Opens new possibilities.
Using the VBScript file implies having another file to worry about. As the simplest solutions are the best, I tried to avoid having the VBScript file and to use form's VBA to call the procedure you kindly suggested. It worked well on the button event, mouse move event, but not on the Form Open event. And I need it to run on Form Open event.
It looks as if there is no other option but the script if I want to use your loop trick on form open event. Is this correct?
I understand that English is not your first language, so I will try to make this clear. Please review your answers in your post #41 of this thread.
Your answers to question #1 and question #9 simply cannot both be correct. If you have only two PCs, you cannot enter data fast enough. Or at least that is how it appears from my side of the screen. With only two people doing data entry, nobody will have a backlog. Therefore, I continue to feel that some information is missing. I am not accusing you of any kind of lying. But I continue to believe we have mis-communicated.
@Pat Hartman - regarding messaging apps, which you mentioned in passing, they usually work based on using WinSock methods to establish a network channel to a chat partner (or chat room). Their events are network I/O completion events, usually. They definitely are NOT using SMB in the "file" sense - though I believe if you can specify the intended recipient as a UNC network device, SMB WILL act as a file-oriented comm link, where the unit of transmission is a text line, with or without attachments.
Sorry, Isaac. I was testing your solution in the time gaps during the workday with lots of distractions. When writing the post #55 I was under wrong impression that the code worked if called from the VBS but not if from the VBA. The code is not working regardels if it is called from the VBS or from the VBA. Therefore, please discard the post #55.
The description:
If on open, load, resize, activate, current, the form does not open.
Open event - form doesn't open at all. Have to reset the code (hitting the square buton in the VBA of the form) to reclame control of the access.
Load event - the form opens only after I reset the code. After reset, the form opens and runs the code well.
Resize event - the same as on Load event, only all happens quicker.
Activate - when Pop up and Modal are "yes", the form opens but the code doesn't run. When Pop and modal are set to "no", the form acts the same as in case of Load or Resize.
Current - the same as on Load..., but canceling the code is messier.
The above is for the VBS. The loop never runs if called from the VBA in cases of open, load... If the code is called from the VBA, the form opens after reset, but the loop doesn't run.
If the code is called any other way (button click, mouse move...) it works perfectly. That is for both VBA and VBS.
To start the VBS I use:
Code:
Option Compare Database
Option Explicit
Const PROCESS_QUERY_INFORMATION = &H400
Const SYNCHRONIZE = &H100000
Const INFINITE = &HFFFFFFFF
Const STILL_ACTIVE = &H103&
Const glrcErrFileNotFound = 53
Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Sub RunApp(strCommand As String, intMode As VbAppWinStyle)
' Run an application, returning immediately to
' the caller. Just presented as a parallel for
' ahtRunAppWait
Dim hInstance As Long
On Error GoTo ahtRunApp_Err
hInstance = Shell(strCommand, intMode)
ahtRunApp_Exit:
Exit Sub
ahtRunApp_Err:
Select Case Err.Number
Case glrcErrFileNotFound
MsgBox "Unable to find '" & strCommand & "'"
Case Else
MsgBox Err.Description
End Select
Resume ahtRunApp_Exit
End Sub
Sub RunAppWait(strCommand As String, intMode As VbAppWinStyle)
' Run an application, waiting for its completion
' before returning to the caller.
Dim hInstance As Long
Dim hProcess As Long
Dim lngRetval As Long
Dim lngExitCode As Long
On Error GoTo ahtRunAppWait_Err
' Start up the application.
hInstance = Shell(strCommand, intMode)
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION Or SYNCHRONIZE, _
True, hInstance)
Do
' Attempt to retrieve the exit code, which will
' not exist until the application has quit.
lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
DoEvents
Loop Until lngExitCode <> STILL_ACTIVE
ahtRunAppWait_Exit:
Exit Sub
ahtRunAppWait_Err:
Select Case Err.Number
Case glrcErrFileNotFound
MsgBox "Unable to find '" & strCommand & "'"
Case Else
MsgBox Err.Description
End Select
Resume ahtRunAppWait_Exit
End Sub
To initiate it:
Code:
RunAppWait "cscript ""D:\DATA\Access\Test Refresh Form in Loop\Test Refresh Form in Loop.vbs""", vbHide
Is there a way to initate the loop with any event on the form start (Open, Load, Resize, Activate, Current)?
I understand that English is not your first language, so I will try to make this clear. Please review your answers in your post #41 of this thread.
Your answers to question #1 and question #9 simply cannot both be correct. If you have only two PCs, you cannot enter data fast enough. Or at least that is how it appears from my side of the screen. With only two people doing data entry, nobody will have a backlog. Therefore, I continue to feel that some information is missing. I am not accusing you of any kind of lying. But I continue to believe we have mis-communicated.
It is great that you are not accusing me of lying. As much as the support I am receiving on this forum is valuable, nothing is worth taking the offense. It is great that we can run the civilized conversation. I agree that there is a lot of misunderstanding.
If by "backlog" (one of the words I am not sure I understand fully) you refer to delay in the appearance of information between PCs, then, as mentioned in the answer to question #9, the delay is insignificant in the case of tasks. We can wait even a full minute before the task appears on the other computer. The speed of refreshments will be important later, with the chat. With the chat, the refreshments need to be instant if you want to run a meaningful conversation.
Further, we are using a timer now for the tasks. The speed of refreshment is more than satisfactory with the timer. I want to replace the timer, not because of speed, but due to other setbacks this function implies, as explained in previous posts.
I hope I understood your comments well this time and that the answer is appropriate. If not, please rephrase the question and I will be happy to respond.
@Pat Hartman - regarding messaging apps, which you mentioned in passing, they usually work based on using WinSock methods to establish a network channel to a chat partner (or chat room). Their events are network I/O completion events, usually. They definitely are NOT using SMB in the "file" sense - though I believe if you can specify the intended recipient as a UNC network device, SMB WILL act as a file-oriented comm link, where the unit of transmission is a text line, with or without attachments.
The normal way chat programs operate like this involves use of a Windows network socket (commonly named as "Winsock" when you see it in articles on the web). I don't know how far you want to go with this, but here is the problem you face.
As you say, that task list can be updated / requeried / refreshed fairly slowly with no harm to your task management procedures. However, a chat application is not nearly so forgiving with regard to speed. And there is your issue. IF you want to write this using all Access / VBA then you need events to drive things - and the Access environment will not provide you with any. You must take action yourself to DO something that will then trigger a usable sequence of events.
Remember that we are not writing a MAIN module when we write event code. We are writing subroutines for which Access itself is the MAIN module. We cannot see the internals of Access code so we cannot tell exactly how to get Access to cooperate - if we ever could anyway.
Most Access events are RESPONSIVE events, not spontaneous events. Timers are the exception. You want to avoid timers. The sad truth is that without them, in an otherwise quiet system where you are waiting for action, there will be no usable events AT ALL until someone touches a mouse or keyboard to DO something. Access is PASSIVE except for the timer. It is the nature of that interface.
We have had a few cases where someone struggled to "split" the attention of Access to do multiple things at once, but another sad truth for your project is that the Access front-end and back-end are single-threaded. Yes, multiple events can theoretically fire - but Access uses event queues to process your events in linear order because, so far as we know, there is only a single thread for the code processor that executes VBA for you. In practical terms, this means that if you are updating a form, you are not (in the same app at the same time) updating a chat window. This event behavior is often described as "linearized." Due to the Gigahertz speed of computers, it all LOOKS like it is simultaneous - but it is not.
If you wrote code that could listen for a Winsock connection, that code would have to enter an I/O wait state inside your code, which would then lock up that part of the code from ever doing anything else until you get a connection. That is because Access won't listen for you. YOU have to set up your listener to passively wait for a message. This listener with a pending socket READ operation might even prevent you from shutting down your PC - which happens if any I/O device still has pending requests.
That chat is a tricky piece of code. This is why I suggested earlier that you might wish to seek a separate commercial chat app. Not because I doubt your tenacity or skills - but because in engineering terms, this is an expensive bit of work. Time is money and this would be a very difficult app to get right if you limit yourself to Access.
Sorry, Isaac. I was testing your solution in the time gaps during the workday with lots of distractions. When writing the post #55 I was under wrong impression that the code worked if called from the VBS but not if from the VBA. The code is not working regardels if it is called from the VBS or from the VBA. Therefore, please discard the post #55.
The description:
If on open, load, resize, activate, current, the form does not open.
Open event - form doesn't open at all. Have to reset the code (hitting the square buton in the VBA of the form) to reclame control of the access.
Load event - the form opens only after I reset the code. After reset, the form opens and runs the code well.
Resize event - the same as on Load event, only all happens quicker.
Activate - when Pop up and Modal are "yes", the form opens but the code doesn't run. When Pop and modal are set to "no", the form acts the same as in case of Load or Resize.
Current - the same as on Load..., but canceling the code is messier.
The above is for the VBS. The loop never runs if called from the VBA in cases of open, load... If the code is called from the VBA, the form opens after reset, but the loop doesn't run.
If the code is called any other way (button click, mouse move...) it works perfectly. That is for both VBA and VBS.
To start the VBS I use:
Code:
Option Compare Database
Option Explicit
Const PROCESS_QUERY_INFORMATION = &H400
Const SYNCHRONIZE = &H100000
Const INFINITE = &HFFFFFFFF
Const STILL_ACTIVE = &H103&
Const glrcErrFileNotFound = 53
Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, lpExitCode As Long) As Long
Sub RunApp(strCommand As String, intMode As VbAppWinStyle)
' Run an application, returning immediately to
' the caller. Just presented as a parallel for
' ahtRunAppWait
Dim hInstance As Long
On Error GoTo ahtRunApp_Err
hInstance = Shell(strCommand, intMode)
ahtRunApp_Exit:
Exit Sub
ahtRunApp_Err:
Select Case Err.Number
Case glrcErrFileNotFound
MsgBox "Unable to find '" & strCommand & "'"
Case Else
MsgBox Err.Description
End Select
Resume ahtRunApp_Exit
End Sub
Sub RunAppWait(strCommand As String, intMode As VbAppWinStyle)
' Run an application, waiting for its completion
' before returning to the caller.
Dim hInstance As Long
Dim hProcess As Long
Dim lngRetval As Long
Dim lngExitCode As Long
On Error GoTo ahtRunAppWait_Err
' Start up the application.
hInstance = Shell(strCommand, intMode)
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION Or SYNCHRONIZE, _
True, hInstance)
Do
' Attempt to retrieve the exit code, which will
' not exist until the application has quit.
lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
DoEvents
Loop Until lngExitCode <> STILL_ACTIVE
ahtRunAppWait_Exit:
Exit Sub
ahtRunAppWait_Err:
Select Case Err.Number
Case glrcErrFileNotFound
MsgBox "Unable to find '" & strCommand & "'"
Case Else
MsgBox Err.Description
End Select
Resume ahtRunAppWait_Exit
End Sub
To initiate it:
Code:
RunAppWait "cscript ""D:\DATA\Access\Test Refresh Form in Loop\Test Refresh Form in Loop.vbs""", vbHide
Is there a way to initate the loop with any event on the form start (Open, Load, Resize, Activate, Current)?
Sorry, but I don't really understand what you mean about the Form "not opening". I thought you already had a working Access database and forms opened and worked fine. Not familiar with the code you're using. My advice would be to skip all the fancy event-driven ways to initiate the VBS and rather just call it in an extremely simple way, like "Shell "wscript " & strScriptPath, vbNormalFocus" (one line of code), when the database first opens. Then add some conditional stuff to the VBS loop to test and see if a certain form is loaded or not - whenever it is, act on it. Run it continuously.
The normal way chat programs operate like this involves use of a Windows network socket ... might even prevent you from shutting down your PC - which happens if any I/O device still has pending requests.
... Not because I doubt your tenacity or skills - but because in engineering terms, this is an expensive bit of work. Time is money and this would be a very difficult app to get right if you limit yourself to Access.
That is exactly why I am hoping to get Isaac's loop working. The timer is doing the job already, Loop can improve things if we get it to work, and later, when I am free I will look into what you above described. Time is money as you correctly noticed, but programming is not my profession. I like it, looking at it as a riddle and am doing it in my free time and feel for it as a kind of fun.
The way I imagined to build the chat was again related to the form refreshing (reqeury). Chat would be for two people initially. Create a table with columns: ID, DateTimeStamp, Person1Comments, Person2Comments. A main form where the chat will be entered, filtered... with continuous subform containing comments and sorted by the timestamp. The table stored at the backend file, form at two separate frontend files. A person 1 types a message on the form on the PC1 and saves it to the table at the beFile as new data. As soon as a form is refreshed at PC2's feFile, a person 2 can see the message and vice versa. All I need to make this work is to refresh the form when needed. If no better solution, the timer should do the trick.
Sorry, but I don't really understand what you mean about the Form "not opening". I thought you already had a working Access database and forms opened and worked fine. Not familiar with the code you're using. My advice would be to skip all the fancy event-driven ways to initiate the VBS and rather just call it in an extremely simple way, like "Shell "wscript " & strScriptPath, vbNormalFocus" (one line of code), when the database first opens. Then add some conditional stuff to the VBS loop to test and see if a certain form is loaded or not - whenever it is, act on it. Run it continuously.
I had your sample working the way you suggested. Open the access file, open the form, and then run the VBS by double-clicking on the VBS file. By using the only command I could find online to call the VBS (as I have no idea how to write a code to call the VBS), I managed to improve it from double-clicking the VBS file to calling it from VBA. When I tried to call the VBS from form open event, the form could not open. That is what I meant.
I will change and call the VBS when the db first opens.
Would you mind writing the simple code to call the VBS?
Command line to Requery the form is in the VBS is:
AccessApp.Forms("FormName").Requery
As I need to refresh the subform, what would be the line in this case?
Written as above the script cannot find the subform.
In the VBA I would write Forms!MainFormName!SubFormName.Form.Requery. This is not working with the VBS.
Command line to Requery the form is in the VBS is:
AccessApp.Forms("FormName").Requery
As I need to refresh the subform, what would be the line in this case?
Written as above the script cannot find the subform.
In the VBA I would write Forms!MainFormName!SubFormName.Form.Requery. This is not working with the VBS.
Good that you asked. It cannot actually find even the main form. I have several access files opened and that is a problem.
I think I should define the access file I want to address to in the line; setAccApp = GetObject(, "Access.Application"). How should I list it? The full path and file name under the ""?
This is the code:
Code:
dim AccessApp
on error resume next
set AccessApp = Getobject(, "Access.Application")
if err.number<>0 then
msgbox "Auto Refresh tool unable to find Access database - quitting script"
wscript.quit
end if
wscript.sleep 5000 'wait 5 seconds
do until 1=2
AccessApp.Forms("Form1").Requery
if err.number<>0 then
msgbox "Auto Refresh tool unable to find or refresh Access form 'Form1' - quitting script"
wscript.quit
end if
wscript.sleep 5000 'wait 5 seconds
loop
Good that you asked. It cannot actually find even the main form. I have several access files opened and that is a problem.
I think I should define the access file I want to address to in the line; setAccApp = GetObject(, "Access.Application"). How should I list it? The full path and file name under the ""?
This is the code:
Code:
dim AccessApp
on error resume next
set AccessApp = Getobject(, "Access.Application")
if err.number<>0 then
msgbox "Auto Refresh tool unable to find Access database - quitting script"
wscript.quit
end if
wscript.sleep 5000 'wait 5 seconds
do until 1=2
AccessApp.Forms("Form1").Requery
if err.number<>0 then
msgbox "Auto Refresh tool unable to find or refresh Access form 'Form1' - quitting script"
wscript.quit
end if
wscript.sleep 5000 'wait 5 seconds
loop
