Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 5th, 2011, 03:08 PM
Registered User
 
Join Date: Aug 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default working with macros

I am working with Access 2010 and know the program well, but have done little with macros. I have set up a db for our collection of dvd's. I have a report that will scroll the list of all the movies that is based on query. I also have a form to add and edit movies. I do have a field with a unique ID for each record. Since the report view is automatically read-only, I am trying to link back to the form to be able to easily edit info. I have succeeded in linking back to the form, but not to the specific record that I was on. Can you help me with this, or is there a way to make the report editable?
 
Old August 5th, 2011, 08:08 PM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Why not just filter the form on open?

Hello rsslack,

I saw your post, thank you for posting your question. specifically, you said:

Quote:
I have succeeded in linking back to the form, but not to the specific record that I was on.
So, it sounds like you are trying to use a macro (instead of code) to do this. What I would do is: for one (or more) of the text box controls on the Report, create a "Click" event for that control and specify the "OpenForm" macro to open your add/edit details form, setting the 'Where Condition' parameter to:

Code:
="[ID]='" & Nz([ID],0) & "'"
To open the form filtered on the record that the user clicked. And really, this is exactly the same operation as if you had done this with the DoCMD.Open() method in VBA. Does this make sense?

Also, I would mention that there is an example of doing this exact operation in an Access database app in Chapter 32 of the Microsoft Access 2010 24-Hour Trainer book. (Sorry...just have to plug my books whenever I get a chance )

Anyway, I really hope this helps answer your question! But if there is anything else I can do to help, please let me know!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
 
Old August 5th, 2011, 10:00 PM
Registered User
 
Join Date: Aug 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you .gjgriffith for the quick reply and the advise.
I entered the code and it still is only going to the first record. I think understand part of the code, but not all. =[ID] should set the variant to be the ID of the record that I click on. then if the ID is null, the NZ should set a null variant to 0. Am I right? What does the last part do? & "'"
If I continue trying to write macros and code, I will definitely ck out your book.
 
Old August 6th, 2011, 01:14 AM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Oops, my code was slightly wrong!

Hello rsslack,

Thanks for the follow up. You know what, I just realized that I made a mistake here, and your question points out that fact inadvertently. In this case, since the "[ID]" is referring to the ID field of the record that you are clicking on (in the report) and it is a Number type field, we don't need to put quotes around the result from the "Nz([ID], 0)" function call. So, in this case, the code should actually be:

Code:
="[ID]=" & Nz([ID],0)
Sorry about that mistake in my previous code! And what this code is doing is simply filtering the form that you're opening to the ID of the record that you clicked on in the report (but make sure that "[ID]" is replaced by the actual named of the ID field for the table. Also, if the ID for the record that was clicked on does not exist, the value of 0 is returned and the form opens in 'new record' mode (which a result of setting the where condition for the form to '[ID]=0'...because it is not valid). Does that make sense?

Anyway, I hope that helps and again, I'm sorry about the previous mistake! If you still have questions, please let me know and I'll do what I can to help!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

Last edited by gjgriffith; August 6th, 2011 at 01:18 AM..
 
Old August 6th, 2011, 12:29 PM
Registered User
 
Join Date: Aug 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default worked

hello gjgriffith,

It worked. Thanks. I also realized that I had failed to include the ID field on the form (duh). Really appreciate the help.
 
Old August 6th, 2011, 01:51 PM
Registered User
 
Join Date: Aug 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default almost works

hello again gjgriffith,

It almost works. It set the first one I tried as a constant. It doesn't change to the next next one clicked on. suggestions???
 
Old August 7th, 2011, 06:14 AM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Macros

Hello rsslack,

I'm glad to hear that it is almost working! Did the form get closed and reopened each time? If the form is already open, you may have close and reopen (or refresh the form).

Anyway, I hope that helps, but please let me know if there is anything else I can do to help!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
 
Old August 7th, 2011, 12:35 PM
Registered User
 
Join Date: Aug 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default now works

hello gjgriffith,

Yes, I had closed the whole db to be sure, and was not changing yesterday. Closed it all and went to bed. Strangely this morning it was going back to adding new record on form, made no changes since yesterday. Erased the whole code then copied and repasted the code you gave me. Yesterday, I had erased the = in front of the formula since the expression builder puts one in giving = =. This time I forgot to do it and it all worked. Thank you, thank you, thank you..
Will definitely look for your book.
 
Old August 9th, 2011, 02:03 AM
gjgriffith's Avatar
Wrox Author
 
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Glad to hear you're up and running!

Hello rsslack,

I'm really glad to hear that the code is working for you now! Thank you for letting me know!

Using the OpenForm macro can be extremely effective, because it is very flexible...you just have to make sure to get the Where Condition parameter perfectly correct. Otherwise weird things will happen, and as you can see from my notes (and mistake) above, it is very easy to make a simple mistake...which I apologize for once again!

And if there is anything else I can do to help, please just let me know!

Thanks,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->





Similar Threads
Thread Thread Starter Forum Replies Last Post
Macros malcolmdixon BOOK: Access 2010 VBA Programmer's Reference 1 June 10th, 2011 04:47 PM
Excel macros mustafayildirim@msn.com Excel VBA 1 August 29th, 2007 10:50 PM
Pause between macros paul20091968 Access VBA 2 April 6th, 2007 01:40 AM
Enable - Macros alannoble26 Excel VBA 2 November 28th, 2005 09:08 AM
Macros vbprogwb Access VBA 21 November 12th, 2003 05:18 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.