Run Append Query from ControlButton on Form

[Tanya]

Hi
I have a problem with an append query, the commandbutton is on a form in add
mode and I need to pick up parameter values from the form. I have also pasted
a copy of the append query:

Private Sub CmdNew_Click()
'Run Append Query

DoCmd.OpenQuery "qryAppendUnitstoStudentClass"

DoCmd.Close acDefault, "qryAppendUnitstoStudentClass"

'Begin New Record
DoCmd.GoToRecord , , acNewRec

End Sub


Append Query:
INSERT INTO tblCompetency ( StudentClassID, SubjectID, UnitID, ElementID,
PerformanceID )
SELECT tblStudentsClasses.StudentClassID, tblSubjects.SubjectID,
tblUnit.UnitID, tblElements.ElementID, tblPerformanceCriteria.PerformanceID
FROM ((tblSubjects INNER JOIN tblUnit ON (tblSubjects.SubjectID =
tblUnit.SubjectID) AND (tblSubjects.SubjectID = tblUnit.SubjectID)) INNER
JOIN (tblClasses INNER JOIN (tblStudents INNER JOIN tblStudentsClasses ON
tblStudents.StudentID = tblStudentsClasses.StudentID) ON tblClasses.ClassID =
tblStudentsClasses.ClassID) ON tblSubjects.SubjectID = tblClasses.SubjectID)
INNER JOIN (tblElements INNER JOIN tblPerformanceCriteria ON
tblElements.ElementID = tblPerformanceCriteria.ElementID) ON tblUnit.UnitID =
tblElements.UnitID
WHERE
(((tblStudentsClasses.StudentClassID)=[forms]![frmAddStudentstoClass]![StudentClassID]) AND ((tblSubjects.SubjectID)=[Forms]![frmClasses]![SubjectID]));

What is happening is, the code is not appending any rows to my table. The
message indicate '0' rows.

I've been playing around with the WHERE values without success and think it
must be something to do with the code.

Thanks in advance.
Tanya

 

[Douglas J. Steele]

Instead of using DoCmd, a better way to run your query is:

CurrentDb.QueryDefs("qryAppendUnitstoStudentClass").Execute dbFailOnError

Note that you won't get any message as to how many rows are appended using
that. To find that, you can use:

Dim qdfAppend As DAO.QueryDef

Set qdfAppend = CurrentDb.QueryDefs("qryAppendUnitstoStudentClass")
qdfAppend.Execute dbFailOnError
MsgBox "You appended " & qdfAppend.RecordsAffected & " records."


Note too that I can't guarantee that making this change will cause your
query to append data! My advise would be to ensure that the subselect works:

SELECT tblStudentsClasses.StudentClassID, tblSubjects.SubjectID,
tblUnit.UnitID, tblElements.ElementID, tblPerformanceCriteria.PerformanceID
FROM ((tblSubjects INNER JOIN tblUnit ON (tblSubjects.SubjectID =
tblUnit.SubjectID) AND (tblSubjects.SubjectID = tblUnit.SubjectID)) INNER
JOIN (tblClasses INNER JOIN (tblStudents INNER JOIN tblStudentsClasses ON
tblStudents.StudentID = tblStudentsClasses.StudentID) ON tblClasses.ClassID
=
tblStudentsClasses.ClassID) ON tblSubjects.SubjectID =
tblClasses.SubjectID)
INNER JOIN (tblElements INNER JOIN tblPerformanceCriteria ON
tblElements.ElementID = tblPerformanceCriteria.ElementID) ON tblUnit.UnitID
=
tblElements.UnitID
WHERE
(((tblStudentsClasses.StudentClassID)=[forms]![frmAddStudentstoClass]![StudentClassID])AND ((tblSubjects.SubjectID)=[Forms]![frmClasses]![SubjectID]));--Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)"Tanya" <[email protected]> wrote in messagenews:[email protected]...> Hi> I have a problem with an append query, the commandbutton is on a form inadd> mode and I need to pick up parameter values from the form. I have alsopasted> a copy of the append query:>> Private Sub CmdNew_Click()> 'Run Append Query>> DoCmd.OpenQuery "qryAppendUnitstoStudentClass">> DoCmd.Close acDefault, "qryAppendUnitstoStudentClass">> 'Begin New Record> DoCmd.GoToRecord , , acNewRec>> End Sub>>> Append Query:> INSERT INTO tblCompetency ( StudentClassID, SubjectID, UnitID, ElementID,> PerformanceID )> SELECT tblStudentsClasses.StudentClassID, tblSubjects.SubjectID,> tblUnit.UnitID, tblElements.ElementID,tblPerformanceCriteria.PerformanceID> FROM ((tblSubjects INNER JOIN tblUnit ON (tblSubjects.SubjectID => tblUnit.SubjectID) AND (tblSubjects.SubjectID = tblUnit.SubjectID)) INNER> JOIN (tblClasses INNER JOIN (tblStudents INNER JOIN tblStudentsClasses ON> tblStudents.StudentID = tblStudentsClasses.StudentID) ONtblClasses.ClassID => tblStudentsClasses.ClassID) ON tblSubjects.SubjectID =tblClasses.SubjectID)> INNER JOIN (tblElements INNER JOIN tblPerformanceCriteria ON> tblElements.ElementID = tblPerformanceCriteria.ElementID) ONtblUnit.UnitID => tblElements.UnitID> WHERE>(((tblStudentsClasses.StudentClassID)=[forms]![frmAddStudentstoClass]![StudentClassID]) AND ((tblSubjects.SubjectID)=[Forms]![frmClasses]![SubjectID]));>> What is happening is, the code is not appending any rows to my table. The> message indicate '0' rows.>> I've been playing around with the WHERE values without success and thinkit> must be something to do with the code.>> Thanks in advance.> Tanya

 

[Tanya]

Hi Douglas
Thank you for this suggestion. However I am now getting an error message:

Runtime error '3061' Too few paramaters expected 2

Any suggestions?

Cheers
Tanya

 

[Douglas J. Steele]

Shoot. That's right. The Execute method doesn't automatically resolve
parameters for you. You'll need something like:

Dim qdfAppend As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfAppend = CurrentDb.QueryDefs("qryAppendUnitstoStudentClass")

For Each prmCurr in qdfAppend.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr

qdfAppend.Execute dbFailOnError
MsgBox "You appended " & qdfAppend.RecordsAffected & " records."


Sorry about that.