dynamic custom delete message (1 Viewer)

[Micron]

OK, I see that you have a fish now. Bon Appetit!

 

[Rachael2211]

Hi Arnelgp, thanks for your response, but i am wanting to avoid system generated message boxes as my database is deployed over an RDP with staff accessing it on their mobile phones, the system message boxes are too small so my idea for a custom one is to make it easier for staff to read and then click a response, cheers Rachael

Micron, yeah wallabies and kanga's are pretty cool although a pest on the land, I've got vineyard and blueberries and they are a pest barrelling through the plants and eating them, fighting in the rows and smashing the blueberry bushes grrrr lol even though they can jump really high, usually choose if they can to go through fences so wreck them too lol

 

[arnelgp]

try breaking the line of msgbox into 2 or 3 lines or use a Form as your custom message handler.

 

[Rachael2211]

yeah that's what I've been trying to do - use a form as a custom message and could get it to work but where I got stuck was requerying the sub subform that the custom message form was called from. If you look at my example database you can see what i was trying to do. I think i've got it fairly wrong but couldn't think how else to do it

 

[deletedT]

ok so I'm still stuck, as usual overcomplicating everything lol

I've attached an example cut down as best as I could, you'll see the trash can and what I was trying to do, your help is greatly appreciated, thank you!

Your problem is that you have two level of sub forms.
Parent ⇒ sub form ⇒ sub form

to requery the parent you should use:
Forms(Parent).Controls(subform1).Controls(Subform1).Requery
OR
Forms("a_TimesheetFRM").Controls("a_Timesheet1FRM").Controls("a_TimesheetsOVFRM").Requery

check the attached database.

Edit: You've complicated your database. You could have the same results with less efforts.

Edit 2 : Your icons were interesting. Designed them yourself?

 

[Rachael2211]

Hi Tera, thank you so much for responding and the requery code.

I know it's complicated but couldn't think how else to make it work so that i could use the same delete message form through out the program, would you have any suggestions on how I could simplify it please, and I really appreciate your input

And yes i did make the icons!

Kind regards, Rachael

 

[Rachael2211]

ahh cool i didn't know you could do Me.Parent.Form.Parent.Form.Name lol

 

[deletedT]

ahh cool i didn't know you could do Me.Parent.Form.Parent.Form.Name lol

There is no other way to access the parent of a parent sub form.
You can explicitly use the name of the form too ( a_TimesheetFRM ). But in case you want to use the same code in different situation, I thought it may be better to use it that way.

In case of making it simpler, I don't think you need a class for it. I try to setup some example as soon as possible, but as everybody already knows, I'm working almost 19 hours a day. So I can not promise when that as-soon-as-possible will be.

Your icons and design are great.

 

[Rachael2211]

Thank you Tera, please don't go to too much effort if you're really busy, my problems aren't important, look after yourself

 

[arnelgp]

your test data.
i commented out some code in the subform's Load event coz i am getting error for
undeclared variable.

 

[Rachael2211]

Hi arnelgp, thank you so much for taking the time here your solution works a treat and I can certainly implement this over the database as a standard delete message, thank you, thank you

 

[The_Doc_Man]

Forms(me.txtSubformName).requery

Looking back at your code, this line suddenly jumped out at me.

It implies that you have the name of a form in a textbox control on the current form. That is certainly possible, don't get me wrong on that point, but it is unusual. A user should normally be unable to know that information so if you have that form name there, it wouldn't have come from a user. But if you load that yourself, the problem is that if the textbox isn't locked, your user could alter then name and perhaps crash the app or at least trigger an error message. You mentioned putting a name somewhere so that you could get to it, but this doesn't seem like a good solution because of its vulnerability to alteration.

You might try looking up either of two possible places to put this name: In a TempVars object or in a general module public variable.

You said you couldn't get that reference to Forms(Me.txtSubFormName).Requery to work right. So.... what DID it do? Anything at all? Error message? Start blankly at you? A reference of that type ONLY works in one context, where the text box is on the currently open form underneath which your code is running as part of the form's class module. That "Me." reference is highly specific.

 

[arnelgp]

you're welcome!
forgot to warn you this, although the class will destroy the form object and the
command button object (of the class) when the subform closes, it is still best to implicitly close these
object on the Unload event of the subform:

Private Sub Form_Unload(Cancel As Integer)
Set clsDeleteRecord = Nothing
End Sub

 

[Micron]

All the query stuff isn't necessary IMHO. Nor is a class, global variables, recordset clones etc. etc.
It's as simple as I said provided that something is added to the form - either textbox or custom property.
That's so baffling that I can't help think I've over-simplified it and something is missing. You'll have to wait until tomorrow to see - or don't - as I'm signing off.

 

[Micron]

Rachael2211 - If you let Access do the work, there's no need to requery anything, no need to run delete sql or query thus no need for that code, nor do you need a custom class (a Rube Goldberg machine in this case). All you need is a way to pass the user choice back to the subform (or sub-subform in this case). You could use an unbound textbox or add a custom property to the form. I chose the latter just to practice it again. Over-complicating your db with such things will make it harder for you to remember what you've done (especially since you didn't) and can leave you struggling when you have to modify or fix it.

Much of the unnecessary code involved is double commented out to distinguish it from any comments. Code I added is bounded by lines with '**
When in the vb editor, you can do a text based search on '** to find all of it.
On your form, click the trash can, get the prompt, and if the choice is Yes, the record should delete with no further prompting or need to requery.
You can restore the records I've already deleted if need be, by deleting the table and renaming the copy of it to the original name.
The delete prompt you'd normally get is suppressed by turning off warnings, which I prefer to not do but sometimes it's unavoidable. In those cases, I will always use an error handler to ensure they are turned back on.

You should check your code for errors. When I compiled it, some of your code generated a few errors. I had to comment out those lines too so I have no idea if what I did will still work if you need to uncomment them.