Refresh listbox in database 1 on the close event of database 2

[aman]

Hi guys,

I have an access database 'DB1' and there is a listbox which shows all the activities . When double click event occurs on that listbox then another database 'DB2' with relevant form opens up and the users are supposed to complete the form . When the user completes the form and close it then database 'DB2' gets closed . Now in this case I want to requery my listbox in 'DB1'. How this can be done?

Can anyone please help me in this?

Thanks

 

[mresann]

Insert this statement at the end of the procedure that closes the external database.

DoEvents
Me.Listbox_Name.Requery

...where Listbox_Name is the name of our listbox control.

NOTE: If you have other calculations on the form, you may wish to precede the listbox query statement with a form requery statement.

 

[aman]

It doesn't work as when database 2 is closed then we need to update listbox in database 1 .

Private Sub lstAcceptedActivities_DblClick(Cancel As Integer)
Dim strEngagePath As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Switchboard_System_Mas_SysName WHERE SystemID = 427")
strEngagePath = "" & rs!FilePath & rs!FileName & rs!FileType
rs.Close
Set rs = Nothing

'Open Engage
Set db2 = GetObject(strEngagePath, "Access.Application")
'DoCmd.Close acForm, Me.Name
'DoCmd.OpenForm "frm_RMS_MinStand_ActivitySummary"
Me.lstAcceptedActivities.Requery
end sub

 

[aman]

Can anyone please help me in this ? It’s very urgent to resolve .

 

[Uncle Gizmo]

Bring the form from DB2 into DB1...

Sent from my SM-G925F using Tapatalk

 

[MajP]

Just capture the closing event of db2 form db1

Private WithEvents db2Form As Access.Form
Private db2 As Access.Application

Private Sub Command1_Click()
     Const fileName = "DB2.ACCDB"
     Dim strPath As String
     strPath = CurrentProject.Path
     strPath = strPath & "\" & fileName

     Set db2 = GetObject(strPath, "Access.Application")
     db2.DoCmd.OpenForm "form1"
     Set db2Form = db2.Forms!Form1
     db2Form.OnClose = "[Event Procedure]"
     
End Sub

Private Sub db2Form_Close()
   'do your requery here
   MsgBox "DB2 Form1 Just Closed"
End Sub

 

[The_Doc_Man]

I believe you should look into the "Activate" event on the form on DB1 because you said that a form in DB2 is taking the data. If so, there will be a Deactivate event for the DB1 form and an Activate event for the DB2 form when that form pops up and does its thing.

When DB2 closes, since that data form is driven from a separate database from DB1, its events are no longer available to power up or interact with anything in DB1. BUT if that form is still open from DB1 and never closed, it should Activate again. I believe the Form Activate event might cover this case, but don't take my word for it. Look it up (and try it.) I have one link, but it is easy enough for you to look up on your own and read more about it.

https://docs.microsoft.com/en-us/office/vba/api/access.form.activate

 

[MajP]

When DB2 closes, since that data form is driven from a separate database from DB1, its events are no longer available to power up or interact with anything in DB1.

Surprisingly that is not true. As I demonstrated you can handle another DBs event and so you can react to DB2s form closing. I did not think it would work but does.
However the easy solution would be to open the form in DB2 as acdialog.

'DoCmd.OpenForm "frm_RMS_MinStand_ActivitySummary",,,acdialog
'This would stop code execution here and will resume once the db2 form closes.
Me.lstAcceptedActivities.Requery

 

[The_Doc_Man]

MajP - my point is that if the DB is closing, its events are no longer accessible - but the first DB is still open and the Activate should do the trick.

I am not surprised that you can use the other DB's events because they are still two databases in the same workspace. But when the other DB is no longer there, that is a different question.

Your other solution is valid but, if I recall correctly, "dialog mode" prevents the Activate event from noticing the transition of focus back to the OP's DB1. So it is one or the other of those two methods. At first blush they appear to be mutually exclusive. On the other hand, the OP has a couple of methods to try.

 

[aman]

In DB1, on the double click event of listbox opens up DB2 (Form1) so when the user populates all the controls and save the data then DB2 is closed (that means its events are no longer accessible). Just wondering how can we auto requery listbox at that stage. It is easy to put a 'Refresh' button on the form to requery listbox manually but can't think of a way to automate it.

 

[isladogs]

Why not do ONE of the following

1. Just create a linked table in DB1, import the form and do everything from DB1

2. Close DB1 when you open DB2. Then when changes have been made in DB2, reopen DB1 with a command line argument. This runs a function to repen DB1 at the required form and refreshing the listbox automatically

 

[MajP]

In DB1, on the double click event of listbox opens up DB2 (Form1) so when the user populates all the controls and save the data then DB2 is closed (that means its events are no longer accessible). Just wondering how can we auto requery listbox at that stage.

It is not that complicated. You do it when the form closes. Either by trapping its event or opening the form AC dialog. Just like you do when you capture another forms closing event in a single database. It happens in the time between the close event of the form and the database closing. When DB2 closes the form closes first and before it closes you can capture its closing event. Did you try either method I showed? They work fine, so I do not understand why keep asking "what if."

 

[moke123]

I'm still trying to wrap my head around why you need to open DB2 and refresh the list in DB1. Is data being passed from DB2 to DB1 that affects the listbox?