ACCESS FEED: Re: [MS_AccessPros] Possible key violations question.

John,

So I took the query and just added the ID from the vacancies table and am trying to append to the vacantID in the trackingcomplete table with the form reference and I get the error. Only one field and I get the error. I changed the fields to both text and still nothing works.

Jim Wagner
________________________________

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, June 19, 2013 11:13 AM
Subject: RE: [MS_AccessPros] Possible key violations question.

Jim-

You can't insert a text field into a number field unless the text field contains only a number. If the number is a foreign key pointing perhaps to a lookup table, that could be the problem. You would have to fix your incoming query to join with the lookup table on the text and then insert the number that matches from the lookup.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Wednesday, June 19, 2013 6:28 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Possible key violations question.

John,

So I went through the query results and did not see anything that was null that should not be null. Although I did notice that in the following tables these differences. Could the differences between the text and number be the cause? But that does not explain why it sometimes works and sometimes does not.

Jim Wagner

TrackingComplete table
PSPosition = Text which is the NEW POS in the Vacancies table PositionNumber = Number which is the OLD POS in the Vacancies table

Vacancies table
NEW POS = Text Appends to the PSPosition field in the TrackingComplete table OLD POS = Text Appends to the PositionNumber field in the TrackingComplete table

Jim Wagner
________________________________

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, June 19, 2013 8:30 AM
Subject: RE: [MS_AccessPros] Possible key violations question.

Jim-

There's some index in your table that the incoming data is violation. When I asked what the SELECT returned, I meant for you to run that query and take a close look at the data. Some field is Null that shouldn't be or some Foreign Key value is invalid, or some duplicate index is being violated.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Wednesday, June 19, 2013 5:12 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Possible key violations question.

Yesterday we changed the property indexed to no and allowed duplicates for the fields and it created duplicate records as expected. But the macro went through without any errors.

Jim Wagner
________________________________

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, June 19, 2013 7:56 AM
Subject: RE: [MS_AccessPros] Possible key violations question.

Jim-

It's saying that you're either trying to insert a duplicate Primary or
Unique key, or a Primary Key value that has a null in it, or a Foreign Key
that has no matching value in its related parent table. What does:

SELECT Vacancies.ID, Vacancies.ACTION, Vacancies.[OLD POS], Vacancies.[NEW
POS], Vacancies.AFFILIATEID, Vacancies.DEPT, Vacancies.VACATEDBY,
Vacancies.[VAC JOB TITLE], Vacancies.[JOB CODE], Vacancies.VACSTATEACCT,
Vacancies.VACSTATEACCT2, Vacancies.VACSTATEACCT3, Vacancies.VACSTATEFTE,
Vacancies.VACSTATEFTE2, Vacancies.VACSTATEFTE3, Vacancies.VACSTATESALARY,
Vacancies.VACSTATESALARY2, Vacancies.VACSTATESALARY3,
Vacancies.VACLOCALSALARY, Vacancies.VACLOCALSALARY2,
Vacancies.VACLOCALSALARY3, Vacancies.VACLOCALFTE, Vacancies.VACLOCALFTE2,
Vacancies.VACLOCALFTE3, Vacancies.VACLOCALACCT, Vacancies.VACLOCALACCT2,
Vacancies.VACLOCALACCT3, Vacancies.[DATE FILLED], Vacancies.FILLEDBY,
[VACSTATESALARY]+[VACSTATESALARY2]+[VACSTATESALARY3] AS
VacantSalaryPlanning, Vacancies.[FILLED COMMENTS],
Vacancies.FILLEDSTATEACCT, Vacancies.FILLEDSTATEACCT2,
Vacancies.FILLEDSTATEACCT3, Vacancies.FILLEDSTATEFTE,
Vacancies.FILLEDSTATEFTE2, Vacancies.FILLEDSTATEFTE3,
Vacancies.FILLEDSTATESALARY, Vacancies.FILLEDSTATESALARY2,
Vacancies.FILLEDSTATESALARY3, Vacancies.FILLEDLOCALSALARY,
Vacancies.FILLEDLOCALSALARY2, Vacancies.FILLEDLOCALSALARY3,
Vacancies.FILLEDLOCALFTE, Vacancies.FILLEDLOCALFTE2,
Vacancies.FILLEDLOCALFTE3, Vacancies.FILLEDLOCALACCT,
Vacancies.FILLEDLOCALACCT2, Vacancies.FILLEDLOCALACCT3, Vacancies.COMMENT,
Vacancies.student,
[FILLEDSTATESALARY]+[FILLEDSTATESALARY2]+[FILLEDSTATESALARY3] AS
FilledNewSalary, Vacancies.SalarySavings, Vacancies.Classification,
Vacancies.[Date of Action], Vacancies.status, Vacancies.Fte FROM Vacancies
WHERE (((Vacancies.ID)=[Forms]![Vacancy Input].[ID]));

... return?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of luvmymelody
Sent: Wednesday, June 19, 2013 4:49 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Possible key violations question.

Hello all,

Going back to post 103517
The error came back again. below is the error and breakdown of the table and
the SQL.

Any help would be appreciated.

Thank You

Jim Wagner

