Help streamline my code? 2

[BLSguy]

Hello everyone! This forum has been very helpful, thank you all! My database will be ready for use soon, but I admit it's present design is lacking. Below is my current setup and the form I'm asking about in this thread:

Here is the VBA I used to navigate the various forms so that a user could click a month and view only the classes from that month:

Option Compare Database
Option Explicit

Private Sub btnDecClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmDecClass"
End Sub

Private Sub btnNovClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmNovClass"
End Sub

Private Sub btnOctClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmOctClass"
End Sub

Private Sub btnSepClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmSepClass"
End Sub

Private Sub btnAugClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmAugClass"
End Sub

Private Sub btnJulClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmJulClass"
End Sub

Private Sub btnJunClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmJunClass"
End Sub

Private Sub btnMayClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmMayClass"
End Sub

Private Sub btnAprClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmAprClass"
End Sub

Private Sub btnMarClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmMarClass"
End Sub

Private Sub btnFebClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmFebClass"
End Sub

Private Sub btnJanClass_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmJanClass"
End Sub

Private Sub ReturnToStart_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmStart"
End Sub
Private Sub btnReg_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmStudents"
End Sub
Private Sub btnChange2_Click()
DoCmd.Close acForm, "frmJanClass"
DoCmd.OpenForm "frmChange"
End Sub


While this functions as I intend and does what I need, any update I need to perform has to be repeated 13 times. So it'd be great if I could get some help with more advanced coding to perform these functions. Also, does anyone know how what code I would use for a double click event on a given record that would take the user to a new form with the date that was double-clicked already populating a combo box on the next form? Thanks for all your help!

 

[dhookom]

One thing I noticed immediately is the difference of the double-quotes. It looks like editing might have been performed in Word or someplace else that uses different quotes.

