Solved

Code not working properly when in macro module

Posted on 2016-11-21
18
34 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
  • 7
  • 7
  • 2
  • +2
18 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

pls try to use

Forms("Form1").ControlA

Open in new window

Regards
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
@Paul

ok, what are the conditions to set the controls to enabled/disabled?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
@Paul
upload a db with the form and related table...
0
 

Author Comment

by:Paul McCabe
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
@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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
It works perfectly, thank you so much !
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now