Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

Forums     

Microsoft Access Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
Microsoft Access TutorialsTHE ACCESS FORUM HAS MOVED!

We are trying something new. We've moved the ACCESS FORUM to a Facebook Group.

Click HERE to join: ACCESS GROUP ON FACEBOOK

Permanent Link
Keywords: microsoft access forum
Post New Topic

Read Macro Open Form Where Condition by Richard S @ 6/7/2018
Hi folks,

Is it possible to have a compound "Where" when creating a command button to open a form ?  

The example in Advanced 1, Lesson 3 is a good starting point.  The condition is a simple "CustomerID=" & [CustomerID].

Is it possible (and how would you code) to have two conditions, such as:
"LastName=" & [LastName]  AND  "FirstName=" & [FirstName]   ?

Please advise when you get a moment.

Hope all is well & till later,
Rick S.
San Antonio, TX


Reply from Richard Rost:

You're close:

"WHERE LastName=""" & [LastName] & """ AND FirstName=""" & [FirstName] & """"

Show Just This Thread        Post Reply
Read Macro Builder by Stephen K @ 3/5/2018
I can't seem to find the If action in the macro builder. Im using Access 2007.


Reply from Alex Hedley:

Access 2007 was quite buggy, might be worth upgrading.

MS Article: Working with Macros and Expressions in Access 2010

Show Just This Thread        Post Reply
Read Copy Macro by Barry B @ 2/19/2018
In lesson 3 of Expert 22 it covers placing the combo box in the form footer and then using a button with a macro to send the information from the combo box to the fields in the form. I have a situation were I need to do just that and I have followed the instructions but it will not work in the database I am building. No error of any kind, it just does nothing. It works in the PC Resale database that I built while watching the videos so that tells me I am doing something wrong. If anyone has done this successfully I would appreciate any suggestions.


Reply from Alex Hedley:

Is it VBA or Macro, if you go to the Property Sheet is there an method against the Event, could it have been renamed?
Show Just This Thread        Post Reply
Read Mailmrege run from a macro by Richard S @ 1/25/2018
Hi folks,

Is it possible to run a Mail Merge by way of a macro ?

The Mail Merge based on a Query and works just fine, but would like to execute same from a Macro or Command Button.

Thoughts ?

Hope all is well & till later,
Rick S.
San Antonio, TX
Show Just This Thread        Post Reply
Read Leaving Dirty Record in Expert 22 macro by Richard S @ 1/23/2018
I noticed that each record added by the cool macro presented in Expert 22 (Lesson 3) left a "Dirty Record".  In the lesson, Rick would move to another record to resolve the "Dirty" condition.   What would be the best way to have the Macro do this automatically ?

I really enjoy the lessons... much appreciated !

Till later,
Rick S.


Reply from Alex Hedley:

You could have a move to record which would move off one and back onto another and it would save.
You could run a save command.
Show Just This Thread        Post Reply
Read Import Macro by Richard S @ 12/28/2017
Hi folks,
AM wondering how to run a saved import from a Macro... any thoughts ?

Hope all is well & till later,
Rick S.
San Antonio, TX


Reply from Alex Hedley:

I think it's covered in Access Expert Level 20 but you can check the outline.
Show Just This Thread        Post Reply
Read Access 2013 Copy Macros by John N @ 6/2/2017
Simply go to the macro builder of the macro you want copy; "Ctrl+A", "Ctrl+C (or Ctrl+X to cut); Exit; Go to the event you want to copy to, builder, place the cursor where you want it to copy to, "Ctrl+V".  That's it
Show Just This Thread        Post Reply
Read Set Focus with Macro by Susan Plotner @ 11/5/2015
The macro I am using has a Requery at the end. After the records are updated, the form always returns to the top. How can I tell the macro to go the next record instead after the requery?


Reply from Alex Hedley:

My Macro knowledge isn't vast, much prefer VBA.
But I think there is a GoToRecord and you can say Last or New.

Show Just This Thread        Post Reply
Read is there a way to print macros by Joseph M @ 10/25/2015
I have several buttons in my database for various functions. Is there a way to print out on paper the macros behind each button? I'd like to have a text record of each.


Reply from Alex Hedley:

This is un-tested (SO).


Dim varItem As Variant
  Dim strMacroName As String

  For Each varItem In CurrentProject.AllMacros
    strMacroName = varItem.Name
    'Debug.Print strMacroName
    DoCmd.SelectObject acMacro, strMacroName, True
    DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
    Application.SaveAsText acModule, "Converted Macro- " & strMacroName, _
      CurrentProject.Path & "\" & "Converted Macro- " & strMacroName & ".txt"
  Next varItem

Show Just This Thread        Post Reply
Read Probelm getting the undo macro to work by Joseph M @ 9/17/2015
I built a routine that sets a flag to one if the data on the form is dirty.

When the user selects to move to another form I have a message box form that tells the user data had been changed on this form. Do you wish to save those changes?
If he selects yes the flag is cleared and he proceeds to the next form.

If he selects no I have a run macro command that does an undo. The undo is failing. I think this may be because focus had changed to the message box.

I'm not sure how to refocus access to look back at the form and undo what was put in by the user. Any thoughts?


Reply from Alex Hedley:

Is this flag a variable in vba or field in the record you are updating?
You shouldn't need to do this. Access handles this itself.
You can do an If (Me.Dirty)

You can use the CONTROL.SetFocus to set focus against a control.

Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 9/1/2015
I deleted the form and created it all over. The message no longer appears.
Show Just This Thread        Post Reply
Read VBA instead of Macros by Ray W @ 8/31/2015
Ok Thanks Alex.
So Access 2010 wizard will not use vba, only macros right? So even when you put a check in the (Always use Event Procedures)that changes nothing right.
Richard it would be good to do a "Mastering Macros Seminars"
Thanks for all your help..


Reply from Alex Hedley:

The always use EP is for when you click the "..." button on the Events tab.
It usually pops up with the 3 options but now it should go straight to the VBA editor,
Macros is the next section in the Access Series.
Show Just This Thread        Post Reply
Read VBA instead of Macros by vicki H @ 8/30/2015
Alex, I am getting an error Ambiguous name detected:~  What could the tilde mean?


Reply from Alex Hedley:

Did you write the same method twice?

MS Article
This error occurs when there are multiple procedures with the same name in a module.
Show Just This Thread        Post Reply
Read VBA instead of Macros by Ray W @ 8/29/2015
Yes I did..


Reply from Alex Hedley:

Are you using the Wizard as that will still use Macros, cancel that and click ... against the Event
Show Just This Thread        Post Reply
Read VBA instead of Macros by Ray W @ 8/26/2015
access 2010 how can I change the way buttons are created with VBA instead of macros?
I went to Options, Object Designers and checked the (Always use Event Procedures)
But it still uses Macros???


Reply from Alex Hedley:

Did you save, close and re-open the db?
Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 8/25/2015
yes it was created in a form using a macro in a field named e-mail.
Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 8/21/2015
yes it was created on a form field.


Reply from Alex Hedley:

If you click on the Field and go to the Properties tab is there anything under events?
If you delete the Field and re-add it does it still pop up?
Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 8/15/2015
I created a macro for a field "e-mail address" to take me to MS Outlook and it did not work the way I planned and I deleted the macro, but it keeps popping up in various places. Where can I find these hidden macros ?


Reply from Alex Hedley:

Was this created on a Form Field?
Show Just This Thread        Post Reply
Read Copying an embeded macro to a standalone macro by Joseph M @ 8/7/2015
I have a button on a form I now wish to turn into a macro and call from other forms. I have tried to copy the code I entered into the button but it does not copy to the macro. Short of rewriting the whole thing are there any tricks to copy button embedded macros to standalone macros?


Reply from Alex Hedley:

You can copy the button and it will copy the macro with it.

MS Blog
Forms(0).Controls("foo").OnLoadMacro = Forms(1).Controls("bar").OnOpenMacro
DoCmd.SaveText acMacro, "foo", "Macro1"
.*Macro "shadow properties"

Alternative

Database Tools
Convert Form's Macros to Visual Basic
Show Just This Thread        Post Reply
Read Assign Macro to F Key by Robert R @ 5/23/2015
How can I assign a statement to one of my Fkeys, (F1-F12)so that if I press it its programmed to auto populate a note for my users. So we make alot of calls and instead of typing in, "called not home" could press F3 and simply have this same thing populate my notes as opposed typing it all day long.


Reply from Alex Hedley:

MS Article

Create the AutoKeys macro

  1. On the Create tab, in the Other group, click Macro. If this command is unavailable, click the arrow beneath either the Module or the Class Module button, and then click Macro.

  2. On the Design tab, in the Show/Hide group, click Macro Names to display the Macro Name column.

  3. In the Macro Name column, press the key or keyboard shortcut to which you want to assign the action or set of actions.

  4. In the Action column, add the action that you want the key or keyboard shortcut to perform. For example, you can add a RunMacro action that runs the Print Current Record macro when CTRL+P is pressed.

  5. AutoKeys macro containing PrintRecord macro action

  6. To assign more than one action to the keyboard shortcut, add the additional actions that you want below the first action. Leave the Macro Name column blank for each succeeding action.

  7. Repeat steps 3 and 4 for any other key assignments that you want to make.

  8. Click the Microsoft Office Button Office button image and then click Save, or press CTRL+S.

  9. In the Save As dialog box, under Macro Name, type AutoKeys.






Macro NameKey or keyboard shortcut
{F1}F1

Show Just This Thread        Post Reply
Read Hidden Macro by Derek Kong @ 3/27/2015
I am an idiot. I should have done that from the beginning, as it only took about 10 minutes to create a new form.


Reply from Alex Hedley:

It would be handy to figure out incase it happens again, you might have a complex Form what wouldn't be too easy to re-create.
I don't use Macros so it's not something I've come across before.
Show Just This Thread        Post Reply
Read Hidden Macro by Derek Kong @ 3/25/2015
No to both
Show Just This Thread        Post Reply
Read Hidden Macro by Derek Kong @ 3/24/2015
Under the macro single step, you will see under macro name: Navigation form: Navigationsubform: onEnter: Embedded macro. I can't find that embedded macro to delete it.


Reply from Alex Hedley:

It might be worth creating a fresh form and deleting this problematic one
Show Just This Thread        Post Reply
Read Hidden Macro by Derek Kong @ 3/24/2015
No events showing up on any of the tabs.
Show Just This Thread        Post Reply
Read Hidden Macro by Derek Kong @ 3/23/2015
Sorry Alex, Try this link.
https://plus.google.com/u/0/100984858311714530229/posts/W9cpJuys5WR


Reply from Alex Hedley:

Thanks.
If you open the form are there any events on that tab?
If you click the button do any show up in the Property Sheet?
Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 3/19/2015
Alex here are the pics.
https://plus.google.com/u/0/stream/circles/p44bbd1258cf9ac8a


Reply from Alex Hedley:

I'm not getting anything for that link
Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 3/18/2015
Hi Alex, thanks for your quick response, but my problem is I can't find the embedded macro anywhere to delete. It is not listed under All Access Objects and I can only see it under Create, Macro, action catalog, in this DataBase, Forms, Navigation form. Wish I could send a picture to you to show you the problem.


Reply from Alex Hedley:

Can you take a picture then upload it to flickr or put it on google plus or something like that and make it public then share the link here.
Show Just This Thread        Post Reply
Read Hidden Macro by Derek K @ 3/16/2015
Under a form I created call Navigation Form a macro called Navigationsubform onenter shows up under the Action Catatalog . I am trying to delete this marco as it keeps popping up as an error notice. Where do I go to find the macro to delete it. This came about when I tried to create an email macro.


Reply from Alex Hedley:

Just backspace the [Embedded Macro] against the Event and save, close and reopen the Form.
Show Just This Thread        Post Reply
Read Data Macros by Joe Mazzeo @ 2/4/2015
Rich
Have you done any training on data macros? I can see a real use for these and I like the way you teach. Any chance you might plan something soon?


Reply from Alex Hedley:

I don't think they've been covered yet, I've passed on the suggestion.



Update Should have used the search
They're covered in Lesson 4 of the DateTime Seminar
Show Just This Thread        Post Reply
Read VBA instead of Macros by Norm @ 1/28/2015
Alex I am sorry I didn't make it clear to you my question. My question was after I made the change you gave me it did produce VBA but no code all I got was this:

Private Sub Add_Click()

End Sub

I tried to convert a Macro and it did but the converted code didn't work, this is what I got: Ambiguous name detected: Ctl_Add_Click


Reply from Alex Hedley:

Did you name the macro with click on it?
That is added in VBA if it's a click event so it probably doesn't like it.
Try naming it something different and converting again.
Show Just This Thread        Post Reply
Read VBA instead of Macros by Norm @ 1/27/2015
Thanks Alex, it worked except it didn't generate any code, just a place to enter the code. As you already know the macro generates the code. is there any way the code can be generated in the VBA


Reply from Alex Hedley:

Ah sorry thought you meant you want to use VBA instead of Macros.

If you want to convert an already made Macro to VBA

In the Navigation Pane, right-click the form or report, and then click Design view.

On the Database Tools tab, in the Macro group, click either Convert Form's Macros To Visual Basic or Convert Report's Macros To Visual Basic.

In the Convert form macros or Convert report macros dialog box, select whether you want Access to add error handling code to the functions it generates. Also, if you have any comments in your macros, select whether you want to have them included as comments in the functions. Click Convert to continue.
Show Just This Thread        Post Reply
Read VBA instead of Macros by Norm @ 1/26/2015
Hi Alex, I have been trying to find where I can change from micros to vba in options. I cannot find it in options. can you tell me more?


Reply from Alex Hedley:

File
Options
Select Object Designers from the list.
Scroll down to the Forms/Reports section.
Click on the Always use event procedures box.
Click on OK to close the window.
Show Just This Thread        Post Reply
Read Command Button Wizard Makes Embedded Macros by Norm Bohana @ 1/24/2015
Thanks, I would like VBA code generate. Each time I create a new form it's not correct or complete. is there a way to show you what I mean?????


Reply from Alex Hedley:

You could upload a pic to Flickr or something similar and share the link.
You can set Access to use Code instead of a Macros for Events in the Options

If the Form is not correct then you need to update the Form to do what you want it to do.
What code did you type into your OnClick event in VBA?
Show Just This Thread        Post Reply
Read Command Button Wizard Makes Embedded Macros by Norm @ 1/21/2015
Sorry but I have not received any response from my last post


Reply from Alex Hedley:

So are you wanting it to be a Macro or use VBA in your Button?
Which Form isn't correct?
Show Just This Thread        Post Reply
Read Command Button Wizard Makes Embedded Macros by Norm @ 1/18/2015
First thanks for getting back to me. I just created a form in and old application and added a button using he wizard and it created VB code. I went to a new application and did he same thing and it created a macro. It's the same system. also the form was not correct.
Show Just This Thread        Post Reply
Read Command Button Wizard Makes Embedded Macros by Richard R @ 1/18/2015
I received a great question today from Norm. To paraphrase, he says he is using Access 2007 and whenever he creates a button using the Command Button Wizard (something like open a form, find a record, etc.) the button creates an Embedded Macro for him, which he doesn't want. He wants VBA so he can edit it.

Unfortunately, if you're using the Command Button Wizard, that's the default setting, and as far as I can tell, the ONLY setting for the Wizard. You can force Access to give you the VBA editor if you're making your OWN buttons, but changing the setting under File > Options > Object Designers > Always Use Event Procedures. This, however, doesn't change the Wizard. The Wizard will still give you an embedded macro.

There is a fix, however. When you're all done creating your Wizard-based buttons, in Form Design mode, click on the Design Tab and in the Tools section you'll see an option that says "Convert Form's Macros to Visual Basic." Click on that and Access will change ALL of the embedded macros in the form to VBA for you.

Easy enough?
Show Just This Thread        Post Reply
Read convert Access 2002 db screens macros by Henry Schweitzer @ 1/23/2014
tried running on 2013 & saving, but it didn't convert the screens and macros..
Show Just This Thread        Post Reply
Read Barcodes in Macros by Susan @ 4/4/2013
Okay...I currently have this code attached to a combo box with columns that fills in the fields associated with the record I choose:

Option Compare Database

Private Sub Asset_AfterUpdate()
Me.CpyNumber.Value = Me.Asset.Column(4)
Me.SetInfo.Value = Me.Asset.Column(3)
Me.Category.Value = Me.Asset.Column(2)

End Sub

If the search box to scan the barcode in is txtFilterSearch, how would I need to re-write my existing code.


Reply from Richard Rost:

You could do it with a combo box, but I would use a simple text box. Like I said before, in the AfterUpdate event, use DLOOKUP to pull the values straight out of your table. See those links for tutorials on those topics.
Show Just This Thread        Post Reply
Read Barcodes in Macros by Susan @ 4/3/2013
I have a form with a subform. I want to enter a "barcode" number for a book in a textbox (search box) on the main form that will automatically fill in the book title and copy number that has the matching "barcode" number on the subform. Can I do it on the macro settings? Please help.


Reply from Richard Rost:

Sure. You could read in the barcode and in the AfterUpdate event for the field perform a DLOOKUP and set the other fields that way. I prefer VBA code myself, but you can also do it with a macro.
Show Just This Thread        Post Reply
Read macro run on timer event by Ricardo @ 1/1/2013
Hi Richard,
I have a macro running well and would like to know if is possible to create a kind of "task schedule" to make this macro run itself. Do you know what I mean?
I want to run the macro every day, same time. Do you know how to do?
Cheers
Ricardo


Reply from Richard Rost:

Set up a form and use it's TIMER event.
Show Just This Thread        Post Reply
Read VBA vs Macros by Clem @ 12/11/2012
I was curious as to why you favor VBA over Macros? Is there any technical reason? -Clem


I could probably write a LONG post about the differences, but for me it's more of a personal preference. I've been a programmer pretty much all my life, and VBA just seems more intuitive to me. VBA is more flexible. VBA is easier to work with (I think). VBA gives you much more power. The only benefit to macros is that they're more portable. You can now distribute "safe" macros in Access 2007 and 2010 that can still run (both on the desktop and the web) without setting off security warnings. Only SIMPLE commands work, but it's good for projects you want to distribute publically. -Richard
Show Just This Thread        Post Reply
Read Converting macros to VBA by David Smith @ 11/14/2012
I am a newcomer to this forum.
Over time I have created a number of complex applications, in commercial use, using Access 2003 and with some exception have managed to control the functionality almost exclusively via macros.  I know, but I am 70 years of age but with a programming background.  I am now faced with bringing at least one system up to Access 2010 level but of course Macro functionality is not a clever as in 2003. I am not adverse to VBA but haven't used it extensively.  How 'safe' is it for me convert all macros in 2003 to VBA functions before migrating wholesale into Access 2010?


Reply from Richard Rost:

I've had pretty good success with converting 2003 macros to VBA. There's an automated tool for that. The best advice I can give you is (1) back up your database, and (2) give it a shot. Test it thoroughly in 2003 first, and if everything seems to work, upgrade it to 2010. I would say that 99.9% of all the VBA code that I've ported over from 2003 to 2010 works just fine.
Show Just This Thread        Post Reply
Read filter in macro by Daniel @ 10/12/2012
Thanks for your prompt response. Let's see if I can be more clear. I have a query that works fine. This query looks for a specific day in a table. I put the date range, and it gives me the correct information. What I'm trying to do is (in the form view of the table) put a date range and get all the entries made for that specific day. The form I'm using is continuous and should be that way to see all the records. I'm not sure if I'm clear enough (I hope).
Keep up the good job. I really like your tips in youtube!


Reply from Richard Rost:

I think you're looking for something like this: Form Field Notation.
Show Just This Thread        Post Reply
Read filter in macro by daniel @ 10/10/2012
I'm trying to put a filter in the argument section of a macro. I have a saved query that I want to use but I don't how to insert the query.


Reply from Richard Rost:

Daniel, explain to me what you're trying to do. Why the macro filter? Why not just create a different query?
Show Just This Thread        Post Reply
Read Macros by Scott Adkins @ 3/22/2012
Hey Richard, I am doing 206 on Access 2010. In the builder, I cannot figure out how to name the macros. Can you steer me in the right direction?
Show Just This Thread        Post Reply
Read Embedded Macros in Access 2010 by Greg Beben @ 12/12/2011
Richard, the recent question about macros prompted me to ask. In earlier versions of Access, when I let the command button wizard create procedures for me, it created VBA code. In the 2010 version (and maybe 2007, don't remember) it creates macros. I'm hoping that this is not a move away from VBA on Microsoft's part? Do you still recommend using VBA over macros? Is there a place to use macros for experienced developers?


Reply from Richard Rost:

Yes, I still do recommend VBA over macros. The wizard now creates something called an EMBEDDED MACRO, which is stored right in your form. The benefit of an embedded macro is that they're GUARANTEED TO BE SAFE. This way malicious coders can't hide something potentially dangerous in the code behind a form and send it out. You run it, and all of the sudden your hard drive is erased (which is possible with VBA). You can open a database you get from someone else and the code behind the forms and reports will still work even if you don't click the "enable" button when you get the security warning upon opening.

PLUS, embedded macros FOR THE MOST PART work if you're going to upload your database to a Sharepoint server for use on the web. There are a few features that don't work, but pretty much all of the popular functions do.

Now, macros have come a long way since earlier versions of Access, and I'll be spending some time on them in the Advanced level classes. They have their place. Obviously if you're building a database you plan on sharing with the world online OR you're planning on porting your database up to Sharepoint, you want to use embedded macros whenever possible.

For the rest of us who are building old-school databases for in-house use (on a LAN) where all of the users trust us, and you're NOT going to put the database online, I prefer VBA myself.

Hope this answers your question. :)
Show Just This Thread        Post Reply
Read Macros in Access by  Felix @ 12/10/2011
how do i use macros in access?



Reply from Richard Rost:

This is certainly not something that has a quick "oh, here's how you do it" answer. :) I do cover macros in several of my current tutorials, starting with Access 204. I'll be spending a lot more time on them in my Access 2010 Advanced series too.
Show Just This Thread        Post Reply
Read SendObject Macro Question by Valerie @ 4/12/2010
Hello! I am new to VBA and I need to write a macro and I am not sure how do it. I need to have an e-mail generated when a certain value is reached in the cell of an Access database. Specifically, I need to have Access automatically create an e-mail when a certain date is reached in a cell in Access. For example, when say someone enters todays date in a cell in Access, how do I have access automatically create and send an e-mail to an outlook account 4 months after that date? and then again 6 months and 1 year? I have been doing research online and I found a page that describes the SendObject option, but it doesn't ell me how to write the actual code. Or maybe it does and I just don't understand that it does. So what would the code looks like and where can I get information about how to write the actual code for this purpose?


Answer from Richard Rost:

It's not terribly hard to use the SendObject command, but if you have absolutely NO background in programming or writing macros, it's going to be.

I will say up front that I do cover this concept in my Access 329 tutorial:

http://www.599cd.com/site/courselist/access/access329?key=BlFAlEx

This is an advanced tutorial, however, and it covers emailing purchase orders to vendors - both individually and in batches. You can probably use the exact same concept for what you're doing - send batches of emails on anniversary dates.

But again, if you have NO experience with macros or VBA programming, you might want to start with a more basic tutorial first.

It would certainly take me much longer than I can spend to explain how to do the whole thing HERE, but basically you're going to need to create a programming loop called a RECORDSET that will iterate through your records that need emails, and then issue the SendObject command for each one.

I know this probably doesn't help you much, but that's about the easiest I can explain it here.
Show Just This Thread        Post Reply
Read Missing Action in Macros by Kevin Robertson @ 3/13/2010
While playing about with Access 2007, I have noticed that there is no 'SetValue' action in Macros.  Is there another way to set a value using a Macro, or is this just a mistake by Microsoft?


Reply from Richard Rost:

Kevin, I covered the case of the missing SetValue command back in October.
Show Just This Thread        Post Reply
Read confirm msgbox macro by Mike @ 3/13/2010
Hey im running a macro and ive turned the warnings off on the delete and append querys. However, i want to get my own confirmation message to appear on the screen when the macro is run. Could you help me out with this please?


Answer from Richard Rost:

Just use the MsgBox command in your macro in place of the confirmation boxes.

Personally, I don't write macros at all. I use straight VB code. You could do something like this:

Docmd.SetWarnings FALSE
If MsgBox("Are you sure",vbYesNo)=vbNo then Exit Sub
Docmd.OpenQuery "QueryName"
Docmd.SetWarnings TRUE

See? Nice and easy. Four lines of code. No macros needed. (I hate macros).
Show Just This Thread        Post Reply
Read SetValue macro command in Access 2007 by Richard Rost @ 10/2/2009
I thought I would repost this here because it came up in one of the Access 206 lessons: the SetValue macro command appears to be missing in Access 2007.

There is an embarrassingly easy solution to the SetValue problem. Earlier, students mentioned that SetValue doesn't seem to exist in Access 2007. Well, it's there - just hidden. While making your macro, click on the Macro Tools > Design ribbon tab. In the Show/Hide section, click on the SHOW ALL ACTIONS button. Now look at the list of available macros. You'll see SetValue there. Access tries to only show you the "safe" commands unless you specifically ask for the dangerous ones.
Show Just This Thread        Post Reply
Read DataBase Contains Macros by Angel @ 9/24/2009
WOW! That was easy. . and to think I spent hours last night redoing my database.

Thank you
Angel
Show Just This Thread        Post Reply
Read DataBase Contains Macros by Richard Rost @ 9/24/2009
Angel, this is a very annoying message indeed.

In Access 2003, you can go to TOOLS > MACRO > SECURITY. On the Security Level tab, select LOW (not recommended). This will bypass the message. Make sure you don't run any Access databases from untrusted sources.

In Access 2007, you have to set up a TRUSTED folder. You can do this on your Desktop or anywhere on your PC. Click on OFFICE BUTTON > Access Options > Trust Center > Trust Center Settings > Trusted Locations. Click the Add New Location button and specify a folder on your system. Now any databases run from this location are TRUSTED and you won't be prompted for macro security.

I'm going to copy this answer to my Blog too, because I get asked it A LOT.
Show Just This Thread        Post Reply
Read DataBase Contains Macros by Angel @ 9/23/2009
Richard,
I am going through your Microsoft Access Tutorial 103.  My database has been developing very nicely and all was well until I added a command button in a form.  I implemented the close command on the form and then saved my database.  Now when I open my database, I receive a message that says my database contains macros and I have to choose to disable, or enable the macros before opening the database.  This message is driving me nuts!  If I choose to disable the macros the database won't open, so I have to choose to enable macros, which then says they can contain viruses.  
Why did this happen? And how can I get rid of this message.
Thank you,
Angel
Show Just This Thread        Post Reply
Read macro button by Richard Rost @ 6/26/2009
Sally, there is very little that you CAN'T do in Access. :)

Here's what I would recommend: make a small POPUP form that you can use for searches. This form has on it just the fields that you want to search on. When the user clicks the SEARCH button, it opens up the book data form, but it stays in the foreground because it's a popup form (remember the popup form property from Access 310?

I cover creating a custom search button in Access 307. You could do something similar with a search form, you just need a DoCmd.OpenForm in there first to switch to the form you want to search on.

If anyone really wants to see how to make a custom search FORM with multiple parameters, let me know and I'll try to include it in the next lesson.
Show Just This Thread        Post Reply
Read macro button by Sally @ 6/26/2009
Form is about :  Book characteristics
Contains:
Book reference number
Book title
Author
Date published
Book bin location

User wants to look up this book by first entering a parameter of “What is the book reference number” .  The  form opens with all the data associated with this book.

Then User wants to look up another book. ……………..
How can I make a button or macro or ?  , that allows the user to clear this current book data and then enter a  new parameter “ What is the book reference number” to bring up a new book  record without having to leave the form.

I can do a “close”, take me back to my DB main menu, then hit the button to ask the parameter  question and open the form. But I was trying not to leave the form, just ask for another record. Kind of like doing a refresh then starting from the “What is the book reference number “ again. Or, think about  the user put in the wrong book reference number and wants to retype it so the correct book shows up.

Does this help?  Sorry , maybe I’m trying to do something that can’t be done in Access.
Sally
Show Just This Thread        Post Reply
Read macro button by Richard Rost @ 6/26/2009
Sally, I don't understand what you're trying to do. Can you give me an example, please?
Show Just This Thread        Post Reply
Read macro button by Sally @ 6/25/2009
I want the user to be on the form for a specific record, then hit a button to see another record (where they put in the parameter for a specific record number)rather than having to go back to the menu to start over again. Does that make sense?
Show Just This Thread        Post Reply
Read macro button by Richard Rost @ 6/25/2009
Sally, I'm not following you. Could you please elaborate?
Show Just This Thread        Post Reply
Read macro button by Sally @ 6/25/2009
Hi Richard,
I'm building a library type DB. I have a form that is based off of a parameter query which asks for the specific ID number of the book to present the data. I want a button to be able to rerun the query to show another specific ID number on the form. When I do a macro, all it gives me back is the ID on the table tied to the query but not on the form. How do I fix this?  
Show Just This Thread        Post Reply
Read set value macros in access 2007? by Richard Rost @ 4/1/2009
Natashaa, you need to understand Form! notation. See this tutorial.
Show Just This Thread        Post Reply
Read set value macros in access 2007? by Natashaa @ 3/27/2009
I need to get the values of different field from one form to another,this should happen when i click a button...what vba or macro should i use and how?
Show Just This Thread        Post Reply

Collapse All Topics

 

Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
 
If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 5+9:
  
  Notify me when the this forum is updated
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

3/30/2020Access DateDiff Function
3/26/2020Sorted Combo Box Values
3/23/2020Browsing and Selecting Files
3/14/2020New Access Group
3/6/2020NEW Access 2019 for Beginners
8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP