Link to home
Start Free TrialLog in
Avatar of FaithDan
FaithDan

asked on

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
Avatar of sshah254
sshah254

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
SOLUTION
Avatar of Sheils
Sheils
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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!
:-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
Avatar of FaithDan

ASKER


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

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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perefect.. thats what I needed.

Thank you very much for your help

Dan