Having Problem with forms and sub-forms

[dipika]

I have 2 tables,
Client("ClientID", Firstname,Surname)
Individual/ProspectClient("ClientID",Taxcode,Bithdate,Age..)
its one-to-one relationship

The primary keys are in double quotes.
I want to display the records frm both tables using a form, if i choose fields frm both the tables, my form doesnt work.

the question is can i build a sub form within a one2one relationship ?
any other suggestions regarding the situation will also be appreciated

 

[jimmythegeek]

What is the purpose for having a one to one relationship. Why don't you just include all the fields in one table. If you want to know the difference between a client, and potential client, just add either a client yes/no field, or a clientstatus text field where you can enter client, lead, potential, etc. Then you can filter your data based on this field. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Terpsfan]

The previous response seems like a good idea - to combine the fields into one table/form. However if you really want to create two separate tables with two separate forms, you can create a one-to-one relationship with a form/subform. You may not be able to create this form because of the data types you have chosen for your primary keys. If you have used an Auto Number data type for your main form primary key, then you should use the Long Integer data type for your primary key for the sub form. Also make sure you create this relationship in your relationships window, declaring referential integrity and cascading updates and deletes.

Chuck

 

[dipika]

Thanks for the help.
The database required is much more extensive than the description i gave above. here is the real deal-

There r many clients, clients can be of 3 types - prospect, business, trust. one client can belong to one or more types at the same time.
accordingly i designed tables like this:

client('clientID', firstname, surname)
prospect('clientID', b'date,taxcode,income)
business('clientID', businessname,Accountant,Solicitor)
trust('clientID', trustname,trustees)

the primary keys are in quotes. i want to create form to display records of all prospect clients, which includes the fields frm client table and prospect table. similarly for the business and trust clients.

i tried doing what Omega36 suggested. it worked but, the problem is that a client can have only one clientID and may belong to more than one client types at the same time. how do i go around this prob ?

 

[weinem01]

There is a very easy way to do what you want. Just create several queries that contain exactly the information that you want to base your form on. Then, set your form source as the query, not the table. Don't worry, your tables will still automatically populate, even though your form is based on a query.

I think the answer to your question lies in queries. I was banging my head against a wall for a while before I figured out this trick.

Matt