New to VBA and Access, Need General Help :) (1 Viewer)

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
No luck being able to find how to pull any macros up yet, if someone could try help that would be highly appreciated

Regards, Rhys.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:14
Joined
Jan 23, 2006
Messages
15,361
Hek,

Click on and expand the modules area shown in your inventory code pic.jpg
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
Hek,

Click on and expand the modules area shown in your inventory code pic.jpg

Hey there, have expanded the modules and found "module 1" have clicked on it to see no code again, any other tips?
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
here is another screen cap of the modules expanded and showing the module has no code associated.
 

Attachments

  • inventory module pic.jpg
    inventory module pic.jpg
    85.8 KB · Views: 136

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
I have done some googling and someone has suggested that i use some VBA code to find embedded macros, im going to post the code here and hopefully one of you legends will be able to interpret it and let me know how to implement it to help me find these macros.

EDIT: I am extremely new to Access and VBA so i have 0 idea about this code, if it could help me i would be extremely appreciative to anyone who can help me interpret it and help me use it.

Code:
Public Sub FindEmbeddedMacros()
    On Error GoTo Error_Handler
    Dim oFrm                  As Object
    Dim frm                   As Access.Form
    Dim oRpt                  As Object
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control
    Dim prp                   As DAO.Property
 
    Access.Application.Echo False
    Debug.Print "Search Results"
    Debug.Print "Object Type", "Object Name", "Control Name", "Event Name"
    Debug.Print String(80, "-")
 
    'Search the forms
    For Each oFrm In Application.CurrentProject.AllForms
        DoCmd.OpenForm oFrm.Name, acDesign
        Set frm = Forms(oFrm.Name).Form
        With frm
            For Each prp In .Properties
                'Form Properties
                If InStr(prp.Name, "EMMacro") > 0 Then
                    If Len(prp.value) > 0 Then
                        Debug.Print "Form", frm.Name, , Replace(prp.Name, "EmMacro", "")
                    End If
                End If
            Next prp
            'Form Control Properties
            For Each ctl In frm.Controls
                For Each prp In ctl.Properties
                    If InStr(prp.Name, "EMMacro") > 0 Then
                        If Len(prp.value) > 0 Then
                            Debug.Print "Form", frm.Name, ctl.Name, Replace(prp.Name, "EmMacro", "")
                        End If
                    End If
                Next prp
            Next ctl
        End With
        DoCmd.Close acForm, oFrm.Name, acSaveNo
    Next oFrm
 
    'Search the Reports
    For Each oRpt In Application.CurrentProject.AllReports
        DoCmd.OpenReport oRpt.Name, acDesign
        Set rpt = Reports(oRpt.Name).Report
        With rpt
            'Report Properties
            For Each prp In .Properties
                If InStr(prp.Name, "EmMacro") > 0 Then
                    If Len(prp.value) > 0 Then
                        Debug.Print "Report", rpt.Name, , Replace(prp.Name, "EmMacro", "")
                    End If
                End If
            Next prp
            'Report Control Properties
            For Each ctl In rpt.Controls
                For Each prp In ctl.Properties
                    If InStr(prp.Name, "EMMacro") > 0 Then
                        If Len(prp.value) > 0 Then
                            Debug.Print "Report", rpt.Name, ctl.Name, Replace(prp.Name, "EmMacro", "")
                        End If
                    End If
                Next prp
            Next ctl
        End With
        DoCmd.Close acReport, oRpt.Name, acSaveNo
    Next oRpt
 
    Debug.Print String(80, "-")
    Debug.Print "Search Completed"
 
Error_Handler_Exit:
    On Error Resume Next
    Access.Application.Echo True
    If Not prp Is Nothing Then Set prp = Nothing
    If Not ctl Is Nothing Then Set ctl = Nothing
    If Not rpt Is Nothing Then Set rpt = Nothing
    If Not oRpt Is Nothing Then Set oRpt = Nothing
    If Not frm Is Nothing Then Set frm = Nothing
    If Not oFrm Is Nothing Then Set oFrm = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FindEmbeddedMacros" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:14
Joined
Jan 23, 2006
Messages
15,361
And if you ran this what was the result of running this routine?

If you haven't run this code and don't know how.

Here is what I would do:

Copy the vba code for the routine (Ctrl C)
Open a copy of your database
open the module1
(Ctrl V) to paste the code into module1

