CODEWITHSUNDEEP
ms-access
Matt Hall
Matt Hall

Reputation: 2412

Macros and VBA on the same event

I used the combo box wizard to allow the combo box to determine what record shows on a form.

The wizard seems to build a macro on the combo box's AfterUpdate event to allow this to happen. I'd like to be able to add some VBA to the AfterUpdate event as well, but from the looks of it Access only lets you 1 option out of Expression/Macro/Code at a time.

Is there some way I can maintain the effects of the combo box created by the wizard and freely add more vba code to the AfterUpdate event as well?

Upvotes: 1

Views: 1577

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

There is no easy way about this that I can tell. I am not mad about the new embedded macro 'feature'.

You can open up the macro, ctrl+A to select all, ctrl+C to copy to the clipboard and then close. Using the ribbon, choose to create a macro and ctrl+V to paste in the embedded macro. You can now save this as VBA, Convert Macros to Visual Basic from the ribbon. You will get:

DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))

And some error coding. Then there is the way I would prefer, which is on these lines, created by selecting [Event Procedure] and typing. You should, of course, add error coding.

If Not IsNull(Me.TheComboName) Then
    ''Force a save
    If Me.Dirty Then
       Me.Dirty = False
    End If

    ''This will go to first record if not found.
    ''You will need quotes for a text data type
    Me.Recordset.FindFirst "ID=" & Me.TheComboName 
Else
   ''This probably should not happen, so you may have 
   ''problems, it's up to you
End If

You might like to read the late D Fenton's answer here

Having completed the above, you now have an event procedure that finds a record and you can add what ever extra code is needed.

Upvotes: 1

Related Questions