Hi Fellows...

I have a form that I created separately with 2 textboxes namely
 Surname and FirstName.

I then created a table exactly with the same fields.

Now I want to add a Save Records button to the form and the data that I input on the form should be sent straight to the table. I DON'T WANT TO USE AN AUTO FORM...

I tried the

With/

!.textbox = Surname
.update
End With/

But then in the Dim statements there is a compile error for the rs statement.

Please help....



TK, Novice VBA Programmer, RSA

Hi TK,

There are two ways to go: using SQL (preferred) or using DAO recordset object methods (probably slower in the long run).

Here's the SQL version. You just need a connection to execute the SQL on:

Private Sub cmdAdd_Click()

    Dim db As DAO.Database
    Dim strSQL As String

    If IsNull(Me!txtFirstName) Then
        MsgBox "First Name is a Required Entry.", 48
        Me!txtFirstName.SetFocus
        Exit Sub
    End If

    If IsNull(Me!txtSurName) Then
        MsgBox "Surname is a Required Entry.", 48
        Me!txtSurName.SetFocus
        Exit Sub
    End If

    Set db = CurrentDb

    strSQL = "INSERT INTO tblNames "
    strSQL = strSQL & "( FirstName, Surname )"
    strSQL = strSQL & " values ('"
    strSQL = strSQL & Me!txtFirstName & "','"
    strSQL = strSQL & Me!txtSurName & "'" & ");"

    ' MsgBox strSQL
    ' Debug.Print strSQL

    db.Execute strSQL

    MsgBox "Changes have been saved."

    Me!txtFirstName = ""
    Me!txtSurName = ""
    Me!txtFirstName.SetFocus

End Sub

Here's the DAO version. You need to instantiate a recordset object and call its methods (AddNew and Update):

Private Sub cmdAdd_Click()

    Dim db As DAO.Database
    Dim rstNames As DAO.Recordset
    Dim strSQL As String

    If IsNull(Me!txtFirstName) Then
        MsgBox "First Name is a Required Entry.", 48
        Me!txtFirstName.SetFocus
        Exit Sub
    End If

    If IsNull(Me!txtSurName) Then
        MsgBox "Surname is a Required Entry.", 48
        Me!txtSurName.SetFocus
        Exit Sub
    End If

    Set db = CurrentDb
    Set rstNames = db.OpenRecordset("tblNames", dbOpenDynaset)

    rstNames.AddNew
    rstNames("FirstName") = Me!txtFirstName
    rstNames("SurName") = Me!txtSurName
    rstNames.Update

    MsgBox "Changes have been saved."

    Me!txtFirstName = ""
    Me!txtSurName = ""
    Me!txtFirstName.SetFocus

End Sub

SQL will generally perform faster than equivalent operations using data access objects. Don't forget to set a reference to the DAO 3.x object library in your references dialog.

HTH,

Bob

You could also write your SQL string as:

strSQL = "INSERT INTO tblNames " & _
         "( FirstName, Surname )" & _
         " values ('" & _
         Me!txtFirstName & "','" & _
         Me!txtSurName & "'" & ");"

Faster yet.