Refresh listbox in database 1 on the close event of database 2 (1 Viewer)

aman

Registered User.
Local time
Today, 11:45
Joined
Oct 16, 2008
Messages
1,250
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

Registered User.
Local time
Today, 11:45
Joined
Jan 11, 2005
Messages
357
Insert this statement at the end of the procedure that closes the external database.

Code:
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

Registered User.
Local time
Today, 11:45
Joined
Oct 16, 2008
Messages
1,250
It doesn't work as when database 2 is closed then we need to update listbox in database 1 .
Code:
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

Registered User.
Local time
Today, 11:45
Joined
Oct 16, 2008
Messages
1,250
Can anyone please help me in this ? It’s very urgent to resolve .
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 18:45
Joined
Jul 9, 2003
Messages
16,244
Bring the form from DB2 into DB1...

Sent from my SM-G925F using Tapatalk
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:45
Joined
May 21, 2018
Messages
8,463
Just capture the closing event of db2 form db1

Code:
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

Immoderate Moderator
Staff member
Local time
Today, 13:45
Joined
Feb 28, 2001
Messages
26,999
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

You've got your good things, and you've got mine.
Local time
Today, 14:45
Joined
May 21, 2018
Messages
8,463
Code:
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.

Code:
'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

Immoderate Moderator
Staff member
Local time
Today, 13:45
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 11:45
Joined
Oct 16, 2008
Messages
1,250
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

MVP / VIP
Local time
Today, 18:45
Joined
Jan 14, 2017
Messages
18,186
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

You've got your good things, and you've got mine.
Local time
Today, 14:45
Joined
May 21, 2018
Messages
8,463
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

AWF VIP
Local time
Today, 14:45
Joined
Jan 11, 2013
Messages
3,852
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?
 
Last edited:

Users who are viewing this thread

Top Bottom