Then in the vba/vbe window:
Put your cursor anywhere in that routine and click
then Click on the green arrow at the top of the window.

You should see some printing in the immediate window

Good luck.
 

Attachments

  • Hek.png
    Hek.png
    70.7 KB · Views: 133
Last edited:

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
And if you ran this what was the result of running this routine?

If you haven't run this code and don't know how.

Here is what I would do:

Copy the vba code for the routine (Ctrl C)
Open a copy of your database
open the module1
(Ctrl V) to paste the code into module1

Then in the vba/vbe window:
Put your cursor anywhere in that routine and click
then Click on the green arrow at the top of the window.

You should see some printing in the immediate window

Good luck.

Hey there, i have done what you said and here is what has popped up in the immediate window:

Search Results
Object Type Object Name Control Name Event Name
--------------------------------------------------------------------------------
Form Employee Form Command61 OnClick
Form Employee Form Command62 OnClick
Form Home Command12 OnClick
Form Home Command47 OnClick
Form Home Command49 OnClick
Form Home Command60 OnClick
Form New Part Form Combo218 AfterUpdate
Form New Part Form Save And New OnClick
Form New Employee Form Command136 OnClick
Form New Employee Form Command116 OnClick
Form New Employee Form Combo137 AfterUpdate
Form Kits Command20 OnClick
Form Kits Command14 OnClick
Form Finishing Details Command65 OnClick
Form Picking Details Command98 OnClick
Form Packing Details Command63 OnClick
Form Splash Screen OnTimer
Form New Order OnLoad
Form New Order OnGotFocus
Form New Order Command69 OnClick
Form New Order Command70 OnClick
Form Completed Orders Command304 OnClick
Form Master Form AfterUpdate
Form Master Form OnTimer
Form Master Form Command59 OnClick
Form Master Form Command576 OnClick
Form Master Form Command58 OnClick
Form Master Form Command461 OnClick
Form Pressure Testing Details Command228 OnClick
Form Fabrication Details Command63 OnClick
Form Administrative Command76 OnClick
Form Administrative Command69 OnClick
Form Administrative Command70 OnClick
Form Administrative Command77 OnClick
Form Administrative Command81 OnClick
Form Administrative Command97 OnClick
Form Parts List Command25 OnClick
Form Parts List Command26 OnClick
Form Customer List Command25 OnClick
Form Customer List Command26 OnClick
Form Modify Order Command59 OnClick
Form Modify Order Command302 OnClick
Form New Customers Command24 OnClick
Form New Order Kits OnGotFocus
Form New Order Kits Command70 OnClick
Form Inventory Modification Command76 OnClick
Form Copy Of New Order OnLoad
Form Copy Of New Order OnGotFocus
Form Copy Of New Order Command69 OnClick
Form Copy Of New Order Command70 OnClick
Form Inventory Command76 OnClick
Form Inventory Inventorybtn OnClick
Form Kits Query Command25 OnClick
Form Kits Query Command26 OnClick
Form Kits Query Command180 OnClick
Form Kits Query Command182 OnClick
Form Copy Of Master Form AfterUpdate
Form Copy Of Master Form OnTimer
Form Copy Of Master Form Command59 OnClick
Form Copy Of Master Form Command58 OnClick
Form Copy Of Master Form Command461 OnClick
Report All Orders Command81 OnClick
Report Completed Orders Command123 OnClick
--------------------------------------------------------------------------------
Search Completed


Edit: Have attached a couple of pics because the format was jumbled from copy to paste
 

Attachments

  • immediate window results 1.PNG
    immediate window results 1.PNG
    74.7 KB · Views: 119
  • immediate window results 2.PNG
    immediate window results 2.PNG
    17 KB · Views: 120
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 06:14
Joined
Jan 23, 2006
Messages
15,361
These results indicate where the embedded macros are located.
eg
Form Employee Form Command61 OnClick indicates:

Employee Form has a control (command button) named Command61 and there is a macro for the OnClick event of that button.

As may have been indicated, not many use macros. However, there my be someone on the forum who may help you with deciphering what you are dealing with.

Good luck with your project.
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
These results indicate where the embedded macros are located.
eg
Form Employee Form Command61 OnClick indicates:

Employee Form has a control (command button) named Command61 and there is a macro for the OnClick event of that button.

