dynamic custom delete message

[Rachael2211]

Hi All,

I want to create a delete message box rather than use the system one purely so I can make it larger for use on phones and tablets over an rdp but I dont want to create an individual pop-up form for every situation that will be specific to deleting the particular record (hope that makes sense).

So, so far my custom pop-up form called "a_DeleteMsg" is called from a command button and I use public variables to pass info to it to create a delete query string and run it. This works fine. What I then want to do is pass some stuff to the pop-up so it will requery the subform it was called from so wanting to recreate this kind of thing

Forms!a_TimesheetsFRM!a_TimesheetsSFRM.form.requery

I've tried passing it as a public variable string, control, object but I cant get this to work, I've also thought of maybe I can call the after-update event that will requery the subform somehow from the pop-up!! Having spent lots of time trying stuff thought one of you amazing coders out there will be able to quickly point me in the right direction.

thank you heaps Rachael

 

[The_Doc_Man]

In order to make your form requery, your best bet is to pass the form name, then somewhere in your code have

Forms(passed-in-form-name).Requery

That should do it for you.

 

[Deleted member 147267]

Your procedure will need parameters; perhaps subformname and main form name. If you want to requery the subform only, I don't see the need for the mainform name. You might be able to use a function call that applies to all forms so that you can pass the form name to (Screen.ActiveForm.Name) it and decide what to do based on the form name. In that case, each button involved would have the same function call: MyFunction(Screen.ActiveForm.Name As String) - that presumes the button is on the subform. If it isn't, you will need to drill down to the subform name.

 

[Rachael2211]

Hi Doc_Man, yeah I've tried that but it doesnt seem to like the it

I tried to create a public variable then set it to "Forms!a_TimesheetsFRM!a_TimesheetsSFRM", catch that in a textbox on the delete message pop-up then do as you've said"

Forms(me.txtSubformName).requery

Just cant get it to work, feel Im close but doing something wrong.

Heres what Im trying, probably barking up the wrong tree!!

'variables for delete msg
Public vtblName As String
Public vtblIDName As String
Public vtblID As LongPtr
Public vRequery As String


Function GetvtblName() As String
GetvtblName = vtblName
End Function
Function GetvtblIDName() As String
GetvtblIDName = vtblIDName
End Function
Function GetvtblID() As LongPtr
GetvtblID = vtblID
End Function
Function GetvRequery() As String
GetvRequery = vRequery
End Function

'open the delete pop-up
Private Sub btnTrash_sm_Click()

vtblName = "Timesheets"
vtblIDName = "TimesheetID"
vtblID = Me.TimesheetID
vRequery = "Forms!a_TimesheetsFRM!a_TimesheetsSFRM"

DoCmd.OpenForm "a_DeleteMsg"
End Sub

'on open event of delete pop-up
Private Sub Form_Open(Cancel As Integer)

Me.tblName = GetvtblName()
Me.tblIDName = GetvtblIDName()
Me.tblID = GetvtblID()
Me.txtRequery = GetvRequery()

End Sub

'click yes to delete
Private Sub cmdYesDel_Click()
DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE " & Me.tblName & ".* FROM " & Me.tblName & " WHERE " & Me.tblName & "." & Me.tblIDName & " = " & Me.tblID & "; ")

Forms(me.txtRequery).requery

DoCmd.Close acForm, "a_DeleteMsg"

DoCmd.SetWarnings True

End Sub

 

[Rachael2211]

thanks for your reply Micron, yes will try this, have posted above what I'm trying to do

 

[Deleted member 147267]

I think you're over complicating it as it should be as simple as Doc and I are saying. However I've read your post a few times and can't really decipher what you have or are trying to do as it seems that the usual advice isn't working for you. If you could post a zipped copy of the db it ought to clear up the confusion - much of which (for me) stems from all these variables with broad scopes. I can't see the need for them based on what you post says you want to do.

BTW I see that no one has asked you to enclose your code in code tags in any of your prior posts. If used and suitably indented and spaced it is so much easier to read and follow. You can go back to your code post and try it if you care to.

 

[Rachael2211]

thank you Micron, sorry I don't know how to enclose in code tags but will learn, sorry.

And yes I'm sure I'm over complicating things, was trying to think up a clever way of using the one form as a custom delete message form for all deleting confirmations through out the database. I've googled til my eyes fell out but seems no one really does this. The reason I didn't want to use the system generated one is the database is being deployed over an RDP and the staff are accessing their timesheets on their phones, the system message is too small to tap ok on so was trying to make a larger custom pop-up for delete confirmation then make it dynamic so I dont end up with hundreds of the same pop-up calling a delete query specific to the particular form it got called from.

 

[Isaac]

I don't know how to enclose in code tags but will learn, sorry

 

[Rachael2211]

View attachment 83971

thank you

 

[Deleted member 147267]

What you've just said is quite doable. You have one form for your message. Whatever user action that causes it to be opened (I don't think you've revealed that) needs to be a function (not sub) that will accept all the parameters you need. Perhaps you should research function calls to see how it's done, but
suppose you want to pass the form name and a custom message so that you can requery that form:

Public MyFunction (frmName As String, strMsg As String)
Msgbox strMsg
Docmd.Requery Forms(frmName) - or subform reference or whatever
End function

The call might be
MyFunction Me.Name, "Enter data, silly!"

