Question Not sure where to begin (1 Viewer)

[Tanya]

Hi I have a db I have been working on for keeping record of student competencies and guess what I want to do will involve maketable queries and intricate forms.
The db works at the moment but it is very tedious selecting each student, then allocating units, elements, competencies.
What I am looking for is a way to select the students in the class to allocate all the units and elements, performance descriptors that they will need to achieve competency in one click.
Then I need to select each individual student in the class and tick that hey are indeed competent in that element.
I have attached my work so far. The form Add Competency is working at the moment as I said but is tedious, you need to select class then create a new record for competency, then select student, then unit, then element then performance criteria, then tick that they are competent.
Any suggestions on where to begin with this would be greatly appreciated.
Regards
Tanya

 

[pl456]

What I would do is create a default set of values that are applied when you add a new student.

 

[Tanya]

Hi
I have thought long and hard about your suggestion and have come up with a query which will append units to a class of students. However I have no idea how to make this happen by default when a student is added to a class? I was thinking along the lines of a list box with multiple selection, however I have not used either of these before.

qryAppendUnitstoStudentClass

Any suggestions would be greatly appreciated.

I have attached a copy of my db of where I am up to, as you will see this is a small but crucial part of a greater project for my classroom management.

Cheers
Tanya

 

[John Big Booty]

You could set the Allow Additions Data property of the form, on which new students would be added, to No. Then on the form put a Command button labelled Add New Student. Behind this button you could then switch the that property to yes, fire your query, then switch the property back to No. Your user could then fill in the missing info.

 

[Tanya]

Thank you John, that sounds like a good solution, I've been looking at it from a different angle, at present my students are added by selecting from a dropdown box as you have probably seen from earlier posts. I will play with it and see.
thanks again.
Tanya

 

[John Big Booty]

Sorry should have mentioned above as you will be using code you will need to set the property to True or False rather than Yes or No.

 

[Tanya]

John
I haven't run an update query and new record from a command before. could you offer me some guidance here please.
Because my list of students is on a subform and I don't want to see all the fields ie studentclassID & ClassID [I would like to inherit classid and create studentclassid from available studentid's, do I need to create a new form to bring up? I hope this makes sense? That is I want to search for student to add.

Tables are as follows:

tblclasses
classID

tblstudentclasses
studentclassID
studentID

tblstudents
studentID


cheers
Tanya

 

[John Big Booty]

Given that we are talking about adding a new record you will need to use an append query. You should just be able to set it up to append the defaults to your student table and in doing so create a new record which will then leave the user to fill in the blanks and change any of the defaults if necessary.

 

[Tanya]

Ok, I think I understand now, So I create an append query which uses for its criteria text on a form i.e. [frmAddStudentsClass].Form![StudentClassID] then run that query from a commandbutton. Is this what you are suggesting, or do I need to run the sql for the query in vba?

 

[John Big Booty]

I'd run the query from the command buttons (that just my current preference though). However I'm sure you could just as easily run the SQL in VBA from a command button.

 

[Tanya]

Hi John
I've made progress but having a little difficulty with the code. My commandbutton is on the first Tab of frmClasses and I cannot get the code to find the field [classID].
I have tried
stLinkCriteria = "[ClassID]=" & Me!frmClasses.Form[ClassID]
&
stLinkCriteria = "[ClassID]=" & Me!Forms.frmClasses[ClassID]

and getting frustrated...

Here is my code

Private Sub CmdAddStudents_Click()
On Error GoTo Err_CmdAddStudents_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmAddStudentstoClass"

stLinkCriteria = "[ClassID]=" & Me![ClassID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_CmdAddStudents_Click:
Exit Sub
Err_CmdAddStudents_Click:
MsgBox Err.Description
Resume Exit_CmdAddStudents_Click
End Sub

Any suggestions?

Also, when I run the query all appears to work, but it doesn't add any new rows, so something is going wrong there also.

regards
Tanya

 

[Tanya]

Here is a copy of the db, may help in understanding the problem.

thanks in advance

 

[John Big Booty]

Your Add Student Button works fine. What you need to do is to add the following to the OnClose event of frmAddStudentstoClass;

    Forms!frmclasses.Refresh

 

[Tanya]

I wish it were that easy John, the problem has grown :|
http://www.access-programmers.co.uk/forums/showthread.php?p=793733#post793733

 

[John Big Booty]

OK then, I'll leave it in Ned's capable hand's then

 

[Tanya]

Thanks John

 

[RainLover]

John

Please feel free to jump in.