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 = "CustomerQuery"
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...just 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