Need some help.

[Freerange]

Hi all, im new to this place (as well as access!) so if im posting in the wrong place, sorry!

Ok, my problem is to do with my project for school. I am building a system using VBA and access to re allocate students to classes if they want to drop a subject and pick a new one up, with the option for the program to rearrange their current subjects to make the new one fit.
Specificaly, the problem I have is to do with the last stage of implimenting this - (i've been told im bad at explaining this, so bear with me) I need to have some code execute when something happens on a diffrent form. The code i need to run involves the me.something.something stuff, so it realy cant be run on the other form. I tried creating a global variable which would change its value when the criteria is met - but ofcourse, there is no event on which to run this from (i.e. cant be on open, as this is already used (plus the form must stay open, or atleast save the data on it at the time closed)). I also tried using inputboxes etc, but this was a bit of a none starter . My main problem is that i have a limmited knowledge of the commands which can be used in vba, and as such i dont realy know how to solve my problem !
So my question to you is (obviosly) what can i do to make this work !


Thanks in advance, Freerange

[Ken Puls]

Hi Freerange,

Welcome to VBAX! Just so you know, we generally don't do homework or school projects, as per our forum rules here. We are all believers that you should earn the benefits of the education you paid for, and that doesn't happen if we do it for you. Having said that, we will help guide you through it, we just want to make sure we don't end up doing it all. (It does sound to me like you are asking for help in your attempts, and this is not meant to offend, but we do try to clear that up in advance! )

In order to help you out, I think that our experts will need some more information from you. Can you give us some more details of what the forms and controls are called, and show us what you've tried that didn't work, and wherever possible try to give descriptive examples of what you think should happen?

[uvba]a[/uvba]

There is also an option to add a file if you can't explain it quite clearly enough. Go to the "Go Advanced" tab and scroll down till you find the "Manage Attachments" button.

Being that it is in a homework situation, do be prepared for a simple "you want this event" type answer as well. No disrespect will be intended from anyone, we just want to make sure we help you learn.

I hope you find this helpful.

Cheers,

[Freerange]

Ok, first i will explain what i have done, then post the code for it (though i wont bore you with the whole system :P).
The part which i have a problem with is:

[vba]
Dim X As Integer
Dim J As Integer
Dim I As Integer
Dim strSQL As String

Me.comboLevel.Value = ""
Me.comboSubject.Value = ""
Me.lstAddClassesStore.RowSource = ""
Me.lstClassesStore.RowSource = ""


DoCmd.SetWarnings False
strSQL = "DELETE tblTempStudClass.* FROM tblTempStudClass"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects1.* FROM tblTempSubjects1"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects2.* FROM tblTempSubjects2"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses.* FROM tblTempPossibleClasses"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses2.* FROM tblTempPossibleClasses2"
DoCmd.RunSQL strSQL

For X = 0 To Me.lstClasses.ListCount - 1
Me.lstClassesStore.AddItem Me.lstClasses.ItemData(X)


Next X
For X = 0 To Me.lstAddClasses.ListCount - 1
Me.lstAddClassesStore.AddItem Me.lstAddClasses.ItemData(X)

Next X



For I = 0 To Me.lstClasses.ListCount - 1
For J = 0 To Me.lstAddClasses.ListCount - 1
If I <> J Then
Me.lstAddClasses.AddItem Me.lstClasses.ItemData(I)
Me.lstClasses.RemoveItem (Me.lstClasses.ItemData(I))


strSQL = "INSERT INTO tblTempPossibleClasses ( Subject, [Level], " _
& "ClassCode, ClassBit, [Min], [Max], SizeNow ) " _
& "SELECT tblTempSubjects1.Subject, tblTempSubjects1.Level, " _
& "qryClassBits.ClassCode, qryClassBits.ClassBit, tblClass.Min, " _
& "tblClass.Max, tblClass.SizeNow " _
& "FROM (qryClassBits INNER JOIN tblClass ON " _
& "qryClassBits.ClassCode = tblClass.ClassCode) " _
& "INNER JOIN tblTempSubjects1 " _
& "ON (tblClass.Level = tblTempSubjects1.Level) " _
& "AND (tblClass.Subject = tblTempSubjects1.Subject)"
DoCmd.RunSQL strSQL

strSQL = "INSERT INTO tblTempPossibleClasses2 ( Subject, [Level], " _
& "ClassCode, ClassBit, [Min], [Max], SizeNow ) " _
& "SELECT tblTempSubjects2.Subject, tblTempSubjects2.Level, " _
& "qryClassBits.ClassCode, qryClassBits.ClassBit, tblClass.Min, " _
& "tblClass.Max, tblClass.SizeNow " _
& "FROM (qryClassBits INNER JOIN tblClass ON " _
& "qryClassBits.ClassCode = tblClass.ClassCode) " _
& "INNER JOIN tblTempSubjects2 " _
& "ON (tblClass.Level = tblTempSubjects2.Level) " _
& "AND (tblClass.Subject = tblTempSubjects2.Subject)"
DoCmd.RunSQL strSQL

Me.lstClasses.RowSource = ""
Me.lstAddClasses.RowSource = ""


For X = 0 To Me.lstClassesStore.ListCount - 1
Me.lstClasses.AddItem Me.lstClassesStore.ItemData(X)

Next X

For X = 0 To Me.lstAddClassesStore.ListCount - 1
Me.lstAddClasses.AddItem Me.lstAddClassesStore.ItemData(X)

Next X

End If


Next J
Next I
DoCmd.SetWarnings True
[/vba]

This code i currently have running in a command button (in order to test it) and it works as it should. (the above code)

[vba]
Dim I As Integer
Dim strSQL As String
Dim J As Integer
Dim X As Integer



DoCmd.SetWarnings False
Initialise
strSQL = "DELETE tblTempStudClass.* FROM tblTempStudClass"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects1.* FROM tblTempSubjects1"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects2.* FROM tblTempSubjects2"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses.* FROM tblTempPossibleClasses"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses2.* FROM tblTempPossibleClasses2"
DoCmd.RunSQL strSQL


pubStudRef = Me.cmbStudRef.Value

For I = 0 To Me.lstClasses.ListCount - 1
pubClassCode(I + 1) = Me.lstClasses.ItemData(I)
strSQL = "INSERT INTO tblTempStudClass ( StudRef, ClassCode ) SELECT '" _
& Me.cmbStudRef.Value & "' AS StudRef, '" _
& Me.lstClasses.ItemData(I) & "' AS ClassCode"
DoCmd.RunSQL strSQL
Next I

For I = 0 To Me.lstAddClasses.ListCount - 1
pubSubCode(I + 1) = Left(Me.lstAddClasses.ItemData(I), 3)
pubLevelCode(I + 1) = Mid(Me.lstAddClasses.ItemData(I), 9, 2)
strSQL = "INSERT INTO tblTempSubjects" & (I + 1) & " ( Subject, [Level] " _
& ") SELECT '" & Left(Me.lstAddClasses.ItemData(I), 3) & "' AS Subject, '" _
& Mid(Me.lstAddClasses.ItemData(I), 9, 2) & "' AS [Level]"
DoCmd.RunSQL strSQL
Next I

strSQL = "INSERT INTO tblTempPossibleClasses ( Subject, [Level], ClassCode, " _
& " ClassBit, [Min], [Max], SizeNow ) " _
& "SELECT tblTempSubjects1.Subject, tblTempSubjects1.Level, " _
& "qryClassBits.ClassCode, qryClassBits.ClassBit, tblClass.Min, " _
& "tblClass.Max, tblClass.SizeNow " _
& "FROM (qryClassBits INNER JOIN tblClass ON " _
& "qryClassBits.ClassCode = tblClass.ClassCode) " _
& "INNER JOIN tblTempSubjects1 " _
& "ON (tblClass.Level = tblTempSubjects1.Level) " _
& "AND (tblClass.Subject = tblTempSubjects1.Subject)"
DoCmd.RunSQL strSQL



DoCmd.SetWarnings True

DoCmd.OpenForm "frmBestSolutions", acNormal, , , acFormReadOnly, acDialog


End Sub
[/vba]
This code is run when the user presses the command button to proccess their selections. Now, both of these bits of code (seem to) work as intended - however, i need the first bit of code to run IF the number of records in "frmBestSolutions" (the form opend by the second lot of code) is = to 0. As you can see from the first bit of code, it refers to several things which are on the process form - and as a result of this, i cant make it run on the second form. I tried using
[vba]
Dim NumOfRecs As Integer
NumOfRecs = Me.RecordsetClone.RecordCount

If NumOfRecs = 0 Then
MsgBox "Error - No matches", vbOKOnly
pubOutput = NumOfRecs
DoCmd.Close
[/vba]
on the second form and then using the "after update" trigger? (not sure if that is the correct word) to run the code i needed to run, but this did not work. I also tried having the msgbox as an input box, and then running the code based on the users response to that - but that did not work either (because it was on the wrong form ! :P).

I am not asking for help with the whole of my project, it all works except this one part of it. I have tried solving this myself, but the vba help and random browsing of the internet provided no solution! so i came here as a last resort .

Freerange

[Ken Puls]

Hi Freerane,

I'm not ignoring your post, but I'm not much of an Access coder. I am bumping your post back up though, to try and make sure one of our Access coders does see it.

[geekgirlau]

Instead of opening the frmBestSolutions form, have you tried creating a recordset using the data source from the frmBestSolutions form and counting the number of records in the recordset?

[Freerange]

Hmm, no i have not. I was not aware you could do this (damn you lacking experience!), and i am not sure how one would go about doing this (unless it is something realy simple and i just dont understand the terminology). Could you give me some pointers on how to do this, or explain it using diffrent terms?

Thanks, Freerange

[geekgirlau]

Let's say the form frmBestSolutions is based on the table "tblBest" where Class Code="Test".

[VBA]
Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String


strSQL = "SELECT [ClassCode] FROM tblBest WHERE [ClassCode]='Test'"

Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.EOF then
<what do you want to happen when no records>
End If

rst.Close
Set rst = Nothing
Set db = Nothing
[/VBA]

[Freerange]

Would this work if based of a query ?

[geekgirlau]

Yes - you can set strSQL to "SELECT * FROM MyQuery"

[Freerange]

Ok, i have used the code you suggested - and it seems to work! However, i have encounterd a snag.


I created the following code, but i get an error when running it (run-time error '3141' The SELECT statement includes a reserved word of argument name that is misspelled or missing, or the punctuation is incorrect) on this:

[vba]
strSQL = "SELECT tblTempPossibleClasses.ClassCode, tblTempPossibleClasses.Subject, " _
& "tblTempPossibleClasses.Min, tblTempPossibleClasses.Max, tblTempPossibleClasses.SizeNow, " _
& "tblTempPossibleClasses2.ClassCode, tblTempPossibleClasses2.Subject, tblTempPossibleClasses2.Min," _
& "tblTempPossibleClasses2.Max, tblTempPossibleClasses2.SizeNow, " _
& "andbits(tblTempPossibleClasses!ClassBit,tblTempPossibleClasses2!ClassB it) AS Answer, " _
& "tblTempPossibleClasses!ClassBit+tblTempPossibleClasses2!ClassBit AS NewClassBits, " _
& "qryStudentBits.StudentTotal, qryStudentBits.StudRef, andbits([NewClassBits],qryStudentBits!StudentTotal) AS Possible" _
& "FROM tblTempPossibleClasses, tblTempPossibleClasses2, qryStudentBits" _
& "WHERE (((andbits([tblTempPossibleClasses]![ClassBit],[tblTempPossibleClasses2]![ClassBit]))=0))"

DoCmd.RunSQL strSQL
[/vba]

Can any of you see anything obviosly wrong with this? The SQL has not been changed from when i copyed it from the querys SQL view, and it seemed to work when i tested it (and i cant see any error myself after looking through it).

Here is the code that it runs in, incase seeing the grand scheme of things helps !

(ps, i think this code is correct - thought i could be wrong )
[vba]
Private Sub cmdProcess_Click()
Dim I As Integer
Dim strSQL As String
Dim strSQL2 As String
Dim J As Integer
Dim X As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset




DoCmd.SetWarnings False
Initialise
strSQL = "DELETE tblTempStudClass.* FROM tblTempStudClass"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects1.* FROM tblTempSubjects1"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects2.* FROM tblTempSubjects2"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses.* FROM tblTempPossibleClasses"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses2.* FROM tblTempPossibleClasses2"
DoCmd.RunSQL strSQL


pubStudRef = Me.cmbStudRef.Value

For I = 0 To Me.lstClasses.ListCount - 1
pubClassCode(I + 1) = Me.lstClasses.ItemData(I)
strSQL = "INSERT INTO tblTempStudClass ( StudRef, ClassCode ) SELECT '" _
& Me.cmbStudRef.Value & "' AS StudRef, '" _
& Me.lstClasses.ItemData(I) & "' AS ClassCode"
DoCmd.RunSQL strSQL
Next I

For I = 0 To Me.lstAddClasses.ListCount - 1
pubSubCode(I + 1) = Left(Me.lstAddClasses.ItemData(I), 3)
pubLevelCode(I + 1) = Mid(Me.lstAddClasses.ItemData(I), 9, 2)
strSQL = "INSERT INTO tblTempSubjects" & (I + 1) & " ( Subject, [Level] ) SELECT '" _
& Left(Me.lstAddClasses.ItemData(I), 3) & "' AS Subject, '" _
& Mid(Me.lstAddClasses.ItemData(I), 9, 2) & "' AS [Level]"
DoCmd.RunSQL strSQL
Next I






strSQL = "INSERT INTO tblTempPossibleClasses ( Subject, [Level], ClassCode, " _
& " ClassBit, [Min], [Max], SizeNow ) " _
& "SELECT tblTempSubjects1.Subject, tblTempSubjects1.Level, " _
& "qryClassBits.ClassCode, qryClassBits.ClassBit, tblClass.Min, " _
& "tblClass.Max, tblClass.SizeNow " _
& "FROM (qryClassBits INNER JOIN tblClass ON " _
& "qryClassBits.ClassCode = tblClass.ClassCode) " _
& "INNER JOIN tblTempSubjects1 " _
& "ON (tblClass.Level = tblTempSubjects1.Level) " _
& "AND (tblClass.Subject = tblTempSubjects1.Subject)"
DoCmd.RunSQL strSQL

strSQL2 = "SELECT [ClassCode] FROM qryBestSolutions"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL2, dbOpenSnapshot)

