Full Version: Delete record in subform
UtterAccess Forums > Microsoft® Access > Access Forms
Acess_help
I have a few records in my subform. How do I delete a particular record? Currently I am able to delete it but when i delete one item, the rest of the items in the subform disappear as well.
uploaded a small sample db and hope that you guys can help me. Nearing my project deadline and I haven't sort out quite a few things!
Note: FrmOrderInfoMain is the form that I'm talking about. I used SerialNum and PONum as the criteria to delete the records but after deleting one item, the rest of the items disappear too.
Thankful and grateful of any help,
Swee
wen
Hi,
downloaded your database and am still figuring out what it is that you are trying to do.
One thing that didn't work was the query QueryOrderInfoSub.
You linked account_desc although these fields were not filled in.
I think you really intended to link the fields account_code.
Regards,
Wen
Acess_help
Think that was a minor error. Think it could be fixed by changing the last column of QueryOrderInfoSub to AccountCode from tblAccountsCode.
That was not what I'm trying to fix though. I'm trying to work on deleting the record in the subform. Did you try that out?
wen
Hi,
just did. It works OK for me.
The real problem I think is that the DataEntry property of form SubFrmOrderInfo is set to 'yes'.
After the requery the form will no longer show any existing records.
Thereby giving the impression that the other records have been deleted.
Oset the property to 'no' to check this, and the other records were shown correctly.
HTH, Wen
Acess_help
I see. Thanks for the help there! Let's say I have three records. After I delete the second record, I'm left with the first and third record with their serial numbers being 1 and 3 respectively. How can I change 3 to 2 automatically after the record before it has been deleted?
Any ideas?
Acess_help
Do you have any suggestions other than using Serial Number? I might take out Serial Number from my form.
wen
Hiding the numbers (if the user doesn't really need them) is the easiest to do.
Acess_help
Hmm why didnt I think of that? lol.. I have made some changes to the db and now the problem is that the delete button doesnt work the first time. It only works after I click on it twice. I guess the first time actually "saves" the record in the db and so it does not carry out the code in the OnClick event of the button.
I have uploaded an edited version of my db. Could you check it out for me? Thanks so much.
wen
You are correct that the first time the record is not saved, but the code does execute.
However because there are no records present the DELETE-statement doesn't find any records to delete. You have two options I think:
1. first save the current record explicitly and then delete it.
2. check for a new record and discard the changes
HTH, Wen
Acess_help
Hi Wen,
Are you saying that the record is deleted when you say the code does execute? Could you further explain the two options that you are talking about? I did not really understand you there.
wen
Hi Swee,
So, what I meant was that when the DELETE-statement executes,
Access searches for all the records that conform to the WHERE-clause. Any records found will then be deleted physically. So the first stage (searching for records) executes anyway, but the second stage (physically removing the data) is not executed because nothing has been found.
The reason that nothing is found, is because Access has not yet saved the data that is visible on the screen to the disk. You could perform the following tests.
1. create a new record and directly after that click on the delete-button.
In this case you will need to click on the button again to really delete it.
2. create a new record and another one.
click on the delete-button for the first record and again for the second record.
In this case the records are deleted immediately, because you switched from one record to the other before trying to delete. When switching from the current record Access will automatically try to save the changes.
In the second test you kind of explicitly saved the first record, but you had to do it in a round-about way. In my first option I suggest that you do it inside the code.
The second option goes like this:
Suppose you created a new record (on screen), the current record has not been switched yet, so Access has not truly saved the data yet. If you click on the Delete-button, the SQL-statement will not find any (PHYSICAL) records. But you could programmatically check whether the form contains a current NEW record. If there is, you can programmatically discard the changes in the form (this is similar to using the Escape key).
I hope this helps. Let me know if this makes things clearer.
Regards, Wen
wen
Hi,
I have inserted some wizard-generated code to make things clearer.
HTH, Wen
Private Sub Knop15_Click()
On Error GoTo Err_Knop15_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Knop15_Click:
Exit Sub
Err_Knop15_Click:
MsgBox Err.Description
Resume Exit_Knop15_Click

End Sub
Private Sub Knop16_Click()
On Error GoTo Err_Knop16_Click
If Me.NewRecord Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Else
MsgBox "nothing new"
End If
Exit_Knop16_Click:
Exit Sub
Err_Knop16_Click:
MsgBox Err.Description
Resume Exit_Knop16_Click

End Sub
Acess_help
Hi Wen,
Thanks a million for the explanation. It really got me moving on. I have modified my code according to what you suggested and have placed this code in the OnClick event of my button.
CODE
    If MsgBox(strmsg1, vbExclamation + vbYesNo, strTitle1) = vbYes Then
        If IsNull(Me.S_N) Or Me.S_N = "" Or IsNull(Me.PONum) Or Me.PONum = "" Then
        Else
            If Me.NewRecord Then
                DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
            Else
                strSQL1 = " DELETE *" & _
                         " FROM tblOrderInfoSub" & _
                         " WHERE PONum = '" & Me.PONum & "' AND SerialNum = " & Me.S_N & ""
        
                CurrentDb.Execute strSQL1
                Me.Requery
            End If
        End If
    End If

Is that what you were trying to say or have I gone the wrong way again? Thanks again for the help rendered!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.