working with macros

rsslack · August 5th, 2011, 03:08 PM

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?

gjgriffith · August 5th, 2011, 08:08 PM

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,

rsslack · August 5th, 2011, 10:00 PM

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.

gjgriffith · August 6th, 2011, 01:14 AM

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,

rsslack · August 6th, 2011, 12:29 PM

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.

rsslack · August 6th, 2011, 01:51 PM

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???

gjgriffith · August 7th, 2011, 06:14 AM

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,

rsslack · August 7th, 2011, 12:35 PM

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.

gjgriffith · August 9th, 2011, 02:03 AM

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,