Solved Fill form field data from another access database file (1 Viewer)

[Acke]

I need to input data to unbound form field from another Access database file. The event is to be triggered by a button in DB-1 and should input data on the form DB-2.

Basically, when the button is pressed on the form at the DB-1, the unbound field at the DB-2 form should display a certain string.

When the event is triggered by DB-1, the target DB-2 file and target DB-2 form are open.

Thanks!

 

[theDBguy]

Hi. You could link to the table you need from DB-2 in DB-1, so you can use the data from DB-2 in DB-1.

 

[Acke]

Hi and thanks.

Both DBs are already linked to the third access DB file which contains tables only. Both DB-1 and DB-2 are using the same data source. DB-1 and DB-2 are two copies of the same software and are on different machines.

The form that needs to be updated is displaying the number of active orders. Let's call it the NumberFrm. It is always open on both machines. If the new order is added at a computer with DB-1, the number of orders at DB-1 NumberFrm is updated with VBA command to +1. What I need is to use the same trigger to update immediately DB-2 NumberFrm to +1. As it is an unbound field, I just need to "copy" string from "DB-1/NumberFrm/NumberField" to "DB-2/NumberFrm/NumberField".

What I think I need is the code for the path to target "DB-2/NumberFrm/NumberField" from DB-1 event.

If it was the same access file I would use something like "Forms!TargetFrm.Form.TargetField = Forms!NumberFrm.form.NumberField" to copy data from the unbound field on form "NumberFrm" to the unbound field on form "TargetFrm".

I am sorry for not using proper terminology to explain the problem, I am not a professional programmer.

 

[theDBguy]

Hi and thanks.

Both DBs are already linked to the third access DB file which contains tables only. Both DB-1 and DB-2 are using the same data source. DB-1 and DB-2 are two copies of the same software and are on different machines.

The form that needs to be updated is displaying the number of active orders. Let's call it the NumberFrm. It is always open on both machines. If the new order is added at a computer with DB-1, the number of orders at DB-1 NumberFrm is updated with VBA command to +1. What I need is to use the same trigger to update immediately DB-2 NumberFrm to +1. As it is an unbound field, I just need to "copy" string from "DB-1/NumberFrm/NumberField" to "DB-2/NumberFrm/NumberField".

What I think I need is the code for the path to target "DB-2/NumberFrm/NumberField" from DB-1 event.

If it was the same access file I would use something like "Forms!TargetFrm.Form.TargetField = Forms!NumberFrm.form.NumberField" to copy data from the unbound field on form "NumberFrm" to the unbound field on form "TargetFrm".

I am sorry for not using proper terminology to explain the problem, I am not a professional programmer.

Hi. If you are not doing it this way, then maybe you should try to see if you can, to make your life easier.

So, any common data you want to use/display on any copy of your database, then you should put that data in one common place. I guess, for your situation, that means the backend database file. It sounds like you have two FEs connected to the same BE file. However, you are "counting" orders using unbound controls on the FEs. If you want to display the same "order count" on all copies of the FE, you could try storing that information somewhere (in another table?) in the BE. Then, you can use a bound control that all FEs would be able to utilize.

Just a thought...

 

[Acke]

It sounds like you have two FEs connected to the same BE file.

This is correct.

Bounding the field did the trick. Thanks. It takes some time for the field to update, with one of the machines even up to a minute, but it does function.

This problem is solved, but I am still curious if there is a way to trigger an event from one access file to another. Is this possible?

 

[theDBguy]

This problem is solved, but I am still curious if there is a way to trigger an event from one access file to another. Is this possible?

Hi. I'm not sure if there's a "push" capability in Access, but you can implement a "pull" functionality. Most of the times, this is done via a Form Timer. It would be nice to learn something new as well, so I am hoping for you. Good luck!

 

[Acke]

Thanks.

Most of the times, this is done via a Form Timer.

The slicker solution, in this case, was to have an immediate and one-time event. Using form timer sometimes makes the form glitchy as the task is triggered according to the interval set. As the form, in this case, is always on, your suggestion to use the bound field was preferable.

The closest subject I could find to trigger events between separate access files was this.

Modifying the code, I managed to open Access file and form.

Function OpenFromAnotherDB (strDbPath as string, strFrmName as string)
Dim objAcc as Object
Dim accFrm as Object

Set objAcc = CreateObject("Access.Application")
objAcc.OpenCurrentDatabase(strDbPath)
objAcc.Docmd.OpenForm strFrmName

objAcc.UserControl = True

End Function

Code to trigger the event:

Private Sub Command_Click()
OpenFromAnotherDB "F:\My Documents\Test.accdb", "TestForm"
End Sub

Successfully opens "TestForm" in "Test" Access file.

What would be great is to modify the code to set focus to "Test" db (as the file is already open when the event is triggered), to make "TestForm" active and to ".Requery" the form instead of opening it.

How can this be done?