VBA -> DoCmd.Close is possessed!

[Dougal]

right, this piece of code:

DoCmd.Close acForm, Me.Name, acSaveNo

seems to sporadicly create a new record in my table


Not sure what I'm doing wrong but here is the rest of the code:

Option Compare Database

Private Sub cmdCancel_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
    DoCmd.OpenForm modPubVar.formPrev
End Sub

Private Sub cmdDelete_Click()

    If MsgBox("Are you sure you wish to delete this contact? NON REVERSABLE", vbYesNo, "Confirm") = vbNo Then Exit Sub
    
    Dim db As DAO.Database
    Dim rcd As DAO.Recordset
    Set db = CurrentDb()
    Set rcd = db.OpenRecordset("Contacts", dbOpenDynaset)
    rcd.MoveLast
    rcd.MoveFirst
    rcd.FindFirst ("SchoolClub = '" & modPubVar.schoolName & "'")
    
    If MsgBox("Last chance to not delete the contact. Delete?", vbYesNo, "Final Confirmation") = vbNo Then Exit Sub
    rcd.Delete
    rcd.Update
    rcd.Close
    MsgBox "Contact deleted"
    
End Sub

Private Sub cmdPrint_Click()

    Dim strSQL As String
    strSQL = "SELECT Contacts.ID, Contacts.SchoolClub, Contacts.Address1, Contacts.Address2, Contacts.Address3, Contacts.Address4, Contacts.Postcode, Contacts.Status, Contacts.CoOrdinator, Contacts.Add1, Contacts.add2, Contacts.add3, Contacts.add4, Contacts.postcode2, Contacts.hometel, Contacts.schooltel, Contacts.mobile, Contacts.email, Contacts.notes FROM Contacts WHERE (((Contacts.ID) Like " & [modPubVar].[ContactID] & "));"

    If (modPubFunc.setSQLQuery("qryContacts", strSQL)) Then
        MsgBox "Failed"
        Exit Sub
    End If
    
    DoCmd.OpenReport ("Contacts")
    
    strSQL = "SELECT Contacts.ID, Contacts.SchoolClub, Contacts.Address1, Contacts.Address2, Contacts.Address3, Contacts.Address4, Contacts.Postcode, Contacts.Status, Contacts.CoOrdinator, Contacts.Add1, Contacts.add2, Contacts.add3, Contacts.add4, Contacts.postcode2, Contacts.hometel, Contacts.schooltel, Contacts.mobile, Contacts.email, Contacts.notes FROM Contacts WHERE (((Contacts.ID) Like '*'));"

    If (modPubFunc.setSQLQuery("qryContacts", strSQL)) Then
        MsgBox "Failed"
        Exit Sub
    End If
    
    
End Sub

Private Sub cmdSave_Click()

    If MsgBox("Are you sure you wish to save changes?", vbYesNo, "Confirm") = vbNo Then Exit Sub
    
    Dim db As DAO.Database
    Dim rcd As DAO.Recordset
    Set db = CurrentDb()
    Set rcd = db.OpenRecordset("Contacts", dbOpenDynaset)
    rcd.MoveLast
    rcd.MoveFirst
    rcd.FindFirst ("SchoolClub = '" & modPubVar.schoolName & "'")
        
    rcd!SchoolClub = txtSchoolClub
    rcd![Address1] = txtAddress1
    rcd![Address2] = txtAddress2
    rcd![Address3] = txtAddress3
    rcd![Address4] = txtAddress4
    rcd![Postcode] = txtPostCode1
    rcd!schooltel = txtSchTel
    rcd!CoOrdinator = txtCoOrdinator
    rcd![Add1] = txtadd1
    rcd![add2] = txtadd2
    rcd![add3] = txtadd3
    rcd![add4] = txtadd4
    rcd!postcode2 = txtPostCode2
    rcd![hometel] = txthometel
    rcd!mobile = txtMobile
    rcd!email = txtEmail
    rcd!Notes = txtNotes
    rcd.Close
    MsgBox "Changes saved"
End Sub

'Private Sub Form_BeforeUpdate(Cancel As Integer)
'    Form_Load
'End Sub

