Oops. Let see if this comes through.
3. tryit
View profile
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 On Aug 19,
7:40 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote: - Hide
quoted text -- Show quoted text -> 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 MVPhttp://
www.accessmvp.com/TWickerath/
__________________________________________ > "tryit" wrote: > >
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
More options Aug 21 2009, 6:32 am
Newsgroups: microsoft.public.access.forms
From: tryit <
[email protected]>
Date: Fri, 21 Aug 2009 07:32:46 -0700 (PDT)
Local: Fri, Aug 21 2009 6:32 am
Subject: Re: Select and item in a combo box AND do an Enter event
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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
On Aug 19, 7:40 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
- Hide quoted text -
- Show quoted text -
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:
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/
__________________________________________
Reply to author Forward
Report spam
Reporting spam
Message reported
Rate this post: Text for clearing space
Cancel
Send Discard
From:
DanDungan <
[email protected]>
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the
picture below or the numbers you hear by clicking the accessibility
icon. Listen and type the numbers you hear
Send Discard
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.
4. Tom Wickerath
View profile
More options Aug 23 2009, 6:07 pm
Newsgroups: microsoft.public.access.forms
From: Tom Wickerath <AOS168b AT comcast DOT net>
Date: Sun, 23 Aug 2009 19:07:01 -0700
Local: Sun, Aug 23 2009 6:07 pm
Subject: Re: Select and item in a combo box AND do an Enter event
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
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/