Help with inherited macro group...ERRORS abound

[imrosie]

Hello,

I took over an application with forms, macros, reports....I've changed some of the controls on the forms,but left the names of forms in tack. I keep getting an error stating that the macro or macrogroup name (for forms, reports) has changed and needs to be saved, or it doesn't exist. I''m staring at the macro's, sitll there, how do I get around this?

I'm new and haven't really touched macro's. thanks in advance

[NeoPa]

You will need to start by posting in the exact error message as it appears to you. Hopefully that will throw enough light on the subject to allow someone to help.

[imrosie]

You will need to start by posting in the exact error message as it appears to you. Hopefully that will throw enough light on the subject to allow someone to help.

Thanks NeoPa,
I've been trying to figure this out for the past 2 days....I'm desperate. I'm completing a database that someone started (including the macros)...they no longer work here. I'm a newbie, so bare with me. I went back to an earlier copy of this app they had and did a 'get external' import of the macro's, queries, reports. So here's where I am now:

I'm now getting error code 3075-"The expression you typed is not valid for the reason indicated in the message. The message literally shows this with the yellow exclamation sign ---------- "Extra ) in query expression '([CustomerID]=)',

It as though it's telling me there is an extra right parenthesis, but I can't figure out where in the heck it's coming from. I stared at the macro's in macrogroup.

Here's what the grey macro box shows before I halt it:

"Action Failed"

Macro Name-
'View Reports Macros.cmdOpenReport: on click

Condition-
True

Action Name-
OpenReport

Arguments-
Orders by Customer, Print Preview,, [CustomerID]=, normal

Any help would be greatly appreciated.thanks

Rosie

[NeoPa]

... so bare with me.

I think we'd better take this off-line. Don't you :D
That's the best offer I've had in many a year.

[NeoPa]

More serious answer this time Rosie.
If you look at the Arguments line, you'll see fourth argument is "[CustomerID]=".
I don't use macros at all myself as they are a hang-over from pre-VBA days. You don't have that luxury though, so let's see what we can see. This looks like a WHERE clause string to me, but an incompletely formed one. This should be in the form "[X]=Y" or "[X]='Y'". Clearly the Y part (CustomerID value you want to select) is missing. That's why the error message complained of an extra closing parenthesis - It found one after the '=' when it was expecting a value (Access added the parentheses itself automatically - it's helpful like that).
So, in short, I'd edit the macro either to add the value, or to remove the criteria altogether.

And, of course, I'll be happy to bear with you if baring with you turns out to be impossible ;)

[imrosie]

I think we'd better take this off-line. Don't you :D
That's the best offer I've had in many a year.

You're hilarious....that made me laugh and took the stress off of macrogroups.....
I'll be careful how I throw that statement around....

Rosie

[imrosie]

More serious answer this time Rosie.
If you look at the Arguments line, you'll see fourth argument is "[CustomerID]=".
I don't use macros at all myself as they are a hang-over from pre-VBA days. You don't have that luxury though, so let's see what we can see. This looks like a WHERE clause string to me, but an incompletely formed one. This should be in the form "[X]=Y" or "[X]='Y'". Clearly the Y part (CustomerID value you want to select) is missing. That's why the error message complained of an extra closing parenthesis - It found one after the '=' when it was expecting a value (Access added the parentheses itself automatically - it's helpful like that).
So, in short, I'd edit the macro either to add the value, or to remove the criteria altogether.

And, of course, I'll be happy to bear with you if baring with you turns out to be impossible ;)

Hi NeoPa

On a serious note, (all bear-ing aside). I agree the Where Condition seems badly formed. The code for the macro & filter is shown below.

1.) Report Name - Orders by Customer
2.)View - Print Preview
3.)Where Condition - =[Forms]![View Reports]![txtReportFilter]
4.)Windows Mode - Normal

txtReportFilter:

=Choose([grpFilterOptions],"[CustomerID]=" & [Forms]![Add an Order and Details].[CustomerID],"")

I need help correctly forming the 'txtReportFilter'.....The value for CustomerID will change with each order (hence the filter)..Any ideas?
thanks
Rosie

[NeoPa]