If rst.EOF Then

Me.comboLevel.Value = ""
Me.comboSubject.Value = ""
Me.lstAddClassesStore.RowSource = ""
Me.lstClassesStore.RowSource = ""



DoCmd.SetWarnings False
strSQL = "DELETE tblTempStudClass.* FROM tblTempStudClass"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects1.* FROM tblTempSubjects1"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempSubjects2.* FROM tblTempSubjects2"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses.* FROM tblTempPossibleClasses"
DoCmd.RunSQL strSQL
strSQL = "DELETE tblTempPossibleClasses2.* FROM tblTempPossibleClasses2"
DoCmd.RunSQL strSQL


For X = 0 To Me.lstClasses.ListCount - 1
Me.lstClassesStore.AddItem Me.lstClasses.ItemData(X)


Next X
For X = 0 To Me.lstAddClasses.ListCount - 1
Me.lstAddClassesStore.AddItem Me.lstAddClasses.ItemData(X)

Next X






For I = 0 To Me.lstClasses.ListCount - 1
For J = 0 To Me.lstAddClasses.ListCount - 1
If I <> J Then
Me.lstAddClasses.AddItem Me.lstClasses.ItemData(I)
Me.lstClasses.RemoveItem (Me.lstClasses.ItemData(I))


strSQL = "INSERT INTO tblTempPossibleClasses ( Subject, [Level], ClassCode, " _
& " ClassBit, [Min], [Max], SizeNow ) " _
& "SELECT tblTempSubjects1.Subject, tblTempSubjects1.Level, " _
& "qryClassBits.ClassCode, qryClassBits.ClassBit, tblClass.Min, " _
& "tblClass.Max, tblClass.SizeNow " _
& "FROM (qryClassBits INNER JOIN tblClass ON " _
& "qryClassBits.ClassCode = tblClass.ClassCode) " _
& "INNER JOIN tblTempSubjects1 " _
& "ON (tblClass.Level = tblTempSubjects1.Level) " _
& "AND (tblClass.Subject = tblTempSubjects1.Subject)"
DoCmd.RunSQL strSQL

