Copy form datasheet to table

[Brian J]

Currently there is a command button that brings up a form (datasheet view)
and populates it. I would like to add to this code the ability to copy this
form's data into a table (tblPE_Temp). Ideally the new code would delete the
current data in the table and re-populate it with the form's refreshed data.
Any suggestions on how to do this?

 

[Stefan Hoffmann]

hi Brian,

Currently there is a command button that brings up a form (datasheet view)
and populates it. I would like to add to this code the ability to copy this
form's data into a table (tblPE_Temp). Ideally the new code would delete the
current data in the table and re-populate it with the form's refreshed data.

E.g.

Dim sql As String

sql = "DELETE FROM tblPE_Temp;"
CurrentDb.Execute sql, dbFailOnError
sql = "INSERT INTO tblPE_Temp (fieldList) " & _
"SELECT fieldList " & Me.RecordSource & ";"
CurrentDb.Execute sql, dbFailOnError

when your forms record source is a simple table. Otherwise you need to
craft the insert sql yourself.


mfG
--> stefan <--

 

[Brian J]

Thank you Stefan. This is exactly what I needed!

 

[Brian J]

Stephen,
Upon tweaking your code to match my modified table names I see that
something is erroring out. I get a popup that states "Object Variable or
With block variable not set". Since my datasheet and table have the same
structure I am using an "*" for field name. Could this be a problem? Here is
my tweaked SQL:

Dim sql As String

sql = "DELETE FROM tblPhysiciansEffortTempMT;"
CurrentDb.Execute sql, dbFailOnError
sql = "INSERT INTO tblPhysiciansEffortTempMT (*) " & _
"SELECT (*) " & Me.RecordSource & ";"
CurrentDb.Execute sql, dbFailOnError

 

[Stefan Hoffmann]

hi Brian,

Upon tweaking your code to match my modified table names I see that
something is erroring out. I get a popup that states "Object Variable or
With block variable not set". Since my datasheet and table have the same
structure I am using an "*" for field name. Could this be a problem? Here is
my tweaked SQL:

Dim sql As String

sql = "INSERT INTO tblPhysiciansEffortTempMT (*) "& _
"SELECT (*) "& Me.RecordSource& ";"
CurrentDb.Execute sql, dbFailOnError

If it is the same field structure, same field names in the same order,
then you can use the asterisk, e.g.

sql = "INSERT INTO tblPhysiciansEffortTempMT "& _
"SELECT * FROM " & Me.RecordSource & ";"

mfG
--> stefan <--