Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Code not working properly when in macro module

I am using MS Accesss 2016, and am trying to call a macro module from a form’s VBA code using the following:

Option Compare Database
Private Sub Form_Current()
MyModule.MyProcedure
End Sub

The module’s name is "MyModule" and contains the following (simplified) code:

Sub MyProcedure()
If IsNull(ControlA) = True Or ControlA = 0 Then
VariableX = 0
Else
VariableX = 1
End If
VariableX = 0 Then
ControlA.Enabled = True

....

Now, the code that is currently in the module works perfectly well when it is incorporated in the form’s VBA code, but when I try to run it by calling the module I get a VBA run-time error 424, at the ControlA.Enabled = True line. I have searched online, but cannot seem to find anything relevant to this case, and am stumped. Can anyone point me in the right direction ?
0
Paul McCabe
Asked:
Paul McCabe
  • 7
  • 7
  • 2
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you have to explicitly call the form whaere the control is if  moved the procedure to a regular module

Sub MyProcedure()
 If  Forms!NameOfForm.ControlA= True Or Forms!NameOfForm.ControlA = 0 Then


.
.
.
 there are other ways to make this procedure usable by any form. but try this one out first
0
 
Rgonzo1971Commented:
Hi,

pls try to use

Forms("Form1").ControlA

Open in new window

Regards
0
 
Gustav BrockCIOCommented:
You can do like this - and simplify the code a little:

Option Compare Database

Private Sub Form_Current()

    MyModule.MyProcedure(Me!ControlA)

End Sub

The module’s name is "MyModule" and contains the following (simplified) code:

Public Sub MyProcedure(ByRef FormControl As Control)

    Dim VariableX As Integer

    If Nz(FormControl.Value, 0) <> 0 Then
        VariableX = 1
    End If

    If VariableX = 0 Then
        FormControl.Enabled = True
    End If

End Sub

Open in new window

/gustav
1
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
NorieCommented:
You could pass the control to the sub in the module.

To do that change the code in the module to this,
Sub MyProcedure(ctrl As Object)
Dim VariableX As Long

    If IsNull(ctl) = True Or ctl = 0 Then
        VariableX = 0
    Else
        VariableX = 1
    End If

    ControlA.Enabled = Iif(VariableX = 1, True, False)
...
End Sub

Open in new window

and call it like this.
MyModule.MyProcedure Me.ControlA

Open in new window

0
 
Paul McCabeAuthor Commented:
Thanks a lot for your suggestion. This seems to be working for text boxes, but I get an error for command buttons. For command buttons, the code I had was: MyButton.Enabled = True, which following your suggestion I converted to: Forms!MyForm.MyButton = True, which gives an error. Could you kindly advise on the correct syntax for commandbuttons in this case ?
0
 
Rey Obrero (Capricorn1)Commented:
now, you need to really revise the codes to accept values for control name and control type

I can't write the codes now but, other experts might do so..
0
 
Paul McCabeAuthor Commented:
Thank you all for your replies ! I had simplified the code for the purpose of asking the question, but in fact, these are 4 controls, consisting of 2 text boxes and 2 command buttons whose enable / disable I am trying to modulate in the code. Sorry, in retrospect I should have provided a more precise scenario from the start.
0
 
NorieCommented:
Paul

You'll still need the Enabled part when you use Forms!MyForm ... etc

PS Have you tried either Gustav's or my code?
0
 
Rey Obrero (Capricorn1)Commented:
@Paul

ok, what are the conditions to set the controls to enabled/disabled?
0
 
Rey Obrero (Capricorn1)Commented:
here is a generic codes you can use

Private Sub Form_Current()
myModule.MyProcedure Me
End Sub

Open in new window



Sub MyProcedure(MyForm As Form)
Dim ctl As Control
For Each ctl In Forms(MyForm.Name).Controls
    
    If ctl.ControlType = acTextBox Then
    Debug.Print ctl.Name, ctl.Value
        'codes here
    
    End If
    If ctl.ControlType = acCommandButton Then
    Debug.Print ctl.Name
        'codes here
    
    End If

