How propogate field data from form to a table?

[SteveL]

I have a form which is not bound to a table. The form
contains several calculated fields. I want to write code
to make the calculated values of each field write over to
a specific unbound table at the click of a button and
don't know where to begin.

Can anyone help? Even one example of a field to be
written to an unbound table field would be helpful.

Thanks.

 

[Nikos Yannacopoulos]

Sub Form_To_Table_Sample()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("TargetTable")

rst.AddNew
rst.Fields("Fieldname") = Forms!MyForm.DataEntryField
rst.Update
rst.Close

End Sub

Nikos

 

[Paul Falla]

Dear Steve

I have never had to do the sort of thing that you are
looking achieve before, so my reply will be a "best
guess", but should hopefully work.

Create a new select query (obviously not based on any
tables)
In the blank Query Field name type NewFieldName:Forms!
[FormName]![FormFieldName]. Once you have done this you
can change the select query to a make table query. When
you execute the query, you will be prompted to provide a
name for the new table. You don't make it clear whether
you want to use this table to store the records each time
you run the form or if you want a new table every time.
Should you want a new table just the once, you may be
better off creating a table and using the method outlined
above to run an append query rather than a make table
query.( you will also need some type of unique identifyer
if you wish to report etc on the data captured)

Once you are happy with how you are going to get the form
and table to interact, it is a relatively simple task to
add a button to your form and build a macro attached to it
which will invoke the query and send the data to your
table.

I am sorry if this has been too simplistic, but you do not
mention your level of competency. I would be curious to
know how you get on, please post back if you need any
further assistance.

Kind regards

Paul J. Falla