strSQL = "INSERT INTO tblTempPossibleClasses2 ( Subject, [Level], ClassCode, " _
& " ClassBit, [Min], [Max], SizeNow ) " _
& "SELECT tblTempSubjects2.Subject, tblTempSubjects2.Level, " _
& "qryClassBits.ClassCode, qryClassBits.ClassBit, tblClass.Min, " _
& "tblClass.Max, tblClass.SizeNow " _
& "FROM (qryClassBits INNER JOIN tblClass ON " _
& "qryClassBits.ClassCode = tblClass.ClassCode) " _
& "INNER JOIN tblTempSubjects2 " _
& "ON (tblClass.Level = tblTempSubjects2.Level) " _
& "AND (tblClass.Subject = tblTempSubjects2.Subject)"
DoCmd.RunSQL strSQL

Me.lstClasses.RowSource = ""
Me.lstAddClasses.RowSource = ""


For X = 0 To Me.lstClassesStore.ListCount - 1
Me.lstClasses.AddItem Me.lstClassesStore.ItemData(X)

Next X

For X = 0 To Me.lstAddClassesStore.ListCount - 1
Me.lstAddClasses.AddItem Me.lstAddClassesStore.ItemData(X)

Next X

End If



Next J
Next I



strSQL = "SELECT tblTempPossibleClasses.ClassCode, tblTempPossibleClasses.Subject, " _
& "tblTempPossibleClasses.Min, tblTempPossibleClasses.Max, tblTempPossibleClasses.SizeNow, " _
& "tblTempPossibleClasses2.ClassCode, tblTempPossibleClasses2.Subject, tblTempPossibleClasses2.Min," _
& "tblTempPossibleClasses2.Max, tblTempPossibleClasses2.SizeNow, " _
& "andbits(tblTempPossibleClasses!ClassBit,tblTempPossibleClasses2!ClassB it) AS Answer, " _
& "tblTempPossibleClasses!ClassBit+tblTempPossibleClasses2!ClassBit AS NewClassBits, " _
& "qryStudentBits.StudentTotal, qryStudentBits.StudRef, andbits([NewClassBits],qryStudentBits!StudentTotal) AS Possible" _
& "FROM tblTempPossibleClasses, tblTempPossibleClasses2, qryStudentBits" _
& "WHERE (((andbits([tblTempPossibleClasses]![ClassBit],[tblTempPossibleClasses2]![ClassBit]))=0))"

