After Cancel (1 Viewer)

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
Hello Friends
I have a button on my main form which triggers a subform.

The subform prompts for a parameter value up-front before opening.

My issue is.. if I decide not to enter a parameter value and choose 'cancel' from the 'Enter a Parameter Value' dialog box, I end up with an unsightly blank subform control.

I would like for the parent form to refresh or reload on this cancellation. I have tried numerous things, but I'm not getting anywhere.
What is the correct method to deal with an 'after cancel' event.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:24
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try adding another step behind your button code to check if the subform is empty or not and react accordingly. Cheers!
 

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
Like 'If me.dirty'? I'll try it.. but, has the form actually opened at this point? The form's filter has executed and is asking for input, but is the form considered open at this point? Curious..
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:24
Joined
Oct 29, 2018
Messages
21,358
Hi. If you can post your actual code, we could try to help you figure it out. Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:24
Joined
Feb 28, 2001
Messages
27,001
When you talk about opening a subform but it offers a parameter box, is that YOUR parameter box or did Access build it? As long as you are still in the subform's Form_Open routine you can supply vbTRUE to the Cancel argument. If you do, the form won't open at all.

But if you let Access display that parameter box for you, I don't know whether the box is before or after the Form_Open routine. If it is after then the subform WILL open. It might not work right but it WILL open.
 

Micron

AWF VIP
Local time
Yesterday, 21:24
Joined
Oct 20, 2018
Messages
3,476
, but is the form considered open at this point?
What form, the subform? It opens first and loads first. Given that, I'm not sure this is a valid point (depends on whether or not I've interpreted it correctly:
As long as you are still in the subform's Form_Open routine you can supply vbTRUE to the Cancel argument. If you do, the form won't open at all.
IMO you will have to do something with the subform depending on whether or not you provide the parameter, and that might require a test for whatever that parameter is (being null or whatever). What that something is depends on the relationship between the 2 forms. Or you need to re-evaluate the whole approach - such as why are you bothering with the parameter, or is it an error you're not addressing, etc.

After reviewing, I guess The Doc Man's suggestion is valid since the parameter prompt is raised by the subform, not the main form. I missed that point. Whether or not cancelling subform opening is what you want to do is another matter.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:24
Joined
Feb 19, 2002
Messages
42,981
Why does the subform prompt for a parameter? Use an unbound textbox on the form to hold the prompt data and when the button is pressed, don't open the subform unless the textbox contains a value.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:24
Joined
Feb 19, 2002
Messages
42,981
We may have a terminology issue here. I assumed the poster was calling a "subform" what was actually a popup form since suborms are always loaded. I probably should have mentioned that. But assuming we are talking about an actual subform, you would leave the SourceObject property of the subform control blank and then populate it when the button is pressed.
 

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
Clearly I need to be more clear..apologies.

First, please know that all my forms are pop-up forms

1. The Main form consists of a column of buttons on the left, with a subform control on the right. (sfctl)
2. Each button tells sfctl what the SourceObject is. ( Me.sfctl.SourceObject="FormName" )
3. Regarding the form in question- I have set filter criteria in the form's underlying query. [Please Enter Serial Number]
4. When I click the button, I am prompted for the filter criteria "Please Enter Serial Number"

---the issue---------
5. If I cancel the prompt, the subform control is left empty (See attached). How do I address this?
---New issue-------
If I input a known serial number into the dialog box, the form opens but it is blank, as if the criteria is not being passed to the form.

Any insight on these issues would be much appreciated.
Thank you.
 

Attachments

  • EXAMPLE1.png
    EXAMPLE1.png
    14.7 KB · Views: 95

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:24
Joined
Oct 29, 2018
Messages
21,358
Clearly I need to be more clear..apologies.

First, please know that all my forms are pop-up forms

1. The Main form consists of a column of buttons on the left, with a subform control on the right. (sfctl)
2. Each button tells sfctl what the SourceObject is. ( Me.sfctl.SourceObject="FormName" )
3. Regarding the form in question- I have set filter criteria in the form's underlying query. [Please Enter Serial Number]
4. When I click the button, I am prompted for the filter criteria "Please Enter Serial Number"

---the issue---------
5. If I cancel the prompt, the subform control is left empty (See attached). How do I address this?
---New issue-------
If I input a known serial number into the dialog box, the form opens but it is blank, as if the criteria is not being passed to the form.