What does the [View Reports].[txtReportFiler] control display (on the form) when (before) you invoke this macro?
What does the [Add an Order and Details].[CustomerID] control display (on the form) when (before) you invoke this macro?
Are both forms open at this stage (they should be)?

[imrosie]

What does the [View Reports].[txtReportFilter] control display (on the form) when (before) you invoke this macro?
What does the [Add an Order and Details].[CustomerID] control display (on the form) when (before) you invoke this macro?
Are both forms open at this stage (they should be)?

1.) The [View Reports].[txtReportFilter] control display (on click) invokes the opening of a form called 'View Reports'; you have a value list of several types to choose from but none work, because by default, they're all filtered through an Option Button (called OptFilterCustomerID) with an option value of 1. Its there so you can filter based on the particular CustomerID account you've got 'opened' in the "Add an Order and Details" at the moment.

There is the another option button on the 'View Reports' (called OptFilterNone) and it's value (ofcourse) is 2.... If selected, then everything works; problem is it provides everything for every customer (Print Preview)on the reports

2.) The[Add an Order and Details].[CustomerID] control displays the Customers account number (CustomerID).
This form ("Add an Order and Details"), is open (used to place orders for existing customers) it has a subform within it that where the items purchased are added (with prices and an extended total) contains also the shipping info (date and method) ....

I hope this helps you understand what's going on....It seems me I've got to simply properly form the string for 'txtReportFilter'. thanks NeoPa.

[NeoPa]

I'm sorry Rosie. I hadn't realised this thread had got a response and was waiting for one from me. Is it still current?

[imrosie]

I'm sorry Rosie. I hadn't realised this thread had got a response and was waiting for one from me. Is it still current?

Hi NeoPa,

i was waiting.....yes, I'm working on 3 so I was sort leaving the ''macrogroup' thing alone until you or someone got back to me ....yes, it's still current . thanks

[NeoPa]

OK Rosie, I'll try to look at this soon but I'm quite busy until Thursday. If I've not replied by Thursday then please feel free to bump the thread so that I don't miss it again ok.

[imrosie]

OK Rosie, I'll try to look at this soon but I'm quite busy until Thursday. If I've not replied by Thursday then please feel free to bump the thread so that I don't miss it again ok.

Thanks NeoPa

Rosie

[NeoPa]

What does the [View Reports].[txtReportFiler] control display (on the form) when (before) you invoke this macro?
What does the [Add an Order and Details].[CustomerID] control display (on the form) when (before) you invoke this macro?
Are both forms open at this stage (they should be)?

Rosie,
I've copied post #8 as that's the one I need you to go back to.
I need these three bits of information :

1. Before you run the macro, copy and paste (into your new post) the contents of that control ([View Reports].[txtReportFiler]).
   Remember to check that the form and control names are exactly as you've specified.
2. Do the same for [Add an Order and Details].[CustomerID].
3. Confirm that the forms are both open BEFORE rou run the macro.

If you can provide clear and accurate answers to these questions then we can probably make some progress here.

[imrosie]

Rosie,
I've copied post #8 as that's the one I need you to go back to.
I need these three bits of information :

1. Before you run the macro, copy and paste (into your new post) the contents of that control ([View Reports].[txtReportFiler]).
   Remember to check that the form and control names are exactly as you've specified.
2. Do the same for [Add an Order and Details].[CustomerID].
3. Confirm that the forms are both open BEFORE rou run the macro.

If you can provide clear and accurate answers to these questions then we can probably make some progress here.

NeoPa,

Sorry, I've been out of the loop (family emergency).
1.)
[View Reports] code:

Add an Order and Details Macros.View Reports : On Click

In the macro it lists; Form Name - View Reports, Veiw - Form, Filter is blank and Where condition is blank, Data Mode is read only and the Window Mode is normal.

[txtReportFilter] code:
control source is:

=Choose([grpFilterOptions],"[CustomerID]=" & Forms![Add an Order and Details].CustomerID,"")

2.)
[Add an Order and Details].CustomerID

I'm not real sure what you're asking for here, but here is the code
control called 'CUSTID' and the control source is CustomerID (from Customers tableCustomerID)

3.)
Both forms are open when I run the macro. the 'View Reports' runs from a command button on the 'Add an Order and Details' form.

thanks
Rosie

[NeoPa]

