Copy text box to table.

[dbazzelle]

Hello,

I have a form with 20 text boxes on it. They are all unbound boxes that I fill in with vba code. What I want to do is take textbox1, textbox2, current user name, amd the date/time and add them to a table as a new record. Then take textbox3, textbox4, and the other information and add that as a new record to the table. And doing the same with the rest of the textboxes in pairs. The table will be set up with a autonumber for primary key.

Thank you,
David Bazzelle

 

[Norie]

Try somethin like this:

Private Sub Command1_Click()
Dim rst As DAO.Recordset
Dim db As Database
Dim I As Integer

Dim ctrl As Control
Dim txt1, txt2
Set db = CurrentDb

Set rst = db.OpenRecordset("Table1")

For I = 1 To 20 Step 2

txt1 = "Text" & I

txt2 = "Text" & (I + 1)

With rst
    .AddNew
    !DateStamp = Now()
    !UserName = "User"
    !Item1 = Me.Controls(txt1)
    !Item2 = Me.Controls(txt2)
    .Update
End With

Next I

End Sub

This assumes that Table1 exists with fields ID, DateStamp, UserNAme, Item1 and Item2.

 

[dbazzelle]

Thank you, that was just about what I needed. Can you help with one more thing. When one of the text boxes is empty I would like it to goto the next one. Thank you for the help.

David Bazzelle

 

[Norie]

I'm not quite sure what you mean.

Do you mean that if TextBox1 is empty then put the value of TextBox2 into field Item1? And so on...

 

[dbazzelle]

If box1 had nothing in it, then go to box3.. Or more like if box7 has nothing in it goto box9.

David Bazzelle

 

[Norie]

What about this:

Private Sub Command1_Click()
Dim rst As DAO.Recordset
Dim db As Database
Dim I As Integer

Dim ctrl As Control
Dim txt1, txt2
Set db = CurrentDb

Set rst = db.OpenRecordset("Table1")

For I = 1 To 20 Step 2

txt1 = "Text" & I

txt2 = "Text" & (I + 1)
If Me.Controls(txt1) <> "" Then

With rst
    .AddNew
    !DateStamp = Now()
    !UserName = "User"
    !Item1 = Me.Controls(txt1)
    !Item2 = Me.Controls(txt2)
    .Update
End With
End If
Next I

End Sub

 

[dbazzelle]

Thank you, that looks like what I needed. I will let you know tomorrow if it works for me. Looks like it should though. Thanks again!

David Bazzelle