• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

How can I get the value from column 2 of a combo box and save it to a different field on the form using Macros?

I'm trying to learn how to use the Macros feature in Access 2010.  I have no problem writing the code using VBA, but I need to figure out how to do this with the macros instead.
I have a combo box that has three columns.  I want to automatically update a field on the form with the value from the last column when the user selects an option from the drop down box.

The combo box is named  EventDate and I'd like to update the EventID once a value is selected.
Here is the VBA in case this helps you to understand what I want to do:
on the afterUpdate event of the [EventDate] combo box:    [EventID] = [EventDate].Column(2)


0
ChampagneGal
Asked:
ChampagneGal
  • 8
  • 5
  • 3
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So, in the AfterUpdate event of this combo, you want to run a Macro to do this instead of the code you have?

Why not skip the code and the macro and just make this the Control Source of EventID:

=[EventDate].Column(2)

mx
0
 
ChampagneGalAuthor Commented:
I want the value stored back to the field [EventID] in the table.   If I change the control source to =[EventDate].Column(2) how does it get back into the table?
0
 
Jeffrey CoachmanCommented:
Whats the need to "Store" the value, if it can be "Derived"?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
is the combo box bound to your EventDate field?

Normally, you would not store both of these values in your table, and I don't normally display EventIDs to my users.  Have you considered using the EventID as the bound column and the control source of the combo box, but displaying the EventDate.

I assume you cannot have more than one Event on any given day.  If you can, you will have a problem with this design.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"If I change the control source to =[EventDate].Column(2) how does it get back into the table?"

Easy ... use the Form BeforeUpdate event:

Private Sub Form_BeforeUpdate (Cancel As Integer)

Me![EventID] = Me.EventDate.Column(2)

End Sub

OR


Private Sub Form_BeforeUpdate (Cancel As Integer)

Me![EventID] = Me.txtEventID

End Sub

This assumes you have a text box named txtEventID with the Control Source set as described above:

=EventDate].Column(2)

mx
0
 
ChampagneGalAuthor Commented:
I can easily do this through VBA code.  I'm trying to figure out how to do it with the new "Macros" in Access 2010.   I was originally going to just store the Event ID in the table, but the client wants to have the event date and event title stored in the table as well.   I know that is redundant, but that's what they want.

I could write a data macro to populate the [EventID] based on the value entered into the [EventDate] and the [EventTitle], but I was trying to find out if I could write a macro that was assigned to the AfterUpdate Event of the [EventDate] combo box on the form and have it automatically populate the [EventID] field on the same form.    It's easy to do with VBA, but I need to write this without VBA - just using the Macros.

Is this possible to do with a macro?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
By far the easiest route is using VBA code as shown :-)

I'm not really a macro guy ....
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Just curious ... why ... do you need to use Macros?  There is virtually no error handling capability with Macros, and this is sooooo easy to do with VBA.

?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Hey ... I will buy a bottle of Trader Joe's Almond Champagne if you use VBA :-)
0
 
Jeffrey CoachmanCommented:
Again, ...whats the need to "Store" the value if it can be derived...?
I am not saying the storing the value is a bad idea, I'm just curious as to why you need to store it.

<I need to write this without VBA - just using the Macros.>
Again, just curious as to Why?


Or are you really only interested in how this is done with a Macro...?

In Versions of Access Prior to 2007, you could do this via the SetValue Action
But now this seems not available: (http://en.allexperts.com/q/Using-MS-Access-1440/2010/4/Missing-SetValue-Macro-Action.htm)


So to do this with a macro you may have to use SetTempVars, to "create/set" the value:
Action: SetTempVar
Argument: strCity, [Forms]![frmCustomers]![cboZip].[column](1)

...then use something like this:
 =Tempvars!strCity
...as the controlsource of a textbox to "display" this value in to a control.

*However* it is not clear (to me at least) how to actually "Store" that value in the table field...

Note that I did not have too much time to dig into this deeper...

Like the other Experts, I use VBA mainly, not macros...

JeffCoachman


0
 
ChampagneGalAuthor Commented:
I totally agree with doing it through VBA, but I need to teach people how to use the new macros in Access 2010.  

When it comes to VBA I haven't run across anything I can't do, but these macros are a PAIN.

I don't think they are very user friendly, but I'm trying to come up with ideas to show non-programmers how to start using the macros.  

It sounds to me like no-one knows how to do this either.  
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"When it comes to VBA I haven't run across anything I can't do"
Totally agree.

"but these macros are a PAIN."
Absolutely.

"I don't think they are very user friendly,"
They are not.

So ... not liking the new macro editor ... when I DO have to deal with a macro.

Meanwhile ... hopefully these KB's will help you:


http://blogs.office.com/b/microsoft-access/archive/2010/02/26/tutorials-access-2010-macro-designer.aspx

http://blogs.office.com/b/microsoft-access/archive/2009/07/28/meet-the-access-2010-macro-designer.aspx

Main link:

http://blogs.office.com/search/searchresults.aspx?q=macros

Now ... about that bottle of Trader Joe's Almond Champagne ....

mx
0
 
Jeffrey CoachmanCommented:
<but I need to teach people how to use the new macros in Access 2010.   >
Yes, unfortunately in the .accdb format the wizards will only create embedded macros...

Also note that Macros of this kind (automation by the Wizards) were only created so that the average user could have some fairly basic automation, without learning VBA.
So here, there really should not be all that much of a need for anything more than the basics
Doing anything more complex than this should be left to just a select few users, not all.

Like most Experts here I may only use a macro for:
AutoExec
AutoKeys
0
 
ChampagneGalAuthor Commented:
Thank you both for spending so much time and giving me some links with additional info.  

I think we all agree that macros are basically a waste of time if you know VBA.  Unfortunately I have to work with them.  

I have not found any real in-depth info on macros and I think that's because no one who knows what they are doing would use macros.   :-)

Again I really appreciate all the effort you both made to try and help me.  Thank you and DatabaseMX - I'll think of you the next time I have a glass of champagne  :-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome.
Are you in So Cal?  and/or have you even had Almond Champagne ?

mx
0
 
ChampagneGalAuthor Commented:
Yes and I like almond champagne  :-)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Really ... and you've been to Trader Joe's then ? ... where I've shopped almost exclusively for 30+ years .....

We may have crossed aisles ...  what part of So Cal ?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now