Forms will not allow new records

[vickimurray]

I have a new table--Candidates. The table does not have any relationships but does query two other tables to look up date (example: enter zipcode and it fills in City, County and State from that table). Currently there are only two sample records in the database--both were entered in the tables datasheet view with no problem. The only index is the primary key--no duplicates allowed.



I have created a new form to enter records and/or view existing records. It does view the two existing records but when I try to enter a new record, no matter what I enter I get the message "The changes 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."
I have tried everything that I can think of and cannot for the life of me figure out why I am getting this message. Any help???

[nick404]

You are getting the error message because you must be trying to enter data that coincides with an already existing/entered primary key. What field is your primary key and what data type is it? I would recommend just having an ID field with autonumber for your PK and this will ensure you don't get that message.

[vickimurray]

Currently my primary field is an ID of Text--7 characters created by the first 4 letters of the last name + first letter of first name + 2 (incrementing) digits. I have used this same format in another unrelated table. I tried adding an autonumber field as primary and changing this format (thinking that the indexes for the two table might be a problem) but that did not solve the problem. The test data that I have entered is nothing like the first to records and it does not matter how I change it

[nick404]

Two indexes for the same table is OK, so long as you search on the non-primary key index at some point in your db (like in a query- otherwise it is useless and taking up memory).

You are sure you have no relationships built in between fields?

Perhaps you can upload the db for viewing? Remove any sensitive data if applicable, (maybe not since you said it is dummy data), run a C & R, zip db, and attach

[vickimurray]

I removed all the queries and reports and have some sample data for all tables and forms. My problem is with the "1stLook Candidates" form. Thanks for your time and assistanceBusiness Connections--sample.zip
I hope that I attached this correctly.

[nick404]

Alrighty, here is the issue I was able to locate...

So your form's record source is a derived from a query connected 3 tables on similar fields- no problem, the table of Zipcodes, the 1st look candidates table, and a third table of industry names, correct?

In your form the control source for your '#1 Industry' textbox under Work Experience Overview is 'JFIndustryName'. This is the primary key for your table ZZ JFIndustryList. You will want to switch the control source to WkExp1Industry since it seems those are the 'connected' fields (from the query builder) and WkExp1Industry is in your 1st Look Candidates table (where I assume you want the data to be stored). The 'JFIndsutryName' should only be used as a reference for the combo box not the control source. This is where you are getting that error message from. When I switched it on the db copy you gave I was able to enter records no problem.

Some images to aid in understanding:

Query Builder Image: Showing the two related fields you will want to switch as control sources for your ComboBox



In the Form, switch JFIndustryName as control source to WkExp1Industry:

[vickimurray]

If I am following correctly your directions, it is not having the desired results. Each candidate can list up to four past work experiences. Each work experience will identify an industry for that work experience with the associated job title. When I changed the control source to JFIndustryname, it entered the industry that was selected into all four work experience listings and into the work experience listings for the existing candidates. I need the dropdown to show the JFIndustryName listing, select one for the WkExp1Industry field, enter an associated job title into WkExp1JobTitle, then go to the same process for work experience #2, #3 and finally #4.
Does that make sense--I know what I want just not sure I'm explaining it correctly. The JFIndustryList table is just a lookup table for these four fields.

OK, I just took the FORM query builder and removed the JFIndustryList from the query. I have the Control source as the field I want the data saved in and then the select query is from the JF Industry Name field and it's working now.

How do I mark my thread as solved? I can't seem to find that.

[nick404]

OK, I just took the FORM query builder and removed the JFIndustryList from the query. I have the Control source as the field I want the data saved in and then the select query is from the JF Industry Name field and it's working now.

Precisely what I was attempting to say! Although may have gotten lost in my post haha
Glad you have it working now!