Special access usage 1

[infoscion]

Hi All:
I am working on an Access form. The form has multiple text boxes. At the bottom of the from is a add button that transfers the data onto a underlying table. I want the contents of each of the text boxes to be appended to the underlying table as a separate record at the click of the add button instead of the contents being populated as different fields of the same row.
Any ideas, suggestions or tactics is appreciated.
Regards,
Info

 

[crobg]

1. Don't bind the form to the table you want to append to.
2. Open the table you want to append as a recordset.
3. Loop through the text boxes and append each field.

Private Sub AppendClick_Click()
On Error GoTo Err_AppendClick_Click

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

strSQL = "Select * From tblTest"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For X = 0 To Me.Controls.Count - 1
    If Me.Controls(X).ControlType = acTextBox Then
        rs![Field1] = Me.Controls(X).Value
    End If
Next

rs = Nothing
db = Nothing


Exit_AppendClick_Click:
    Exit Sub

Err_AppendClick_Click:
    MsgBox Err.Description
    Resume Exit_AppendClick_Click
    
End Sub

 

[infoscion]

Hi:
Thank you for forwarding me the code. Just wondering, should it it be a SELECT statement or be an INSERT statement in the SQL code. Also, how does the statement
rs![Field1] = Me.Controls(X).Value, help us?
Regards,
Info

 

[crobg]

Sorry, for the sloppy code. SQL statement was just to open the table. Here's an update:

Private Sub AppendClick_Click()
On Error GoTo Err_AppendClick_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest")

For X = 0 To Me.Controls.Count - 1
    If Me.Controls(X).ControlType = acTextBox Then 'Only add values from a text box
        rs.AddNew 'Starts a new record
        rs![Field1] = Me.Controls(X).Value 'Updates the field with the text box value in the new record
        rs.Update 'Adds the new record to the table
    End If
Next

rs.Close
db.Close


Exit_AppendClick_Click:
    Exit Sub

Err_AppendClick_Click:
    MsgBox Err.Description
    Resume Exit_AppendClick_Click
    
End Sub

I have changed to just opening the table itself. No need to have an INSERT statement. The code in the IF statement handles the updating of the table.

You will want to read the help file on the openrecordset command to make sure that you open the table correctly for your enviroment. (ie multi-users)