As may have been indicated, not many use macros. However, there my be someone on the forum who may help you with deciphering what you are dealing with.

Good luck with your project.

I briefly understand what you mean, but im not sure what one of these macros is responsible for the "on order" column values in the inventory/inventory modification forms to be false. Any ideas?

Your help is highly appreciated, as im very stuck.

Kind Regards, Rhys.
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
So after talking with a few people, i have managed to somewhat rectify some small issues they were having with the program, but still nothing on the "on order" column values. Still dont understand why the values are entering in as negative values rather than the true value.
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
scratch that, no issues fixed on the front end yet, my end seems fine but its not changing on the front end
 

isladogs

MVP / VIP
Local time
Today, 10:14
Joined
Jan 14, 2017
Messages
18,186
Stumbled across this: https://support.microsoft.com/en-au...orrect-sorted-order-when-you-sort-the-negativ

Not sure if this has anything to do with the issue im having with the "On Order" column displaying incorrect values.

No it doesn't.
The article refers to a bug causing the order that records are sorted to be incorrect in some cases.
You have incorrect values in a field showing the number of each item 'On Order'

You need to go through each of the embedded macros on that form to determine whether those values are being calculated or have just been wrongly entered.
Unfortunately, without seeing your database or the embedded macros, nobody can tell you what is wrong with this application
 

isladogs

MVP / VIP
Local time
Today, 10:14
Joined
Jan 14, 2017
Messages
18,186
To view the contents of your embedded macros in your form...
Open the form in design view.
Click and hold (or right click) one of the controls listed as having an embedded macro. Now select Build event from the menu that appears.
The macro contents will be displayed.
Look for anything related to the On Order field.
Take a screenshot if relevant and you don't understand it.

Note you can also view embedded macros by selecting the ellipsis (…) in the controls' property sheet need to the words Embedded macro.

Another possibility is that On Order is a calculated field.
Go to the design view of the table containing that field and check its datatype.
If it says Calculated then look at the expression showing what the calculation used is.

Hope that helps you move forward.
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
ill try some of the things you are saying and ill let you know what happens.

Thanks so much isladogs, you are helping very much, Cheers.
 

isladogs

MVP / VIP
Local time
Today, 10:14
Joined
Jan 14, 2017
Messages
18,186
This might be a quicker method of viewing all your embedded macros.
First make a backup of your application just in case.
Now open your form in design view.
Go to the design tab and click Convert form macros to visual basic.
You can then view the code in the form module window and all macros will be gone.

Repeat for each database object containing embedded macros

BTW for the benefit of others, the code listing embedded macros was by Daniel Pineault and is available at https://www.devhut.net/2017/05/24/ms-access-find-embedded-macros/
 
Last edited:

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
i did the converting macros to VB and have come across some code for the "Inventory Modification" form, the code reads:
Code:
'------------------------------------------------------------
' Command76_Click
'
'------------------------------------------------------------
Private Sub Command76_Click()
On Error GoTo Command76_Click_Err

    DoCmd.OpenForm "Kits Query", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "Inventory Modification"


Command76_Click_Exit:
    Exit Sub

Command76_Click_Err:
    MsgBox Error$
    Resume Command76_Click_Exit

End Sub
 

Hek

Registered User.
Local time
Today, 03:14
Joined
Oct 30, 2018
Messages
73
any comments on the VBA code i posted above? that code is for the inventory modification form.

much appreciated isladogs

Regards, Rhys.
 

isladogs

MVP / VIP
Local time
Today, 10:14
Joined
Jan 14, 2017
Messages
18,186
i did the converting macros to VB and have come across some code for the "Inventory Modification" form, the code reads:
Code:
'------------------------------------------------------------
' Command76_Click
'
'------------------------------------------------------------
Private Sub Command76_Click()
On Error GoTo Command76_Click_Err

    DoCmd.OpenForm "Kits Query", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "Inventory Modification"


Command76_Click_Exit:
    Exit Sub

Command76_Click_Err:
    MsgBox Error$
    Resume Command76_Click_Exit

End Sub

That code opens a new form then closes the existing form. Not relevant to your issue.
Is that the correct form and the only embedded macro in it?
If so, either the on order values are calculated or just incorrectly entered manually. Look at the table design as mentioned previously.
 

Users who are viewing this thread

Top Bottom