Start Free Trial

asked on

Adding a Remove function to a form

What is the best way to add a checkbox to a continous form and a funtion to a command button
that would remove an Order that is in a queue?

In this example, frmReviewOrder is a continuous form that a Supervisor may use to review an order that created by an employee in another form.
A Supervisor may choose to remove any of the order(s)in queue after a review, In this case how can this be achieved?

I have attached a sample db for this purpose.
Ordersample.txt

Add the following code to your Remove button On_Click event.

Private Sub btnRemove_Click()
On Error GoTo Err_btnRemove_Click
 
 
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
Exit_btnRemove_Click:
    Exit Sub
 
Err_btnRemove_Click:
    MsgBox Err.Description
    Resume Exit_btnRemove_Click
    
End Sub

Open in new window

ASKER

I tested your suggested code and when I clicked "No" such that record should not be removed - the record from the continuous form still disappeared.

Secpndly, I was hoping to add a feature unto the continuous form such that a particular record can be selected to be removed.

ASKER

Something like adding a checkbox to the continuous form would do.

OK. I'll check but I didn't have that problem in my test.

Ok. Try this solution. I am attaching a revised copy of your db. I have changed the form by adding a Delete checkbox and changed the code in the Remove button. Rename to mdb type extension.
ordersample1.txt

ASKER

Thanks for the revised sample. There are still some issues.
(1) The system msfbox indicate that "0" record was about to be deleted. The msgbox should indicate
number of record(s) about to be deleted. Please test this at your end.

(2). Is it better to use a custome msgbox like "Are you sure you want to delete the 1 record?
...this can be utilized in place of the system msgbox.

(3). When user clicks "No" - not to delete the record, there was an error msgbox.
"Run-Time error '2501':
The RunSQL was canceled

on....

DoCmd.RunSQL "DELETE tblWorkOrders.*, tblWorkOrders.chkDelete " _
& "FROM tblWorkOrders " _
& "WHERE (((tblWorkOrders.chkDelete)=True));"

I guess adding an Error trapping msgbox may resolve this.

I'll give it a look. I need a cup of coffee. Be back in a few.

ASKER

Emil_Gray:
I tested the updated samplw db which you mistakenly posted in another thread. Here are my findingsL
When I select like two records to delete, - the delete msgbox indicated that (1) msgbox was about to be deleted.

When I select like one record to delete, - the delete msgbox indicated that (0) msgbox was about to be deleted.

When I select like three records to delete, - the delete msgbox indicated that (2) msgbox was about to be deleted.

The "Run-Time error '2501': The RunSQL was canceled still pops out when user cancels "No" - not to delete a record.

Regards
Bill

ASKER

Emil_Gray:
I quite agree with you, a cup of coffee will do

Add Me.Refresh as in the sample code below. That should fix the problem. It wouldn't hurt also to add error trapping. Do you know how?

Private Sub btnRemove_Click()
 
Me.Refresh
 
DoCmd.RunSQL "DELETE tblWorkOrders.*, tblWorkOrders.chkDelete " _
& "FROM tblWorkOrders " _
& "WHERE (((tblWorkOrders.chkDelete)=True));"
 
End Sub

Open in new window

ASKER

.....add error trapping. Do you know how?

I should be able to handle that... I will try out the refresh technique also and let you know later..

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ok,
I further tweaked the code to suppress the system warnings and the run time error 2051 when the user response is "Yes":
**************
Private Sub btnRemove_Click()
On Error GoTo Err_btnRemove_Click
Me.Refresh
Response = MsgBox("Hello, are you sure you want to delete the selected record(s)?", vbYesNo, "User Information")
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblWorkOrders.*, tblWorkOrders.chkDelete " _
& "FROM tblWorkOrders " _
& "WHERE (((tblWorkOrders.chkDelete)=True));"
DoCmd.SetWarnings True
Me.Refresh
Else
Exit Sub
End If
Exit_btnRemove_Click:
Exit Sub

Err_btnRemove_Click:
MsgBox Err.Description
Resume Exit_btnRemove_Click
End Sub

What do you think?

ASKER

Is there anyway yo could add a record count to the msgbox just like the system msgbox does it?

Thanks for your assistance.

Regards
Bill

ASKER

There are some routines you added to the sample you sent such as "Importing Relationships and converting db. etc..

I think I could find it useful if only I know how to use them.

