Take the records selected in a form and put in a new table.

[Rjconrep]

I have a form with combo boxes that get their information from several different tables. What I need the form to do is: update a new table with the input that the user has selected on the form.
How is this done?

 

[rbowes]

Do all of the combo boxes and fields on the form get saved to the same table? Are these updates to an existing record or a brand new record?

Bob

 

[Rjconrep]

They are all unbound controls that will be saved to the same (new) data file.
I am thinking that I have done the whole form incorrectly.
What I need is a form that will track all newsletters for a client that we have.
There are several different departments that each newsletter will have to go through. I have combo boxes (that obtain there data from an existing data file) for each department process and once the process is selected I need the amount per piece to auto fill a text box. I need all the information to be saved in a new data file so that I can go back and pull information out at a later date.
I hope this helps you better in understanding what I need.

 

[rbowes]

Well, I'm not totally certain about how your form works, but I will give it a try.
I am assuming that each combo box has a text box that displays the calculated cost for the process. In your new table you should have fields that mimic the form. cboProcess1 = Process1; txtProcessCost1 = ProcessCost1...etc.

On Save, (after verifying that all of the required fields have values) put this:

Private Sub cmdSave_Click()
Dim rst as Adodb.Recordset
Dim cnn as adodb.Connection
dim strSQL as string


set cnn = currentproject.connection 'Assumes the database is part of the mdb
set rst = new adodb.recordset

strsql = "SELECT * FROM MyTable WHERE 1 = 2"
with rst
.Source = strsql
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.ActiveConnection
.Open
.Add
.fields("Process1&quot = me.cboProcess1.value
.Fields("txtProcessCost1&quot = me.ProcessCost1.value
.update
end with

set rst = nothing

end sub

--OR--

Private Sub cmdSave_Click()
Dim cnn as adodb.connection
Dim strsql as string

set cnn = currentproject.Connection

strsql = "INSERT INTO MyTable ( [Process1], [ProcessCost1] )"

strsql = strsql & " SELECT '" & cboProcess1 & "', '" & txtProcessCost1 & "'"

cnn.Execute strsql

end sub

Each does the same thing, but you will not get an error message should one occur using the latter method. Be sure not to forget the single quotes surrounding strings: SELECT '" & cboProcess1 & "'".

Does that help? Hope it does.

Bob