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

Rabu, 19 Juni 2013

Re: [MS_AccessPros] Possible key violations question.

John,

It returns the record selected on the form as requested. WHERE (((Vacancies.ID)=[Forms]![Vacancy Input].[ID]));

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]