Some help with this event...

  • Thread starter Thread starter Rob Meade
  • Start date Start date
R

Rob Meade

Hi, a friend of mine has sent me this...

Dim stTableName As String
stTableName = "Archive Invoices"
DoCmd.OpenTable "Accounts Register Invoices", acViewNormal, acEdit
Application.RunCommand acCmdSelectRecord
Application.RunCommand acCmdCut
DoCmd.Close acTable, "Accounts Register Invoices"
DoCmd.OpenTable (stTableName)
Application.RunCommand acCmdPasteAppend
DoCmd.Close acTable, stTableName
DoCmd.OpenForm "Registers Menu", acNormal

He is trying to have this fire on a close form event, but he says the 'cut'
part isn't working...

Can anyone offer any suggestions/help for this...

Thanks in advance,

Regards

Rob
 
Rob Meade said:
Hi, a friend of mine has sent me this...

Dim stTableName As String
stTableName = "Archive Invoices"
DoCmd.OpenTable "Accounts Register Invoices", acViewNormal, acEdit
Application.RunCommand acCmdSelectRecord
Application.RunCommand acCmdCut
DoCmd.Close acTable, "Accounts Register Invoices"
DoCmd.OpenTable (stTableName)
Application.RunCommand acCmdPasteAppend
DoCmd.Close acTable, stTableName
DoCmd.OpenForm "Registers Menu", acNormal

He is trying to have this fire on a close form event, but he says the
'cut' part isn't working...

Can anyone offer any suggestions/help for this...

Thanks in advance,

Regards

Rob
Click to expand...

If you could ask your friend to say what exactly it is he is trying to
achieve with this code, it wold help a lot. It appears that he's trying
to move a record from one table to another, but if so the approach he's
taking is far, far from the best way to do it. Even using that
approach, there's nothing in that code that identifies exactly which
record from the table "Accounts Register Invoices" is going to be
selected and copied, so if that table contains more than one record,
then -- even if the "cut" part worked -- he'd be taking an awful risk.
 
...
If you could ask your friend to say what exactly it is he is trying to
achieve with this code, it wold help a lot.
Click to expand...

Hi Dirk, many thanks for your reply.

As you mentioned below, he is indeed trying to take one record from one
table and place it in another, my understanding of the problem is that there
is only ever 1 record in his form, so the select method used in the code
would only select one record. I have already pointed out several other
rather large issues with the entire database design to him, I think for the
here and now its a quick fix job with the aim to rewrite it all properly
shortly afterwards (he's inheritted this from elsewhere).

Regards

Rob
 
Rob Meade said:
...


Hi Dirk, many thanks for your reply.

As you mentioned below, he is indeed trying to take one record from
one table and place it in another, my understanding of the problem is
that there is only ever 1 record in his form, so the select method
used in the code would only select one record. I have already
pointed out several other rather large issues with the entire
database design to him, I think for the here and now its a quick fix
job with the aim to rewrite it all properly shortly afterwards (he's
inheritted this from elsewhere).

Regards

Rob
Click to expand...

This is how I would do it -- provided there is only one record in the
source table:
Dim stTableName As String
stTableName = "Archive Invoices"
DoCmd.OpenTable "Accounts Register Invoices", acViewNormal, acEdit
Application.RunCommand acCmdSelectRecord
Application.RunCommand acCmdCut
DoCmd.Close acTable, "Accounts Register Invoices"
DoCmd.OpenTable (stTableName)
Application.RunCommand acCmdPasteAppend
DoCmd.Close acTable, stTableName
DoCmd.OpenForm "Registers Menu", acNormal
Click to expand...

'----- start of proposed code -----
(your Sub header here ...)

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim stSourceTable As String
Dim stTargetTable As String

stSourceTable = "Accounts Register Invoices"
stTargetTable = "Archive Invoices"

Set db = CurrentDb

db.Execute _
"INSERT INTO [" & stTargetTable & _
"] SELECT * FROM [" & stSourceTable & "]", _
dbFailOnError

db.Execute _
"DELETE * FROM [" & stSourceTable & "]", _
dbFailOnError

Set db = Nothing

DoCmd.OpenForm "Registers Menu", acNormal

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
'----- end of proposed code -----

Depending on the structure and indexes of the two tables, it may be
necessary for the append query to list the specific fields to be
selected from the source table and the target fields to which they are
to be assigned. I would certainly test this out on a copy of the
database to make sure you don't lose any data if it needs tinkering.
 
Back
Top