Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    HK
    Posts
    7

    Unanswered: delete record problem in a form

    I got 3 records in a table and I have a form where the source is based on
    this table. Now I created a delete button from toolbox wizard and it should
    delete the current record. If I press that button, the current record on
    the form is empty out instead of deleted. After deletion and refresh, there are 3 records and 1 is all empty. How can I make it deleting a record instead of empty out the record? I am running access 97. Please help. Thanks.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure that is an actual record or possibly on a new record? Usually you don't need to issue a refresh command after a delete but I'm not sure how you have things set up. I'm guessing that once you've deleted the record, you are possibly seeing the new blank record to enter data on the form. Creating a delete button via the wizard isn't designed to clear out the fields on the form but is designed to actually delete the record. After it deletes the record, it usually goes to the next record. Try deleting the 1st record and see if you then see a blank record. You can also try issuing a me.requery command after you do the deletion to see if that makes a difference. Do you have an autonumber field in the table and on the form? It's always good to have an autonumber field and can help you diagnose if it's clearing out the fields or actually deleting the record.

    Also, a technique I use after I've done the delete wizard is add a little bit of code....
    Private Sub cmdDeleteRec_Click()
    On Error GoTo Err_cmdDeleteRec_Click
    Dim QI As Integer
    QI = MsgBox("Are you sure you want to delete this record?", vbYesNo)
    If QI = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 <- delete code created via the wizard
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 <- delete code created via the wizard
    DoCmd.SetWarnings True
    End If
    Exit_cmdDeleteRec_Click:
    Exit Sub
    Err_cmdDeleteRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteRec_Click
    End Sub

    Usually you don't need to issue a refresh command after a delete but I'm not sure how you have things set up. I'm guessing that once you've deleted the record, you are seeing the new blank record to enter data on the form. Creating a delete button via the wizard isn't designed to clear out the fields on the form but is designed to actually delete the record. After it deletes the record, it usually goes to the next record. Try deleting the 1st record and see if you then see a blank record.

    Also, to make sure you are deleting an actual data record, add this to the procedure...
    if not isnull(me!MyAutonumberField) then
    ....delete code
    end if

    And also, if you don't want to see the blank new record (and make users click an "Add New" button instead of going to the last "new" record), you can manipulate this with the form's allowadditions property and setting this to false. But then you also need to set it to true to add a new record.
    Ex: (Set the form's Allow Additions property to False and have an "Add New Record" button on the form with code like this...
    Private Sub cmdAddNewRecord_Click()
    Me.AllowAdditions = True
    DoCmd.GoToRecord , , acNewRec
    Me!DateEntered = Date
    Me.FirstName.SetFocus
    Me.AllowAdditions = False
    End Sub

    Note that you must set "some" value for a field (such as me!DateEntered = Date()) to generate the autonumber field/create the record before you can then set the AllowAdditions to false, otherwise it won't work.

    Hope that helps. If you still have problems, feel free to zip a copy of the mdb and upload it. It's much easier for us to help you that way and we can get you a quick specific answer to your question on why it's doing what it's doing.
    Last edited by pkstormy; 10-03-07 at 23:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2002
    Location
    HK
    Posts
    7
    You are right. This problem only happens when I delete the last record. I am struggling to fix it. Thanks.

  4. #4
    Join Date
    Dec 2002
    Location
    HK
    Posts
    7
    I have worked around the problem. By adding the following code to load form event:

    Me.AllowAdditions = False

    and set it to right for unload form event. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •