Table to Table Update With VBA

M

Mike W

I am trying to update a table with values from another table, based upon an
entry on a form. I would appreciate suggestions on this.

Thanks
Mike
 
A

Arvin Meyer [MVP]

On the click event of a command button, or in the after update event of the
form, write a piece of code like (aircode):

DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "INSERT INTO Table2 SELECT * FROM Table1 Where ID=" &
Me.txtRecordID

txtRecordID is the ID value of the key
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
T

tina

off-hand, and in general, i can think of two ways to do it. 1) in the form's
module, open a Recordset of the table you want to update, and then add or
edit the record(s) in the recordset. if the table that has the values is not
bound to the form, then you'll need to open another Recordset first, to
access that data. 2) create a Select query to pull the data you want to
update into the other table. then turn the query into an Append query. if
you want to edit data in the receiving table, rather than appending new
records, you'll need to go about it differently.

if you want specific, on-target information, then post specific information
about your tables, and the form, and exactly what you're trying to do - add
records? or update records?

hth
 
M

Mike W

I have created a table for storing customer return data, and a simple form
that the customer service manager will use to enter roughly four pieces of
information to open a work request. To start with he will click a command
button to open a new record and then fill in specific information. The text
windows of this form are bound to the data table. One text window is for the
product type and based on the type, I want to extract parameters from a
different table and update the current record with this data before closing
out the data entry session. This updated data is will be used to populate
the repair worksheet form.

Thanks,
Mike
 
T

tina

One text window is for the
product type and based on the type, I want to extract parameters from a
different table and update the current record with this data before closing
out the data entry session.
Click to expand...

if the parameters are in the same table as the product type that you're
referring to, and if the product type *control* (that's the correct name for
a "text window" in a form) is a combo box whose RowSource is based on the
product types table, then you can simply add additional columns to the combo
box to pull those parameters, and then set the value of the controls in your
form to those parameters. in the combo box's AfterUpdate event, use a
SetValue action in a macro or VBA, something along the lines of

Me!MyTextboxControlName = Me!ComboboxControlName.Column(n)

(n) refers to the index number of the column. since combobox column index is
zero-based, the first column (in left-to-right order) is 0, the second
column is 1, the third column is 2, etc.

hth
 
Top