Filtering Between Subforms

[Katie]

I need to store a history of two fields in two seperate tables:
Event - where the key is Event_ID
Stage - where the key is Stage_ID

The joins are:
Main Table [Account Number]
Event Table [Account Number] [Event_ID]
Stage Table [Event_ID] [Stage_ID]

My screen has:
An unbound main form which draws account number from the previous
screen
A continuous form Event subform linked to the main form by Account
Number
A continuous form Stage subform

My problem is this:
I can link and filter the Main form to the Event subform as there will
only be one event for each account
I need to filter the Stages subform by the Event_ID, which I can do in
the query behind the form, but if there are no stages the query filters
to nothing and I can't add a new record.

I have tried filtering in VBA on change of one of the fields on the
Event form, but to no avail.

In my head I can see this is such a simple problem, but I have tried to
sort it for three days now and I have a deadline looming.

Help!

[kingston via AccessMonster.com]

Try using a nested subform (no code necessary):

Create 3 forms - one for each table (no queries needed): FormA, FormB, FormC
Each form should work as expected (allow edits, additions, etc.).
Embed FormC into FormB and use [Event_ID] as the linking field.
This should work as expected (move to record in FormB and the corresponding
records in FormC should appear, edits and additions should work...).
Now embed this combined form into FormA and use [Account Number] as the
linking field.

Katie wrote:

>I need to store a history of two fields in two seperate tables:
Event - where the key is Event_ID
Stage - where the key is Stage_ID

The joins are:
Main Table [Account Number]
Event Table [Account Number] [Event_ID]
Stage Table [Event_ID] [Stage_ID]

My screen has:
An unbound main form which draws account number from the previous
screen
A continuous form Event subform linked to the main form by Account
Number
A continuous form Stage subform

My problem is this:
I can link and filter the Main form to the Event subform as there will
only be one event for each account
I need to filter the Stages subform by the Event_ID, which I can do in
the query behind the form, but if there are no stages the query filters
to nothing and I can't add a new record.

I have tried filtering in VBA on change of one of the fields on the
Event form, but to no avail.

In my head I can see this is such a simple problem, but I have tried to
sort it for three days now and I have a deadline looming.

Help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[Katie]

Thank you so much! I honestly don't know what I would do without these
groups!!

kingston via AccessMonster.com wrote:

Try using a nested subform (no code necessary):

Create 3 forms - one for each table (no queries needed): FormA, FormB, FormC
Each form should work as expected (allow edits, additions, etc.).
Embed FormC into FormB and use [Event_ID] as the linking field.
This should work as expected (move to record in FormB and the corresponding
records in FormC should appear, edits and additions should work...).
Now embed this combined form into FormA and use [Account Number] as the
linking field.

Katie wrote:

I need to store a history of two fields in two seperate tables:
Event - where the key is Event_ID
Stage - where the key is Stage_ID

The joins are:
Main Table [Account Number]
Event Table [Account Number] [Event_ID]
Stage Table [Event_ID] [Stage_ID]

My screen has:
An unbound main form which draws account number from the previous
screen
A continuous form Event subform linked to the main form by Account
Number
A continuous form Stage subform

My problem is this:
I can link and filter the Main form to the Event subform as there will
only be one event for each account
I need to filter the Stages subform by the Event_ID, which I can do in
the query behind the form, but if there are no stages the query filters
to nothing and I can't add a new record.

I have tried filtering in VBA on change of one of the fields on the
Event form, but to no avail.

In my head I can see this is such a simple problem, but I have tried to
sort it for three days now and I have a deadline looming.

Help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200612/1