How get form data to write to an unbound 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.

 

[tina]

try this

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset("TableName",
dbOpenDynaset)
Rst.AddNew
Rst!FieldNameA = Me!ControlA
Rst!FieldNameB = Me!ControlB
Rst!FieldNameC = Me!ControlC
Rst.Update

Rst.CLOSE
Set Rst = Nothing

if you're working in Access 2000 (or newer, possibly), you
will need to set a library reference to DAO if you haven't
done so already.

hth

 

[Greg Kraushaar]

Soabox On
Why save calculated fields? You can always calculate them again laterTo answer your question...
dim db as database
dim rstTarget as recordset
set db = currentdb
set rstTarget = db.OpenRecordset("MyTable")
with rstTarget
.AddNew
!MyCalcData = me.txtMyField
.Update
end With
rstTarget.Close
set rst = Nothing
set db = Nothing

 

[SteveL]

-----Original Message-----
Why save calculated fields? You can always calculate them again later
To answer your question...
dim db as database
dim rstTarget as recordset
set db = currentdb
set rstTarget = db.OpenRecordset("MyTable")
with rstTarget
.AddNew
!MyCalcData = me.txtMyField
.Update
end With
rstTarget.Close
set rst = Nothing
set db = Nothing



.

Greg,

Thanks for the reply. I'm getting an error when I try to
execute this...

Compile Error: User-defined type not defined. Seems to
happen on the "dim db as database" line of code.

Any thoughts?

 

[SteveL]

-----Original Message-----
try this

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset("TableName",
dbOpenDynaset)
Rst.AddNew
Rst!FieldNameA = Me!ControlA
Rst!FieldNameB = Me!ControlB
Rst!FieldNameC = Me!ControlC
Rst.Update

Rst.CLOSE
Set Rst = Nothing

if you're working in Access 2000 (or newer, possibly), you
will need to set a library reference to DAO if you haven't
done so already.

hth


.

Tina, Thanks for the reply. But I'm getting an error
when I run the code...

Compile Error: User-defined type not defined.

Do you know how I fix this? Seems to be stuck on the
line that reads "Dim Rst As DAO.Recordset"

--Steve

 

[tina]

yes, you need to set the reference i mentioned earlier.

1. open your form in design view, then open the VBA window.
2. click on Tools, References.
3. scroll down until you see the Microsoft DAO 3.6 Object
Library, and check the box next to it.
4. click OK.
5. try to compile your code. if you get an error on the
same line again:
6. click Tools, References again.
7. click on the DAO line (not the checkbox) to highlight
it.
7. click on the Priority button with the "up arrow", until
it is grayed out.
8. click OK, and try to compile your code again.