After Cancel

[qweeqweg]

Hi. I decided to try and make a demo as well. I hope it's close enough to what you are doing. If not, please post your demo when it's ready.

Okay, in the attached file, first click on the button labeled "Query1." You should see that there are three records in the database (IDs 1, 2, and 3). Now, click on the button labeled "Query2." You should be prompted to enter an ID. Enter any ID between 1 and 3. The report should show up in the subform. Do it again with a different ID value, but still valid (1-3), just to verify the subform changes.

Then, click Query2 one more time, but this time enter an invalid ID value. You should now see the error message and the subform sends you back to Query1.

Hope that helps...

Thanks DBguy
I've reviewed your example and it is way different than the direction I took. Your query2 button fires off a report based on a query requiring a parameter value. My button fires off a form based query. I would think that mine should work.. but its not. Have a look at my attached DEMO. Where have I gone wrong? Open the DASHBOARD form and click MOVE INVENTORY. oh.. FIRST, open DASHBOARD in design view and select the unbound subform control (sfctl) and set the visible property back to NO. Then proceed. I forgot to return that value to where I want it before I zipped the db.
You can grab a valid serial number from the inventory table.
Note that there are 2 versions of each form. One is a standalone for use thru the switchboard, and the other is the subform version im using with the dashboard. The only differences are cosmetic. All these forms work fine if you open them directly. And they all work if you open them thru the Switchboard. The problem lays in how Im trying to open them thru the DASHBOARD form using SourceObject. Why is this all like this? Because I like the DASHBOARD method, and Im simply trying to modify the interface from the prior Switchboard interface.

 

[qweeqweg]

You could take a look at this. I was supposed to get back to tweaking it but things have constantly been getting in the way. It will probably solve your immediate privacy issue. Any questions, let me know.

Thanks Micron. I've bookmarked this for future need. Much obliged.

 

[theDBguy]

Thanks DBguy
I've reviewed your example and it is way different than the direction I took. Your query2 button fires off a report based on a query requiring a parameter value. My button fires off a form based query. I would think that mine should work.. but its not. Have a look at my attached DEMO. Where have I gone wrong? Open the DASHBOARD form and click MOVE INVENTORY. oh.. FIRST, open DASHBOARD in design view and select the unbound subform control (sfctl) and set the visible property back to NO. Then proceed. I forgot to return that value to where I want it before I zipped the db.
You can grab a valid serial number from the inventory table.
Note that there are 2 versions of each form. One is a standalone for use thru the switchboard, and the other is the subform version im using with the dashboard. The only differences are cosmetic. All these forms work fine if you open them directly. And they all work if you open them thru the Switchboard. The problem lays in how Im trying to open them thru the DASHBOARD form using SourceObject. Why is this all like this? Because I like the DASHBOARD method, and Im simply trying to modify the interface from the prior Switchboard interface.

Hi. I took a look at your demo, and you're right, there's something else going on with your db. I even tried to put a new button to try to do the same thing, and it still didn't work. I am suspecting a possible corruption, but I'll have to try it as well on the demo I posted to make sure it's not something else. In the meantime, I tried creating a Navigation Form (to mimic the UI you're trying to make), and it works fine. You can take a look at the attached, but I'll let you know later when I get a chance to play with my demo to confirm if it's possible or not using a regular form/subform setup. If not, then my next suggestion was going to be similar to what Pat just said. I was going to recommend using your own "user prompt" and then store it in a TempVar, which you can then use in your query or your form.

 

[qweeqweg]

Hi. I took a look at your demo, and you're right, there's something else going on with your db. I even tried to put a new button to try to do the same thing, and it still didn't work. I am suspecting a possible corruption, but I'll have to try it as well on the demo I posted to make sure it's not something else. In the meantime, I tried creating a Navigation Form (to mimic the UI you're trying to make), and it works fine. You can take a look at the attached, but I'll let you know later when I get a chance to play with my demo to confirm if it's possible or not using a regular form/subform setup. If not, then my next suggestion was going to be similar to what Pat just said. I was going to recommend using your own "user prompt" and then store it in a TempVar, which you can then use in your query or your form.

DBguy.. something I have done with the tables in this db, which I never have is, some of the columns are the result of a query within the column property. Open the inventory table in design view and look under the lookup tab of 'PropertyID'. Could this be causing problems? Im pretty sure its a bad practice, but I thought I would see what came of it.

 

[theDBguy]

DBguy.. something I have done with the tables in this db, which I never have is, some of the columns are the result of a query within the column property. Open the inventory table in design view and look under the lookup tab of 'PropertyID'. Could this be causing problems? Im pretty sure its a bad practice, but I thought I would see what came of it.