Next
End Sub

Open in new window

0
 
Paul McCabeAuthor Commented:
Sorry, I got called away for a few hours...I tried adapting both Gustav's and Norie's code to a situtation with 4 controls (2 textboxes and 2 command buttons) but unfortunately wasn't able to get the code to work. I was a little unsure how to interpret implementing Rey's last code with multiple controls. Sorry. Here is a more detailed background in case it is of help:
Background: I have a form "F_Invoices_Inbound"for recording invoice information. This has two combo boxes, "invoice_manufacturer" and "invoice_support". The combo boxes are overlaid with transparent command buttons ("btn_manufacturer" and "btn_support") that bring up other forms with details of the respective manufacturer or support company.  Only one of these combo boxes should have a value (can't have the same invoice coming from 2 different companies), so when a value is input into one combo box, I want the other combo box and its command button to be inactivated to prevent input.

The following is the code that is working fine outside of a module. I developed it under as Private Sub Form_Current() routine, but it will
need to be replicated for combo box after update events as well. All this code is rather unwieldy, hence my attempts to get it stored in a module.

As you can see, I am pretty much a beginner at VBA, so although I am sure the code below is simplistic and inefficient, but at least I can comprehend it, and it works ! So if any potential solutions could follow it as closely as possible I would be very grateful.

Private Sub Form_Current()

'Set whether or not there is anything in the Manufacturer or Support combo boxes (define as: "something there" = 1, "null or zero" = 0)

If IsNull(invoice_manufacturer) = True Or invoice_manufacturer = 0 Then
Manufacturer = 0

Else

Manufacturer = 1

End If

If IsNull(invoice_support) = True Or invoice_support = 0 Then
Support = 0

Else

Support = 1

End If

'Check combinations and act acordingly

'Both combo boxes are empty
If Manufacturer = 0 And Support = 0 Then

invoice_manufacturer.Enabled = True
btn_manufacturer.Enabled = True
invoice_support.Enabled = True
btn_support.Enabled = True

End If

'Both combo boxes have values; unlikely situtation, but...just in case (keep both combos enabled to make correction possible)
If Manufacturer = 1 And Support = 1 Then

invoice_manufacturer.Enabled = True
btn_manufacturer.Enabled = True
invoice_support.Enabled = True
btn_support.Enabled = True

End If

'Only one combo box has a value
If Manufacturer = 1 And Support = 0 Then

invoice_manufacturer.Enabled = True
btn_manufacturer.Enabled = True
invoice_support.Enabled = False
btn_support.Enabled = False

End If

'Only one combo box has a value
If Manufacturer = 0 And Support = 1 Then

invoice_manufacturer.Enabled = False
btn_manufacturer.Enabled = False
invoice_support.Enabled = True
btn_support.Enabled = True

End If

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
@Paul
upload a db with the form and related table...
0
 
Paul McCabeAuthor Commented:
It is in Japanese so I need to construct the relevant tables and forms from scratch. I will get back to you on this asap, please give me a day or two.
0
 
Paul McCabeAuthor Commented:
Here it is...the VBA code is working as it should, but it would be good to get it into a module to avoid its repeated repetition. This has been my sticking point.
TestDB.accdb
0
 
Rey Obrero (Capricorn1)Commented:
@Paul
Is there any reason why you have a Default Value of 0 in fields "IssuedByMfg" and "IssuedBySupport" on table T_IncomingInvoices ?
0
 
Paul McCabeAuthor Commented:
There is no particular need to have 0 as a default value; Access automatically set it up this way when I created the fields in the table.
0
 
Rey Obrero (Capricorn1)Commented:
see this revised db.

I created a new form "F_IncomingInvoices"  your form "F_Invoices seems to be corrupted

the codes are all in the Form's module. I don't see any relevance of placing them on a regular module
TestDB.accdb
0
 
Paul McCabeAuthor Commented:
It works perfectly, thank you so much !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 7
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now