Application level Custom Event

[NSNewey]

Hi,

I have an access 2003 app with many forms displaying data in listboxes.

There are several places where saving a record in one form needs to refresh the lists in other open forms.

I currently do this by calling a public sub which takes the form and listbox name then calls the listbox's requery method. here is the code...

Public Sub requeryControl(ByVal formName As String, ByVal ctrlName As String)
[green]' used to requery list boxes in related forms when data is changed[/green]
    If IsLoaded(formName) Then
        Forms(formName).Controls(ctrlName).Requery
    End If
End Sub

This works fine but means that when I add a new form with a list that may need updating, I have to go back to each form that potentially changes the data being displayed and add another call my requeryControl() sub

If I created an application level event that was fired when the save button is clicked in each data entry form and is listened for in each form displaying related data it would save a lot of time adding new calls to my requeryControl().

I have not looked into creating custom events and am not even sure if what I describe is possible in Access VBA.

Has anyone done something similar or maybe has a different solution with the same outcome?

Many thanks.

Neil.

 

[PWise]

Create A table "TblrequeryControlSearch"
fields
CallingFormName
CallingFormControl
UpdateFormName
UpdateFormContrl

create this sub

Public Sub requeryControlSearch( ByVal ctrlName As String)
dim mydb as database
dim rst as recordset
set mydb =currentdb
set rst = mydb.openrecordset("Select UpdateFormName,
UpdateFormContrl from TblrequeryControlSearch where CallingFormName=" & Application.Screen.activeform.name & " and
CallingFormControl=" & ctrlName )
do while not rst.eof
requeryControl(ByVal Rst!UpdateFormName As String, ByVal Rst!UpdateFormContrl As String)
rst.movenext
loop

end Sub

and call this after each button
requeryControlSearch me.activecontrol.name

 

[MajP]

Yes you can trap other form or report events on another form/forms. Here is an example:

1) build a form that raises an event. I built a form called "frmRaise" and it has a command button called "cmdRaise". On the on click event I select "[event procedure]" and give it and empty event.

2) Now build one or more forms to trap the event. In the forms you need code like this:

Public WithEvents ctl As Access.CommandButton

Private Sub Form_Load()
  Set ctl = Forms("frmRaise").Controls("cmdRaise")
End Sub

Private Sub ctl_Click()
  'trap the "frmRaise" click event here
End Sub

 

[NSNewey]

Thanks for the info guys.

I will look at both these examples.