duplicate values in index, primary key, etc in splitform

[REDandBLACKpack]

I have a split form that reads in information from two tables. The first table is called Contracts and contains multiple columns of data. The primary key is a unique ID that I created called ContractID. I also have ContractID as a drop down selection menu from using the lookup wizard for its data type to find all of the ContractID's that I copied into a seperate table.

The second part of the split form reads from a table called Comments that keeps all of the comments for each ContractID in a table. This table does not have a primary key but there is a 1 to many relationship between my first table's contractID (primary key) and this table's contractID.

My issue is when I go into my form, if I do not click on the arrows at the bottom to move between records or use the search function, but rather select a new contractID from the drop down in the contractID section I will get an error if I try to edit the comments. The form will refresh automatically and show the correct comments for the new contractID that I selected but I can not edit them. I believe this is because at the bottom, the record number beside the navigation arrows does not change.

I wanted to know if there is a way to fix tihs from happening so that when I select a new contractID it will allow me to add/edit comments. Also, none of the other information changes to the new contractID besides the comments in the second part of the split form. Is there a way for the rest of the first part of the split form to change as well?

If not, is there an easier way to navigate through records besides searching or using the arrows.

Thanks!

[rpeare]

are you doing this on a form/subform? I have to assume you are. On the subform is the primary key from your parent form correctly linked to the child id from your comments table?

What is the data source of your child item subform, is it just the comments table fields or is it linked to anything else?

[REDandBLACKpack]

are you doing this on a form/subform? I have to assume you are. On the subform is the primary key from your parent form correctly linked to the child id from your comments table?

What is the data source of your child item subform, is it just the comments table fields or is it linked to anything else?

Yes I am doing this on a form/subform, I mistakenly called it a split form. My primary key, ContractID, is linked to the column ContractID on the subform. I do not have a primary key on my subform.

My subform is ContractID, CommentBody, CommentDate and that is it. It is not linked to anything else at all.

[rpeare]

Let me just reiterate your problem and see if I understand it. Your form works as you want it to until you add a new record to your main form and immediately try to add comments to the newly created record?

If that's the case have you tried using one of the events of the subform (on current, on got focus, etc) that was me.refresh. The refresh command updates your database with the most recent information and it should then allow you to modify your data.

[REDandBLACKpack]

Let me just reiterate your problem and see if I understand it. Your form works as you want it to until you add a new record to your main form and immediately try to add comments to the newly created record?

If that's the case have you tried using one of the events of the subform (on current, on got focus, etc) that was me.refresh. The refresh command updates your database with the most recent information and it should then allow you to modify your data.

I am not sure if that is entirely my problem. I will try to explain it as best as I can.

I go into my form, which has the mainform and the subform underneath it. In my mainform, I have the ContractID for that record which is my primary key in my table. I can click ont hat ContractID and because I used the lookup wizard to generate it, I have a listing of all of the ContractID's in that table. If I select a different record's ContractID, the subform which has comments and comment dates in it, will refresh to the specific comments for that ContractID. The comments are linked by the ContractID. The rest of my info in the main form does not change so it does not match the ContractID. Additionally, the record number at the bottom of the form (by the navigational arrows) does not change to reflect the correct record number for that ContractID. Because of this, I can not edit the comments and I get the error that is in the title of this thread.

I want to know if there is a way to fix this so that I can edit the comments, besides using the arrows to go to the correct record number. If not, is there an easier way to navigate through records besides the arrows/search function.

[rpeare]

perhaps if you post a sample database with fake data it might be easier to understand what you're doing. Your description hasn't really changed from the first post and I am not following the problem as you are describing it. For instance I do not use any wizards in MS access, they cause way more problems than they solve for me.

[REDandBLACKpack]

perhaps if you post a sample database with fake data it might be easier to understand what you're doing. Your description hasn't really changed from the first post and I am not following the problem as you are describing it. For instance I do not use any wizards in MS access, they cause way more problems than they solve for me.

ok here is a basic sample database. i copied my real database and deleted out a bunch of tables and queries that are not needed in this (and the issue still arrieses so none of that impacted this).

If you go into the contact form you will see at the bottom of the form the comments that are pulled from my comment table.

If you click on contract ID at the top of the form you will see a drop down box with a listing of all of the fake contractIDs. if you click on a different one from the one you are on you will notice the comments at the bottom change. The rest of the form stays the same( there is not much info in there but it does not change). if you then try to add a comment at the bottom you will get an error. if you notice, the record number at the bottom of the form( by the navigational arrows and search) will still show the record number from the one you were on before you changed by selecting a new contractID.

I want to know if there is a way to fix this from happening so that you can use the drop down box from counterpartyID (which is the primary key) to switch between pages on the form. if you can, is there also a way for the rest of the info on the top part of the form to refresh as well to match what should be there for the selected contractID.

hopefully this makes a little more sense

[rpeare]

Ok, here is the problem. If your intent on your CONTRACTID field is to perform a search, that's not what your form is doing. When you select a new contract it is going to ATTEMPT to change the contractID of the current record you're looking at (and will bring up the 'correct' comments), note that if you select a new contractID and try to navigate away from that record you're going to get an error message saying it would create a duplicate index.

Here's a copy of your database back.

You'll notice I've changed the data source of your form to be a query based on whatever is in the SEARCHFIELD combo box I've added to your form and in the ON EXIT property of the SEARCHFIELD combo box it refreshes the data that you see.

[rpeare]

oops here it is

[REDandBLACKpack]

thank you very much for fixing that. is there a way that you can implement the search function like you did, but still also be able to edit information in that record after you have searched for it. for me, when i select a contract via the search for contract, it doesn ot refresh until i click somewhere in that form but it does not allow me to edit any of the information

[rpeare]

in the on exit property of the search combo box just type in

DOCMD.GOTOCONTROL ("COUNTERPARTY")

That'll force your cursor to go to the first field that you want to allow data entry on (contractID should be locked) Any change you make is automatically accepted because you're using a bound form

[REDandBLACKpack]

Do I leave this part in the exit property:
Private Sub searchfield_Exit(Cancel As Integer)
Me.Requery
End Sub

and add the other line or replace that line with the new one

[rpeare]

just add the docmd.gotocontrol after that refresh command

[REDandBLACKpack]

thank you so much! now what steps do i need to complete to add this back into my original database?

[rpeare]

if your tables, queries forms are all named the same in your original database you just do the same thing I did, which I explained in the first post.

You'll notice I've changed the data source of your form to be a query based on whatever is in the SEARCHFIELD combo box I've added to your form and in the ON EXIT property of the SEARCHFIELD combo box it refreshes the data that you see.