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

T

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
 
T

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/
__________________________________________
 
T

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/
__________________________________________

tryit said:
Thanks to someone here, I've learned that to set my combo box to a
specific item I do something like:
Click to expand...
     Me.cboCBB = Me.cboCBB.ItemData(0)
Click to expand...
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.
Click to expand...
The above item-selecting code, however, merely sets the cbo box to
ItemData(0) without running the embedded macro.  Argh.
Click to expand...
How do I ALSO get the embedded macro to run?
Click to expand...
TIA,
Tom
Click to expand...
Click to expand...
 
T

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.
Click to expand...

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/
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Search using a combo box and then creating a report. 4
Combo box that will select and Go To a record on a form? 2
Combo box - not in list 2
Button and Combo Box 3
Disable ENTER key in Combo Box 6
Requery Combo box in popup form 2
Putting a CheckBox next to each item in a combo box 8
combo box to select a table 9

Top