Data Entry Tabbed Forms

[Navyguy]

Hi All

I have tried my first attempt at making some tabbed subforms and I have come up with an error that I cannot seem to put my finger on.

The error states “The changes that you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again.”

What I have is basically 2 versions of the same Mainform and subforms, one for Editing Data and one for Data Entry. Both are set up exactly except for the Data entry property set as required. The problem is on the data entry Mainform and subforms.

I have one tabbed form’s source the same as the mainform and the other 3 subforms source as different tables. The Mainform has some of the tombstone information, IDNumber, Surname, FirstName, etc. The first tabbed form has an continuation of the information, Address, HomePhone etc. The other tabbed forms have info like NextOfKin information and EmploymentQualifications, etc.

If I enter the data on the Mainform and select any of the other tabs (except for the first subform) it allows me to enter the data and saves the record. If I enter the first tabbed form (the one with the same source as the Mainform) as soon as I try to exit that subform the error above comes up.

I am sure that I have the correct linked master and child fields, my relationships seem to be OK when working with the same forms in the edit mode and my PK are set to no duplicates.

Main form table has a PK PersonID Autonumber
PK IDNumber (this is the field that is used for linking all the other tables) (set to no duplicates)

My guess is that the problem is when I move from the Mainform to the subform with the same source it saves the IDNumber, then when I move from the first subform to the next it tries to save the IDNumber again, but I am not sure.

Can somebody point me in the right direction?

As always, thanks for your help

 

[livvie]

Make sure the data in the mainform is saved before you move on to the tab to enable you to passon the id field automatically.

 

[Navyguy]

I tried to do that by putting a DoCmd.Save on the LostFocus event of the IDNumber but that did not work. Maybe I have the right idea but my implementation is wrong?

Always looking for suggestions.

 

[Navyguy]

Looking for some guidance on this one, I have tried DoCmd.Save in various location and on various events to try to get this to work.

Could this be a bug? The only thing I have not tried was to compact repair and export to new DBFE. Is this the last resort or am I missing something really simple?

 

[smercer]

I once had a problem where I wanted the cursor to go to a new record when the user clicks on a button. The problem I had was when clicked it would also create a new blank record which was not in the code of the same sub. as it turns out it was another sub of another form with a txt box with "OnEnter" event I found that was causing a blank record.

So question is, is there any other subs through out the entire thing that will cause this, eg when you click on the tab you would bring another form in to view, and execute another event you have put in for that form.

 

[Navyguy]

Smercer thanks for the idea. I did what you had suggested. I even looked at things that were not even related and came up with nothing.

I am convinced that it is a problem with having the main form and the first subform linked to the same table for data entry purposes. What I tried was taking the fields from the main form and putting them on the first subform, but it still gives an error as the mainform wants the required entry on the PK field (IDNumber).

I tried exporting to new DB but that had no effect either. I think what I am left with is putting all those field on the mainform and just using the Tabs for the other subforms. This idea does defeat the concept of using tabs in the first place as there is numerous fields on my form that are related to the same table.

Smercer

I had a look at your post. I am afraid I cannot help with that, it is quite a bit out of my league. But once again that for your suggestion.

 

[Navyguy]

If somebody is willing to take a look at what I have I would appreciate it. I took out alot of the combo boxes and related lookup tables to keep the size down.

Thanks

 

[smercer]

I took out alot of the combo boxes and related lookup tables to keep the size down.

You must have fixed it without realising it. I did put in information on the master form and clicked on the tabs but all is normal.

Possible cause:
1) you deleted entries that may have had duplicate entries
2) the relationships of one of the tables that you deleted could also cause a problem.

Please check my post again, I have put in a picture so you can see what is happening. Thanks for trying

 

[Navyguy]

smercer

Thanks for looking. I tried it again and I still have the same problem. If I put info in the main form like ServiceNumber then move to the first tab (employment) form and put in something for component then click the NOK tab (or any other) I get the error that is in the first post of this thread.

If that is what you did and did not get the error, then I must be chasing a bug in my software. Maybe I will reinstall access and try again. We have a network version here, so if there is a problem then it is on all the machines (som 50 or so) and that would explain my inabilioty to do this on any machine.

Could this be a reference problem? I am not sure why if would be...but you never know.

I will take a look at your new post and see if I can lend a hand.

Thanks for your help so far.

Edit: I just tried a copy on an standalone machine and had the same problem, so I think it is something that I have done with the set-up of the forms.

 

[WindSailor]

Just a quick reply, I haven't looked at your sample...
I went back and looked at a project I have that uses a main form and tabbed subforms, and the main form is from a separate table from the subforms. The four tabbed subforms are from the same table but come from different queries to provide different views. The data that is changed in those subforms is saved back to the table that they come from.
For sorting info or the combo boxes on the main form to filter the subforms by, those values are not from the same table as the subforms, if I remember correctly if I input a value using a lookup from the same table as the subforms 'table2' that value that I just inserted into the text box was just saved as input, so now instead of 1,2,3 etc it is now 1,4,3,4 etc. the original values and sequence of the lookup now have changed (this is by manually inserting a value over the suggested value of the drop down box). Again this is from memory but there is an issue there so be carefull on how you sort or filter your subforms and recheck your original values.
Double entries... the only time that I have gotten double entries is having more than one table in a query with links, the query didn't like the links.
I realize that my purpose for my form with tabbed subforms may be of different design, but I hope this helped.

 

[WindSailor]

I forgot to add I found an issue working with tabbed subforms, when using code to resize your forms to another display setting, the tabbed subforms may not display correctly. This is obviously in the code, just passing this on incase you want to do this and spend a little more time refining it.

 

[smercer]