Firstly, and most importantly, I hope everything is well with you and your family. There is no need to worry about not responding for delayed periods. Sometimes I take a bit longer to get back up to speed with a problem after a long delay, but that's very rarely a problem (I have some others to keep me busy don't worry ;)).

On to the issue :
#3 we can consider well and truly put to bed then ;)
For #s 1 & 2 I need to explain myself better. What I'm looking for is the value showing in the running forms for the two controls. So, at the point just before running the macro, I need you to select the values currently in the two controls, then copy and paste them into your new post. It may well be that the Control Source for the two controls will be helpful too, but my main query is about the values in the running forms at the moment.
BTW Aplogies for posting the TextBox name incorrectly. It should have read [View Reports].[txtReportFilter] (as I'm sure you realised).

[imrosie]

Firstly, and most importantly, I hope everything is well with you and your family. There is no need to worry about not responding for delayed periods. Sometimes I take a bit longer to get back up to speed with a problem after a long delay, but that's very rarely a problem (I have some others to keep me busy don't worry ;)).

On to the issue :
#3 we can consider well and truly put to bed then ;)
For #s 1 & 2 I need to explain myself better. What I'm looking for is the value showing in the running forms for the two controls. So, at the point just before running the macro, I need you to select the values currently in the two controls, then copy and paste them into your new post. It may well be that the Control Source for the two controls will be helpful too, but my main query is about the values in the running forms at the moment.
BTW Aplogies for posting the TextBox name incorrectly. It should have read [View Reports].[txtReportFilter] (as I'm sure you realised).

Thanks NeoPa for you kind comments, appreciated.

Ok, here goes:
1.)
On ([View Reports].[txtReportFilter]), there are no contents 'in a control' to copy because a command button called 'View Reports' from the form 'Add an Order and Details' opens the 'View Reports' form on click; this form displays a list of report options (types of reports to open)
which are:
Orders by Customer
Customer Listing
Receivables Aging
Total Prices of Orders

On 'View Reports' I'm selecting 'Orders by Customer' in my case Also on the 'View Reports' you have the choice of two filter options; one to view order for this Customer only (the customer that's currently selected in the CustomerID control on the Add an Order and Details form) or the other filter which is 'Do not apply filter. I'm having difficulty with the option number one.

When I select the option 'Orders by Customer' with the 'view orders for this customer only' filter, it automatically starts up the macro, which is why there are no contents to copy into this reply.

2.)

As for the CustomerID on the 'Add an Order and Details' form. Here are the contents of the account number and customer name controls prior to opening 'View Reports':
[Add an Order and Details].CustomerID - Account No. - 1898
[Add an Order and Details].thefullname - Customer Name - Sam Gaut

I hope this helps. thanks...
Rosie

[NeoPa]

Rosie,
Thanks for all the explanation, but I'm not really looking to make it so complicated. I'm struggling to understand what's what here.
Let me try :

1. Where do I start?
   Are you saying that the value is not accessible to you because as soon as it becomes set, the macro runs without your being able to copy the contents?
2. Is the value "1898"?
   If not, is it "Account No. - 1898"?
   This may look the same to you, but you're not a computer. I need to understand exactly what is being passed to the process. For instance, the former could work whereas the latter would indicate a problem.

This will be difficult for me anyway, as I have to try to follow the logic of the thing without having access to it other than what you tell me. If the information is clear and precise (hard for most people I know) then the job is less complicated. If all the information comes out in unconnected snippets, it's very hard to piece them together in the right order before trying to go on and understand what your problem may be.

Please don't think I'm upset with you as I'm not. I know you're trying to be helpful. I'm just frustrated that getting the right information is so difficult via a forum page like this.
Using macros makes life harder too as I never use them myself, but I'm not giving up. I'm sure we can get there in time.

[imrosie]

Rosie,
Thanks for all the explanation, but I'm not really looking to make it so complicated. I'm struggling to understand what's what here.
Let me try :

1. Where do I start?
   Are you saying that the value is not accessible to you because as soon as it becomes set, the macro runs without your being able to copy the contents?
2. Is the value "1898"?
   If not, is it "Account No. - 1898"?
   This may look the same to you, but you're not a computer. I need to understand exactly what is being passed to the process. For instance, the former could work whereas the latter would indicate a problem.