Any insight on these issues would be much appreciated.
Thank you.
Hi. I asked for the code earlier, but let's say you have something like this:
Code:
Me.SubformName.SourceObject="FormName"
Then, you could try changing it to this:
Code:
Dim strSourceName As String
strSourceName = Me.SubformName.SourceObject
Me.SubformName.SourceObject="FormName"
If Me.SubformName.Form.Recordset.RecordCount = 0 Then
    MsgBox "No records to show...", vbInformation, "Not Allowed!"
    Me.SubformName.SourceObject=strSourceName
End If
Hope that helps...
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:24
Joined
Feb 19, 2002
Messages
42,981
theDBGuy has a different view so I'll stop confusing the issue.
 

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
We may have a terminology issue here. I assumed the poster was calling a "subform" what was actually a popup form since suborms are always loaded. I probably should have mentioned that. But assuming we are talking about an actual subform, you would leave the SourceObject property of the subform control blank and then populate it when the button is pressed.

Thanks Pat.
Yes, the SourceObject property on the form is blank, and populated from the button click. This is what is coming around to bite me in the ass. If I cancel the dialog box, then the SourceObject does not get specified, and that leaves a large empty white box where the form would appear. I need to deal with this somehow.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:24
Joined
Oct 29, 2018
Messages
21,358
theDBGuy has a different view so I'll stop confusing the issue.
Hi Pat. Sorry if I was out of line. I was just following up on what I suggested back in Post #2, which I think maybe I didn't communicate properly.
 

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
Hi. I asked for the code earlier, but let's say you have something like this:
Code:
Me.SubformName.SourceObject="FormName"
Then, you could try changing it to this:
Code:
Dim strSourceName As String
strSourceName = Me.SubformName.SourceObject
Me.SubformName.SourceObject="FormName"
If Me.SubformName.Form.Recordset.RecordCount = 0 Then
    MsgBox "No records to show...", vbInformation, "Not Allowed!"
    Me.SubformName.SourceObject=strSourceName
End If
Hope that helps...

Thanks DBguy
I tried this, and I think it wants to work, but my new issue is interfering with it. That issue being, when I enter the serial number into the "Enter Parameter" dialog box, it does not get passed to the form. The form opens blank, and your message box is invoked. Here is the code I used:


Private Sub btn_minv_Click()

Me.sfctl.Visible = True

Dim strSourceName As String
strSourceName = Me.sfctl.SourceObject
Me.sfctl.SourceObject = "MoveInv_sub"
If Me.sfctl.Form.Recordset.RecordCount = 0 Then
MsgBox "No records to show...", vbInformation, "Not Allowed!"
Me.sfctl.SourceObject = strSourceName
End If

End Sub
___________
If I cancel at that point, I get an error at: If Me.sfctl.Form.Recordset.RecordCount = 0 Then
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:24
Joined
Oct 29, 2018
Messages
21,358
Thanks DBguy
I tried this, and I think it wants to work, but my new issue is interfering with it. That issue being, when I enter the serial number into the "Enter Parameter" dialog box, it does not get passed to the form. The form opens blank, and your message box is invoked. Here is the code I used:
Hi. That's an interesting result. I wasn't expecting that. Just to verify, if the subform opens up empty, what would you rather happen then? In the code I suggested, I assumed you wanted the previous subform to stay on. Is that correct?
 

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
Hi. That's an interesting result. I wasn't expecting that. Just to verify, if the subform opens up empty, what would you rather happen then? In the code I suggested, I assumed you wanted the previous subform to stay on. Is that correct?

The cleanest thing would be to reopen the main form and allow the user to move to the next task. I need that to happen anyway, after the user makes the required edit thru this form.
Here are more screenshots if they help.
 

Attachments

  • 1 Dialog.png
    1 Dialog.png
    15.7 KB · Views: 93
  • 2 AfterDialog.png
    2 AfterDialog.png
    55.4 KB · Views: 87
  • on OK.png
    on OK.png
    14.7 KB · Views: 92

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
I should add that, the subform control is not visible until a button is clicked. Button code changes that property back to 'visible'.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:24
Joined
Oct 29, 2018
Messages
21,358
Hi. Before I ask my next question regarding those images, let me ask you this first? Are you able to post a demo version of your db? It might help us find a solution faster.
 

qweeqweg

Member
Local time
Yesterday, 20:24
Joined
Dec 12, 2019
Messages
33
Hi. Before I ask my next question regarding those images, let me ask you this first? Are you able to post a demo version of your db? It might help us find a solution faster.

Negative. Data aggregation security concerns. Sorry.

On second thought, I could prepare a 'demo'. I just don't have time right now. Maybe tomorrow.
 
Last edited:

Users who are viewing this thread

Top Bottom