Append current record to table

[jim_s via AccessMonster.com]

Hi all,

I must have scoured this forum for a day looking for some alternative answer
to this question, and googling the issue doesn't seem to help me much either.

I have two tables, 'Search Result' and 'For Export'. When a user wants to
amend a record in the Search Result form/table, the form's text boxes all
unlock for editing. Once this is done, the user has two options - Save
Changes or Cancel. I think you'll have caught on by this stage that the
'Cancel' button works.

When 'Save Changes' is hit, the button needs to append the current record to
a new table, 'For Export'

The closest I've come is through some VB code posted on the forums:

Const myTarget As String = "For Export"
Const myPrimaryKey = "RTL_DAS_Ref"

Dim pk As Variant
Dim strSQL As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
pk = rs(RTL_DAS_Ref).Value
strSQL = "INSERT INTO " & myTarget & " SELECT * FROM " & rs.Name & " WHERE "
& myPrimaryKey & "=" & pk
Set rs = Nothing

CurrentDb.Execute (strSQL), dbFailOnError

Whilst it all seems logical to get the SQL code to match the recordsetclone
PK to the current record PK, I'm getting a syntax error in the SQL statement
for the INSERT INTO command. It looks okay to me! Any pointers? Please don't
tell me it's something blazingly obvious that I've not spotted!

 

[Tom van Stiphout]

On Thu, 14 Jan 2010 13:35:07 GMT, "jim_s via AccessMonster.com"

Set a breakpoint, step over the "strSql = " line, and write in the
Immediate window:
? strSql
Paste the result in a new query in sql view, and switch to design
view. The parser will bark at you.

Rather than rs.name you'll likely want to use Me.RecordSource, and all
the recordset-related code can be omitted.
The PK value can be retrieved from the form as well: Me.RTL_DAS_Ref
(or if you wish: Me.Controls(myPrimaryKey))

-Tom.
Microsoft Access MVP

 

[John Spencer]

Since For Export contains spaces you need to refer to it with brackets around
it. [For Export] not For Export

That aside there are other difficulties.

Are you going to save the record into the Search Result Table? IF so, you
should save the record before you attempt to copy it into For Export table.

If Me.Dirty then Me.Dirty = False 'Force the record to be saved

strSQL = "INSERT INTO [" & myTarget & "] SELECT * FROM [Search Result] WHERE
[ThePrimaryKey=" & Me.ThePrimaryKey

Currentdb().Execute strSQL, dbFailOnerror

I don't know why you are appending a duplicate record to another table. I
would probably just have a field in Search Result table that I could set to
the current date and time. Then I could use a query to extract the records
that have been newly added or changed when I needed to do an export based on
the datetime of the field in the Search Result table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dorian]

Can you display strSQL directly before the execute and post here what it is?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[jim_s via AccessMonster.com]

Tom,

Thanks for the reply. Before I start changing the code to omit the
recordsetclone, I'm not having much success with the debugging. Could I just
go through this with you:
Set a breakpoint- the breakpoint is set up on the line 'pk = rs(RTL_DAS_Ref).
Value'
Step over the 'strSQL = ' line - when stepping over the line, it still gives
me the syntax error.
write in the immediate window - the options in the immediate window are end
or debug. Which window is it that you're referring to?

Apologies if these are debug programming basics, I'm training in Access first,
self-teaching myself VB at the moment before I go on a course.

Thanks for your reply,
Jim

Set a breakpoint, step over the "strSql = " line, and write in the
Immediate window:
? strSql
Paste the result in a new query in sql view, and switch to design
view. The parser will bark at you.

Rather than rs.name you'll likely want to use Me.RecordSource, and all
the recordset-related code can be omitted.
The PK value can be retrieved from the form as well: Me.RTL_DAS_Ref
(or if you wish: Me.Controls(myPrimaryKey))

-Tom.
Microsoft Access MVP

Hi all,

[quoted text clipped - 30 lines]

for the INSERT INTO command. It looks okay to me! Any pointers? Please don't
tell me it's something blazingly obvious that I've not spotted!

 

[jim_s via AccessMonster.com]

John,

What has just been edited by the user needs to be appended - as long as these
fields make it through the append it doesn't particularly matter what happens
to it as a requery happens.

The duplicate record is required to send it to the export 'pot'. All the
records in the export pot will eventually have a schema written up to export
the records in that table to a fixed-width text file that is used as part of
a data feed. Once the record has been exported, it will be copied back into
the Archive table (the Search Result table draws records from here) with a
new PK. These amended duplicate records are necessary for the nature of what
is required by whom I'm making the DB for.

Will have some playing to see if I can get the SQL suggestions working.

Thanks,
Jim

Since For Export contains spaces you need to refer to it with brackets around
it. [For Export] not For Export

That aside there are other difficulties.

Are you going to save the record into the Search Result Table? IF so, you
should save the record before you attempt to copy it into For Export table.

If Me.Dirty then Me.Dirty = False 'Force the record to be saved

strSQL = "INSERT INTO [" & myTarget & "] SELECT * FROM [Search Result] WHERE
[ThePrimaryKey=" & Me.ThePrimaryKey

Currentdb().Execute strSQL, dbFailOnerror

I don't know why you are appending a duplicate record to another table. I
would probably just have a field in Search Result table that I could set to
the current date and time. Then I could use a query to extract the records
that have been newly added or changed when I needed to do an export based on
the datetime of the field in the Search Result table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi all,

[quoted text clipped - 30 lines]

for the INSERT INTO command. It looks okay to me! Any pointers? Please don't
tell me it's something blazingly obvious that I've not spotted!

 

[jim_s via AccessMonster.com]

Dorian,

This is part of the code before the SQL. There is a large chunk missing, but
these are data validation rules for when the record is amended and not really
relevant.

Jim

'record the current record for bookmark purposes

Dim CurrentPK As Integer
CurrentPK = RTL_DAS_Ref

'Populate the modified by field with the current global user

amendedby.Value = globaluser.Value

'Populate the last update field with the current date

Me.Update_Date = Date

'requery to show the new values

Me.Requery

'recall bookmark

Me.RecordsetClone.FindFirst "[RTL_DAS_Ref] = " & CurrentPK
Me.Bookmark = Me.RecordsetClone.Bookmark

'copy the current record to the export archive

Const myTarget As String = "For Export"
Const myPrimaryKey = "RTL_DAS_Ref"

Dim pk As Variant
Dim strSQL As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
pk = rs(RTL_DAS_Ref).Value
strSQL = "INSERT INTO " & myTarget & " SELECT * FROM " & rs.Name & " WHERE "
& myPrimaryKey & "=" & pk
Set rs = Nothing

CurrentDb.Execute (strSQL), dbFailOnError

Can you display strSQL directly before the execute and post here what it is?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Hi all,

[quoted text clipped - 30 lines]

for the INSERT INTO command. It looks okay to me! Any pointers? Please don't
tell me it's something blazingly obvious that I've not spotted!