This will be difficult for me anyway, as I have to try to follow the logic of the thing without having access to it other than what you tell me. If the information is clear and precise (hard for most people I know) then the job is less complicated. If all the information comes out in unconnected snippets, it's very hard to piece them together in the right order before trying to go on and understand what your problem may be.

Please don't think I'm upset with you as I'm not. I know you're trying to be helpful. I'm just frustrated that getting the right information is so difficult via a forum page like this.
Using macros makes life harder too as I never use them myself, but I'm not giving up. I'm sure we can get there in time.

Hi NeoPa,

No, I don' t think you're upset,,,,it is frustrating.
On number 1, yes, the value is not displayed in a control in order for me to copy and paste for you.

After a customer is selected (Add an Order and Details form), now you can select the products (on same form) to order.

Afterwards products selections you can then open 'Veiw Reports' form through a command button on same Add an Order and Details form. Veiw Reports form opens based off of the customer (and Account number)you've selected on Add and Order and Details form.

Yes as soon as you click ' Veiw Reports', the macro automatically runs....someone designed it like that. the form opens with the customers information, you merely select which filter you want to use prior to previewing the 'Report'. and when I select--------txtReportFilter (the one that filters for this customer only)....the macro fails.


2.)The account number is actually 1898 for Sam Gaut (Customer).

The only thing else I can do is to zip and email....I have no problem with that. Let me know. thanks

Rosie

[NeoPa]

OK. I've PMed you the details.
Let's hope it runs the same on my setup ;)

When I have it I'll see if I can clarify the question a bit in the post so that everyone can follow. It's easier to do if you know what it is you're looking for (which I hope I do).

[NeoPa]

You will need to give me instructions (in here please) on how I should go about reproducing the problem too. From opening the db to getting the error.

[NeoPa]

Here's what I do, pull up a customer (search) then, once the customer is there, you'll see to the right a command button for 'Veiw Reports'. The only form that's open at first if the 'Add an Order and Details'.
When you click 'View Reports' you'll see the reports form open with the choices. I select the filter which filters for the customer I've just selected.

