Updating combos and subforms

[raycafc]

I have 2 problems that I would like help with please.

They both concern the linking of two forms, each with their own subforms.
The first pairing is a form called Job and a subform called Job Spec. The
second pairing is Area and its subform Services.

On the Job form there is a drop-down which lists all of the Areas and
Services for that Job. These are concatenated into one field, so it says
something like Hot Water Area / Taps etc. It will only list the Area /
Services for that Job. These are selected in the Area / Services forms (the
2nd pairing). If I go from the Job form to the Area / Services form I can
select more or delete existing Areas / Services.

The first question therefore is:
how do I keep the drop-down showing the correct data, so that if a new Area
/ Service is added it shows in the drop-down.

The second question is:
As I have cascade delete on the Services / Job Spec relationship, for good
reasons, if I delete a Service or Area, the Job Specs linked to them are also
deleted. But the Job Spec subform, which has remained open, shows the deleted
lines with the words "Deleted". I understand why it is doing this, and if I
do a Refresh they disappear, but how do I make them disappear automatically?

I hope this hasn't gone on too long, and if you have any questions please do
not hesitate to ask.

 

[Allen Browne]

Both questions relate to how to have muliple forms open at once, and ensure
the combos/forms on background forms hear about the changes you made.

What I do is to have the AfterUpdate and AfterDelConfirm events of every
form in the database call a standard function. The standard function is only
a skeleten until all the forms are in place, because you don't know what the
dependencies will be at the time you create the forms. So after all forms in
in place, you write this function. It's a monster Select Case construct that
says if the calling form is this one, then see if these forms are open, and
if so reqery that combo/subform/whatever needs to hear about the changes.

The function is declared like this:
Public Function NotifyCombos(strSourceForm As String, _
Optional iStatus As Integer = acDeleteOK)
and called in Form_AfterUpdate as:
Call NotifyCombos(Me.Name)
and in Form_AfterDelConfirm as:
Call NotifyCombos(Me.Name, Status)

The code in the function is like this:
If iStatus = acDeleteOK Then
Select Case strSourceForm
Case "Form1"
If IsLoaded(strSourceForm) Then
Forms(strSourceForm)!cboGoto.Requery
End If
strForm = "Form2"
If IsLoaded(strForm) Then
Forms(strForm)!SomeCombo.Requery
End If
Case "Form2"
If ...

It's not that difficult, but it is a *long* function. Be sure to include
error handling. I use Resume Next after error 2465 and 2118.