Is it possible to requery form on another computer?

[robsworld78]

Hi, I'm making a db that has 2 frontends on different computers, is it possible that after a form closes in one frontend it updates an open form on the other front end which is on a different computer. The computers are networked together.



This is exactly what I want to do.

When the user on computer1 opens a form he enters data in it, then closes the form. In the "OnClose" event of that form I want code that will requery a form that will be open on computer2's frontend.

Thanks

[June7]

I say no. It is probably technically not possible and then it is just rude. The requery could disrupt user #2 work.

[robsworld78]

Yeah I know if it could be done it wouldn't easy code to write. This isn't rude, user #2 wants that to happen so he has less work and knows whats going on.

I wanted to do it so the left hand knows what the right hand is doing, kinda thing.

[June7]

Guess I am wrong about the technicalities. This thread says it is possible to open form of another project - if you can open the form should be able to requery it - but I can't get the code to work http://www.pcreview.co.uk/forums/ope...-t3999128.html

[robsworld78]

I always like to think anything can be done, especially with computer code. Thanks for that, I'll check it out and see if I can do anything, if I do I'll post.

Any other ideas would be nice to hear.

[R_Badger]

I have an idea

My idea is thus, rather than looking an update with an onclose event, you could have a slightly different method although convoluted of achieving more or less the same result.

On the form (referred to as frm_1) you put a textbox(txt_loadcount) visible=no (unless you want to see it) with its control source set to =dcount("[primary_key]","[tbl_1]")

This should then give you your total entries for tbl_1 (add criteria here if needs be).

You can then utilise the on_timer event, set the timer interval on the form to 60000 (approx 1 minute if memory serves)

Private Sub Form_Timer()
dim newcount as integer
newcount=dcount("[primary_key]","[tbl_1]")
if newcount>[txt_loadcount] then
me.requery
docmd.openform "Frm_2"
endif
End Sub

Another option is a manual update by simply adding a command button and the code.

Private Sub_command0_Click()
me.requery
End Sub

[robsworld78]

I don't think that will work, I want to requery a from in one database from another database. The form that the requery runs for is already open. I know I can add a button however I don't want that because if the user doesn't/forgets to click it they don't get the updated data to work with so they could be working with old data. If I can get this requery to work then its automatic and the user always has the correct data.

I tried the code June7 linked to, it works however it opens the database and form everytime the button is clicked. The db and form will already be open. Here's the code that opens it.

Public Sub OpenForeignForm()
Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\Fodler\file.accdb"
appAccess.DoCmd.OpenForm "DashboardInventory"
Set appAccess = Nothing
End Sub

[June7]

Next thought is to have a public procedure (Sub or Function) in db2 that has the Requery code. Then db1 calls the proc as described in http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

So here is my discovery.

I tested with a message box in the called procedure and it works.

However, requery code in called procedure fails. The Run line gives error message 'You made an illegal function call'.

[R_Badger]

The dcount method I suggested earlier should work. I understand you are using separate frontends, whether they are different or the same is not important for this to work.

Reason being, although they are different frontends (i.e individual files), they must share at least one table, otherwise you wouldn't want to force a requery.

The [txt_loadcount] would display the number the last time it was loaded/requeried. The newcount variable will get the latest saved amount of data from the table therefore it will not count entries that are not complete. therefore a new

This means by using the ontimer event you can automatically check whether the requery is required, as often as you like (I suggested 1 minute as ontimer events that run too often tend to cause slowdown) so therefore you could at worst case be working with data that is 59 seconds old, but as i said you can always reduce the timer interval.

Albeit that that only works for new entries, there is a method for working with edits too, although it will likely involve adding a field to your table.

You would need a [last_edited] field on your table, with the default value set to =now() on your form.

Then change the [txt_loadcount] default value to
=now()
This then identifies the last time and date the form was requeried.

A minor change to the ontimer_event:

