Prev: rs.RecordCount
Next: record count issue
From: Brian J on 20 Apr 2010 08:41
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?
From: Stefan Hoffmann on 20 Apr 2010 09:19
hi Brian,

On 20.04.2010 14:41, Brian J wrote:
> 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 <--
From: Brian J on 20 Apr 2010 10:06
Thank you Stefan. This is exactly what I needed!

"Stefan Hoffmann" wrote:

> hi Brian,
>
> On 20.04.2010 14:41, Brian J wrote:
> > 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 <--
> .
>
From: Brian J on 20 Apr 2010 10:50
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" wrote:

> hi Brian,
>
> On 20.04.2010 14:41, Brian J wrote:
> > 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 <--
> .
>
From: Stefan Hoffmann on 20 Apr 2010 10:53
hi Brian,

On 20.04.2010 16:50, Brian J wrote:
> 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 <--

 | 
Pages: 1
Prev: rs.RecordCount
Next: record count issue