Regards
Bill

In the Remove Button,
Set filter to records to be deleted. Confirm the deletetion of number of records.
Delete thr records if confirmed, then remove filter.
Attached file.
Ordersample.zip.txt

billcute,

If you have VBA experience make a copy of a db with a bunch of stuff in it (no macros though, the code doesn't look for them and I don't recommend using macros at all) and play with the code. That will give you a start. It has been a while since I used those modules and I don't know that I gave you everything that all of the modules require to work. I would have to check with a friend in Florida and see if he still has the CD I sent him with everything. That is really the only way I would know if all necessary db objects are included. I'll send him an email and ask him if he still has the CD.

ASKER

hnasr.
Your code works great without any flaws and it resolved all my concerns. I am posting it here for all to see.

Regards
Bill

Private Sub btnRemove_Click()
    Dim strFilter As String
    Dim rs As Recordset, rc As Long
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    strFilter = "toBeDeleted=" & True
    Me.Filter = strFilter
    Me.Refresh
    Me.Filter = strFilter
    Me.FilterOn = True
    Set rs = Me.RecordsetClone
    rc = rs.RecordCount
    If rc = 0 Then
        Me.FilterOn = False
        Exit Sub
    End If
    Msg = CStr(rc) & " record/s to be deleted, " & "do you want to continue ?"    ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
    Title = "Delete Selected Records"    ' Define title.
    
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then    ' User chose Yes.
        MyString = "Yes"    ' Perform some action.
        DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord
        
        DoCmd.SetWarnings False
        DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete
        
        DoCmd.SetWarnings True
    Else    ' User chose No.
        MyString = "No"    ' Perform some action.
    End If
    
    Set rs = Nothing
    Me.FilterOn = False
End Sub

Open in new window

ASKER

Emil_Gray:
Please note that expert "hnasr" posted his code after this post has closed. It worked fine and I think he deserves some credit even though he did not ask for it.

To this end, I'll like to re-open this post and share the points if you dont mind.

I am sure that you would agree with me as a fair minded person.

Regards
Bill

ASKER

In this case, I will be ask the CS to re-open this question in order to share the points.

Regards
Bill

ASKER

Emil_Gray
Thanks for the CD offer. I will look forward for it.

Regards
Bill

ASKER

hnasr:
I actually meant to post this comment here instead of the other thread.
************
It might be a good idea to add another features that limits the function to delete only records with data in it.
Note:
If a line has no data in it give a msgbox...record #...cannot be deleted....please uncheck it in order to continue.

Usually the last line in the continuous form has no data in it. As such it should be filtered out and not counted as a record to be deleted. It should in fact prevent delete action since it contained no data.

What do you think?

Regards
Bill

ASKER

hnasr.
Did you see my last comment on limiting the function to delete only records with data.

Regards
Bill

ASKER

hnasr:
I'll close this post and open a new one to address the additional info.

Thanks
Bill

ASKER

hnasr:
In order for me to grade your code, you will need to post the code yourself.

Regards
Bill

billcute,
I'll try to post with the modification! Thanks

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

hansr:
The updated code works without flaws.

Here is the code:
Private Sub btnRemove_Click()
Dim strFilter As String
Dim rs As Recordset, rc As Long
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
strFilter = "toBeDeleted=" & True
Me.Filter = strFilter
Me.Refresh
Me.Filter = strFilter
Me.FilterOn = True
Set rs = Me.RecordsetClone
rc = rs.RecordCount
If rc = 0 Then
Me.FilterOn = False
Exit Sub
End If
Msg = CStr(rc) & " record/s to be deleted, " & "do you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Delete Selected Records" ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete

DoCmd.SetWarnings True
Else ' User chose No.
MyString = "No" ' Perform some action.
End If

Set rs = Nothing
Me.FilterOn = False
End Sub

ASKER

Emil_Gray:
I appreciated your assistance and I awarded the points at ratio 3:2 in favor of hnasr because his code worked best.
It's the fairest thing for me to do.

Best regards
Bill

Sorry. I checked with my man in Florida and he can't find/lost the CD I sent him with all of the objects that make everything work. I doubt I have kept the stuff anywhere. The only place it might be is on an old computer using Windows 98 (YUCK) that hasn't seen the light of day for a while. If the opportunity arises and I'm thinking about it I'll look.

EG

ASKER

Thanks