Private Sub Form_Timer() dim newchange as date newchange=dmax("[last_edited]","[tbl_1]") if newcount>[txt_loadcount] then me.requery endif End Sub

And then since the =now() on the form would return the date & time of the change. If the date & time are greater than the date and time stored in [txt_loadcount] (i.e newer) then it would force a requery.

This would also work for new entries for the same reason. The only thing it wouldn't work for is deletions.

If that's a problem, I would simply resort to using the ontimer to call a requery every 60 seconds, although there are other options available for that, I think my wall of text is long enough so far!

[robsworld78]

Ah, that makes sense and smart too except still not quite what I need.

I started adding the field to my table but then it hit me, I'm checking more than one table. Here's my senerio.

I have a dashboard, it has 4 subforms on it showing different data. Each subform has a query as a record source which comes from multiple tables and queries. For that db when the user works the dashboard is always open and the forms open over it, I have them set when they close the 4 subforms on the dashboard requery. That's why I wanted to go with the onclose event on the other database so when the form closes I can set all 4 subforms to requery. The data from the other computers are just edits, no deletions or additions happen there.

I like your idea because it wouldn't matter where the other frontend files sits, if the code goes in the other db it would need a path to the other db so it would always have to be installed on the same network.

[R_Badger]

There is another route to go down then, its a little convoluted however:

create a new table [tbl_lastedit] with 3 fields [id],[lastedit]
Then create an append query [qry_lasteditupdate] you only need only field in it
dt: =now() append to [lastedit]

Set your onclose event (on each of the four forms) to

Private Sub Form_Close()
application.setoption "Confirm Action queries", 0
application.setoption "confirm record changes",0
'disables the warning for action queries so it doesn't bother/scare the user
docmd.openquery "qry_lasteditupdate"
'run the action query to append a new record
application.setoption "Confirm Action queries", 1
application.setoption "confirm record changes", 1
're-enables the warnings
end sub

That will add a new record to the tbl_lastedit every time the form is closed, then you can adjust the previous code to refer to that table with the ontimer event and it should then requery every time an entry is added.

[robsworld78]

I'm getting closer, I have the table and query made and when I close a form I want to monitor it adds a record to the table with a date.

On the other db's frontend I have the text box "txt_loadcount" and this is where I'm confused.

What should I have for the control source, the dcount or dmax? I've tried both but doesn't seem to be working because I don't know what I should be putting there now.

When I have the Dcount as control source it does give me the record count so that works.

For the OnTimer event I have the following.

Dim newchange As Date

newchange = DMax("[LastEdited]", "[tbl_lastedit]")

If newcount > Me.txt_loadcount Then
MsgBox "requery"
Else
MsgBox "no changes"
End If

When the timer code runs it errors on the "newcount" part of the code, it says "variable not defined"?

[R_Badger]

For the [txt_loadcount] you should have =now()
This then shows the time the form was last loaded/requeried (which should be greater than the last edit beforehand)

I corrected the ontimer event

Dim newchange As Date  
newchange = DMax("[LastEdited]", "[tbl_lastedit]") 
 If newchange > Me.txt_loadcount Then 
MsgBox "requery" 
Else 
MsgBox "no changes" 
End If

The variable not defined error is because

If newcount > Me.txt_loadcount Then

should read

If newchange > Me.txt_loadcount Then

[June7]

If no record additions or deletions then can't you just rely on the project Refresh interval? Is once a minute not frequent enough?

[robsworld78]

That works great, thanks so much for your help and sticking with me.

There should be no problem for additions or deletions, because when the form closes it adds a record to the tbl_lastedit table and looks at that. So if I open the form to add, edit or delete it still closes and adds that record to the new table so I can't see it not working.

I love this solution and I'm going to be using it more in my project, the nicest thing is the files can be located anywhere and it'll work.

For me personally one minute is to long, I turned it down to 30 seconds, instant would be ideal.