Select and item in a combo box AND do an Enter event

[tryit]

Thanks to someone here, I've learned that to set my combo box to a
specific item I do something like:

Me.cboCBB = Me.cboCBB.ItemData(0)

However, the combo box has a macro associated with it. When the user
actually manually selects an item in the combo box, it does a
SearchForRecord.

The above item-selecting code, however, merely sets the cbo box to
ItemData(0) without running the embedded macro. Argh.

How do I ALSO get the embedded macro to run?


TIA,
Tom

 

[Tom Wickerath]

Hi Tom,

Your use of the term "embedded macro" suggests that you are using Access
2007. Is this correct? Since you are apparently using some VBA code [ie.
Me.cboCBB = Me.cboCBB.ItemData(0)], why not just convert your embedded macro
to VBA code? Combo boxes that are used to find records are unbound; the
wizard-created code for Access 2003 and prior versions creates an AfterUpdate
procedure. Thus, I think you can convert your embedded macro to the
equivalent AfterUpdate procedure, and then call this procedure from the same
procedure (Form_Load?) that you are using to set the combo box to a specific
item. An example of the type of VBA code needed to find a record is shown in
paragraph 11 of this article that I wrote:

Find a Record
http://www.access.qbuilt.com/html/find_a_record.html


Try something like this (assumes bound field of cboCBB is a long integer,
not a text data type):

Private Sub Form_load()
Me.cboCBB = Me.cboCBB.ItemData(0)
cboCBB_AfterUpdate
End Sub


Private Sub cboCBB_AfterUpdate()
On Error GoTo ProcError

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindFirst "[pkMemberID] = " & Str(Nz(Me![cboCBB], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cboCBB_AfterUpdate..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[tryit]

Hi Tom,

Why not, indeed.

Yes, I am using 2007. I would really love to know how to convert an
"embedded macro" to VBA code. That would save me a world of
problems. I was told this was not possible. Can you please let me
know how to do this?


Many thanks,
Tom

Hi Tom,

Your use of the term "embedded macro" suggests that you are using Access
2007. Is this correct? Since you are apparently using some VBA code [ie.
Me.cboCBB = Me.cboCBB.ItemData(0)], why not just convert your embedded macro
to VBA code? Combo boxes that are used to find records are unbound; the
wizard-created code for Access 2003 and prior versions creates an AfterUpdate
procedure. Thus, I think you can convert your embedded macro to the
equivalent AfterUpdate procedure, and then call this procedure from the same
procedure (Form_Load?) that you are using to set the combo box to a specific
item. An example of the type of VBA code needed to find a record is shownin
paragraph 11 of this article that I wrote:

    Find a Record
   http://www.access.qbuilt.com/html/find_a_record.html

Try something like this (assumes bound field of cboCBB is a long integer,
not a text data type):

Private Sub Form_load()
    Me.cboCBB = Me.cboCBB.ItemData(0)
    cboCBB_AfterUpdate
End Sub

Private Sub cboCBB_AfterUpdate()
On Error GoTo ProcError

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

    rs.FindFirst "[pkMemberID] = " & Str(Nz(Me![cboCBB], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ExitProc:
    Exit Sub
ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
          vbCritical, "Error in procedure cboCBB_AfterUpdate..."
    Resume ExitProc
End Sub

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________

Thanks to someone here, I've learned that to set my combo box to a
specific item I do something like:

     Me.cboCBB = Me.cboCBB.ItemData(0)

However, the combo box has a macro associated with it.  When the user
actually manually selects an item in the combo box, it does a
SearchForRecord.

The above item-selecting code, however, merely sets the cbo box to
ItemData(0) without running the embedded macro.  Argh.

How do I ALSO get the embedded macro to run?

TIA,
Tom

 

[Tom Wickerath]

Hi Tom,

Sorry, I did not notice your reply from 8/21 until just a few minutes ago...

I was told this was not possible.

It is not possible to convert a .accdb file with embedded macros to an
earlier version .mdb file, by using the convert wizard. It is, however,
possible to convert embedded macros to the equivalent VBA code. The process
may not seem so straight-forward, and you may need to fill in some gaps along
the way, but I think it can be done. Lets give it a try.

Open the Northwind 2007 template. Open the Customer List form in design
view. Display the Property Sheet, and select the ID field. You should see the
following, on the Event tab:

On Click [Embedded Macro] V ...

Click on the Build button (the button with the three dots). You should now
see the Embedded Macro in all it's glory. Click on the Save As button in the
ribbon. Click "OK" to the prompt to Save 'Customer List' to a macro with the
same name. Close the Macro Builder, and close the Customer List form. Select
your new Customer List macro without opening it (it will error if you attempt
to run it). After selecting the Customer List macro, click on the Database
Tools tab on the ribbon. In the first group, Macro, you should see "Convert
Macros to Visual Basic". Click on this option. You should see a Conversion
Finished message. In my case, I now see the Visual Basic Editor, with a grey
background.

Click on View | Project Explorer from within the Visual Basic Editor (VBE).
In the Modules section, you should see "Converted Macro - Customer List".
Double-click on this module. You should see some code that includes "With
CodeContextObject", etc. I don't think you need to worry about incorporating
that line of code into your final conversion. It looks like it should be
something like this, once converted:

Option Compare Database
Option Explicit

Private Sub ID_Click()
On Error GoTo ProcError

If Me.Dirty = True Then 'Save the record
Me.Dirty = False
End If

DoCmd.OpenForm "Customer Details", _
WhereCondition:="[ID] = " & Nz([ID], 0), _
WindowMode:=acDialog

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ID_Click..."
Resume ExitProc
End Sub


To be honest, I'm really not too sure about the rest of the converted VBA
code, which includes the TempVars stuff, with tests for If Not IsNull(.ID)
and If IsNull(.ID). Seems to me like saving the record would fail if the ID
was null. And, in this particular case, the ID field in the form is bound to
the ID field (Autonumber) in the Customers table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________