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

Dan
LVL 1
FaithDanAsked:
Who is Participating?
 
mbizupCommented:
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.

0
 
sshah254Commented:
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 ...

SS
0
 
SheilsCommented:
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
0
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!
:-O

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?

JeffCoachman
0
 
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 & "')"

[Cost_Est_Summary].Requery




End Sub

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

0
 
FaithDanAuthor Commented:
Perefect.. thats what I needed.

Thank you very much for your help

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