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
I've never tried to use GetObject in a case where I had to differentiate between multiple instances of an open application, so unsure how to directly solve that, but, I can think of yet another approach you might try.
Have your end user click on the VBScript, and only the VBScript.
Code the VBScript to 1) fire up an Access application itself, 2) open the target database, 3) open the necessary form, and then begin acting on it.
I guess this thread is maybe kind of an example of why when people talk about GetObject vs. CreateObject - and sometimes they're very focused on using something like:
If GetObject returns something then
'use it
Else
'create it using CreateObject
End If
..Whereas I have had sporadic and unreliable results with GetObject, and I recommend just always creating it, if there's no particular reason not to. In your case, this might be the only option.
I'm not sure why I'm still reading this thread. We are 71 posts in and I'm pretty sure that none of the people trying to help have any clue why a two-person application has such timing sensitivity that one PC MUST force the other PC to requery an open form. What BAD thing will happen if PC2 isn't requeried for a minute or two?
I gave you an example of the BAD thing that happened in an eight-person scenario where one person failed to update the white board and how that adversely affected customer relations which prompted the creation of the Access app. What BAD thing happens in your two-person world?
I think we have a winning combination. The loop is working. It is initiated from form timer event, but with the timer running only for a moment at the beginning
The code is simple:
Code:
Private Sub Form_Timer()
Dim varNr As Long
varNr = 1
Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshFormFunction ' sepparate function that refreshes the form and stops the loop if the form is not loaded
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub
Summary...
The task: refresh the information on the continuous subform on a remote computer after new data addition.
Considered solutions:
1. Trigger from PC1 to update remote PC2 and vice versa - The most practical solution as there is no procedure running in the back all the time. Even if possible, it would require serious skills.
2. Timer - a solution with setbacks. During 5 days working with "Timer", we noticed three setbacks. A - Working on the VBA is annoying if the timer form is loaded. B - timer form was interfering with DoCmd.GotoControl command from other forms resulting in bugs. C - application on one of the PCs was unacceptably slow. On the PC1 there are three copies of the same application (copies with different file names) running all the time. When all three task forms on the PC1 are open and running the timer, the application on the PC2 is next to unusable.
3. Loop - To work with the VBA it is necessary only to hit the reset button to stop the procedure. No more bugs with DoCmd.GotoControl procedure. Application on the PC2 works the same regardless of any or all three task forms are opened on the PC1. The forms are being updated almost instantly.
This is the day one. So far neither one setback is noticed with the Loop. I hope it will stay that way.
There was some misunderstanding about the reason the imidate update was a requirement. An instant update of the task is not significant. As mentioned for example in my post #55: " We can wait even a full minute before the task appears on the other computer". A fast form update will be required in the next step of the project with building the chat. I hope this clarifies the speed issue.
I want to thank everybody involved in this thread. Especially @The_Doc_Man for helping me understand access functioning on a deeper level, and also to @Isaac for handing out the solution.
I'm glad you got it working. Sounds like you didn't use the VBS but it was fun and eye opening to see the possibilities.
I'm surprised the in-VBA infinite loop doesn't just freeze everything up and make it impossible to work in the database. Guess I learned something here too! I am a little confused on how what you posted keeps going, since varNr will equal 2 after one round.
I think we have a winning combination. The loop is working. It is initiated from form timer event, but with the timer running only for a moment at the beginning
The code is simple:
Code:
Private Sub Form_Timer()
Dim varNr As Long
varNr = 1
Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshFormFunction ' sepparate function that refreshes the form and stops the loop if the form is not loaded
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub
Summary...
The task: refresh the information on the continuous subform on a remote computer after new data addition.
Considered solutions:
1. Trigger from PC1 to update remote PC2 and vice versa - The most practical solution as there is no procedure running in the back all the time. Even if possible, it would require serious skills.
2. Timer - a solution with setbacks. During 5 days working with "Timer", we noticed three setbacks. A - Working on the VBA is annoying if the timer form is loaded. B - timer form was interfering with DoCmd.GotoControl command from other forms resulting in bugs. C - application on one of the PCs was unacceptably slow. On the PC1 there are three copies of the same application (copies with different file names) running all the time. When all three task forms on the PC1 are open and running the timer, the application on the PC2 is next to unusable.
3. Loop - To work with the VBA it is necessary only to hit the reset button to stop the procedure. No more bugs with DoCmd.GotoControl procedure. Application on the PC2 works the same regardless of any or all three task forms are opened on the PC1. The forms are being updated almost instantly.
This is the day one. So far neither one setback is noticed with the Loop. I hope it will stay that way.
There was some misunderstanding about the reason the imidate update was a requirement. An instant update of the task is not significant. As mentioned for example in my post #55: " We can wait even a full minute before the task appears on the other computer". A fast form update will be required in the next step of the project with building the chat. I hope this clarifies the speed issue.
I want to thank everybody involved in this thread. Especially @The_Doc_Man for helping me understand access functioning on a deeper level, and also to @Isaac for handing out the solution.
I'm glad you got it working. Sounds like you didn't use the VBS but it was fun and eye opening to see the possibilities.
I'm surprised the in-VBA infinite loop doesn't just freeze everything up and make it impossible to work in the database. Guess I learned something here too! I am a little confused on how what you posted keeps going, since varNr will equal 2 after one round.
The VBS is a great trick. Thank you for pointing that out.
The goal was to get the loop going only while the form is open. Any event on form open start was freezing everything up. The only way was to use the timer. The loop in the timer code is being used only to trigger the other loop which is doing the refresh. In response to @theDBguy I will explain everything in detail.
The timer loop needs to have a short pause. Not sure why, but without a slight timer delay it freezes everything up.
There are 2 PCs connected over a network and 5 access files. Let's call access files beDB, feDB1, feDB2, feDB3 and feDB4. "be" as backend, "fe" as frontend. All "fe" files are copies of the same file but with different file names and are all table-linked to the beDB. feDB1 to feDB3 are running on the PC1 on 3 monitors. feDB4 runs on PC2.
The process starts when the user on either PCs, any of the forms, enters a new task or edits/deletes an existing task. When any of this happens, the condition is set for all forms, except the form user is working at, to updateable state. The state condition is being set in a special table. I have a "tblScrapBook" table with several numeric and text fields that are being used for various purposes in the application. The tblScrapBook ID in this case is 11. The fields that store the conditions are nField1 to nField4. Conditions are 0 - not updatable; 1 - updateable.
The code to set the updatable condition:
Code:
Function SetUpdatable()
Dim db as Database
Dim rs as Recordset
Set db = CurrentDB
set rs = db.OpenRecordset("SELECT * FROM tblScrapBook WEHERE ID = 11", dbOpenDynaset)
rs.edit
If Not ComputerName = "PC1" Then 'ComputerName function defines PC's name
'PC2, or any other computer in the future, sets the condition to updatable for all three acc files on the PC1'
rs!nField1 = 1 'sets condition for the form in the file feDB1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
If ComputerName = "PC1" Then
rs!nField4 = 1 ' sets to updatable for the file feDB4 that runns on the PC2'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB1 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB2 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB1'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB3 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
rs.update
rs.close
db.close
Once the condition is set, any form that is open is being refreshed as it is having the following refresh loop running. The refresh loop code:
Code:
Function RefreshForm()
'I was adding the XYZ to the usual naming as the loop will be running almost all the time, to stop interfering with other codes. Not sure if this was necessary, but did it just in case. '
Dim dbxyz as Database
Dim rsxyz as Recordset
Set dbxyz = CurrentDB
Set rsxyz = dbxyz.OpenRecordset("SELECT * FROM tblScrapBook WHERE ID = 11, dbOpenDynaset)
Do Until CurrentProject.AllForms("frmTasks").IsLoaded = False
If ComputerName = "PC1" AND CurrentProject.Name = feDB1 AND rsxyz!nField1 = 1 Then
rsxyz.edit 'the edit/update is listed in the code for each IF to update data in the table only if the condition is met.
'This makes the application run smoother'
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB1'
rsxyz.update
Forms!frmTasks!frmTasksSub.Form.Requery ' refreshes data in the subform'
Forms!frmTasks!frmTasksSub.Form.Refresh ' refreshes the form visually. Task priorities are set as numbers and I am using
conditional formating to present priorities with colors. Without the "Refresh", the numbers
are visible in their fields for some time before the fields show colors again'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB2 AND rsxyz!nField2 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB2'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB3 AND rsxyz!nField3 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB3'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If
If ComputerName = "PC2" AND CurrentProject.Name = feDB4 AND rsxyz!nField4 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB4 on the PC2
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If
Pause (0.1) ' Not sure if time delay here is necessary. Function "Pause" written separately to delay for 0,1 sec.'
Loop
rsxyz.Close
dbrsxyz.Close
End Function
This Loop stops when the form is closed. The Loop is triggered each time any of the forms is being open using the Form's timer event:
Code:
Private Sub Form_Timer()
Dim varNr As Long
varNr = 1
Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshForm
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub
The Timer loop is used to trigger the Refresh loop and to stop the Form Timer running.
That's it. In case of any questions, I will be happy to respond.
There are 2 PCs connected over a network and 5 access files. Let's call access files beDB, feDB1, feDB2, feDB3 and feDB4. "be" as backend, "fe" as frontend. All "fe" files are copies of the same file but with different file names and are all table-linked to the beDB. feDB1 to feDB3 are running on the PC1 on 3 monitors. feDB4 runs on PC2.
The process starts when the user on either PCs, any of the forms, enters a new task or edits/deletes an existing task. When any of this happens, the condition is set for all forms, except the form user is working at, to updateable state. The state condition is being set in a special table. I have a "tblScrapBook" table with several numeric and text fields that are being used for various purposes in the application. The tblScrapBook ID in this case is 11. The fields that store the conditions are nField1 to nField4. Conditions are 0 - not updatable; 1 - updateable.
The code to set the updatable condition:
Code:
Function SetUpdatable()
Dim db as Database
Dim rs as Recordset
Set db = CurrentDB
set rs = db.OpenRecordset("SELECT * FROM tblScrapBook WEHERE ID = 11", dbOpenDynaset)
rs.edit
If Not ComputerName = "PC1" Then 'ComputerName function defines PC's name
'PC2, or any other computer in the future, sets the condition to updatable for all three acc files on the PC1'
rs!nField1 = 1 'sets condition for the form in the file feDB1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
If ComputerName = "PC1" Then
rs!nField4 = 1 ' sets to updatable for the file feDB4 that runns on the PC2'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB1 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB2 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB1'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB3 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1 'sets condition for the form in the file feDB3'
End If
rs.update
rs.close
db.close
Once the condition is set, any form that is open is being refreshed as it is having the following refresh loop running. The refresh loop code:
Code:
Function RefreshForm()
'I was adding the XYZ to the usual naming as the loop will be running almost all the time, to stop interfering with other codes. Not sure if this was necessary, but did it just in case. '
Dim dbxyz as Database
Dim rsxyz as Recordset
Set dbxyz = CurrentDB
Set rsxyz = dbxyz.OpenRecordset("SELECT * FROM tblScrapBook WHERE ID = 11, dbOpenDynaset)
Do Until CurrentProject.AllForms("frmTasks").IsLoaded = False
If ComputerName = "PC1" AND CurrentProject.Name = feDB1 AND rsxyz!nField1 = 1 Then
rsxyz.edit 'the edit/update is listed in the code for each IF to update data in the table only if the condition is met.
'This makes the application run smoother'
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB1'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery ' refreshes data in the subform'
Forms!frmTaks!frmTasksSub.Form.Refresh ' refreshes the form visually. Task priorities are set as numbers and I am using
conditional formating to present priorities with colors. Without the "Refresh", the numbers
are visible in their fields for some time before the fields show colors again'
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB2 AND rsxyz!nField2 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB2'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If
If ComputerName = "PC1" AND CurrentProject.Name = feDB3 AND rsxyz!nField3 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB3'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If
If ComputerName = "PC2" AND CurrentProject.Name = feDB4 AND rsxyz!nField4 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB4 on the PC2
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If
Pause (0.1) ' Not sure if time delay here is necessary. Function "Pause" written separately to delay for 0,1 sec.'
Loop
rsxyz.Close
dbrsxyz.Close
End Function
This Loop stops when the form is closed. The Loop is triggered each time any of the forms is being open using the Form's timer event:
Code:
Private Sub Form_Timer()
Dim varNr As Long
varNr = 1
Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshForm
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub
The Timer loop is used to trigger the Refresh loop and to stop the Form Timer running.
That's it. In case of any questions, I will be happy to respond.