Adding records to a filtered form

OK, here's my dilemma. I am trying to create a system to track cases
and clients. There are instances when cases have multiple clients, and
there are instances where clients have multiple cases. I have several
tables to house all the information, but the three that connect the
dots are

tblClients
tblCases
tblCaseClients

tblCaseClients contains CaseClientID (primary Index), ClientID, CaseID

I have the following forms: frmCaseTabs whose recordsource is tblCases
frmClients whose recordsource is tblClients
frmCaseClients whose recordsource is tblCaseClients, and it appears as
a subform on both the frmCaseTabs and frmClients

I am having trouble creating a screen on the frmCaseTabs where I can
add an new client to an existing case, because the subform showing the
clients is filtered, therefore I cannot add another one. I know I can
accomplish this somehow, but my brain is fried right now and I can't
come up with the solution. Any help would be greatly appreciated.

Thanks,
Dave

re: Adding records to a filtered form

Have a command button on the main form that inserts a new client. I
suggest in that case you develop a small dialog box to choose one. Or,
display an otherwise hidden combobox, again on the main form.

When the new client is inserted, you can issue Me.Recalc (does a Requery
on listboxes, comboboxes and subforms)

dbaxter7@yahoo.com wrote:
[color=blue]
> OK, here's my dilemma. I am trying to create a system to track cases
> and clients. There are instances when cases have multiple clients, and
> there are instances where clients have multiple cases. I have several
> tables to house all the information, but the three that connect the
> dots are
>
> tblClients
> tblCases
> tblCaseClients
>
> tblCaseClients contains CaseClientID (primary Index), ClientID, CaseID
>
> I have the following forms: frmCaseTabs whose recordsource is tblCases
> frmClients whose recordsource is tblClients
> frmCaseClients whose recordsource is tblCaseClients, and it appears as
> a subform on both the frmCaseTabs and frmClients
>
> I am having trouble creating a screen on the frmCaseTabs where I can
> add an new client to an existing case, because the subform showing the
> clients is filtered, therefore I cannot add another one. I know I can
> accomplish this somehow, but my brain is fried right now and I can't
> come up with the solution. Any help would be greatly appreciated.
>
> Thanks,
> Dave
>[/color]

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

re: Adding records to a filtered form

The main form is called frmCaseTabs, it's recordsource is the tblCases
table. If I add a button to add a new record, it will be a new Case
record, not a new Client record. The first tab contains the case
information, the second tab contains a subform showing the clients on
that case (obviously filtered). The way I have it working currently is
that you can open the frmClients and add people, then you close that
form and open the frmCaseTabs form where you fill out the case
information, and choose the clients from a drop down list of all
clients in the database. This updates the table tblCaseClients with
the CaseID and ClientID and creates a primary index called
CaseClientID. So my problem is that because I have this intermediary
table between the tblCases and tblClients, I can't figure out how to
add a client from within the frmCaseTabs screen. The recordsource is
not the table tblCaseClients. The subform containing that information
is on the second tab, and it's recordsource is qryCaseClients which
shows all clients under this case number.

If I am going to add a new client from this screen, I really need to
set it to populate the tblCaseClients table as well as the tblClients
table. It would have to set the CaseID in tblCaseClients, and it would
have to set the ClientID to whatever the new ClientID is when the new
person is created. It sounds so confusing, but I know it's quite
simple, but I just can't figure it out.

Thanks,
Dave

re: Adding records to a filtered form

[color=blue]
> The main form is called frmCaseTabs, it's recordsource is the tblCases
> table. If I add a button to add a new record, it will be a new Case
> record, not a new Client record.[/color]

That is not automatically. You can make a button any way you like. But
it is probably not the best place, indeed.
[color=blue]
> The first tab contains the case
> information, the second tab contains a subform showing the clients on
> that case (obviously filtered).[/color]

That would be the logical place for the 'add client' button. I
understand you add an existing client to this case, on that particular spot?

The way I have it working currently is[color=blue]
> that you can open the frmClients and add people, then you close that
> form and open the frmCaseTabs form where you fill out the case
> information, and choose the clients from a drop down list of all
> clients in the database. This updates the table tblCaseClients with
> the CaseID and ClientID and creates a primary index called
> CaseClientID. So my problem is that because I have this intermediary
> table between the tblCases and tblClients, I can't figure out how to
> add a client from within the frmCaseTabs screen. The recordsource is
> not the table tblCaseClients. The subform containing that information
> is on the second tab, and it's recordsource is qryCaseClients which
> shows all clients under this case number.[/color]

Is that query not updatable, then? And why do you use a query at all?
Provided you have LinkMasterFields and LinkChildFields correctly set up,
you will automatically see just the clients for this case.
[color=blue]
> If I am going to add a new client from this screen, I really need to
> set it to populate the tblCaseClients table as well as the tblClients
> table.[/color]

You would choose the client from a combobox based upon tblClients. Set
its LimitToList property to Yes (or True or what is it), and use the
NotInList event to trigger the complete new client insertion procedure.
At the end of this event procedure, do Response=acDataErrAdded.
If NotInList doesn't fire, it means the user selected an existing client
to be added to this case.
Both ways, the client gets added to the case.

Maybe it's just qryCaseClients that frustrates function here.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea