Solved

Code not working properly when in macro module

Posted on 2016-11-21
18
49 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
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 49

Expert Comment

by:Rgonzo1971
ID: 41895845
Hi,

pls try to use

Forms("Form1").ControlA

Open in new window

Regards
0
 
LVL 49

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
 
LVL 33

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 119

Expert Comment

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

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 119

Expert Comment

by:Rey Obrero
ID: 41895963
@Paul

ok, what are the conditions to set the controls to enabled/disabled?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

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

Expert Comment

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

Expert Comment

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

Accepted Solution

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

24 Experts available now in Live!

Get 1:1 Help Now