[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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

  • 3
  • 2
  • 2
  • +2
2 Solutions
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
Jeffrey CoachmanCommented:
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?

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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 CoachmanCommented:
So no explanation of why you need to duplicate existing data...?
Jeff -
I think he is working with an unbound form...
Jeffrey CoachmanCommented:
oh ok...

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.

FaithDanAuthor Commented:
Perefect.. thats what I needed.

Thank you very much for your help


Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now