INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help streamline my code?

Help streamline my code?

(OP)
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!

RE: Help streamline my code?

What is the difference between frmJanClass and frmDecClass?
Do you have 12 forms that are exactly the same, they just display information for different month? ponder

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.

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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.

RE: Help streamline my code?

(OP)
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!

RE: Help streamline my code?

"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.

RE: Help streamline my code?

Quote:

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.

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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


CODE -->

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.

CODE -->

classDay = me.listboxName.Column(2) 
If there is no item selected the value will be null so to be safe use the NZ function

CODE -->

classDay = nz(me.listboxName.column(2),0) 
or check to make sure a value is selected

CODE -->

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?

RE: Help streamline my code?

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?

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

(OP)
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:

RE: Help streamline my code?

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... sad

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Help streamline my code?

(OP)
Andrzejek, I give stars because I figure it's the least I can do to show my appreciation :D

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...

RE: Help streamline my code?

Quote:

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

CODE -->

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.

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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.

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.

CODE

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 

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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.

CODE --> vba

5. DoCmd.OpenForm "frmReg", , , , acFormAdd
6. Forms(frmReg).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

RE: Help streamline my code?

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.

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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.

CODE --> vba

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?

CODE --> vba

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

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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: How to debug your code?

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

RE: Help streamline my code?


Highlight area and use icon. See yellow arrow.

RE: Help streamline my code?

(OP)
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

CODE -->

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 

RE: Help streamline my code?

(OP)
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.

RE: Help streamline my code?

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

RE: Help streamline my code?

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

CODE --> SQL

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.

RE: Help streamline my code?



On the design tab, view, Sql View.

RE: Help streamline my code?

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

RE: Help streamline my code?

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

RE: Help streamline my code?

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

CODE --> SQL

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.

RE: Help streamline my code?

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 "me" and "Me" 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

RE: Help streamline my code?

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

CODE -->

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?

RE: Help streamline my code?

(OP)
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!

RE: Help streamline my code?

Something like this which uses fairly simple If Then logic:

CODE --> vba

Private Sub lstClasses_DblClick(Cancel As Integer)
    Dim ClassID As Long
    'Check for space in the class
    If me.lstClasses.Column(4) = me.lstClasses.Column(5) Then
        'It's full
        MsgBox ("Class is full!")
       Else    'there is room so open the registration form
        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

RE: Help streamline my code?

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

CODE --> VBA

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!!

RE: Help streamline my code?

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 Exit Sub (avoid it if you can)
4. Is commented properly.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Help streamline my code?

(OP)
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!!

RE: Help streamline my code?

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 winky smile

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close