How to get form fields into a new record in table

Posted on 2011-10-19
Last Modified: 2012-05-12
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

Question by:FaithDan
    LVL 9

    Expert Comment

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

    LVL 16

    Assisted Solution

    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
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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?

    LVL 1

    Author Comment


    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

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    So no explanation of why you need to duplicate existing data...?
    LVL 61

    Expert Comment

    Jeff -
    I think he is working with an unbound form...
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    oh ok...

    LVL 61

    Accepted Solution

    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.

    LVL 1

    Author Closing Comment

    Perefect.. thats what I needed.

    Thank you very much for your help


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now