G
Guest
I would need a little assistance in a applying data via an append query
function and hope that someone has been there and done this.
My plight is this:
I have a primary form named frmOrgsInstMain which has a link command button
on it which calls a form named frmOrgLink.
Form frmOrgLink has no input record source. It does have 4 zones on this
form as follows:
a: an unbound text box field named txtname with a source control value of
=Forms!frmOrgsInstMain!subfrmOrgs!OrgName.
b: an unbound textbox field named txtOrgIDLookUp with a source control value
of
=Forms!frmOrgsInstMain!subfrmOrgs!ORG_ID
c: An unbound listbox named list2 with a row source value of:
SELECT Inst.INST_ID, [LastName] & ', ' & [FirstName] AS Name,
Inst.DLNumber, Inst.City FROM Inst ORDER BY [LastName] & ', ' & [FirstName];
Which build a pick list of Data (lastname, firstname, DL Number and City
d: An subform named subfrmlink with a source object named subfrmOrgLink
which when populated has data of instructor names that are associated with
the Organization (name of txtname – field A) and (organization ID of ORG_ID
field B) which are present at the time of data display. (I’ll call this data
ORGList).
The left pick list (c) is available for the user to scroll through data is
not in the current display of the ORGList data (d).
When the user finds a name that they wish to add to it, the user needs to
double click on the selected name, this in turn is to add the selected name
of the person from list2 box (c) and add it to ORGList area (d).
At the same time, I want to run an append query that will add the value that
is currently in the form field B (ORG_ID) and the selected Instructors ID
(INST_ID) value (from c) to a third table named LinkInstOrg. LinkInstOrg is
being used as a pivottable function.
I thought the best way would be to use the combination of an update query
named qryUpdtLinkInstOrg, which has the following sql :
INSERT INTO LinkInstOrg ( INST_ID, ORG_ID )
SELECT forms!frmOrgsInstMain!subfrmOrgs!Inst_ID AS INST_ID,
forms!frmOrgsInstMain!subfrmORgs!ORG_ID AS ORG_ID;
In the subform named frmOrgLink, when the user double clicks on the selected
name that they wish to add, I’m thought of using code to
DoCmd runMacro mcoUpdtLinkInstOrg
which in turn runs query qryUpdtLinkInstOrg which I hoped would take two
values and add them to the table LinkInstOrg.
Problem is, its not working, I get the following message of:
Driver Improvement, DI-DDC & MVAPC set 0 field(s) to Null due to a type
conversion failure, and it didn’t add 0 record(s) to the table due to key
violations, 0 record(s) due to lock violations, and 1 record(s) due to
validation run violations.
Do you want to run the action query anyway? To ignore the error(s) and run
the query, click yes, for an explanation of the cause of the violations,
click help.
The table being appended is named LinkInstOrg, and its 3 fields are:
LINK_ID, autonumb, primary key
INST_ID = required field, dupes ok,
ORG_ID = required field, dupes ok.
Can some one assist and identify what I’ve done wrong and maybe an easier
way to do this in vb code?
Thanks,
function and hope that someone has been there and done this.
My plight is this:
I have a primary form named frmOrgsInstMain which has a link command button
on it which calls a form named frmOrgLink.
Form frmOrgLink has no input record source. It does have 4 zones on this
form as follows:
a: an unbound text box field named txtname with a source control value of
=Forms!frmOrgsInstMain!subfrmOrgs!OrgName.
b: an unbound textbox field named txtOrgIDLookUp with a source control value
of
=Forms!frmOrgsInstMain!subfrmOrgs!ORG_ID
c: An unbound listbox named list2 with a row source value of:
SELECT Inst.INST_ID, [LastName] & ', ' & [FirstName] AS Name,
Inst.DLNumber, Inst.City FROM Inst ORDER BY [LastName] & ', ' & [FirstName];
Which build a pick list of Data (lastname, firstname, DL Number and City
d: An subform named subfrmlink with a source object named subfrmOrgLink
which when populated has data of instructor names that are associated with
the Organization (name of txtname – field A) and (organization ID of ORG_ID
field B) which are present at the time of data display. (I’ll call this data
ORGList).
The left pick list (c) is available for the user to scroll through data is
not in the current display of the ORGList data (d).
When the user finds a name that they wish to add to it, the user needs to
double click on the selected name, this in turn is to add the selected name
of the person from list2 box (c) and add it to ORGList area (d).
At the same time, I want to run an append query that will add the value that
is currently in the form field B (ORG_ID) and the selected Instructors ID
(INST_ID) value (from c) to a third table named LinkInstOrg. LinkInstOrg is
being used as a pivottable function.
I thought the best way would be to use the combination of an update query
named qryUpdtLinkInstOrg, which has the following sql :
INSERT INTO LinkInstOrg ( INST_ID, ORG_ID )
SELECT forms!frmOrgsInstMain!subfrmOrgs!Inst_ID AS INST_ID,
forms!frmOrgsInstMain!subfrmORgs!ORG_ID AS ORG_ID;
In the subform named frmOrgLink, when the user double clicks on the selected
name that they wish to add, I’m thought of using code to
DoCmd runMacro mcoUpdtLinkInstOrg
which in turn runs query qryUpdtLinkInstOrg which I hoped would take two
values and add them to the table LinkInstOrg.
Problem is, its not working, I get the following message of:
Driver Improvement, DI-DDC & MVAPC set 0 field(s) to Null due to a type
conversion failure, and it didn’t add 0 record(s) to the table due to key
violations, 0 record(s) due to lock violations, and 1 record(s) due to
validation run violations.
Do you want to run the action query anyway? To ignore the error(s) and run
the query, click yes, for an explanation of the cause of the violations,
click help.
The table being appended is named LinkInstOrg, and its 3 fields are:
LINK_ID, autonumb, primary key
INST_ID = required field, dupes ok,
ORG_ID = required field, dupes ok.
Can some one assist and identify what I’ve done wrong and maybe an easier
way to do this in vb code?
Thanks,