• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Control a from on another Access file, on another PC (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 08:53
Joined
Mar 14, 2017
Messages
2,322
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.
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
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.

Thank you for the comments. It helps in a deeper understanding of the processes, which I as an access enthusiast don't have.

... 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.
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
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?

Thanks.
 

Isaac

Lifelong Learner
Local time
Today, 08:53
Joined
Mar 14, 2017
Messages
2,322
When I tried to call the VBS from form open event, the form could not open. That is what I meant
I see what you mean. Weird .. I suppose the VBS interfered with normal form behavior if form was not already open.

Would you mind writing the simple code to call the VBS?
Code:
dim strScriptPath as string
strScriptPath = "c:\folder\scriptname.vbs"
Shell "wscript " & strScriptPath, vbNormalFocus
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
I see what you mean. Weird .. I suppose the VBS interfered with normal form behavior if form was not already open.


Code:
dim strScriptPath as string
strScriptPath = "c:\folder\scriptname.vbs"
Shell "wscript " & strScriptPath, vbNormalFocus

Thanks.

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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:53
Joined
Oct 29, 2018
Messages
12,373
Thanks.

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.
Hi. Sorry for butting in but what happens if you simply Requery the main form? I am hoping it will indirectly requery the subform too. Just curious...
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
Hi. Sorry for butting in but what happens if you simply Requery the main form? I am hoping it will indirectly requery the subform too. Just curious...

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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:53
Joined
Oct 29, 2018
Messages
12,373
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
Yes, that would be the first thing I would try. So, replace this:
Code:
set AccessApp = Getobject(, "Access.Application")
with something like this instead:
Code:
set AccessApp = Getobject("C:\FolderName\Filename.ACCDB")
Hope that helps...
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
Yes, that would be the first thing I would try. So, replace this:
Code:
set AccessApp = Getobject(, "Access.Application")
with something like this instead:
Code:
set [CODE]AccessApp = Getobject("C:\FolderName\Filename.ACCDB")
[/code]
Hope that helps...

When only one DB is opened and when the code is:
Code:
 set AccessApp = Getobject(, "Access.Application")
it is working well.

In case of
Code:
AccessApp = Getobject("C:\FolderName\Filename.ACCDB")
, it opens the same file again.

We are getting closer, but still no solution.
 

Isaac

Lifelong Learner
Local time
Today, 08:53
Joined
Mar 14, 2017
Messages
2,322
When only one DB is opened and when the code is:
Code:
 set AccessApp = Getobject(, "Access.Application")
it is working well.

In case of
Code:
AccessApp = Getobject("C:\FolderName\Filename.ACCDB")
, it opens the same file again.

We are getting closer, but still no solution.
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:53
Joined
Feb 19, 2002
Messages
29,668
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?
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
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.
 

Isaac

Lifelong Learner
Local time
Today, 08:53
Joined
Mar 14, 2017
Messages
2,322
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.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:53
Joined
Oct 29, 2018
Messages
12,373
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.
Hi. Glad to hear you found a solution. I would like to learn something new as well. Can you share the code for "RefreshFormFunction?" Thanks.
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
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.
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
Hi. Glad to hear you found a solution. I would like to learn something new as well. Can you share the code for "RefreshFormFunction?" Thanks.

Sure. I will explain the full setup.

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.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:53
Joined
Oct 29, 2018
Messages
12,373
Sure. I will explain the full setup.

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.
Hi. Thanks for taking the time to explain. Please let us know how it goes when you get the chat part implemented. Good luck with your project.
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
Hi. Thanks for taking the time to explain. Please let us know how it goes when you get the chat part implemented. Good luck with your project.

Thanks @theDBguy. The skeleton of the chat is already implemented. I will open a new thread for the Chat and will post a link here.
 

Acke

Registered User.
Local time
Today, 17:53
Joined
Jul 1, 2006
Messages
156
The chat project thread is posted. Anybody interested to support the project is more than welcome.
 

Users who are viewing this thread

Top Bottom