Simple Question: How to get VBA to Press OK for you automatically

[Bill Bisco]

This is a repost of a question at http://www.mrexcel.com/forum/showthread.php?t=381639

I'm adding an attachment of my current database attempts for the benefit of Bob Larson trying to help me (my sincere thanks Bob )

Any help is appreciated.

Sincerely,
Bill

 

[boblarson]

So just to be sure, what am I supposed to try to run on each?

 

[Bill Bisco]

As I said on the other forum: To get to where I'm talking about:


On frmWelcome Form, click the Combo Box below the Command Button "View LS and RS Stations Together" and choose Station 1, 2, or 3.

On the Database that's called Double Click, just press Ok several times, don't type anything else.

If you compare the 2 versions, you'll notice that Double Click Database Correctly displays the information in the footer, whereas as Extra Query Database gets errors in the footer.

Sincerely,
Bill

 

[boblarson]

Okay, here you go. The problem was that you really needed an interim query as the calculations going on with [OptionMix] was causing issues. I added a query as the interim and then added the two fields to the other one. Take a look and see if that helps.

 

[Bill Bisco]

Well, Bob I want to thank you very much. I had actually tried that interim query approach, but I put all my equations within the subquery whereas you left out OptionMixDSTSum: [OptionMix]*[SumofDSTTime] and OptionMixStdSum: [OptionMix]*[SumofElementTime] from the subquery.

Once again, wish I had thought of that.

Thanks a million Bob,
Bill

 

[boblarson]

Thanks a million Bob,

No problem. So, the thing to be gained from this is that if you get those prompts - something isn't right and it needs to be examined closer. You should not get those prompts for input (unless you specifically have input parameters that you have included as criteria).

I know that there are some other of those in places on your form but I'll see if you can figure them out.

I would also highly suggest getting more familiar with normalization as your data structure is, shall we say, "something to be desired." It is not normalized and that can also be a source of pain and suffering when dealing with a relational database.

See here for more on normalization:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

 

[Bill Bisco]

No problem. So, the thing to be gained from this is that if you get those prompts - something isn't right and it needs to be examined closer. You should not get those prompts for input (unless you specifically have input parameters that you have included as criteria).

I know that there are some other of those in places on your form but I'll see if you can figure them out.

I would also highly suggest getting more familiar with normalization as your data structure is, shall we say, "something to be desired." It is not normalized and that can also be a source of pain and suffering when dealing with a relational database.

See here for more on normalization:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

You are quite correct Bob that getting these prompts tells you that something is wrong or at the very least not ideal. When I tested this problem a few times, I thought that the only way to have my footer equations work would be to have my query set up so that the query would not draw from another query.

You kindly demonstrated to me that I could have my cake and eat it too, that I could use a subquery to remove the prompt and have my footer equations work.

There are other places in my form that ask for inputs when you select them, but this is only because my database and references have changed, and I have not gone back to fix them. I have simply been focusing on that form you helped me with.

I read the article you provided, and other than some calculated field names in my form, what would you change regarding some of my Naming Conventions?

Sincerely,
Bill