input form fields into table ms access vba

I have an Access form with several data stored in Labels.  I would like to input the data contained within these labels into a target table called "target_table" within the same acccess dbase.  All the data will go into the same column, but in different rows.  I was wondering how to go about doing this in VBA, to kick off a function when the user selects a button on the form?

Who is Participating?
Chi Is CurrentConnect With a Mentor Commented:
Vishalarya ~

"Labels", do you mean text boxes on the form?


1) Setting the record source for the form to the destination table.
2) Use "text boxes" instead of labels on your form.
3) Set the control source for each text box on the form to the corresponding field on your table.

vishalaryaAuthor Commented:
No labels are fine since it's simple text data.  Actually can't I just use DoCmd.RunSQL query and reference my caption for the field.  As I'm not too familiar with SQL, does anyone know the code.  For example:

Form name: vtest
caption name: label47

label47 contains text = "abcd"

I want to insert label47's caption into table "target_table".  What would be the INSERT INTO statement for doing so?

Chi Is CurrentCommented:
If you are using a form that is not bound or cannot be bound, you can do this by placing the data in a recordset that updates to your destination table.

dds110 provides this example on solution:

Sub YourButton_Click()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Table_To_Be_Updated")

With rst
  .Fields("Table_Field_Name1") = Me.ControlName
  .Fields("Table_Field_Name2") = Me.ControlName2
End With


Set rst = Nothing
Set db = Nothing

End Sub

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Chi Is CurrentCommented:
For this to work you will need to store the data in 'text box' controls on your form.  I don't think you'll be able to reference 'labels'.
vishalaryaAuthor Commented:
no jacob you are wrong.

THe following code works:

DoCmd.runSQL ("UPDATE target_table set col2 = '" & label47.caption & "' where col1 = 'one'")

no need for text boxes.
Chi Is CurrentCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.