Table Not Updating Correctly

[wildthingcg]

When entering data into forms all tables update correctly except Employers. See EmployerID 1 and 11. Data should all be for EmployerID 1, the JobCardNbr 11 is being entered in as EmployerID 11. Any help in fixing this would be greatly appreciated.

Thanks,


Cheryl

[June7]

Not understanding the issue. As far as I can tell the Employers form and its subform are working properly.

[rpeare]

I'm stumped what the problem is too, like june the form you mention is correctly applying the jobcard number in the correct field in your JOBS table.

[wildthingcg]

June7 & rpeare thank you for your replies. I'm really new at this so sorry if I've confused you at all.

If you go to the Employers FORM you will see that EmployerID 1 has data entered in the Jobs SUBFORM for JobCardNbr 11 (which corresponds to JobsID 1 in the TABLES). If you then go to the different TABLES for all of the SUBFORMS, you will see that they all show the correct EmployerID, JobsID and JobCardNbr. Finally, go to the Employers TABLE look at EmployerID 1 and there is nothing entered. But if you go to EmployerID 11, you will see that is where the data is entered. The 11 SHOULD represent the JobCardNbr NOT the EmployerID #.

I hope this makes more sense.

Thank you for your help!

Cheryl

[rpeare]

Ok, well the problem may be that you can have the same JobCardNbr for multiple customers. What you really should be doing is linking all the sub forms on your tab control by JOBID. JobID is the unique identifier for your JOBS table not the JobCardNbr. The JOBID is in turn linked to your EMPLOYERS table via the EmployerID field.

[wildthingcg]

rpeare,

Actually, there should be only one JobCardNbr per Job (a Job is the Jobs SUBFORM plus the Tabbed SUB-SUBFORMS, Benefits, HowToApply, etc.) and no duplicate JobCardNbr(s) between Employers.

Cheryl

[rpeare]

Ok, well... here's what I think happened. It looks like when you originally set up your relationships you joined Employers and Jobs from EmployerID to JobCardNbr, then at some point realized that was wrong and re-established the link to the correct field but the relationship, for some reason, is not correctly updating when you look at your table. So here is what I did.

I created a new table copied the structure of the Employers and called it EmployersNew

Created an append query with this statement:

INSERT INTO EmployersNew ( EmployerName, Address, City, StateCode, ZipCode, PhoneNbr, ExtNbr, FaxNbr, EmailAddress, WebsiteAddress, PriContactFirstName, PriContactLastName, SecContactFirstName, SecContactLastName, Marketing, DoNotCall, Notes, ReferredByFirstName, ReferredByLastName )
SELECT Employers.EmployerName, Employers.Address, Employers.City, Employers.StateCode, Employers.ZipCode, Employers.PhoneNbr, Employers.ExtNbr, Employers.FaxNbr, Employers.EmailAddress, Employers.WebsiteAddress, Employers.PriContactFirstName, Employers.PriContactLastName, Employers.SecContactFirstName, Employers.SecContactLastName, Employers.Marketing, Employers.DoNotCall, Employers.Notes, Employers.ReferredByFirstName, Employers.ReferredByLastName
FROM Employers
ORDER BY Employers.EmployerID;

When you open the table the datasheet should have no + sign in the far left column.

Go to the Relationships setup, link the EmployersNew to Jobs through the EmployerID

Delete the relationship between Employers and Jobs.

Open up the table EmployersNew, verify that when you hit the + sign it's showing you the correct related records.

Rename the table Employers to something else
Rename the table EmployersNew to Employers

Everything should work as you originally intended (I didn't do full testing but it appeared to for me)

I *never* set up relationships in my database so someone who knows more than I do may be able to give you an easier way to do this.

[wildthingcg]

Thank you, rpeare!

I will try this and let you know what the outcome is. I really appreciate your help.

Cheryl

[wildthingcg]

rpeare,

Works like a charm. You ROCK!

Thank You,

Cheryl