Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not saving a record - VBA (2002 SP2?)

    As usual - a dumb question <img src=/S/grin.gif border=0 alt=grin width=15 height=15> that I'm hoping has an easy answer.

    I have a main form which has an 'add new qualification' button which executes my form using
    DoCmd.OpenForm "fmaddqualification", acNormal, , , acFormAdd

    I have a click event on this form which attempts to do some integrity checks and discard the field changes for the record update. Sadly, it seems to add a record even where I've detected it is a duplicate.
    Any suggestions on the correct approach?

    Private Sub Save_Click()
    Dim Qual
    Dim I As Long
    If IsNull(Me.Institutions.Value) Or _
    IsNull(Me.QualificationDescription.Value) Or _
    IsNull(Me.QualificationTypeID.Value) Then
    MsgBox "All fields are required to be entered"
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acDelete, , acMenuVer70
    DoCmd.Close acForm, Me.Name, acSaveNo
    Exit Sub
    End If
    For I = 0 To Forms!fmQualificationMain.Qualifications.ListCount - 1
    Qual = Forms!fmQualificationMain.Qualifications.ItemData( I)
    If Qual = Me.QualificationDescription.Value Then
    MsgBox Me.QualificationDescription & " already exists", , "Qualification not added"
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acDelete, , acMenuVer70 ' <----- <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> ---- this doesn't seem to work ---- nor anything else I try
    DoCmd.Close acForm, Me.Name, acSaveNo
    Exit Sub
    End If
    Next I
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close acForm, Me.Name, acSaveNo

    Exit_Save_Click:
    Exit Sub

    Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not saving a record - VBA (2002 SP2?)

    Try replacing the delete instruction with Me.Undo

    BTW, you can use

    RunCommand acCmdDeleteRecord

    instead of the antiquated Access 95 (=7.0) code generated by the Wizard to delete a record.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not saving a record - VBA (2002 SP2?)

    Hans
    Thanks very much <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    That was one of the by-products of having Access generate code - I remember my early days with Excel code generation all too painfully <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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