DoCmd.RunSQL strSQL

strSQL = "SELECT qryTwoSubjects.tblTempPossibleClasses.ClassCode, qryTwoSubjects.tblTempPossibleClasses.Subject, qryTwoSubjects.tblTempPossibleClasses.Min, qryTwoSubjects.tblTempPossibleClasses.Max, qryTwoSubjects.tblTempPossibleClasses.SizeNow, qryTwoSubjects.tblTempPossibleClasses2.ClassCode, qryTwoSubjects.tblTempPossibleClasses2.Subject, qryTwoSubjects.tblTempPossibleClasses2.Min, qryTwoSubjects.tblTempPossibleClasses2.Max, qryTwoSubjects.tblTempPossibleClasses2.SizeNow, qryTwoSubjects.Answer, qryTwoSubjects.NewClassBits, qryTwoSubjects.StudentTotal, qryTwoSubjects.StudRef, qryTwoSubjects.Possible, tblStudents.Surname, tblStudents.Forenames" _
& "FROM qryTwoSubjects INNER JOIN tblStudents ON qryTwoSubjects.StudRef = tblStudents.StudRef" _
& "WHERE (((qryTwoSubjects.Possible)=0))"
DoCmd.RunSQL strSQL


strSQL2 = "SELECT [tblTempPossibleClasses.ClassCode] FROM qrySubjectMatches"
Set db = CurrentDb
Set rst2 = db.OpenRecordset(strSQL2, dbOpenSnapshot)

