MS Access DAO连接在退出时放弃更改 [英] MS Access DAO Connection Discard Changes On Exit

问题描述

因此，我具有此访问表单，在其中使用此VBA代码以及与MySQL数据库的DAO连接.一切都很好，但是如果用户在不单击保存"按钮的情况下关闭表单，则无论如何都会保存新记录.

So I have this Access form where I use this VBA code with a DAO connection to a MySQL database. Everything works great but if the user closes the form without clicking save button the new record is saved anyway.

所以我要寻找的是，是否有某种on on close事件可以阻止将新记录保存到数据库中?

So what I'm looking for is if there's any way the on the on close event I can stop the new record being saved to the database?

我拥有的代码，

Private Sub Form_Load()
    'Set Form Recordset
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim SQL As String

    Set db = OpenDatabase("", False, False, Globales.ConnString)
    SQL = "SELECT tbl6Suplidores.ID, tbl6Suplidores.NombreSuplidor, tbl6Suplidores.NumeroComerciante, tbl6Suplidores.DescripcionBienes, tbl6Suplidores.NombreContacto, tbl6Suplidores.NumeroTelefono, tbl6Suplidores.Email " _
        & "FROM tbl6Suplidores;"

    Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbAppendOnly)
    Set Me.Form.Recordset = rs
End Sub

我在想，既然我使用了dbAppendOnly，就不会让我只在关闭事件时删除当前记录吗?

I'm thinking that since I used the dbAppendOnly it won't let me just delete current record on close event?

欢迎任何想法！ 谢谢！

Any ideas welcome! Thanks!

推荐答案

考虑另一种方法，您可以让用户输入一个未绑定的表单，然后单击保存"按钮以从填充字段中更新MySQL表.不保存就退出表格将无济于事.这也是一种更主动的方法，因为它允许您在运行保存操作之前检查验证和其他逻辑.

Consider a different approach where you have users enter an unbound form and click a save button to update the MySQL table from populated fields. Exiting form without save will do nothing. This is also a more proactive approach as it allows you to check validation and other logic prior to running save action.

以下使用

Below uses a parameterized append query with QueryDefs. Also, ID is assumed to be an autonumber and hence left out of query. Sub should be placed behind the OnClick trigger event of save button.

Private Sub SaveButton_Click()
    Dim db As DAO.Database, qdef As DAO.QueryDef
    Dim SQL As String

    Set db = OpenDatabase("", False, False, Globales.ConnString)

    ' PREPARED STATEMENT WITH NAMED PARAMETERS
    SQL = "PARAMETERS ns_param VARCHAR(255), ncom_param INTEGER, db_param VARCHAR(255), " _
          & "         ncnt_param INTEGER, nt_param INTEGER, e_param VARCHAR(255);" _
          & " INSERT INTO (NombreSuplidor, NumeroComerciante, DescripcionBienes, " _
          & "              NombreContacto, NumeroTelefono, Email) " _
          & " VALUES (ns_param, ncom_param, db_param, ncnt_param, nt_param, e_param);"

    ' INITIALIZE QUERYDEF
    Set qdef = db.CreateQueryDef("", SQL)

    ' BIND PARAMETERS TO FORM FIELDS
    qdef!ns_param = Forms!MyFormName!NombreSuplidor
    qdef!ncom_param = Forms!MyFormName!NumeroComerciante
    qdef!db_param = Forms!MyFormName!DescripcionBienes
    qdef!ncnt_param = Forms!MyFormName!NombreContacto
    qdef!nt_biens_param = Forms!MyFormName!NumeroTelefono
    qdef!e_param = Forms!MyFormName!Email

    ' RUN ACTION QUERY
    qdef.Execute dbFailOnError

    Set qdef = Nothing    
End Sub

这篇关于MS Access DAO连接在退出时放弃更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！