asked on

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?

Thanks,
Vishalarya

ASKER CERTIFIED SOLUTION

Chi Is Current

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vishalarya

ASKER

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?

Thanks,
Vishalarya

Chi Is Current

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: https://www.experts-exchange.com/questions/20814041/Storing-data-on-Unbound-Form.html?sfQueryTermInfo=1+data+form+from+tabl+unbound

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

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

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

rst.Close
db.Close

Set rst = Nothing
Set db = Nothing

End Sub

Chi Is Current

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'.

vishalarya

ASKER

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 Current

Great!