Linking Id's through a Macro in Access

[aytee111]

When you convert a macro to code, Access creates a VBA module with the macro's name, in this case it would be "macro1" or something like that. You can open it from the navigation pane by double-clicking on it. Once open, copy the code (without the first and last lines) to your OnClick event.



Well done for getting this far!

[June7]

If the macro was embedded in an event, such as button OnClick, after converting macro the event property should show [Event Procedure] then click the ellipsis (...) to go to the VBA procedure.

Review https://support.office.com/en-us/art...0-DAB7C75CBE0C

[JBros]

Hello and thank you both for your appreciate answers. Finally I've converted the Macro into VBA in a way I can access it, and in the navigation panel now I have the module for the Macro to access it through VBA. Here's the code of the Macro so far:

Function OnClick()
On Error GoTo OnClick_Err

    With CodeContextObject
        If (IsNull(Forms![Company Detail]!Id)) Then
            Beep
        End If
        If (Not IsNull(Forms![Company Detail]!Id)) Then
            DoCmd.OpenForm "Company Detail", acNormal, "", "[Id]=" & .Id, , acDialog
        End If
    End With


OnClick_Exit:
    Exit Function

OnClick_Err:
    MsgBox Error$
    Resume OnClick_Exit

End Function

For the moment, I think the code will work to what I'm doing. However, I'm still reluctanct when it comes to add code because I may change something I don't wish to. Which would be the code to add at the beginning so the form can be opened and not appear the error message? Did you already post it?

I'll keep checking Access documentation and try to familiarize more with it. I'd also like to, as said before, the elements in the sub-form (the names of the companies) appear like a hyperlink so it is more visual that you may click on it to see information. Again, thank you very much, this is being really useful for me thank you.

[aytee111]

Like I said before, you are referring to a form that hasn't been opened yet right from the start, that is where your error is coming from. Maybe you want to check the ID of the form that you are on instead? It would be If IsNull(Me!ID)

