"Uwe Range" <ur****@gmx.d e> wrote in message
news:64******** *************** ***@posting.goo gle.com...
"Fletcher Arnold" <fl****@home.co m> wrote in message
news:<bt******* ***@sparta.btin ternet.com>...
"Uwe Range" <ur****@gmx.d e> wrote in message
news:64******** *************** ***@posting.goo gle.com... Hi to all,
I am displaying a list of records in a subform which is embedded in a
popup main form (in order to ensure that users close the form when
leaving it).
It seems to be impossible to delete a record in this subform.
When I switched modal off and tried to delete a record from the list,
I deleted a record on another form (below the popup form).
Does anybody know how to do this?
(I hope the decription is somewhat understandable. )
Thanks in advance for any help!
Uwe
How have you tried to delete it? From the edit menu? By right-clicking
the subform and selecting delete? A button on the main form? You will have
problems with the first method if you want the main form to be modal,
but what about methods 2 and 3?
Fletcher
First of all thanks for the hints.
I have tried all of this. Right-click doesn't give me a choice for
delete. I can only try the menu-delete, if the form is not modal. Then
it deletes the current record on the form below which is not set to
popup. I also tried to put a delete-button on the list-form (with the
button-wizard). It does the same as the menu-delete, because it uses
the menu-command. I would have liked to create my own delete button,
but I could not find a proper command for deleting a record ona form.
Is there one?
Uwe
My first (and simplest) suggestion is to find which shortcut menu opens when
you right-click the subform. Then customize this to make sure it contains
the delete button. This assumes you know something about customizing menus
and toolbars ... it is the same in lots of applications.
However, if you have started to look at code, then it might be good to learn
how it works rather than just using a wizard. Here is an example of code
that would do what you want. It assumes you have a table called
'tblMyTable' with a field 'ID' which is a number and is also the primary
key. The form has a button named 'cmdDelete' and a subform named 'sbfSub1'
Private Sub cmdDelete_Click ()
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim lngID As Long
Dim strMsg As String
Dim strSQL As String
lngID = Nz(Me.sbfSub1.F orm!ID, 0)
If lngID = 0 Then
strMsg = "First select a record to delete"
MsgBox strMsg, vbExclamation
Exit Sub
End If
strMsg = "Are you sure you wish to delete record:" & vbCrLf & _
"ID = " & lngID & vbCrLf & _
"Other Info = ....(make sure user knows which record)"
If MsgBox(strMsg, vbExclamation Or vbYesNoCancel, _
"Confirm") <> vbYes Then
Exit Sub
End If
strSQL = "DELETE FROM tblMyTable WHERE ID=" & lngID
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
If dbs.RecordsAffe cted = 1 Then
Me.sbfSub1.Form .Requery
Else
strMsg = "Cannot delete record"
MsgBox strMsg, vbCritical
End If
Exit_Handler:
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
