Need function to Append/InputBox/then Delete on Response

[Namsha]

I need a function to append the data to another table then pop up with a message box that requires a yes or no answer and if yes deletes data from original table....
I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[Darrylles]

Hi,

You can open the original record in an Access form, open another form with the table to be copied to - transfer the record (easy).

Then fire the msgbox - and delete the record from the original table - on the 'original' Access form - IF the user says: YES in the msgbox.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Namsha]

Let me be more specific.....On click, a command that fires an append query if my client chooses to append the records to the table and it is successful then I want to delete the records in the previous table. I think what I need is a function that fires, and yes I did learn VB.....took less than a couple of years and sarcasm isn't appreciated! I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[jebry]

Hi!

Try this in the click event of the button:

If MsgBox("Do you want to transfer the record to NewTable?", vbYesNo) = vbYes Then
CurrentDb.Execute "YourStoredAppendQuery"
CurrentDb.Execute "YourStoredDeleteQuery"
End If

Of course, you can do this in two steps to give the user the option to add the record to the new table without deleting it from the old table if this is advisable.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[danvlas]

I strongly suggest to use transactions in this type of operation. If any error occurs, all actions are rolled back and you'll save yourself a lot of time and trouble...
I assumed your 'archive' table has the same structure and field names as the 'working' table.

Sub ArchiveRecords()
dim rsToAppendFrom, rsToAppendTo, fld, TransStarted
On Error GoTo ErrHandler
If MsgBox("Do you want to transfer the records?",vbYesNo)=vbYes Then
Set rsToAppendFrom=CurrentdDb.OpenRecordset("SQL Statement to select the records you want to transfer&quot
Set rsToAppendTo=CurrentDb.OpenRecordset("Select * From TableName Where 0=1&quot
DbEngine.BeginTrans
TransStarted = True
Do Until rsToAppendFrom.EOF
rsToAppendTo.AddNew
For Each fld In rsToAppendTo.Fields
rsToAppendTo(fld.Name) = rsToAppendFrom(fld.Name)
Next
rsToAppendTo.Update
rsToAppendFrom.MoveNext
Loop
If MsgBox("Do you want to delete the records from the old table?",vbYesNo)=vbYes Then
With rsToAppendFrom
.MoveFirst
Do Until .EOF
.Delete
.MoveNext
Loop
End With
End If
End If
If TransStarted Then Db.Engine.CommitTrans: TransStarted=False
CloseRecordsets:
rsToAppendFrom.Close
Set rsToAppendFrom = Nothing
rsToAppendTo.Close
set rsToAppendTo = Nothing
Exit Sub
ErrHandler:
If TransStarted Then Db.Engine.Rollback: TransStarted=False
MsgBox "Error: " & Err.Number & ". All records restored"
Resume CloseRecordsets
End Sub

Hope this helps,

dan

 

[Namsha]

Question!!! Can't I just fire up the appendme query and on vbyes = true fire the delete query?

I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[danvlas]

Well, that's exactly what happens in the code I posted. With one correction:
- You get prompted whether to append the records
- If no, exit the procedure
- If yes, after appending, you get prompted whether to delete records

If you don't want the prompt for appending records, delete the first 'If' line and the last 'End If' line.
This will append automatically and prompt to delete.

Dan

 

[Darrylles]

Hi Namsha,

Really don't understand what you mean by 'sarcasm'.

Gimme a clue.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Namsha]

I would respond but you know well enough what you wrote..... I can expect anything and everything of myself;
But can only accept or reject what you have to offer!

 

[nettie205]

Ok, this is the kind of code I was looking for to Audit Trail my database. I have a quick question. Where can you find the system variable names for forms, columns, tables. I have a form that is used for updating, deleting or adding records. When an action is taken, I want the system to send the column name, form name, table name that is getting the change to the audit tables. How do I put the form name in the audit trail table as a value?

Please, I'm desperate at this point.

Thanks,
Annette

 

[Darrylles]

Hi Namsha,

You got a mental problem?

Of course I know what I wrote, it's the first response to this thread and it's on this page!

Do you understand English, and do you understand the meaning of the word 'sarcasm'?

What are you talking about?

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[SBendBuckeye]

Hey Namsha,

I watch these forums a lot and if you are referring to the quote at the bottom of Darrylles message, that is a signature line on the bottom of every post I have ever seen from him. I don't think it was directed at you in any way, its just programmer humor.

Have a great day!

 

[Darrylles]

Hi namsha,

(Thanks SBend for pointing that out - to me!)

You sort of forget about the 'quote of the day'.

Sorry Namsha - the quote is really just something that I feel is really meaningful.
We've all argued with people who talk cr@p and we tend to get 'drawn in' to stupid discussions, we drop to their level before we realise it and they start beating us in the argument because they're good at talking cr@p.

This quote applies to me probably more than most, and is never aimed at anyone that I'm trying to help - it's just a 'quote of the day' (even though it's my quote of EVERY day).

Sorry for any misunderstanding (but my fave quote stays - lol ).

Kind regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."