Solved

Code not working properly when in macro module

Posted on 2016-11-21
18
55 Views
Last Modified: 2016-11-23
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
Comment
Question by:Paul McCabe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
  • +2
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41895841
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
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41895845
Hi,

pls try to use

Forms("Form1").ControlA

Open in new window

Regards
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41895859
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
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.

 
LVL 34

Expert Comment

by:Norie
ID: 41895864
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
 

Author Comment

by:Paul McCabe
ID: 41895865
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41895872
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
 

Author Comment

by:Paul McCabe
ID: 41895884
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
 
LVL 34

Expert Comment

by:Norie
ID: 41895892
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41895963
@Paul

ok, what are the conditions to set the controls to enabled/disabled?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41895989
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
 

Author Comment

by:Paul McCabe
ID: 41896208
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41896726
@Paul
upload a db with the form and related table...
0
 

Author Comment

by:Paul McCabe
ID: 41897018
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
 

Author Comment

by:Paul McCabe
ID: 41898564
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41899472
@Paul
Is there any reason why you have a Default Value of 0 in fields "IssuedByMfg" and "IssuedBySupport" on table T_IncomingInvoices ?
0
 

Author Comment

by:Paul McCabe
ID: 41899558
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41899635
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
 

Author Comment

by:Paul McCabe
ID: 41899980
It works perfectly, thank you so much !
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question