Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Code not working properly when in macro module

Posted on 2016-11-21
18
Medium Priority
?
60 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

688 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