Thanks for these instructions.
I've received the databases and will have to look at this over the weekend probably as I'm quite tired now and out most of Friday evening (Karate class, after which I'm also quite tired generally) too.

[imrosie]

Thanks for these instructions.
I've received the databases and will have to look at this over the weekend probably as I'm quite tired now and out most of Friday evening (Karate class, after which I'm also quite tired generally) too.

Wow Karate,,,,,,,,that's what I feel like when struggling with Access, like I've been in a Karate battle.

thanks so much for all your help...it is greatly appreciated. Have a good weekend and I'll be waiting for your reply.

Rosie

[NeoPa]

Wow Karate,,,,,,,,that's what I feel like when struggling with Access, like I've been in a Karate battle.

thanks so much for all your help...it is greatly appreciated. Have a good weekend and I'll be waiting for your reply.

Rosie

It is loads of fun, but on a Friday evening after a full week's work ... Refreshing it aint :D

Catch up later in the weekend :)

[NeoPa]

Rosie,

I tried to run through what you'd suggested with "Sam Gaunt" as the customer and the thing seemed to work. The only problem I found was that I couldn't select anything from the [View Reports] controls, so it always ran "Orders by Customer" & "Only show Orders for this Customer".

[NeoPa]

You said something in one of your emails (which I didn't try to read) about making some changes to the database before sending it to me. If that's what you really did then that was perhaps unwise.
I didn't read about potential new problems as I'm focusing on the original until it is resolved. I hope I'm experienced enough not to do otherwise. We can never make progress (working at a distance as we are) unless we both know what we're dealing with. Making changes to the database so that it's not as has been described, while understandable, leaves me working in the dark to a large extent.
Perhaps we need to get back to a position where we both know where we are. I have the latest version you sent, I'm happy to receive another one if necessary, but if your original problem expresses in this one, we should be able to continue from here. I need to know that all your descriptions and explanations are related to this database though.

[imrosie]

You said something in one of your emails (which I didn't try to read) about making some changes to the database before sending it to me. If that's what you really did then that was perhaps unwise.
I didn't read about potential new problems as I'm focusing on the original until it is resolved. I hope I'm experienced enough not to do otherwise. We can never make progress (working at a distance as we are) unless we both know what we're dealing with. Making changes to the database so that it's not as has been described, while understandable, leaves me working in the dark to a large extent.
Perhaps we need to get back to a position where we both know where we are. I have the latest version you sent, I'm happy to receive another one if necessary, but if your original problem expresses in this one, we should be able to continue from here. I need to know that all your descriptions and explanations are related to this database though.

Hello NeoPa,

You're correct, when we originally started this discussion I was having difficulty 'ViewReport' for the selected customer (in this case Sam Gaut). However, after I zipped and emailed...I continued to work on it and when you wrote that you still had not received the zipped file, I merely sent you a second file (which was the version that I'd been working on). By that time I had seeminly solved the 'VeiwReports' for a 'selected customer.
I included verbiage regarding the fact that I couldn't view any of the other reports and also that I now couldn't select the option to view reports without a filter.....as appears you've also discovered those problems.

So moving on that's what I'm attempting to fix now. I hope you may have suggestions on how to correct those newer problems. That is the postion I"m
in now. Sorry for the confusion.

Thanks,
Rosie

[NeoPa]

Hello NeoPa,

You're correct, when we originally started this discussion I was having difficulty 'ViewReport' for the selected customer (in this case Sam Gaut). However, after I zipped and emailed...I continued to work on it and when you wrote that you still had not received the zipped file, I merely sent you a second file (which was the version that I'd been working on). By that time I had seeminly solved the 'VeiwReports' for a 'selected customer.
I included verbiage regarding the fact that I couldn't view any of the other reports and also that I now couldn't select the option to view reports without a filter.....as appears you've also discovered those problems.

So moving on that's what I'm attempting to fix now. I hope you may have suggestions on how to correct those newer problems. That is the postion I"m
in now. Sorry for the confusion.

Thanks,
Rosie

OK Rosie, then we need to keep working from that version unless and until you issue me with a new copy. That's all fine though.
What I don't like is that when I tried to find out what was going on with that I wasn't able to :(
Don't get me wrong, I will give it another go, but it was certainly not obvious from what I could see. All the settings were correct as far as I could tell, yet both of those controls were unchangeable. Time will be at a premium again for the next couple of days, but if I haven't managed anything by Thursday evening then I will have some then I expect. It's a bit of a squeeze time-wise with a full-time job and a family clamouring for my time. If I get a chance before then obviously, I will.

BTW. If you want to see the filter as it's going to be applied, simply set the control's .Visible property to Yes. It was there all the time but simply invisible.

[NeoPa]

OK, so I don't take well to being beaten :(
The macro [Add an Order and Details.View Reports : On Click] is used to handle that CommandButton.
If you open up that macro (Add an Order and Details) in Design View and go to the 11th line with "View Reports : On Click" in the Macro Name column, you will see why it's stuck. Look in the details down the bottom and you will see that the Data Mode is set to "Read Only". This is what's stopping you from changing any of the values on the form. You can move the selection but not change values (regardless of the fact that they're unbound controls - this is the form mode - not the underlying data). Change this to "Edit" if you want it to work normally.
Good luck! :)

[imrosie]

OK, so I don't take well to being beaten :(
The macro [Add an Order and Details.View Reports : On Click] is used to handle that CommandButton.
If you open up that macro (Add an Order and Details) in Design View and go to the 11th line with "View Reports : On Click" in the Macro Name column, you will see why it's stuck. Look in the details down the bottom and you will see that the Data Mode is set to "Read Only". This is what's stopping you from changing any of the values on the form. You can move the selection but not change values (regardless of the fact that they're unbound controls - this is the form mode - not the underlying data). Change this to "Edit" if you want it to work normally.
Good luck! :)

NeoPa,,,,,,I'm a newbie,,,which means I was trying everything I could think of until something worked....anyway. I took your advice...and there it was in Plain Sight 'Read Only'.....

I changed it to "Edit" and of course it's working like a charm. thanks for all the effort you put into helping me out....

take care
Rosie

[NeoPa]

I'm very pleased that worked for you Rosie. It's been a joy (not totally without problems ;)) working with you on it :)