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!

 

[Andrzejek]

What is the difference between frm[blue]Jan[/blue]Class and frm[blue]Dec[/blue]Class?
Do you have 12 forms that are exactly the same, they just display information for different month?

The same goes for your queries - 12 queries that do exactly the same job, just for different month?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BLSguy]

Precisely, 13 identical forms. One for each month, and one that lists all the scheduled courses together. Each form and query are so that a user can view all the information specific to one month.

 

[MajP]

This can be done with a single line of code.

As mentioned you only need one form and you filter it when you open it. Also you can get away from each of the different events and create a single function.
1) In each button go to the “tag” property and type in the name of the month. The tag property is on the “Other” tab.
2) Now make a function. It has to be a function not a sub routine. Put the function in the form’s module
Public function ShowForm()
docmd.openform “frmClass”, , , “[Month] = ‘” & activeControl.tag & “’”
end function
3) Select all of the controls at once. In the on click event, type in the name of the function preceded by “=”. This is in place of [Event Procedure]
= ShowForm()

The way this works is when you click a button it fires the function and the function reads the tag in the active control. It opens the form filtered to the month tagged in the active control.

 

[BLSguy]

Thank you! This is going to make updates MUCH easier! I copied in the code and followed your steps. Now I just need to learn how to do the filtering part!

 

[MajP]

https://msdn.microsoft.com/en-us/library/bb243764(v=office.12).aspx

May help

 

[Andrzejek]

13 identical forms" bad approach, a nightmare to maintain.
When I see 2 or more of anything of "the same" in the programming, it is time to fix it.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

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

Do you want to go to an existing record that matches the date or do you want to go to a new record and default the date to the date clicked? What are you populating the combobox with? Are you populating it with the date? Are the dates real dates (datetime field) or a string representing a date? Looks like your start time field is just an integer and not a real time.

 

[BLSguy]

MajP, thank you for the link. I've found similar information from a variety of sources but I'm struggling to understand how to apply it to the contents of my list box using your tag method.

Andrzejek, I completely agree, and that's why I knew you all would be able to help!

MajP, on double-clicking the date from the list box, I would like the form that opens to be one that adds a new record tied to the date that was selected. The next form has a combo box with the available class dates in it, presently (in a ClassID, Class_Date, Class_Month format with columns set to 0",1",1" and bound to column 1). It'd just be nice if the date was already selected according to what they clicked on the previous form. And start time is just when the class starts so the user can inform the registering student.

 

[MajP]

One thing. When working with dates and times it is so much easier to store the information in a single field and use a real date. You can always display it in separate controls using formatting to show the month, date, time in any type of format that you would want. So I could store the value of classStart 03/02/2017 08:00 AM. I could then display in the listbox the values 2, March, 0800 in separate columns by just applying some formatting on the single date field. Using real dates allows you to query, sort, and do date calculations easily. Something to consider. Working with parts of dates in multiple fields get cumbersome and difficult.
If you double click the listbox you can get the value of the first column “class day” in two ways. If the first column is bound by

Dim classDay as integer
classDay = me.ListBoxName.value

If the column I want is not the bound column, you can get the value using the column property and the column index. The column indices are numbered 0,1,… so the first column is column 0.

classDay = me.listboxName.Column(2)

If there is no item selected the value will be null so to be safe use the NZ function

classDay = nz(me.listboxName.column(2),0)

or check to make sure a value is selected

if not isnull(me.listboxname.column(2)) then
    classDay = me.listboxName.column(2)
   ‘some code here
end if

I do not understand how the records are related between the listbox and the opened form. When I open the second form are you creating a new class or is this a child record assigning a person to a specific class? Either way it does not seem to make any sense to be creating a new record and copying date and time information. Seems you should pass the class_ID as a foreign key to a new child record. Can you explain better?

 

[MajP]

2 Things. Can you post an image of the second form, that could help? Also when you click a month button on the main form do you really want to open a filtered form or do you just want to filter the listbox on the main form?

 

[BLSguy]

MajP, thanks again for your valuable insight! I will make those changes immediately to the best of my ability. I'm sure your method would expedite the process of inputting new classes down the road.

 

[Andrzejek]

MayP is suggesting the same approach to a Class_Date as I did in thread701-1774506

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BLSguy]

Andrzejek, sorry I didn't give you credit earlier! I went back and starred your posts. I'm still not sure how to transfer the portions of the date I'm after into a combo box or list box, but I know your advice was a big step in the right direction. I also made the changes to my tables and relationships.

