How to get form fields into a new record in table

I have a form that I have a button

I want this button to perform an action.
I want this button to take fields from the form and place it in a table as a new record.
I assume to use the code builder
I have a table called "Cost_Est_Summary" that I want this data to go into
The rows in the table are "Road_Street", "Repair_Tech", and "Cost"
I want the button to create a new record in this table adding Text26 to the "Road_Street"
Text34 concatenate with text86 to go to the "Repair_Tech" field
and Text73 to the "Cost" field

Thanks for your help with this

Who is Participating?
Assuming you are working with an unbound form (ie: it's recordsource property is blank), or if you are writing to a table that is not related to the form's recordsource, the following will do the trick:

dim strSQL as string
strSQL ="Insert Into Cost_Est_Summary(Road_Street,Repair_Technique,cost)  "
strSQL = strSQL & "Values('" & Me.Text26 & "','" & Me.Text34 & Me.text86 & "'," & Me.Text73 & ")"
Currentdb.execute strSQL, dbfailonerror

Open in new window

The above also assumes the following:
1 - Road_Street and Repair_technique are text
2 - Cost is numeric

If those assumptions are not correct, the syntax would be a little different.  Text values need to be embedded in quotes, numeric values use no delimiters.

Also if you are working with a bound form and you are inserting this data into the form's recordsource table, you will be creating duplicate records as Jeff mentioned earlier.

You can write VB code for the button's 'onclick' event.

docmd.runsql "insert into Cost_Est_Summary values ('" & Text26.value & "', '" & Text34.value & " " & text86.value & "', '" & Text73.value & "');"

I am writing this off the top of my head, so there may be some changes needed ... (e.g. Text73.value may be Text73.text).  Also, you may need to create a db connection (something like docmd.setdatabase = currentdb).

But you get the idea ...

Try this:

-Right click on the button whenthe form is opened in design view
-Select properties
-In the property dialog box select the event tab
-double click on the button next to the onclick, This will open the vb editor
-Insert the following code

Private Sub Onlick_ButtonName

if Not Is Null Me.Text26  Then

Currentdb.Execute "Insert Into TableName(Road_Street) Values('" & Me.Text26  & "')"

End If

End Sub
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
If the form is "Bound", then these values are already stored in a table.

Storing these values in another table will create duplicate data.
This can cause all sorts of issues...
For example;
1. What is you plan for updating the second table if a record in the first table is Deleted, or Changed...?
2. What happens if a user forgets to click the button...?, ...or worse, clicks the button twice by accident!

Typically you can simulate your "Other table" with a query, and no need for storing duplicate data is needed. (or worrying about missed or duplicate button clicks)

So, ..can you first take a step back and clearly explain your ultimate goal here?

FaithDanAuthor Commented:

I have the following so far.... the below creates 3 seperate rows of data.. how can I combine them so that only 1 row of data is created?

Private Sub Command85_Click()

CurrentDb.Execute "Insert Into Cost_Est_Summary(Road_Street) Values('" & Me.Text26 & "')"

CurrentDb.Execute "Insert Into Cost_Est_Summary(Repair_Technique) Values('" & Me.Text34 & "')"

CurrentDb.Execute "Insert Into Cost_Est_Summary(cost) Values('" & Me.Text73 & "')"


End Sub

Jeffrey CoachmanMIS LiasonCommented:
So no explanation of why you need to duplicate existing data...?
Jeff -
I think he is working with an unbound form...
Jeffrey CoachmanMIS LiasonCommented:
oh ok...

FaithDanAuthor Commented:
Perefect.. thats what I needed.

Thank you very much for your help

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.