However, if you want something more complicated we (at least I ) have to understand exactly what that is in order to provide more than just general advice.

EDITED so that code reflects a form call and not a subform call.

 

[Rachael2211]

thanks again for replying, i really appreciate your help

I've tried to make an example to show you but as you said Iv'e overcomplicated things and its to hard sorry

Perhaps I need to forget what I was trying to do and ask the question

"How do I create a custom delete message that is opened when a user clicks on a command button that says delete (instead of doing a docmd.runcommand accmddeleterecord and having the system generated message pop-up ofr confirmation) I want the command button to open a pop-up form that kinda looks like a delete message confirmation but is bigger. When the user clicks 'yes' it deletes the record and requeries the subform it was called from. I want to use the same delete message pop-up throughout the database so need to be dynamic. Its needs to grab table, field and ID numbers to run the delete, then I wanted it to requery the underlying subform)"

Sorry if I'm a pain, not sure how else to explain it,, thanks for your time

 

[Rachael2211]

ill try explain with images

See the trash can, click on it to delete a line

next pic show my custom pop-up delete message

Clicking yes deletes the line using the code i posted earlier, then my subform displays like next pic

I want to requery it, its a Sub-Subform. I want to use this same delete pop-up through out the whole database so needs to pass variables from the form its opened from to do the delete.

Hope that makes sense, thanks in advance
Rachael

 

[Deleted member 147267]

Sorry if I'm a pain,

Not at all! Just so you know, I don't usually advocate the deletion of records as it's so final. I prefer to flag them somehow, most times with a date field that marks them as "archived" or whatever label seems appropriate.

One more question if I may: you have not stated what options the user should have and what they should be able to do. I'm surmising that you not only want to present a custom form/message but give them the option to cancel the delete. Or is it to just notify them with a custom form/message and to the delete anyway? That's not clear. Maybe you need to review https://docs.microsoft.com/en-us/office/vba/api/access.form.beforedelconfirm(even) to provide the option. That is a higher degree of difficulty than just saying "your record was deleted" or something, in a custom form.

 

[Rachael2211]

Thank you!

The other option you'll see in the second pic is for them to choose 'no' which simply closes the form and nothing changes.

I'm trying your suggestion with the function above, I'm learning all the time and appreciate your patience

 

[Deleted member 147267]

Ahh, so the beforedelelteconfirm event needs to call a function that opens your confirmation form modally. However the function needs to return a value (I have not shown that in anything I've posted here thus far) based on the button click. So yes button should return -1, no should return 0 to the event procedure Cancel parameter; or you can use other command button captions such as OK / Cancel or whatever you like. Then the beforedeleteconfirm either deletes the record, or it does not. If you want to show a delete confirmation message after, you need the after event as the link shows.

So I guess your trashcan click event will invoke the record deletion, the beforedeleteconfirm event will call a function to open the confirmation form modally
e.g. Cancel = myFunction
that form function will return the chosen value to the Cancel parameter, and the event will either allow the record delete or cancel it. I'm not seeing the need for a delete sql statement unless the form is unbound. If not, the code that runs from the trash can click event would parse the sql statement.

If I understand the process now, it is not too difficult to implement if you know how, but there are several steps and things that need to be in place and it's a bit much to walk anyone through it via a forum. If you're still stuck, consider posting a zipped db copy that you'll be able to work with when it's passed back to you.

 

[Rachael2211]

thanks so much, its always awesome to have someone point you in the right direction, I can usually figure the rest out once im on the right track, I'll stop pestering you and see how I go, cheers Rachael

 

[Deleted member 147267]

You're not a bother. You seem so cheerful (is it your posts or smiling face??) so it's a pleasure.
You also seem to be someone who wants to be shown "how to fish" which is GREAT and that attitude will get you more help for sure. Yeah, there are one or two here who might jump in and spoon feed you as if it's their call in life, but you can learn so much more by researching and improvising/implementing what you find, especially when that involves more than just copying and pasting code you don't understand. Having said that, it's my personal take that if it comes down to working on a db you post, I have no issue with that because you're not just looking for someone to do the work for you. I once spent days working on a problem for someone who had a similar attitude and an issue that was quite complex and out of their realm, but it was obvious that they had genuinely tried.

Good luck and be sure to come back if you get stuck!

 

[Rachael2211]

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!

 

[Rachael2211]

haha posted at the same time, thanks I am cheerful lol, we dont have alotta choice at the moment heading back into COVID full lockdown here on wednesday in Victoria, Australia, plenty of time to work on my database lol and yes understand what you say about just doing stuff for people, nobody learns anything, I want to learn and be cleverer and I like smiley faces haha

PS see you're in Canada, my best mate is a Quebecer, visited her two years ago and loved Ontario (and went fishing lol), won't be seeing her for ahile now though, were not likley to international travel for 4 years theyre saying poo!!

 

[Deleted member 147267]

Off topic but...
I went to AU and NZ on a cruise a few years ago. Can't begin to tell you how awesome that was. Couldn't understand the effect that vacation had on me for months after, and I'd be embarrassed to explain what I mean by that. My wife's favourite part was the reserve with the wallaby's and kangaroos (I think that was Adelaide).

Will download tonight, but will probably won't be able to get into it until tomorrow. I may have questions - or someone more talented than me might beat me to it given that AWF is pretty much world-wide with all sorts of different time zones with so many talented people here.