I had another look and I see what you mean now. but you left something out. when you are opening it with no records and enter data for the first time you get no errors. you can put in as many records as you like. and go back and see your records you have just created. but when you close the form then reopen, you cant see the records that already exist, therefore if you enter a number that already has been used it will give that error.

interesting. I will keep looking at it.

If I made you some code that will do the Primary key you won't get that error message. how would you like the primary key made up?

Here is how I have mine in my data base:

CP-PR-HIT-1

The "CP" is the category of the book. (Computers)

the PR is the sub category of the book (Programming)

The "HIT" is the first 3 characters of the author’s surname

the number at the end is the quanity plus one when it was added.

so would you like me to make you some code for this?

 

[smercer]

I have changed the "Data Entry" to "No". and the problem I discribed goes away. (Meaning you can see what you have entered).

If you what to only allow additions, change "Allow Additions" to "Yes", and "Allow Deletions" and "Allow Edits" to "No"

Does this help? Please respond.

 

[Navyguy]

Thanks for the replys so far...

I am running in and out of the office today...so this will be a quick one

I have two versions of the same tabbed forms setup. One is for Data Entry and one for Editing. The only difference is that the data entry set are linked directly to the tables and the data entry property is set to "Yes". The editing ones are generated by queries and from the tables directly (combination) and the data entry is set to "No". The editing set of tabbed forms works OK. It is the problem with the Data Entry set. As I am typing here, I am thinking that I should make a duplicate set of subforms and make them all data entry also. I guess I am starting to grasp at straws here. The linked field is ServiceNumber whick is the PK (No Duplicates).

 

[WindSailor]

Quick note, on mine the data entry is set to "No".
This is starting to get interesting...
I use another form which isn't a subform and have a command button to add new records with other buttons to either 'cancel', 'save' etc.
I went backed and checked... on the form in which I have those command buttons to add a new record etc. the Data Entry option is still set to "No".
Honestly I have never used it on a subform but the idea should be similar and I have never expanded my knowledge on why the Data Entry should be set to "Yes".
I should say I have never built anything with the Data Entry set to "Yes" I have always performed double duty on a form until now so I am very limited in that area, but this is something that I interested in now.

 

[smercer]

I use another form which isn't a subform and have a command button to add new records with other buttons to either 'cancel', 'save' etc.
I went backed and checked... on the form in which I have those command buttons to add a new record etc. the Data Entry option is still set to "No".
Honestly I have never used it on a subform but the idea should be similar and I have never expanded my knowledge on why the Data Entry should be set to "Yes".

I have used a simalar set of cmd buttons for subforms, and it is different, depending on what you want to do, for instance you may only want the subform to go to a new record but not the main form, you can't use GotoRecord for this.

Navyguy: Why do you need the Data entry set to "Yes"? Why can't you get by with only "Allow Aditions" set to "yes" and the allow deletions and edits set to "No" while the data entry is set also to "no"?

if you need the form to go to a new record when you open the form, in my opinon it would be no problems if you had a code that either makes up the ID number or you had a gotorecord statement in the OnOpen event

 

[Navyguy]

Just a quick note here

I use the different set-up on the forms mostly because I find it is easier (limited experience speaking) for the user to be clear in what they are doing. When talking to people, when they open up the GUI and find the ability to move around records etc they panic a bit. This way I find that Data Entry operators just focus on data entry and the people that are more familiar can do the editing of the selected records.

I could put in some security, but there really has not been the need to as of yet.

I am always open to suggestions, so maybe there is a better way of accomplishing the same result.

 

[smercer]

After looking at your code on the Frm-SFrmStaffTombstone, the code:

Private Sub Enter_Service_Number_LostFocus()
DoCmd.Save
End Sub

is wrong because there is no control named "Enter_Service_Number" for this form. On the main form yes there is a text box named "Enter Service Number" but you have done it all wrong (notice the text box name has no Underscore instead of spaces).

1) the name for a start should have no spaces "Enter_Service_Number" is a good name but "txt_Enter_Service_Number" is better because you know that it is a text box from visual basic (Less confusing)

2) if you want it to save when you leave that text box, you need to have the event in the same form that it is in (Not a subform)

I do not think that a save is going to help the problem.

As I said earlier, If you have a code that will make up the primary key you won't get that problem. however if you want to do it quick and dirty you can always make the primary key a autonumber, and in the child tables (With the foreign keys) you can then have a combo box that looks up your master table where they can choose a name but will save as the number the name corresponds to. Much more user friendlier this way.

 

[WindSailor]

Ok...
I went back and made a main form with subforms all with Data Entry properties set to "Yes" with no code on the forms.
It will fly. If you give a repeated number or text in the text box that is set to no duplicates, Access will give you a warning msg about duplicate values, otherwise everything will run.
With that said, smercer has good advice.
Your "ServiceNo" text box on the main form is wrong, make all your "ServiceNo" text boxes visible and watch what happens after you input your data in the "ServiceNo" text box on the main form. As soon as you input data in that box on the main form you should see your "ServiceNo" box on your subforms correspond to the one on the main form, it doesn't. All linked items between the main form and subforms should correlate(?).
I will try and attach an example database.
Get it to work without the code first and then tweak to your preferences.

 

[Navyguy]

WindSailor

I tried your forms and they worked great. When I look at what I have, I thought I have the same thing. I must be missing something pretty basic. I thought it may have been my relationships, but I changed them on yours and they worked fine also.

I did the visible ServiceNumbers on the subforms when trying to problem solve this and they appeared to work OK so I thought that my linking was OK. I will check again. I think there just is a minor problem with this and it is likely so clear that I am missing it.

Thanks for your help so far.