Transforming a Private VBA Code to Public Function

Reese

Registered User.
Local time
Today, 03:26
Joined
Jan 13, 2013
Messages
387
I have several controls that do the same thing in different forms. I currently have individual private subs in each form associated with each control. Each private sub has the same code that I copied/pasted from form to form (the names of the controls have remained constant from form to form).

I want to turn this into a public function module to help streamline and tidy up my database.

The problem is that the VBA in these private subs refer to values in controls within each form. Here is an example:

Code:
Private Sub AgeStartCombo_AfterUpdate()

If Me.AgeStartCombo = "All Ages" Then
    Me.AgeEndCombo = "All Ages"
    Me.AgeStartCodeTxt = "1"
    Me.AgeEndCodeTxt = "16"
End If

End Sub

The 'Me' grammar doesn't seem to work in a public function. What do I change so that it applies this code to the controls in which ever form is currently open?

I have a feeling that I have to use a string statement, declare a variable as the current form and then use that variable in replace of 'Me'. But I don't understand how to code variables very well and I can't seem to find a straight answer on declaring the current form name as a variable when I search Access forums.

Any help would be appreciated. Thanks!
 
Code:
Public Sub SetCombos(frm As Form)

 With frm
     If .AgeStartCombo = "All Ages" Then
        .AgeEndCombo = "All Ages"
        .AgeStartCodeTxt = "1"
        .AgeEndCodeTxt = "16"
     End If
 End With
  
 End Sub
Call it like this

Code:
 Private Sub AgeStartCombo_AfterUpdate()

   SetCombos Me
  
 End Sub
 
Thank you Galaxiom! Unfortunately, when using the form in a test, I get the warning "Compile error: Expected variable or procedure, not module". Here's the code I am using (the earlier example was just a section, I'm including the whole block this time in case somehow it means I have to do something different):

Code:
Public Sub AgeStartSetCombo(frm As Form)

With frm
    If .AgeStartCombo = "All Ages" Then
        .AgeEndCombo = "All Ages"
        .AgeStartCodeTxt = "1"
        .AgeEndCodeTxt = "16"
    End If
    If .AgeStartCombo = "Senior Citizens" Then
        .AgeEndCombo = "Senior Citizens"
        .AgeStartCodeTxt = "15"
        .AgeEndCodeTxt = "15"
    End If
    If .AgeStartCombo = "Pre-K & Nursery" Then
        .AgeStartCodeTxt = "2"
    ElseIf .AgeStartCombo = "Kindergarten" Then
        .AgeStartCodeTxt = "3"
    ElseIf .AgeStartCombo = "1st Grade" Then
        .AgeStartCodeTxt = "4"
    ElseIf .AgeStartCombo = "2nd Grade" Then
        .AgeStartCodeTxt = "5"
    ElseIf .AgeStartCombo = "3rd Grade" Then
        .AgeStartCodeTxt = "6"
    ElseIf .AgeStartCombo = "4th Grade" Then
        .AgeStartCodeTxt = "7"
    ElseIf .AgeStartCombo = "5th Grade" Then
        .AgeStartCodeTxt = "8"
    ElseIf .AgeStartCombo = "6th Grade" Then
        .AgeStartCodeTxt = "9"
    ElseIf .AgeStartCombo = "7th Grade" Then
        .AgeStartCodeTxt = "10"
    ElseIf .AgeStartCombo = "8th Grade" Then
        .AgeStartCodeTxt = "11"
    ElseIf .AgeStartCombo = "High School" Then
        .AgeStartCodeTxt = "12"
    ElseIf .AgeStartCombo = "College" Then
        .AgeStartCodeTxt = "13"
    ElseIf .AgeStartCombo = "Adults" Then
        .AgeStartCodeTxt = "14"
    End If
End With

End Sub
I'm calling it with:

Code:
Private Sub AgeStartCombo_AfterUpdate()

    AgeStartSetCombo Me

End Sub
 
Last edited:
why don't you use select case ?
for 1st to 7th you can use Left(AgeStartCombo, 1) + 3
 
why don't you use select case ?
I don't know what select case does or how it is used. I have it in one other module in my database, but that's only because in a previous post I asked how to do something and someone gave me the code straight up, so I copy/pasted it in. It was one of those "it works so I'm not going to question it" kind of things.

for 1st to 7th you can use Left(AgeStartCombo, 1) + 3
I don't know if your suggestion would work or not in this particular case, but it may not work for everything. This is just one of the codes that I want to turn into a module. One, for example, is designed to enable/disable other controls in a form based on the value of a combo box:

Code:
Private Sub PayCatCombo_AfterUpdate()