5. DoCmd.OpenForm "frmReg", , , , acFormAdd
6. Forms([COLOR=#CC0000][highlight #FCE94F]“[/highlight][/color]frmReg[COLOR=#CC0000][highlight #FCE94F]”[/highlight][/color]).cboClasses.Value = classID

The highlighted versions are wrong.

Also, consider always using the TGML code tag for your code.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Concur. There is something wrong with those quotes. Retype in VBA. I pasted that version and it will not compile, it does not treat the quotes as quotes.

 

[BLSguy]

Fantastic! That did the trick! For the sake of having the database ready in time, I'm foregoing buttons in favor of one big list.

 

[BLSguy]

My database is just about ready to deploy! One last dilemma, however...
This code works great:
Private Sub ListBoxName_DblClick(Cancel As Integer)
Dim classID as long
If not isnull(me.listboxName.value) then
classID = me.listboxName.value
docmd.openform “YourSecondFormName”,,,, acFormAdd
forms(“YourSecondFormName”).comboboxName.value = classid
else
Msgbox “No class selected.”
End if
End Sub

My last question is, how would I add a validation rule to a calculated field in a query to check the field value prior to executing the above code? I.e. if the count of tblStudents.ClassID = 12, then display MsgBox "Class is full!" and prevent the user from proceeding to next form with that date.

Thanks again! I'm really excited to put my first database into action! I still need to refine a few things and automate a few others, but at least it will be more than adequate for expediting the registration process and the administration thereof.

 

[dhookom]

Again, I would add the maximum and current number of students to the Row Source of the list box. Then you simply compare the value of one column to the other.

If me.listboxName.Column(x) < me.listboxName.Column(y) Then

x is the column displaying the current count and y is the column displaying the max.


Again "Also, consider always using the TGML code tag for your code."

See how much easier this is to read?

Private Sub ListBoxName_DblClick(Cancel As Integer)
    Dim classID as long
    If not isnull(me.listboxName.value) then
        classID = me.listboxName.value
        docmd.openform “YourSecondFormName”,,,, acFormAdd
        forms(“YourSecondFormName”).comboboxName.value = classid
      else
        Msgbox “No class selected.”
    End if
End Sub

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[BLSguy]

Of course I'm sure I'm doing something wrong, but I couldn't get that code to work.

Private Sub lstClasses_DblClick(Cancel As Integer)
If me.lstClasses.Column(5) = me.lstClasses.Column(6) Then
MsgBox ("Class if full!")
End If
Dim ClassID As Long
If Not IsNull(Me.lstClasses.Value) Then
ClassID = Me.lstClasses.Value
DoCmd.OpenForm "frmReg", , , , acFormAdd
Forms("frmReg").cboClasses.Value = ClassID

End If
DoCmd.Close acForm, "frmClasses"
End Sub

I tried moving around End If and other parts of code, but it either prevented the double-click to next form action or just didn't work at all. I do have a count and maximum column in the table and query however, as you suggested.

 

[dhookom]

TGML Please If you don't know how to do this, it's basically the same as most word processors.

Please post the Row Source of your list box as well as the Column Count.

Did you try place a breakpoint in your code at the first executable line of code faq705-7148?


Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Highlight area and use icon. See yellow arrow.

 

[BLSguy]

Thank you MajP, I was wondering how to highlight code the way you all do.

Dhookom, I tried adding the break point as you suggested. It highlighted in yellow the line: If me.lstClasses.Column(5) = me.lstClasses.Column(6) Then

Private Sub lstClasses_DblClick(Cancel As Integer)
 If me.lstClasses.Column(5) = me.lstClasses.Column(6) Then
 MsgBox ("Class if full!")
 End If 
 Dim ClassID As Long
 If Not IsNull(Me.lstClasses.Value) Then
 ClassID = Me.lstClasses.Value
 DoCmd.OpenForm "frmReg", , , , acFormAdd
 Forms("frmReg").cboClasses.Value = ClassID

 End If
 DoCmd.Close acForm, "frmClasses"
 End Sub

 

[BLSguy]

Row source of lstClasses is qryAllClasses

in qryAllClasses, there are 6 columns. Column 5 is the count of students in each class, column 6 is the maximum numbers of students allowed in that class.

 

[MajP]

Go to qryAllClasses in design viewa and select SQL view. Copy and past the SQL.

 

[BLSguy]

I'm not having much luck with the SQL either.

Capacity: IIf([tblStudents]![ClassID] < [Capacity], "Open", "Full")

Tried a VBA variant and no luck there. I'm sure the solution to this is simple, it's just complicated to me.

 

[MajP]

On the design tab, view, Sql View.

 

[BLSguy]

Thank you MajP. I managed to navigate to the SQL view, but the proper code is still eluding me as of yet.

 

[dhookom]

BLSguy,

Please just copy and paste the SQL view into a reply. No pictures, just the text.

Then as asked before "Please post the Row Source of your list box as well as the Column Count."


Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[BLSguy]

I appreciate your patience as you all bear with me as I learn this software. Here is the SQL for qryAllClasses:

SELECT tblClasses.ClassID, tblClasses.Class_Date, tblClasses.Class_Time, tblClasses.Type, Count(tblStudents.ClassID) AS CountOfClassID, tblClasses.Capacity
FROM tblClasses LEFT JOIN tblStudents ON tblClasses.ClassID = tblStudents.ClassID
GROUP BY tblClasses.ClassID, tblClasses.Class_Date, tblClasses.Class_Time, tblClasses.Type, tblClasses.Capacity
ORDER BY tblClasses.ClassID;

If this isn't the information you requested in quotes in your last reply, please clarify as these are the correct responses to your question to the best of my current knowledge:

Row source of lstClasses is qryAllClasses

In qryAllClasses, there are 6 columns. Column 5 is the count of students in each class, column 6 is the maximum numbers of students allowed in that class.

 

[dhookom]

Columns in combo and list boxes are 0-based so the fifth and sixth columns are:

me.lstClasses.Column(4) AND me.lstClasses.Column(5)

When you copy and paste your code, how do you get "[highlight #FCE94F]m[/highlight]e" and "[highlight #FCE94F]M[/highlight]e" with upper and lower-case "m"? If this code is actually copied and pasted, you should consider proper indenting.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

I am confused, it appears you have changed things. You show in your post from 2 Mar 14:11 a registration table, so you can do a many to many relation between classes and students. Your SQL however has

LEFT JOIN tblStudents ON tblClasses.ClassID = tblStudents.ClassID

This suggests you did away with the registration table and now you have a one to many. That may be OK if a student can only sign up for one class. Is that what you intended?

 

[BLSguy]

dhookom, I completely forgot that the first column is 0! Thank you! I'll try it again! It works!! However, how do I get it to stop the advancement to the next form after the "Class is full" popup?

MajP, yes, I did away with the registration table because it was giving me some headaches with queries. Also, you are correct in that I will only have a students assigned to a single class at any given time.

As soon as I get it set so that a full class can't easily be overbooked, the program is ready to be up and running!! Yay!

 

[dhookom]

Something like this which uses fairly simple If Then logic:

Private Sub lstClasses_DblClick(Cancel As Integer)
    Dim ClassID As Long
    [COLOR=#4E9A06]'Check for space in the class[/color]
    If me.lstClasses.Column(4) = me.lstClasses.Column(5) Then
        [COLOR=#4E9A06]'It's ful[/color]l
        MsgBox ("Class is full!")
       Else    [COLOR=#4E9A06]'there is room so open the registration form[/color]
        ClassID = Me.lstClasses
        DoCmd.OpenForm "frmReg", , , , acFormAdd
        Forms("frmReg").cboClasses = ClassID
        DoCmd.Close acForm, "frmClasses"
    End If 
End Sub

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016