Linking Table Data on Forms

[P5C768]

I have 2 tables, a client table with a autonumber primary key and a policy table autonumber primary key. I have created 2 forms to display the data in each of them.

The problem is that if you are on record 20 of the client data, when you transfer to the policy form, it goes to the first record. I am worried that the primary keys aren't linked correctly.

What changes can I make so that when you are on a specific client and navigate to the policy form, it goes to the same record on the policy form?

[Stressed]

I have 2 tables, a client table with a autonumber primary key and a policy table autonumber primary key. I have created 2 forms to display the data in each of them.

The problem is that if you are on record 20 of the client data, when you transfer to the policy form, it goes to the first record. I am worried that the primary keys aren't linked correctly.

What changes can I make so that when you are on a specific client and navigate to the policy form, it goes to the same record on the policy form?

You can create a button to open the the policy form based on a selection criteria from the client form.

Just change the RECORD SOURCE in the Properties > Data tab on the policy form to a query and link back to the client form. (SELECT * FROM CLIENT_TABLE WHERE POLICY_ID = FORMS!CLIENT_FORM!CLIENT_ID) then the only record in the policy form will be for that client.

Use the query builder it's easier... the structure for the criteria is

forms!form name!form field

[P5C768]

I'm not sure I understand. Are you saying to create a query first? Do you mean to use the properties>data tab associated with the client ID field?

Is there any issue with having 2 different tables with 2 different autonumber fields? Should I have the policy table primary key somehow linked to the client table primary key?

[Stressed]

I'm not sure I understand. Are you saying to create a query first? Do you mean to use the properties>data tab associated with the client ID field?

Is there any issue with having 2 different tables with 2 different autonumber fields? Should I have the policy table primary key somehow linked to the client table primary key?

If you can post a copy of your database I can take a look at your structure.
In theory, you should use the autonumber as a record number so that you can easily identify your records in a particular table.

What unique identifiers do you have in your client table that are the same in your policy table (ex. Account number, client reference number, etc)?

[P5C768]

I tried uploading my database, but it is nearly 1 MB over the limit. I also tried the suggestion of creating a query, but the query now prompts me to enter a client id when going to a record. I would like it to pull the client ID for the policy table directly from the client table.

I would also like a record to be created on the policy table every time a record is created on the client table, so that even if no policy information is entered, there is still a record associated with the client.

[pbaldy]

Here's another method, presuming they have a common field:

http://www.baldyweb.com/wherecondition.htm

[Stressed]

I tried uploading my database, but it is nearly 1 MB over the limit. I also tried the suggestion of creating a query, but the query now prompts me to enter a client id when going to a record. I would like it to pull the client ID for the policy table directly from the client table.

I would also like a record to be created on the policy table every time a record is created on the client table, so that even if no policy information is entered, there is still a record associated with the client.

I've attached a sample for your to view. By the sounds of it, you are close. You need to reference the client ID field from the client form.

[P5C768]

Thanks for the sample Stressed. I was trying to set up the query on a field, rather than for the entire form.

One more question. Now that the update is working, all the fields on the policy form cannot be changed. Is that a way to keep the policy form working on a query from the client form, but also allow updates to the policy information? Is it possible that I need to delete and re-add the fields I want since I changed the structure of the form?

[P5C768]

Just an update. I realized the data entry property on the form was set to 'no,' but when I change it to 'yes,' all of the fields on my form are no longer visible. Any ideas?

[Stressed]

Just an update. I realized the data entry property on the form was set to 'no,' but when I change it to 'yes,' all of the fields on my form are no longer visible. Any ideas?

Can you clear all the contents except for 1 record in each table that match and post it.

It would make it easier for me to see what the issue is.

You can check to see if the field properties are Visible=NO if so, then change to YES.

Another thing is that on the form properties... make sure that the Allow edits, deletions, additions are set to yes. As well as the Record Locks.

[P5C768]

Here is the database. I verified that the options you mentioned were set correctly.

[Stressed]

I took a look at your DB. I think it will be easier to manage the policy information using a non-bound form.

I started for you so you can see what I am doing. Just complete the steps that i have commented on in the code and you should be good to go.

This will make your live way easier in the long run.

[princess]

pls pls pls, can someone help me. I'm building a databse for a school (that will keep records of staff, students with the subjects they do, their class and grades per subject and Payment status for all the students) as my undergraduate project, what are the tables i need to include and the attributes? i currently have: Student, Staff, Parent, Subject and payment. Should i add more table and what will be their relationship?
Thanks