Definitely a "bad practice," but I don't know if it will cause the problem you're having with your form, since it worked for me using a Nav Form. Did you try it?

The Access Web - The Evils of Lookup Fields in Tables

 

[theDBguy]

Hi. So, I modified my demo and just added a form for Query2. The bad news is it worked for me, so there must be something about your file that's making it not work as expected. Please try the attached updated demo, and maybe you'll notice what could be different between it and your file.

 

[qweeqweg]

Hi. So, I modified my demo and just added a form for Query2. The bad news is it worked for me, so there must be something about your file that's making it not work as expected. Please try the attached updated demo, and maybe you'll notice what could be different between it and your file.

Yay! I figured it out. Both the main form and the subform being called were bound to the inventory table. I had copied the main form as a template and forgot to clear that out. After removing the inventory table as the main form's record source, it worked.

Now (if you will) we can get back to dealing with the blank subform control after the cancellation of the parameter request. If I click 'OK' without providing a parameter, your code works great and flashes the 'No records to show' dialog. But if I hit cancel, Boom.. I get a big blank, and I get a runtime error on the 'If Me.sfctl.Form.Recordset.RecordCount = 0 Then' portion of your code.

 

[theDBguy]

Hi. Glad to hear you figured it out. Did you take a look at the demo I posted last night? Do you get an error with it also?

 

[theDBguy]

Hi. I modified your demo again. Please take a look...

PS. Please also try the Navigation Form I created and let me know what you think of it (even if you don't use it). Thank you.

 

[qweeqweg]

Hi. I modified your demo again. Please take a look...

PS. Please also try the Navigation Form I created and let me know what you think of it (even if you don't use it). Thank you.

Thanks very much, Dbguy. I have tried both your demos, (to include the navigation form), and they both produce a run-time error when I choose 'cancel' on the dialog box. That said, I'm pleased to report that the blank subform control no longer appears. Only 'Run-time 2467' appears. When you click 'Cancel' on the Enter Serial dialog box, does it not produce an error on your end?

 

[theDBguy]

Thanks very much, Dbguy. I have tried both your demos, (to include the navigation form), and they both produce a run-time error when I choose 'cancel' on the dialog box. That said, I'm pleased to report that the blank subform control no longer appears. Only 'Run-time 2467' appears. When you click 'Cancel' on the Enter Serial dialog box, does it not produce an error on your end?

Ah, I forgot to check if the user hit the Cancel button. Let me try that...

 

[theDBguy]

Hi. I had some time today to make some changes. Please take a look...

 

[qweeqweg]

Hey! That works! Thanks DBguy! Im a bit embarrassed that all it took was to keep the subform control hidden. Simple solution. I should have thought of that. Very much appreciative of your time in helping me out. Very grateful. Thanks again!

 

[theDBguy]

Hey! That works! Thanks DBguy! Im a bit embarrassed that all it took was to keep the subform control hidden. Simple solution. I should have thought of that. Very much appreciative of your time in helping me out. Very grateful. Thanks again!

Hi. You're very welcome. How did you like the Nav Form version? Good luck with your project.

 

[qweeqweg]

Hi. You're very welcome. How did you like the Nav Form version? Good luck with your project.

Yes.. I should have gone with the Navigation Control. Looks to be a lot easier. I filed that in my 'VB Tips' folder for future need. Thanks!
This little project is nearly complete. Ran into trouble with the cascading combo boxes in "AddInventory", but I got it figured out. I wasn't including the parent form in the requery of the second combo box. Even knowing this, it took me a couple hours to figure out the proper syntax. Ungh! Oh well.. Its all part of the learning curve. I'm an old MCSE. My background is in Servers and Workstations with a lot of Networking experience. Haven't taken the CCNA exam but I'm pretty sure I would pass. Coding is a new direction for me. Before playing with VBA, I had only written DOS Batch files. Even that's gone now with the advent of Powershell.

Oh.. I meant to ask you- Can you explain what this code is doing?

If Me.sfctl.Form.Recordset.RecordCount = 0 Then

 

[theDBguy]

Hi. That was basically the same code I gave you before. It checks if the subform is empty. If so, we know the serial number the user provided did not have a match, and we can close or hide the subform.

 

[qweeqweg]

Hi. That was basically the same code I gave you before. It checks if the subform is empty. If so, we know the serial number the user provided did not have a match, and we can close or hide the subform.

Okay.. so the 'RecordCount' would have been '1' if a match was found?

 

[theDBguy]

Okay.. so the 'RecordCount' would have been '1' if a match was found?

Correct!

 

[qweeqweg]

Correct!

Great! Thanks Again!

 

[theDBguy]

Great! Thanks Again!

You're welcome. Good luck!