The pseudo code would be:
If IsNull(Me!ID) Then
Beep
Else
If detail form is open then (post #6)
filter it (post #8)
else
DoCmd.OpenForm....
End If

[JBros]

Like I said before, you are referring to a form that hasn't been opened yet right from the start, that is where your error is coming from. Maybe you want to check the ID of the form that you are on instead? It would be If IsNull(Me!ID)

The pseudo code would be:
If IsNull(Me!ID) Then
Beep
Else
If detail form is open then (post #6)
filter it (post #8)
else
DoCmd.OpenForm....
End If

I don't think I'm doing it well, as too many If statements make me confuse a little bit; thus, the filter part I haven't completely understood it so I'm pretty sure I'm using a bad syntax. I've tried some things but the error keeps appearing. I'm sharing my code so you can see where the syntax is not good.

Function OnClick()
On Error GoTo OnClick_Err

    With CodeContextObject
        If (IsNull(Me!Id)) Then
            Beep
        Else
        If CurrentProject.AllForms("Company Detail").IsLoaded = False Then
            ' Form is closed
        Else
            ' Form is open
        End If
        Forms!companydetailsform.Filter = "companyid=" & Me!companyid
        Forms!companydetailsform.FilterOn = True
        Forms!companydetailsform.Filter.Requery
        If (Not IsNull(Forms![Company Detail]!Id)) Then
            DoCmd.OpenForm "Company Detail", acNormal, "", "[Id]=" & .Id, , acDialog
        End If
    End With


OnClick_Exit:
    Exit Function

OnClick_Err:
    MsgBox Error$
    Resume OnClick_Exit

End Function

Thank you very much.

[aytee111]

Function OnClick()
On Error GoTo OnClick_Err

    With CodeContextObject
        If (IsNull(Me!Id)) Then
            Beep
        Else
        If CurrentProject.AllForms("Company Detail").IsLoaded = False Then
            ' Form is closed
            DoCmd.OpenForm "Company Detail", acNormal, "", "[Id]=" & Me!Id, , acDialog
        Else
            ' Form is open
            Forms![Company Detail].Filter = "id=" & Me!ID
            Forms![Company Detail].FilterOn = True
            Forms![Company Detail].Requery
        End If
    End With


OnClick_Exit:
    Exit Function

OnClick_Err:
    MsgBox Error$
    Resume OnClick_Exit

End Function

[JBros]

Thank you for fixing the code. However, I'm still struggling to make it work. The error that shows still is the same. I've named this macro 'OnClick', so in the events of the table it appears like this:



However, when I click in Access to see the Macro, it appears like this, which I think it is the same structure than before the changes:



On the navigation panel, I have the Macro 'OnClick' in the part of 'Macros'. When I click on it, it shows the same error than always. When I click on the VBA in modules it appears the VBA with the code you've just provided.



I don't know if with this reference you may know what may be happening here that shows the same error again and again... Thank you very much for all your help so far

[aytee111]

In the properties list, in English it is called "OnClick". Next to this you will select [Event Procedure]. Then click on the 3 dots next to it and it will take you to the VBA code attached to that event. That is where you will put the code.

Or if you want to leave it as a macro (although I don't see why you would want to do this) you can run the VBA function inside your macro using RunCode.

[June7]

Did you thoroughly review the referenced tutorial link in post 17? It describes in detail how to convert macros to VBA and how to view the resulting VBA procedure.

I agree with aytee111, if you use VBA, why mix in macro coding? Move the code into the button's Click VBA procedure, not in a general module.

I have never seen CodeContextObject. Did the macro converter generate that? I don't think it is necessary.

[JBros]

Thank you both for the answers.

I have never seen CodeContextObject. Did the macro converter generate that? I don't think it is necessary.

Yes, this was actually automatically generated.

Regarding the main topic, I've changed the 'OnClick' event to where it says [Event Procedure] and added the code to the VBA associated to it. However, now it pops up another error message. The code that is in the [Event Procedure] VBA is this one:

Private Sub Company_Click()

End Sub
Function OnClick()
On Error GoTo OnClick_Err

    With CodeContextObject
        If (IsNull(Me!Id)) Then
            Beep
        Else
        If CurrentProject.AllForms("Company Detail").IsLoaded = False Then
            ' Form is closed
            DoCmd.OpenForm "Company Detail", acNormal, "", "[Id]=" & Me!Id, , acDialog
        Else
            ' Form is open
            Forms![Company Detail].Filter = "id=" & Me!Id
            Forms![Company Detail].FilterOn = True
            Forms![Company Detail].Requery
        End If
    End With


OnClick_Exit:
    Exit Function

OnClick_Err:
    MsgBox Error$
    Resume OnClick_Exit

End Function

However, now the error message that appears says this:



Which translated would be something like:

The expression 'OnClick' you've specified as a value of the event property give the error 'The member already exists in an object module from where it is from'. This error occurs when an event could not be executed because the location of the event logic can not be evaluated. For example, if the OnOpen property of a form is set to = [Field], this error occurs because an event or macro name is expected to be executed at the occurrence of the event.

I don't know why this is actually that complicated... do you have any ideas why it gives me this error? Thank you very much.

[aytee111]

When you click on the three dots next to Event Procedure, what does it show you? From here it looks like the subroutine is empty, there is no code associated with the event. This is showing that when you click on the field named "Company" nothing will happen. So where can the error coming from? There is something/somewhere else that we are not being shown.

[JBros]

I have re-created the database in order to avoid possible past mistakes. So, first of all I create the table 'Companies' and fill in some data. Next, I'd create the Subform Companies with the information in Companies' table. Then I create the 'Home' form, which is an empty form with the subform Companies embedded. It shows correctly. Finally I create the 'Company Detail' form, which is an emergent form with some data from the 'Companies' table.

Then I go to detail view of 'Home' form, click into the 'Company name' field inside the SubForm and select [Event Procedure], press the ... button and add the code. When I look into it again, it shows this code:

Private Sub Form_Click()
Function OnClick()
On Error GoTo OnClick_Err

    With CodeContextObject
        If (IsNull(Me!Id)) Then
            Beep
        Else
        If CurrentProject.AllForms("Company Detail").IsLoaded = False Then
            ' Form is closed
            DoCmd.OpenForm "Company Detail", acNormal, "", "[Id]=" & Me!Id, , acDialog
        Else
            ' Form is open
            Forms![Company Detail].Filter = "id=" & Me!Id
            Forms![Company Detail].FilterOn = True
            Forms![Company Detail].Requery
        End If
    End With


OnClick_Exit:
    Exit Function

OnClick_Err:
    MsgBox Error$
    Resume OnClick_Exit

End Function
End Function

The error shown is the same as before. It seems that it says that the register I'm clicking on it already exists in the destination form; well, that's what I want as the Company I click should be the same company that appears in 'Company detail' but just with some more detail. I can't figure out for the moment where the error can be. I'd provide more information if necessary but I think this is pretty much all. Thank you very much.

[aytee111]

Sub and Function are both words used to denote the start of the routine. You have two End Function's and no End Sub. I don't see how Access could have created this.

Private Sub Form_Click() - this is an OnClick event for the form, not for the Company.

You are doing something wrong somewhere! Click on the Company field, make sure it is highlighted, in the Properties list make sure that the name of the object is Company. Then click on the three dots and it should end up looking something like:
Private Sub Company_Click()

With
....
End With

End Sub

[JBros]

Hello,

you are right with your response. I've referred to the specific 'Company' field just like this:



And when I click the three dots it appears this code:



I've manually changed the last 'End Function' for an 'End Sub'. Is the idea clear for Access this way? The error it keeps showing is the same. Any thoughts on where or what may be causing this to not work? Thank you very much.

[aytee111]

Make it look like I showed you in post #28. Remove all other code. Include all the code in between With til End With.