Problem with Append Query

[Tanya]

Hi I have a commandbutton which invokes an append query. The idea is when I add a student to a class the student is allocated a number of units, elements and competencies which they need to become competent in. I could run the append query for the whole class without paramater for studentclassid but then the query would append all students in the class even if they had already been appended.

Here is the code:

CurrentDb.Execute ("qryAppendUnitstoStudentClass"), dbFailOnError

I get the following error:
Run-time error '3061' Too few paramaters expected 2


When I tried the openquery method no rows were appended, however there were no errors:

'DoCmd.OpenQuery "qryAppendUnitstoStudentClass"
'DoCmd.Close acDefault, "qryAppendUnitstoStudentClass"

My sql skills are very poor but here is a copy of the append query in sql:

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]));


Grateful for any support.

regards
Tanya

 

[Tanya]

I have been fiddling with the append query and have found I could do away with a couple of the tables, here is what I have come up with:

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

Now.. when I run the query with a fictitious studentclassID as though I am taking the the next autonumber I append '0' rows which is the exact problem I have been having with the following code:

'DoCmd.OpenQuery "qryAppendUnitstoStudentClass"
'DoCmd.Close acDefault, "qryAppendUnitstoStudentClass"

Which makes me think, it has something to do with the fact that I am dealing with a new record that hasn't been saved yet.

Any thoughts anyone?

 

[RainLover]

Tanya

Let's fix the Parameters problem first.

What is happening is that your VBA Code cannot Evaluate the Values located on the Form. For some reason it simply can't see the values.

So place the Following Code in a Module.


Public Function fRunQuery(NameOfQuery)
On Error GoTo Err_Func

Dim qdf As querydef
Dim prm As Parameter

Set qdf = CurrentDb.QueryDefs(NameOfQuery)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute dbFailOnError

Exit_Func:
Exit Function

Err_Func:
Select Case Err.Number
Case 3265
MsgBox "No query of this name exists.", vbCritical + vbOKOnly, _
"Query Not Found..."
Case 2450
MsgBox "The form needed to run this query is not open.", vbCritical + vbOKOnly, _
"Form Not Open..."
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume Exit_Func
End Function


Then to run the query use

fRunQuery "qryAppendUnitstoStudentClass"

Hope this helps in part.

 

[Tanya]

Hi
Thank you for your input. I placed the code as you said in module1 and added the code fRunQuery "qryAppendUnitstoStudentClass" and nothing happened. It was like there was no code attached to the commandbutton.

Any thoughts?
Regards
Tanya

 

[Tanya]

Here is a copy of the db, perhaps it will help.

From frmClasses, there is a commandbutton 'Add Students' which opens another form frmAddStudentstoclass which has the commandbutton in question.

cheers
Tanya

 

[RainLover]

Tanya

I assume you replaced your code

CurrentDb.Execute ("qryAppendUnitstoStudentClass"), dbFailOnError

with

fRunQuery "qryAppendUnitstoStudentClass"

If you did and nothing happened then you have fixed the Parameter problem. As in you did not get an error message. If you want to prove that the code is working place a temporary MsgBox after the code.

Just noticed that you posted a copy. I will have a look.

 

[Tanya]

Yes, I did replace the code, and as you suggested, placed a temporary message after

fRunQuery "qryAppendUnitstoStudentClass"

and the message appeared without error.

which is really really weird

 

[Tanya]

Do you think it is because the form is in new record mode?

 

[RainLover]

Tanya

I looked at the query. It has Criteria that looks at frmAddStudentstoClass.

So I opened that Form and it has no records. This is because you have the Properties in the > Data tab > Data Entry set to yes. This causes the form to always open to a new record with no data.

There must be data for the criteria.

 

[RainLover]

You answered the question for yourself before I finished replying.

 

[RainLover]

Yes, I did replace the code, and as you suggested, placed a temporary message after

fRunQuery "qryAppendUnitstoStudentClass"

and the message appeared without error.

which is really really weird

It looks like the code is working but the query has no records.

 

[RainLover]

Tanya

You can view the results of your query to see what is going to happen.

Open the Query in design View then Change the view to Datasheet. This will not update anything just allows you to see the results.

 

[Tanya]

Hi
I have changed the view to datasheet as you suggested and it works fine with studentid where the number exists. However when I use the next number i.e. for a new record the result is blank. Which again leads me to believe it must have something to do with the form being in new record mode?
Any suggestions on another method of achieving the same outcome would be greatly appreciated.
Kind regards
Tanya

 

[Tanya]

Perhaps I need to tacktle this a new way...

I am thinking of creating a tickbox which when selected runs the append query, the tick box would then indicate to me that I have appended the outcomes to the student, thereby completing enrolment.

I will play with this idea for now.

 

[RainLover]

Tanya

You need to look at this append Query. What you have created is know as a
Cartesian Query.
I think you need to add the table tblSubjects.
To see what I mean save the query as a standard Select Query. then open it. It will show you every possible combination available between the two sets of queries.
Then add the table tblSubjects then check the new results.
When you are ready please post an update of where you are up to.

EDIT

Remove the Criteria first.

 

[Tanya]

Ok.. here is the update,
I have created a yes/no field and an afterupdate event:
Private Sub chkEnrolled_Click()

DoCmd.OpenQuery "qryAppendUnitstoStudentClass"
DoCmd.Close acDefault, "qryAppendUnitstoStudentClass"
End Sub

problem now is it is not finding the studentclassID [NB: I have since added it to the datasource], therefore I am presuming updating the yes/no field for a particular record does not select the whole record, hence my new delema.

so.. new question, how do I select record with an event that I can use the data in other fields?

I hope this makes sense?

Cheers
Tanya

 

[RainLover]

As a by the way.

Do not use spaces in Names. Also do not use strange characters like %

Also please check my edit on my previous post

 

[Tanya]

I did as you said, and I remembered to remove the paramaters.
The result was:
without subject table; all subjects
with subject table; only subject 1
By my way of thinking the result without tblsubjects was accurate.
Please excuse me, but I don't quite follow what you are trying to achieve?

 

[RainLover]

It therefore would appear that my thing was incorrect.

If you are happy with the results then lets proceed along those lines.

 

[RainLover]

Tanya

In your Query qryAppendUnitsToStudenClass you have in the second field StudentID where it is equal to ClassID

I think this may be incorrect