Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010

    Question Unanswered: Save data on form to table?

    I have made my form with code to display data populate the form with data from a table but am not sure how to code a button to save the data I edit back to the table. This is my code so far:

    Option Explicit
    Option Compare Database
    Dim connection As New ADODB.connection
    Dim part As New ADODB.Recordset
    Private Sub Form_Load()
    Set connection = CurrentProject.connection
    part.Open "SELECT * FROM part ORDER BY partID", connection, _
    adOpenDynamic, adLockOptimistic
    End Sub
    Private Sub populateForm()
    If part.EOF Then
    ElseIf part.BOF Then
    End If
    Me.txtPartId = part.Fields("partId")
    Me.txtCost = part.Fields("cost")
    Me.txtDescription = part.Fields("description")
    Me.txtOnHand = part.Fields("onHand")
    Me.txtOnOrder = part.Fields("onOrder")
    Me.txtListPrice = part.Fields("listPrice")
    End Sub

    What would I need to add to my save button to save a record I edit on my form to a table?


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    As the recordset is dynamic (writable: "adOpenDynamic") you can simply write the values from the controls back using the recorset.Update method.

    Otherwise you can use a "dynamic query" (SQL string built in VBA) and have it executed:

    Dim strSQL as String
    strSQL = "UPDATE part SET ( partId, cost, ..., etc. ) VALUES ( " & Me.txtPartId.Value & ", " & Me.txtCost.Value & ", ...etc. )"
    including the proper delimiters (single-quotes (') when dealing with text data type, etc.) in the string, then execute it:
    CurrentDb.Execute strSQL
    If you're working with a server, you can also call a stored procedure to update the data.
    Have a nice day!

Posting Permissions

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