Prev: Error Message in 2002
Next: "This file might not be safe if it contains code..." message with runtime version.
From: Jim Devenish on 5 Aug 2010 07:48 I have a continuous form. I can select a number of records by clicking on a record selector and then shift-clicking on another one. The whole group from the first to the second is selected. If I then select 'Delete record' from the Edit menu, I am asked if I wish to delete n records. Good! It works fine but I would like to perform the same activity by means of a button that I place on the form. If I create a button to delete a record with the help of the Control wizard I end up with code such as: DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 After sorting out the meaning of the magic numbers I get to: DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70 When I run this it only deletes one, not many, records. Furthermore, the help system informs me that the DoMenuItem has been replaced RunCommand!! However exploring the RunCommand gets me no further. By the way I am using Access 2000. The menu item seems to know that I have selected more than one record. How I can do this in code?
From: Jim Devenish on 5 Aug 2010 11:53 I have a partial solution to my problem. When using the Delete record item directly from the Edit menu I discover that the Delete event is called for each selected record. This enables me to count how many records have been selected and then set my own message: Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer) If MsgBox("You are about to delete " & nDeletions & " plants" & vbCrLf & vbCrLf & "Are you sure?", vbYesNo, "Deleting plants") = vbNo Then Cancel = True End If Response = acDataErrContinue nDeletions = 0 End Sub Private Sub Form_Delete(Cancel As Integer) nDeletions = nDeletions + 1 End Sub Private Sub Form_Open(Cancel As Integer) nDeletions = 0 End Sub This works OK. However I would still like to provide a button on the Form. I thought that this would work: Private Sub deleteButton_Click() On Error GoTo Err_deleteButton_Click DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70 Exit_deleteButton_Click: Exit Sub Err_deleteButton_Click: If Err.Number <> 2501 Then MsgBox Err.Description, , Err.Number End If Resume Exit_deleteButton_Click End Sub but it only deletes one record. I tried removing the first line: DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , acMenuVer70 If I do this then nothing is deleted. When one uses the menu item directly, the system seems to know which records have been selected but when one uses code with DoMenuItem, you have to do the selection. There seems to be no way of selecting the whole group. Any suggestions?
From: Douglas J. Steele on 5 Aug 2010 11:58 The proper way to do deletions is to use a Delete query, not to try and use the antiquated DoMenuItem method. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "Jim Devenish" <internet.shopping(a)foobox.com> wrote in message news:3771a3a5-78e6-4843-a1eb-8d4e6d6b8003(a)l20g2000yqm.googlegroups.com... >I have a partial solution to my problem. When using the Delete record > item directly from the Edit menu I discover that the Delete event is > called for each selected record. This enables me to count how many > records have been selected and then set my own message: > > Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As > Integer) > If MsgBox("You are about to delete " & nDeletions & " plants" & > vbCrLf & vbCrLf & "Are you sure?", vbYesNo, "Deleting plants") = vbNo > Then > Cancel = True > End If > Response = acDataErrContinue > nDeletions = 0 > End Sub > > Private Sub Form_Delete(Cancel As Integer) > nDeletions = nDeletions + 1 > End Sub > > Private Sub Form_Open(Cancel As Integer) > nDeletions = 0 > End Sub > > This works OK. > > However I would still like to provide a button on the Form. I thought > that this would work: > Private Sub deleteButton_Click() > On Error GoTo Err_deleteButton_Click > DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , > acMenuVer70 > DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70 > Exit_deleteButton_Click: > Exit Sub > Err_deleteButton_Click: > If Err.Number <> 2501 Then > MsgBox Err.Description, , Err.Number > End If > Resume Exit_deleteButton_Click > End Sub > > but it only deletes one record. I tried removing the first line: > DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , > acMenuVer70 > If I do this then nothing is deleted. > > When one uses the menu item directly, the system seems to know which > records have been selected but when one uses code with DoMenuItem, you > have to do the selection. There seems to be no way of selecting the > whole group. > > Any suggestions?
From: Jim Devenish on 5 Aug 2010 12:12 On Aug 5, 4:58 pm, "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote: > The proper way to do deletions is to use a Delete query, not to try and use > the antiquated DoMenuItem method. > > -- > Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele > Co-author: Access 2010 Solutions, published by Wiley > (no e-mails, please!) > Doug That is what I always do when deleting a single record. However one user has asked for the ability to delete a selected group. My problem is determining which records have been selected. The system seems to know. So why can I not find out in code?
From: Salad on 5 Aug 2010 12:33 Jim Devenish wrote: > I have a continuous form. I can select a number of records by clicking > on a record selector and then shift-clicking on another one. The > whole group from the first to the second is selected. If I then > select 'Delete record' from the Edit menu, I am asked if I wish to > delete n records. > > Good! It works fine but I would like to perform the same activity by > means of a button that I place on the form. > > If I create a button to delete a record with the help of the Control > wizard I end up with code such as: > > DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 > DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 > > After sorting out the meaning of the magic numbers I get to: > > DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord, , > acMenuVer70 > DoCmd.DoMenuItem acFormBar, acEditMenu, acDelete, , acMenuVer70 > > When I run this it only deletes one, not many, records. > > Furthermore, the help system informs me that the DoMenuItem has been > replaced RunCommand!! However exploring the RunCommand gets me no > further. By the way I am using Access 2000. > > The menu item seems to know that I have selected more than one > record. How I can do this in code? I think you need to use Seltop and Selheight. This is a method I used. I probably got it off the web and modified. Maybe somebody has another method. On the form's MouseMove event I store the current "Sel" values. If I press a button to delete, the "Sel" values are lost, thus they are reset in the SelRestore() routine. In this I display the list of employee names when the Delete button is pressed. I commented out the code to do the actual deletion. Option Compare Database Option Explicit Dim lngSelTop As Long Dim lngSelHeight As Long Dim intMouseDown As Integer Dim frm As Form Dim lngID As Long Dim s As String Private Sub Form_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) s = SelRecord([Form], "Down") End Sub Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) s = SelRecord([Form], "Move") End Sub Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) s = SelRecord([Form], "Up") End Sub Function SelRecord(f As Form, MouseEvent As String) As String Select Case MouseEvent Case "Move" ' Store the form and the form's Sel property settings ' in the MySel variables ONLY if mouse down has not ' occurred. If intMouseDown = True Then Exit Function Set frm = f lngSelTop = f.SelTop lngSelHeight = f.SelHeight Case "Down" ' Set flag indicating the mouse button has been pushed. intMouseDown = True Case "Up" ' Reset the flag for the next time around. intMouseDown = False End Select End Function Public Sub SelRestore() ' Restore the form's Sel property settings with the values ' stored in the lngSel variables. frm.SelTop = lngSelTop frm.SelHeight = lngSelHeight End Sub Private Sub CommandDelete() ' Restore the lost selection. SelRestore ' Enumerate the list of selected employee names. DisplaySelectedEmployeeNames() End Sub Private Sub DisplaySelectedEmployeeNames() Dim i As Long Dim f As Form Dim rst As Recordset Dim sDel As String ' Get the form and its recordset. Set f = Me Set rst = f.RecordsetClone ' Move to the first record in the recordset. If rst.RecordCount > 0 Then rst.MoveFirst ' Move to the first selected record. rst.Move f.SelTop - 1 ' Enumerate the list of selected records presenting ' the employee field in a message box. lngID = 0 For i = 1 To f.SelHeight sDel = sDel & rst![LastName] & " ," rst.MoveNext Next i MsgBox sDel rst.Close 'sDel = Left(sDel, Len(sDel) - 2) 'remove comma 'sDel = "Delete * From tblEmployees Where EmployeeID In (" & sDel & ")" 'DoCmd.SetWarnings False 'DoCmd.RunSQL sDel 'DoCmd.SetWarnings True 'remove them from form 'Me.Requery Else MsgBox "There are no records.", , "Info" End If End Sub
|
Next
|
Last
Pages: 1 2 Prev: Error Message in 2002 Next: "This file might not be safe if it contains code..." message with runtime version. |