If Me.PayCatCombo = "BOCES" Then
    Me.ProgPriceTxt.Enabled = False
        Me.AudStyleChk.Enabled = False
        Me.AudPriceTxt.Enabled = False
        Me.MileFeeTxt.Enabled = False
        Me.PayNotesTxt.Enabled = False
        Me.PaidCheck.Enabled = False
        Me.DatePaidTxt.Enabled = False
        Me.PayTypeCombo.Enabled = False
        Me.CCNumTxt.Enabled = False
        Me.CheckNumTxt.Enabled = False
        Me.BOCESPOTxt.Enabled = True
        Me.NumStudTxt.Enabled = True
        Me.BOCESProgPOTxt.Enabled = True
        Me.ThemeSameCheck.Enabled = True
        Me.BOCESProgPracTxt.Enabled = True
        Me.CmdPrintBillingInvoice.Enabled = False
        Me.PrintBillIanCmd.Enabled = False
        Me.PrintBillMelCmd.Enabled = False
        Me.cmdPrintEdReport.Enabled = False
        Me.CmdPrintSchoolContract.Enabled = True
        Me.PrintContractIanCmd.Enabled = True
        Me.PrintContractMelCmd.Enabled = True
        Me.CmdPrintBOCESEdInvoice.Enabled = True
        Me.StudCostLabel.Visible = True
        Me.BookMoreCmd.Enabled = False
Else
        Me.ProgPriceTxt.Enabled = True
        Me.AudStyleChk.Enabled = True
        Me.AudPriceTxt.Enabled = True
        Me.MileFeeTxt.Enabled = True
        Me.PayNotesTxt.Enabled = True
        Me.PaidCheck.Enabled = True
        Me.BOCESPOTxt.Enabled = False
        Me.NumStudTxt.Enabled = False
        Me.BOCESProgPOTxt.Enabled = False
        Me.ThemeSameCheck.Enabled = False
        Me.BOCESProgPracTxt.Enabled = False
        Me.CmdPrintBillingInvoice.Enabled = True
        Me.PrintBillIanCmd.Enabled = True
        Me.PrintBillMelCmd.Enabled = True
        Me.cmdPrintEdReport.Enabled = True
        Me.CmdPrintSchoolContract.Enabled = False
        Me.PrintContractIanCmd.Enabled = False
        Me.PrintContractMelCmd.Enabled = False
        Me.CmdPrintBOCESEdInvoice.Enabled = False
        Me.StudCostLabel.Visible = False
        Me.BookMoreCmd.Enabled = True
End If

End Sub
Would a select case be able to handle that as well?
 
Last edited:
Does this work for you?
Code:
    Dim boces As Boolean: boces = (Me.PayCatCombo = "BOCES")
    
    Me.ProgPriceTxt.Enabled = !boces
    Me.AudStyleChk.Enabled = !boces
    Me.AudPriceTxt.Enabled = !boces
    Me.MileFeeTxt.Enabled = !boces
    Me.PayNotesTxt.Enabled = !boces
    Me.PaidCheck.Enabled = !boces
    Me.DatePaidTxt.Enabled = !boces
    Me.PayTypeCombo.Enabled = !boces
    Me.CCNumTxt.Enabled = !boces
    Me.CheckNumTxt.Enabled = !boces
    Me.BOCESPOTxt.Enabled = boces
    Me.NumStudTxt.Enabled = boces
    Me.BOCESProgPOTxt.Enabled = boces
    Me.ThemeSameCheck.Enabled = boces
    Me.BOCESProgPracTxt.Enabled = boces
    Me.CmdPrintBillingInvoice.Enabled = !boces
    Me.PrintBillIanCmd.Enabled = !boces
    Me.PrintBillMelCmd.Enabled = !boces
    Me.cmdPrintEdReport.Enabled = !boces
    Me.CmdPrintSchoolContract.Enabled = boces
    Me.PrintContractIanCmd.Enabled = boces
    Me.PrintContractMelCmd.Enabled = boces
    Me.CmdPrintBOCESEdInvoice.Enabled = boces
    Me.StudCostLabel.Visible = boces
    Me.BookMoreCmd.Enabled = !boces
 
BlueIshDan, thank you for the suggestion, but again I'm not very familiar with booleans.

I think this is breaking away from my initial need into things that are far more complex. My understanding of modules is that they are essentially pockets of VBA code that can be referenced from event procedures in forms instead of having to apply the same code again and again.

I already have code that works in all of my forms. All I want to do is consolidate all of the event procedures that are essentially the same into VBA modules. The problem is that using "Me" to identify the controls from a public module isn't working.

So I need to find away to, from a public module, identify controls in a form (not a specific form, but whichever form is currently being focused on) other than using "Me".
 
Sorry, I thought I seen the problem had already been solved.

Pass the form to a public function.
 
Pass the form to a public function.

Yes, but how do I do that? I thought that I was successfully doing that with Galaxiom's suggestion but Access is giving me a warning saying "Compile error: Expected variable or procedure, not module".
 
Yes, but how do I do that? I thought that I was successfully doing that with Galaxiom's suggestion but Access is giving me a warning saying "Compile error: Expected variable or procedure, not module".

What have you called the Standard Module where the sub is located? It must not be the same name as any procedure.
 