If rst2.EOF Then
MsgBox "Error - No matches", vbOKOnly, Error

Else


DoCmd.SetWarnings True
DoCmd.OpenForm "frmTwoSubs", acNormal, , , acFormReadOnly, acDialog


End If
rst2.Close
Set rst2 = Nothing
Set db = Nothing

Else

DoCmd.SetWarnings True

DoCmd.OpenForm "frmBestSolutions", acNormal, , , acFormReadOnly, acDialog


End If

rst.Close
Set rst = Nothing
Set db = Nothing






End Sub
[/vba]


Thank you again, Freerange

[geekgirlau]

Access won't like the use of "min" and "max" as field names, as these are standard functions. If it is not possible (or too much work) to change the field names, try modifying your query SQL to rename the field in the query (eg "tblTempPossibleClasses.Min AS MinClass1")

[Freerange]

Hm, okay - however, the previous SQL with min / max in worked when run.. so im not sure that this is it. I will look into it though (when i am at my PC with the work on!), but do you think it could be anything else ?

Thanks for all your help by the way! Freerange

[geekgirlau]

The only purpose of the SQL is to see if there are any records, so you might want to scale down your SQL to select only 1 field, but containing all the criteria.

The SQL in your sample above does not look correct - the FROM section is definitely not right. Try copying and pasting directly from the SQL window of your query, without worrying too much about line breaks at the moment.

Also, you need to use the recordset as I showed you above - you can't run the SQL in a Select query, because it doesn't do anything.