Changing relationships in form

[john]

I have a form with Members and in that form a subform with Transactions.
Those are linked (on banknr) 1xM which I've set in the database relations.
Now I would like to copy the form and modify it. I would like Transactions
to become the main table in the form so I can filter all new Transactions
(seems like a Mx1 relation). When new transactions have arrived I would like
to go through them one by one, and make some changes in the linked Members.
I'd rather not design the whole form again. My questions are:
1. Can I have a relation between tables specifically for one form and
independent of the main database relations and where do I set that?
2. How can I use all the layouting of my first form to create this new form.
Thanks in advance,
john

[salad]

john wrote:

I have a form with Members and in that form a subform with Transactions.
Those are linked (on banknr) 1xM which I've set in the database relations.
Now I would like to copy the form and modify it. I would like Transactions
to become the main table in the form so I can filter all new Transactions
(seems like a Mx1 relation). When new transactions have arrived I would like
to go through them one by one, and make some changes in the linked Members.
I'd rather not design the whole form again. My questions are:
1. Can I have a relation between tables specifically for one form and
independent of the main database relations and where do I set that?

You already have a relationship between Members and TXs on banknr. What
other relationship do you propose?

FYI, you can change recordsources in forms. Let's say you wanted a form
to present either Customer or Employee data. Both tables have similar
data to present and in the queries you have ensured the columns have the
same names. The following shows how to change the CustomerID field to
have the name as EmpID. Ex:
EmpID : CustomerID
Here the CustomerID field will have a column alias of EmpID. Now when
you open the form you can pass the argument to the form of "Emp" or
"Customer". If the recordsource is Emp, you keep the same recordsource,
otherwise use the Customer recordsource. Ex:
'from the calling form
Docmd.Openform "EmpCust",,,,,, "Customer"

'OnOpen event of called form
If Me.OpenArgs = "Customer" then
'only need to check for cust since default
'recsource is Emp.
Me.Recordsource = "CustomerQu ery"
Endif

2. How can I use all the layouting of my first form to create this new form.

Why bother? You are going from a form with Members and subform a list
of TXs related to the member. If seems ludicrous to have the subform
drive which records are displayed in the mainform based on the subform'r
records.

Queries can be used as recordsets...ju st like tables. You could create
a query with both tables...Create a new query, drop in the two tables,
and drag the fields into the columns you want on your form. Then create
a continuous form that lists those fields with the recordsource of that
query. If you want to update info for members, have an OnDblClick event
to open a form that presents the member data and update the member data
there. Close the single form and continue on.

Thanks in advance,
john

[john]

"salad" <oi*@vinegar.co mschreef in bericht
news:Ge******** ********@newsre ad4.news.pas.ea rthlink.net...

john wrote:

>I have a form with Members and in that form a subform with Transactions.
Those are linked (on banknr) 1xM which I've set in the database
relations. Now I would like to copy the form and modify it. I would like
Transactions to become the main table in the form so I can filter all new
Transactions (seems like a Mx1 relation). When new transactions have
arrived I would like to go through them one by one, and make some changes
in the linked Members. I'd rather not design the whole form again. My
questions are:
1. Can I have a relation between tables specifically for one form and
independent of the main database relations and where do I set that?

You already have a relationship between Members and TXs on banknr. What
other relationship do you propose?

The type of relationship is different because now I want all records of TXs
and only those of Members that matches TXs on banknr. In my firs form it is
the other way around.

FYI, you can change recordsources in forms. Let's say you wanted a form
to present either Customer or Employee data. Both tables have similar
data to present and in the queries you have ensured the columns have the
same names. The following shows how to change the CustomerID field to
have the name as EmpID. Ex:
EmpID : CustomerID
Here the CustomerID field will have a column alias of EmpID. Now when you
open the form you can pass the argument to the form of "Emp" or
"Customer". If the recordsource is Emp, you keep the same recordsource,
otherwise use the Customer recordsource. Ex:
'from the calling form
Docmd.Openform "EmpCust",,,,,, "Customer"

'OnOpen event of called form
If Me.OpenArgs = "Customer" then
'only need to check for cust since default
'recsource is Emp.
Me.Recordsource = "CustomerQu ery"
Endif

>2. How can I use all the layouting of my first form to create this new
form.

Why bother? You are going from a form with Members and subform a list of
TXs related to the member. If seems ludicrous to have the subform drive
which records are displayed in the mainform based on the subform'r
records.

Queries can be used as recordsets...ju st like tables. You could create a
query with both tables...Create a new query, drop in the two tables, and
drag the fields into the columns you want on your form. Then create a
continuous form that lists those fields with the recordsource of that
query. If you want to update info for members, have an OnDblClick event
to open a form that presents the member data and update the member data
there. Close the single form and continue on.

I'll look into that. Thanks.
john

[salad]

john wrote:

"salad" <oi*@vinegar.co mschreef in bericht
news:Ge******** ********@newsre ad4.news.pas.ea rthlink.net...

>>john wrote:

>>>I have a form with Members and in that form a subform with Transactions.
Those are linked (on banknr) 1xM which I've set in the database
relations. Now I would like to copy the form and modify it. I would like
Transactio ns to become the main table in the form so I can filter all new
Transactio ns (seems like a Mx1 relation). When new transactions have
arrived I would like to go through them one by one, and make some changes
in the linked Members. I'd rather not design the whole form again. My
questions are:
1. Can I have a relation between tables specifically for one form and
independen t of the main database relations and where do I set that?

You already have a relationship between Members and TXs on banknr. What
other relationship do you propose?

The type of relationship is different because now I want all records of TXs
and only those of Members that matches TXs on banknr. In my firs form it is
the other way around.

I see no difference. A relationship is a relationship. But it seems
odd to have a subform define the actions of the mainform. In fact, it
doesn't make sense. For example, if the link between main/sub forms is
the memberid, how would the subform display main recs of another Memberid?

>

>>FYI, you can change recordsources in forms. Let's say you wanted a form
to present either Customer or Employee data. Both tables have similar
data to present and in the queries you have ensured the columns have the
same names. The following shows how to change the CustomerID field to
have the name as EmpID. Ex:
EmpID : CustomerID
Here the CustomerID field will have a column alias of EmpID. Now when you
open the form you can pass the argument to the form of "Emp" or
"Customer". If the recordsource is Emp, you keep the same recordsource,
otherwise use the Customer recordsource. Ex:
'from the calling form
Docmd.Openfor m "EmpCust",,,,,, "Customer"

'OnOpen event of called form
If Me.OpenArgs = "Customer" then
'only need to check for cust since default
'recsource is Emp.
Me.Recordsour ce = "CustomerQu ery"
Endif

>>>2. How can I use all the layouting of my first form to create this new
form.

Why bother? You are going from a form with Members and subform a list of
TXs related to the member. If seems ludicrous to have the subform drive
which records are displayed in the mainform based on the subform'r
records.

Queries can be used as recordsets...ju st like tables. You could create a
query with both tables...Create a new query, drop in the two tables, and
drag the fields into the columns you want on your form. Then create a
continuous form that lists those fields with the recordsource of that
query. If you want to update info for members, have an OnDblClick event
to open a form that presents the member data and update the member data
there. Close the single form and continue on.

I'll look into that. Thanks.
john

In your case, I'd simply create a continuous form of your txs linked to
the members table.