Consulting

Results 1 to 3 of 3

Thread: Change data in two tables from form

  1. #1

    Change data in two tables from form

    Hello, I have a form that has no record source. On this form I want to change data from a text box txtChangeVersionNumber for two different tables

    tbl-version_fe_master \Field: fe_version_number
    tbl-fe_version \Field: fe_version_number

    How can I do this?

  2. #2
    I figured it out but is there a better way?

    Private Sub cmdSaveVersion_Click()
    'Required Reference:  Microsoft DAO 3.6 Object Library
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        
    'Check if there is a version number
        If IsNull(Me.txtChangeVersionNumber) Then
            MsgBox "A version number must be provided! ", vbCritical, "Entry Error"
            Exit Sub
        Else
            
    'Update table master version
            Set db = CurrentDb
            Set rs = db.OpenRecordset("tbl-version_fe_master", dbOpenDynaset)
            rs.Edit
            rs!fe_version_number = Me.txtChangeVersionNumber
            rs.Update
            rs.Close
            Set rs = Nothing
            
    'Update table version
            Set db = CurrentDb
            Set rs = db.OpenRecordset("tbl-fe_version", dbOpenDynaset)
            rs.Edit
            rs!fe_version_number = Me.txtChangeVersionNumber
            rs.Update
            rs.Close
            Set rs = Nothing
            
            Me.txtVersion_fe_master.Requery
            Me.txtFe_version.Requery
        End If
    End Sub

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,164
    Location
    How do you know which records to update in the 2 tables.
    I can't see in your code where you open the appropriate record.
    Why use an unbound form, why not a from based on a query with the fields from both tables and update one manually and the other by VBA in the after update event of the updated field?

Posting Permissions

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