Here's the new layout:

 

[Andrzejek]

I did not post my last replay to receive (ask for) a star (but thank you anyway).
I did it because you see the same suggestion from MayP, so it is not just one guy's opinion about the Dates in the data base.
That also points to another problem many programmers face: create a program(s), interface, and a lot of other work, and then create / set / re-set / modify the data base. As you can see that could make your life difficult. That's why it is so important to first start with the right design of the DB. It makes life a lot easier to do the rest of the work. But you are not the only one in this situation... :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[BLSguy]

Andrzejek, I give stars because I figure it's the least I can do to show my appreciation

With the way I've got this database laid out, and the relatively simple tasks it needs to accomplish, once I have it all working it should require minimal maintenance. I won't be adding to it once it's finished, but rather creating new databases for separate tasks and then trying to make them talk to each other. But that's for much later...

 

[MajP]

double-clicking the date from the list box, I would like the form that opens to be one that adds a new record tied to the date that was selected. The next form has a combo box with the available class dates in it, presently (in a ClassID, Class_Date, Class_Month format with columns set to 0",1",1" and bound to column 1). It'd just be nice if the date was already selected according to what they clicked on the previous form. And start time is just when the class starts so the user can inform the registering student.

Looking at your tables, I interpret the above to mean you want to open a form to register a student to a class. You want to default the combo box on the form to the class you selected in the listbox. You are not creating a new class. You can call it from the double click event of the listbox.
Add the classID to your listbox as the first column and make it bound. You can set the column width to zero so the user cannot see it. Then add the following procedure

Public Sub RegisterStudent
  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

I am guessing here, but I would think you could simplify the user interface. This assumes people sign up for more than one class. The design would be overkill if only signing up for one class. If it was me I would have a combobox at the top of the form to pick a student. Next to the combbox I would have an "add" button to pop open a form to add a new student (this form would not do registration). On closing the new student form, the students name would not be in the combobox. Under the combobx I would have a listbox (or subform) like you have showing only the classes available for that student to pick from. In other words it would not include classes they already have slected. This is done using a “not in” query. Below the listbox would be a subform (or a listbox) showing all classes assigned to that student. If you double click in the available classes it would run an insert query to add that class for that student. It would disappear from the available list and show in the assigned list. If you double click the assigned to listbox (subform) it would remove it. So on one form you can add, remove, classes for any student just by clicking on one of the lists. If you do not want a doubleclick in your two lists, you could use a subform and put add and remove buttons next to a record.

 

[BLSguy]

I've tried many of the excellent suggestions here, but for my lack of experience have been unable to get them to work as I need. I have a rapidly approaching deadline to get this database operational so I've had to revert to some less than ideal structuring in favor of functionality.

I entered the code you mentioned with the requisite changes in form names applied but I get the following error message:

Thus far I've had little success with Public sub codes. I've been able to get "Private Sub" to work for various functions, but obviously that won't do what I need in this instance.

 

[MajP]

Looks to me that in the double click event procedure you put the name of the procedure. Instead just add a double click event. You should see the words [event procedure] in the property. Then in the listbox's doublec click event you can just put the code.

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

If I want to call the same code from multiple events then I would make it a stand alone procedure and call it from each different event.

Private Sub ListBoxName_DblClick(Cancel As Integer)
   RegisterStudent
End Sub

Private Sub ListBoxName_SomeOtherEvent(Cancel As Integer)
   RegisterStudent
End Sub

Public Sub RegisterStudent
  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

 

[BLSguy]

I've been struggling to grasp the nature of declaring and using variables and have had little success in my attempts to use them. I used the first block of code you posted:

Code-->
1.Private Sub lstClasses_DblClick(Cancel As Integer)
2. Dim classID As Long
3. If Not IsNull(Me.lstClasses.Value) Then
4. classID = Me.lstClasses.Value
5. DoCmd.OpenForm "frmReg", , , , acFormAdd
6. Forms(“frmReg”).cboClasses.Value = classID
7. Else
8. Msgbox “No class selected.”
9. End If
10.End Sub

When I double click on the list, I get "compile error: Variable not defined"
In debug, line 1 is highlighted yellow, "frmReg" of line 5 is highlighted as a selection, and the text of line 8 is always red.

However, on property sheet, it does now say [Event Procedure] as you specified.