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!

 

[BLSguy]

Hey, I figured out something that works! Check out this code:

Private Sub lstClasses_DblClick(Cancel As Integer)
 If Me.lstClasses.Column(4) = Me.lstClasses.Column(5) Then
 MsgBox ("Class is full!")
 Exit Sub
 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

It does what I was hoping. But would there be an advantage to pasting in the code you suggested? Not trying to challenge you or anything, but for the sake of understanding could you explain why one code set might be superior to another? Thanks again for all your help!!

 

[Andrzejek]

Duane's code is "superior to another" because:
1. It is indented properly - a lot easier to read and understand
2. Has variable declaration at the top, not in the middle
3. Does not have [tt]Exit Sub[/tt] (avoid it if you can)
4. Is commented properly.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BLSguy]

Andrzejek, excellent points for me to learn from, thank you! Changes applied

Now to input a boat load of data, and my program is ready to go! I intend to make a few more databases to handle our other classes and administrative tasks. By the time I'm done, I might even be able to contribute around here All your help was sincerely appreciated!!

 

[dhookom]

Thanks Andy,

I have a tendency to post examples but not sufficiently explain what I take for granted.

BLSGuy, If you intend to develop robust applications, you should read up on good coding habits and conventions. I don't agree with your table structures since I don't think they are sustainable in the future. However, it's your project, not mine ;-)

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