Private Sub Form_Load()

    
        Dim db As DAO.Database
        Dim rcd As DAO.Recordset
        Set db = CurrentDb()
        Set rcd = db.OpenRecordset("Contacts", dbOpenDynaset)
        rcd.MoveLast
        rcd.MoveFirst
        If modPubVar.formPrev = "frmEdit" Then
            rcd.FindFirst ("SchoolClub Like '" & modPubVar.schoolName & "*'")
            If rcd.NoMatch = True Then
                If (MsgBox("No contact found, would you like to add one?", vbYesNo, "No contact") = vbYes) Then
                    modPubVar.formPrev = Me.Name
                    DoCmd.Close acForm, Me.Name, acSaveNo
                    DoCmd.OpenForm "frmNewContact"
                    Exit Sub
                Else
                DoCmd.Close acForm, Me.Name, acSaveNo
                DoCmd.OpenForm modPubVar.formPrev
                Exit Sub
                End If
            End If
        End If
        If modPubVar.formPrev = "frmListContacts" Then
            rcd.FindFirst ("ID = " & modPubVar.ContactID & "")
            If rcd.NoMatch = True Then
                If (MsgBox("No contact found, would you like to add one?", vbYesNo, "No contact") = vbYes) Then
                    modPubVar.formPrev = Me.Name
                    DoCmd.Close acForm, Me.Name, acSaveNo
                    DoCmd.OpenForm "frmNewContact"
                    Exit Sub
                End If
            End If
        End If
        
        txtSchoolClub = rcd!SchoolClub
        txtAddress1 = rcd![Address1]
        txtAddress2 = rcd![Address2]
        txtAddress3 = rcd![Address3]
        txtAddress4 = rcd![Address4]
        txtPostCode1 = rcd![Postcode]
        txtSchTel = rcd!schooltel
        txtCoOrdinator = rcd!CoOrdinator
        txtadd1 = rcd![Add1]
        txtadd2 = rcd![add2]
        txtadd3 = rcd![add3]
        txtadd4 = rcd![add4]
        txtPostCode2 = rcd!postcode2
        txthometel = rcd![hometel]
        txtMobile = rcd!mobile
        txtEmail = rcd!email
        txtNotes = rcd!Notes
        modPubVar.ContactID = rcd![ID]
        rcd.Close
End Sub

[GizmoStu]

VBA behaving in a sporadic and unpredictable fashion? Shurely shome mishtake?

[Dougal]

Anyone? Please?

[herulach]

Does it always create a new record? Is this in access? Cos if it is then youre being far too crazy about it, since youre not doing any validation theres no need to use a form and insert all the data yourself. The only thing i can think of is if your sat there on a blank record when you close the form. Although acsaveno should take care of that. Try putting a watch in with the table open, see if its actually that statement, it could be something entirely differnt.

[Dougal]

i checked it, put a break on that line.

Nothing was added to the database.

Put a break on the next line, for the DoCmd.OpenForm and the record has been added.

So its definately the DoCmd.Close that is adding the record.

The form loads a particular record for editing and verifying and printing.

Its for council people who aren't the best but just want soemthing simple.

[herulach]

i checked it, put a break on that line.

Nothing was added to the database.

Put a break on the next line, for the DoCmd.OpenForm and the record has been added.

So its definately the DoCmd.Close that is adding the record.

The form loads a particular record for editing and verifying and printing.

Its for council people who aren't the best but just want soemthing simple.

Whats it putting as primary key? Please dont say you have it set on autonumber?

[Dougal]

I won't then.


sorry i am not a vba or access fan, its just my summer job.

[herulach]

I won't then.


sorry i am not a vba or access fan, its just my summer job.

I have issues with autonumbers, namely all the type mismatchs it throws up when you set up relationships. If you dont find a solution set your primary key to integer and use the domain functions to automagically assign a new id. i belive its DMAX(Field)

[Dougal]

I'll have a look at work tomorrow.

I may just have to copy the code off, delete the form and remake it.

Its done errors when buttons didn't work untill i deleted the function headers (which it had made) and pasted the code back.

[Dougal]

Just gone through every line of code.

The problem seems to be that when there is data read in through the Form_load() sub if there is any data in the text boxes on the form it decides to write them as a new record.

How do I open them in read only?

[herulach]

Just gone through every line of code.

The problem seems to be that when there is data read in through the Form_load() sub if there is any data in the text boxes on the form it decides to write them as a new record.

How do I open them in read only?

Open The dataset read only? Or the text boxes? The text boxes are just a simple property, and theres a parameter to pass to the record set, but i cant recall what it is. You can also set the entire form read only, but ive had funny issues doing that, buttons in the main bit (not header/footer) wouldnt work

[Dougal]

I found a (crap) way round the problem, but at least that now works.

When the index loads up (normally every 10 or so forms) it purges thetable of empty records :S

Now I have to know why when the index loads first time (via Autoexec) its fine, but when its opened up from another form it is off the screen :@

[herulach]

I found a (crap) way round the problem, but at least that now works.

When the index loads up (normally every 10 or so forms) it purges thetable of empty records :S

Now I have to know why when the index loads first time (via Autoexec) its fine, but when its opened up from another form it is off the screen :@

Theres a property on the form somewhere, called autocenter, does exactly what it says on the tin, I belive you can also specify location from docmd, but i might be wrong.

[Dougal]

It didn't like the auto center and auto resize both being on :s

Really have an excuse to hate, soz, LOATHE, VBA

[herulach]

It didn't like the auto center and auto resize both being on :s

Really have an excuse to hate, soz, LOATHE, VBA

Yep, as does everyone else whos ever tried to do anything more than use the access wizards.