Code:
Public Sub AgeStartSetCombo(frm As Form)

With frm
    If .AgeStartCombo = "All Ages" Then
        .AgeEndCombo = "All Ages"
        .AgeStartCodeTxt = "1"
        .AgeEndCodeTxt = "16"
    End If
    If .AgeStartCombo = "Senior Citizens" Then
        .AgeEndCombo = "Senior Citizens"
        .AgeStartCodeTxt = "15"
        .AgeEndCodeTxt = "15"
    End If
[COLOR="DarkRed"]    Select Case .AgeStartCombo
        Case "Pre-K & Nursery"
             .AgeStartCodeTxt = "2"
        Case "Kindergarten"
             .AgeStartCodeTxt = "3"
        Case "High School"
             .AgeStartCodeTxt = "12"
        Case "College"
             .AgeStartCodeTxt = "13"
        Case "Adults"
             .AgeStartCodeTxt = "14"
        Case Else
             .AgeStartCodeTxt = Left(.AgeStartCombo, 1) + 3
    End Select
[/COLOR]End With

End Sub
 
What have you called the Standard Module where the sub is located? It must not be the same name as any procedure.
Galaxiom: The module is called AgeStartSetCombo. The event procedure is AgeStartCombo. I purposefully chose a name that was different from anything else.

ACtually show me what you've got now.
BlueIshDan: The codes can be found in my second post on this thread.

Smig: Thank you for the suggestion of Select Case, but it doesn't address my problem right now. My problem is that now Access isn't recognizing my attempts to call a module.

I will try it, however, and see if that changes anything.
 
Last edited:
The problem is that you can't call a private sub in module from a separate form.

The sub in the module has to be public. The sub on your forms module can be private.
 
The problem is that you can't call a private sub in module from a separate form.

The sub in the module has to be public. The sub on your forms module can be private.

Yes, but that's what I have, isn't it? Here's the codes again:

Module:
Code:
Public Sub AgeStartSetCombo(frm As Form)

'Sets background age code

With frm
    If .AgeStartCombo = "All Ages" Then
        .AgeEndCombo = "All Ages"
        .AgeStartCodeTxt = "1"
        .AgeEndCodeTxt = "16"
    End If
    If .AgeStartCombo = "Senior Citizens" Then
        .AgeEndCombo = "Senior Citizens"
        .AgeStartCodeTxt = "15"
        .AgeEndCodeTxt = "15"
    End If
    If .AgeStartCombo = "Pre-K & Nursery" Then
        .AgeStartCodeTxt = "2"
    ElseIf .AgeStartCombo = "Kindergarten" Then
        .AgeStartCodeTxt = "3"
    ElseIf .AgeStartCombo = "1st Grade" Then
        .AgeStartCodeTxt = "4"
    ElseIf .AgeStartCombo = "2nd Grade" Then
        .AgeStartCodeTxt = "5"
    ElseIf .AgeStartCombo = "3rd Grade" Then
        .AgeStartCodeTxt = "6"
    ElseIf .AgeStartCombo = "4th Grade" Then
        .AgeStartCodeTxt = "7"
    ElseIf .AgeStartCombo = "5th Grade" Then
        .AgeStartCodeTxt = "8"
    ElseIf .AgeStartCombo = "6th Grade" Then
        .AgeStartCodeTxt = "9"
    ElseIf .AgeStartCombo = "7th Grade" Then
        .AgeStartCodeTxt = "10"
    ElseIf .AgeStartCombo = "8th Grade" Then
        .AgeStartCodeTxt = "11"
    ElseIf .AgeStartCombo = "High School" Then
        .AgeStartCodeTxt = "12"
    ElseIf .AgeStartCombo = "College" Then
        .AgeStartCodeTxt = "13"
    ElseIf .AgeStartCombo = "Adults" Then
        .AgeStartCodeTxt = "14"
    End If
End With

End Sub

In the form:
Code:
Private Sub AgeStartCombo_AfterUpdate()

    AgeStartSetCombo Me

End Sub
 
Smig: I tried using the Case Select technique and it didn't resolve the issue of Access producing an error when trying to reference a public module from my form.

I'm sure that it could replace my ElseIf statements if I ran it as an event procedure within the form--not as a public module--but I see no need to do so since I know that the ElseIf statements work in the form. Why change something that works? Thanks, though.
 
BTW I would have handled this entirely differently.

Include the AgeStart and AgeEnd settings as fields in a table along with the field that holds the text to be displayed in the combo.

Include the AgeStart and AgeEnd as hidden columns in the combo's RowSource based on this table.

Refer to these columns in the RecordSoure of the AgeStart and AgeEnd controls.
eg
Code:
= AgeStartcombo.Column(1)

The advantages of this approach are manifold. Firstly, no code is required. Secondly the ages and the displayed text in the combo can be edited in a table instead of being hard coded.
 

Users who are viewing this thread

Back
Top Bottom