The Error
Microsoft Access set 0 field(s) to Null due to a type conversion failure,
and it didn't add 1 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations.
Do you want to run the action query anyway? ........

Button caption:
MOVE Record to Tracking History - deletes it from your vacant table
Embedded Macro:
Macro
mcro_AppendVacantRecordToTracking
SaveObject: Form, Vacancy Input
OpenQuery: qry_AppendVacancyRecordToTrackingComplete
Append query: Appends Vacancies table record to Tracking complete table
based on vacantID = [Forms]![Vacancy Input].[ID]

SQL for qry_AppendVacancyRecordToTrackingComplete
INSERT INTO TrackingComplete ( vacantID, ActionReq, PositionNumber,
PSPosition, AFFILIATEID, DeptCode, VacantName, VacancyJobTitle, [JOB CODE],
VacantStateAreaOrg, VacantStateAreaOrg2, VacantStateAreaOrg3,
VacantStateFTE, VacantStateFTE2, VacantStateFTE3, VACSTATESALARY,
VACSTATESALARY2, VACSTATESALARY3, VACLOCALSALARY, VACLOCALSALARY2,
VACLOCALSALARY3, VacantLocalFTE, VacantLocalFTE2, VacantLocalFTE3,
VacantLocalAreaOrg, VacantLocalAreaOrg2, VacantLocalAreaOrg3,
FilledEfftDate, FilledName, VacantSalaryPlanning, FilledComment,
FilledStateAreaOrg, FilledStateAreaOrg2, FilledStateAreaOrg3,
FilledStateFTE, FilledStateFTE2, FilledStateFTE3, FILLEDSTATESALARY,
FILLEDSTATESALARY2, FILLEDSTATESALARY3, FILLEDLOCALSALARY,
FILLEDLOCALSALARY2, FILLEDLOCALSALARY3, FILLEDLOCALFTE, FILLEDLOCALFTE2,
FILLEDLOCALFTE3, FilledLocalAreaOrg, FilledLocalAreaOrg2,
FilledLocalAreaOrg3, VacantComment, student, FilledNewSalary, SalarySavings,
Classification, [Date of Action], status, Fte ) SELECT Vacancies.ID,
Vacancies.ACTION, Vacancies.[OLD POS], Vacancies.[NEW POS],
Vacancies.AFFILIATEID, Vacancies.DEPT, Vacancies.VACATEDBY, Vacancies.[VAC
JOB TITLE], Vacancies.[JOB CODE], Vacancies.VACSTATEACCT,
Vacancies.VACSTATEACCT2, Vacancies.VACSTATEACCT3, Vacancies.VACSTATEFTE,
Vacancies.VACSTATEFTE2, Vacancies.VACSTATEFTE3, Vacancies.VACSTATESALARY,
Vacancies.VACSTATESALARY2, Vacancies.VACSTATESALARY3,
Vacancies.VACLOCALSALARY, Vacancies.VACLOCALSALARY2,
Vacancies.VACLOCALSALARY3, Vacancies.VACLOCALFTE, Vacancies.VACLOCALFTE2,
Vacancies.VACLOCALFTE3, Vacancies.VACLOCALACCT, Vacancies.VACLOCALACCT2,
Vacancies.VACLOCALACCT3, Vacancies.[DATE FILLED], Vacancies.FILLEDBY,
[VACSTATESALARY]+[VACSTATESALARY2]+[VACSTATESALARY3] AS
VacantSalaryPlanning, Vacancies.[FILLED COMMENTS],
Vacancies.FILLEDSTATEACCT, Vacancies.FILLEDSTATEACCT2,
Vacancies.FILLEDSTATEACCT3, Vacancies.FILLEDSTATEFTE,
Vacancies.FILLEDSTATEFTE2, Vacancies.FILLEDSTATEFTE3,
Vacancies.FILLEDSTATESALARY, Vacancies.FILLEDSTATESALARY2,
Vacancies.FILLEDSTATESALARY3, Vacancies.FILLEDLOCALSALARY,
Vacancies.FILLEDLOCALSALARY2, Vacancies.FILLEDLOCALSALARY3,
Vacancies.FILLEDLOCALFTE, Vacancies.FILLEDLOCALFTE2,
Vacancies.FILLEDLOCALFTE3, Vacancies.FILLEDLOCALACCT,
Vacancies.FILLEDLOCALACCT2, Vacancies.FILLEDLOCALACCT3, Vacancies.COMMENT,
Vacancies.student,
[FILLEDSTATESALARY]+[FILLEDSTATESALARY2]+[FILLEDSTATESALARY3] AS
FilledNewSalary, Vacancies.SalarySavings, Vacancies.Classification,
Vacancies.[Date of Action], Vacancies.status, Vacancies.Fte FROM Vacancies
WHERE (((Vacancies.ID)=[Forms]![Vacancy Input].[ID]));

Tables
Vacancies table
ID = AutoNumber
Field Size = Long Integer
New Values = Increment
Indexed = Yes (No Duplicates)

TrackingComplete table
ID = AutoNumber
Field Size = Long Integer
New Values = Increment
Indexed = Yes (No Duplicates)

vacantID = Number
Field Size = Long Integer
Decimal Places = Auto
Required = No
Indexed = Yes (No Duplicates)

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]