Help Moving Records from a Form

[weilerdo]

I have an Employee Database with a form that displays the employee record. When an employee leaves they want to be able to click on a button on the form and have the record moved to a different table. My thought was to do a append query to move the record from my active employee table to the termed employee table, but that would still leave a copy in my active table. I'm not real up on coding that much so Im not sure if there's a way to have code move a record from 1 table to another table or copy the data from 1 table to another table and delete it from the first table. Any help would be greatly appreciated.

 

[izyrider]

what you want to do is very do-able.

one issue: this is a real slash & burn thing to do to your db, so you really should wrap the whole thing in an explicit transaction: all completes OK or all is undone. what you don't need to happen is that the delete-from-main works but the append-to-archive fails. explicit-transactions fix that stuff
...but that means code.

what are you using? i can offer you example explicit-transaction code in DAO for exactly this purpose, but if you are a non-code person and in a2k or later, it wont help.

explicit-transactions exist in ADO but a/ i am not an ADO person, and b/ the documentation is quite poor.

izy

 

[weilerdo]

Thanks Izyrider

I am using Access 2K and have a little coding but not much. I agree that it is a very dangerous thing, it would not be good if only half of it worked. I do have ADO and DAO so any examples would be great.

 

[trucktime]

Move

You could ad a yes/no field to the employee table, and for instance set the field to 'True' in case the record needs to be moved.

Then put some code behind a command button that does the whole operation
for a current records in steps:

1 - Set the field to 'True'

2- Save the record

3 - Run an append query, where in the criteria row for the yes/no field the value is set to 'true'

4 - Delete the record


Another, much easier option would be to just leave the record in the
employee table, ad a 'status' field which has 2 options: 'active' and 'terminated' for an employee who leaves just
change the status and use a query for your forms and reports so
you filter the employees based on their status.

In case you like to post your db (without sensitive data) I'll be happy to look at it and and give you some sample code.

 

[trucktime]

Move

Another thought is that you will need a way to reverse the process,
in case the wrong record has been moved, or in case an employee who
left decides to come back.

So for the second table you would also need a form with a button to
move the record back to the first table.

 

[izyrider]

sorry - the first example i found is actually quite messy and contains all sorts of (sanitized) crap that is specific to my application. hope you can make some sense of it.

however, check out the begintrans, committrans, rollback methods (and note that these apply to the DAO.workspace object).

i'll sanitize & post the copy / delete routines in a moment or two. you have enough to read here for a while

izy

Private Sub butDelete_Click()
    
    Dim wsp As DAO.Workspace
    Dim msg As String
    Dim whenArchived As String
    
    On Error GoTo err_butDelete_Click
    
    msg = "This action will delete the currently selected xxxxxxxxx "
    msg = msg & "from the active database." & vbCrLf & "Only the superuser will "
    msg = msg & "able to recover the deleted xxxxxxxxxx." & vbCrLf
    msg = msg & vbCrLf & "Delete this xxxxxxxxxxx?"
    If MsgBox(msg, vbQuestion + vbOKCancel, "WARNING!") = vbCancel Then
        'user decided not to delete
        MsgBox "Delete cancelled", vbInformation, "Cancel"
        Exit Sub
    Else
        Screen.MousePointer = 11
        'else archive and delete the currently selected xxxxxxxxx
        whenArchived = MakeServerDateTime(Now()) 'get an archival datetime
        'the archive/delete process is wrapped in an explicit transaction
        'either EVERYTHING completes without error, or EVERYTHING is returned to the initial status
        Set wsp = DBEngine(0)
        
        '   ########### SPECIAL ERROR HANDLING FOR THE TRANSACTION ############
        On Error GoTo err_butDelete_Transaction
        wsp.BeginTrans          '   ############ BEGIN TRANSACTION ############
        If CopyToArchive(Me!IDxxxxx, whenArchived) = False Then GoTo rollback_butDelete_Click
        If DeleteXxxxxRec(Me!IDxxxxxx) = False Then GoTo rollback_butDelete_Click
        wsp.CommitTrans         '   ############# END TRANSACTION #############
        
        
        On Error GoTo err_butDelete_Click   'return to normal error handling
'the next 3 lines are to do with a temp table - ignore them
        DoCmd.SetWarnings False
        DoCmd.RunSQL ("DELETE * FROM tmpMyXxxxxxxx WHERE IDxxxxx = " & Me!IDxxxxxxx & ";")
        DoCmd.SetWarnings True
        
        
        MsgBox "The xxxxxx was archived and deleted", vbInformation, "Delete COMPLETED"
      
    End If
    
exit_butDelete_Click:
    'begin my app specific stuff
    Forms!frmMain!cboXxxxxx.SetFocus 'move focus to safety
    Forms!frmMain!cboXxxxxx.Requery  'requery the combo now that one entry has gone
    Forms!frmMain!cboXxxxxx = Null   'reset xxxxxxx reference
    Forms!frmMain!subMain.SourceObject = "subRefresh"
    Forms!frmMain!subMnu.Visible = False
    Screen.MousePointer = 0
    'end my app specific stuff
    Set wsp = Nothing                   'tidy up
    Exit Sub                            'quit
    
err_butDelete_Click:            'trap system errors
    MsgBox "ERROR " & Err.Number & ": " & Err.Description, vbCritical, "butDelete_Click"
    'unexpected error in a transaction routine - be conservative and quit!    
MsgBox "Application will close: you should be able to restart with no problems", vbExclamation, "Aborting"
    GoTo err_butDelete_Quit:
    
err_butDelete_Transaction:
    MsgBox "ERROR " & Err.Number & ": " & Err.Description, vbCritical, "butDelete_Click"
    Resume rollback_butDelete_Click

rollback_butDelete_Click:       'trap errors from CopyToArchive and DeleteXxxxxRec
    On Error GoTo catastrophe_butDelete_Click
    wsp.Rollback                '   ########### ROLLBACK TRANSACTION ###########
    On Error GoTo post_butDelete_Click
    MsgBox "Delete was cancelled following an error", vbInformation, "Delete CANCELLED"
    GoTo exit_butDelete_Click
    
catastrophe_butDelete_Click:           'nightmare error where rollback fails
    msg = "! ! !   F A T A L   E R R O R   ! ! !" & vbCrLf & vbCrLf
    msg = msg & "Make a note of the error message below and inform a superuser URGENTLY:" & vbCrLf
    msg = msg & vbCrLf & "Rollback failed in subXxxxx.butDelete_Click with boxXxxxxxID = "
    msg = msg & Me!IDXxxxx & vbCrLf
    msg = msg & "ERROR " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf
    msg = msg & "The system will send e-mail to the superuser and shut down. Warn all other users that the "
    msg = msg & "database is in an unstable state - all users should quit the system NOW!"
    MsgBox msg, vbCritical, "FATAL ERROR!"
    If Not izyMailer(izymailer_Send, "fred@fred.com", "XXXXXXX CRASH " & gloNamUser, msg) Then
        MsgBox "Auto-mail failed! "
    End If

err_butDelete_Quit:
    Screen.MousePointer = 0
    DoCmd.Quit
    
post_butDelete_Click:
    'there are no objects to clear
End Sub

 

[Mile-O]

Based on your title I should say that you can't move records from a form - they are only presented there. They always exist in the underlying recordset.

My thought was to do a append query to move the record from my active employee table to the termed employee table, but that would still leave a copy in my active table.

It's fair enough. You'd just use a delete query to remove the emplyee from the first table after you've run the append query.

However, it's more logical just to add a field in the table, if you haven't got one already, for the employee's leaving date. If this is empty then the employee still works there and if there is a date then the employee has left. You can simply use a query with an [LeaveDate] Is Null criteria to get your current employees.

This way you are not creating any possible problems. Maybe you have historical data where an employee (that has left) previously processed something. This would mean EmployeeID appears as a foreign key in another table - you'd lose this and a heap of records just wouldn't relate.

Make sure this query is bound to the form rather than the employee table. This is the way things should be.

 

[weilerdo]

My Database

I tried to zip my DB but it is 4 mb and it will not let me attach it.

 

[Mile-O]

Another, much easier option would be to just leave the record in the employee table, ad a 'status' field which has 2 options: 'active' and 'terminated' for an employee who leaves just change the status and use a query for your forms and reports so you filter the employees based on their status.

Made redundant by the person's leaving date.

 

[izyrider]

here are the sanitized copy & delete.
again, more complicated than they should be since they are extracts from a running app which is complicated by a related table that needs archiving too.

anyhow - despite all the confusion i have probably caused you , the basic rules are:

duplicate EXACTLY mainTable structure in archTable.

recommendation: add an autonumber PK to archTable

...in any case, design-view all copied fields in archTable as longs not as autos!!!.

probably add a dateArchived field (using Now() as table-default is enough)

now do the transaction thing

on error goto transactionerror
begintrans
INSERT INTO archTable SELECT * FROM mainTable WHERE myPK = 12345
DELETE * FROM mainTable WHERE myPK = 12345
committrans
on error goto normal error handler



transactionerror:
rollback


have fun!



izy

Private Function CopyToArchive(thisXxxxx As Long, whenArchived As String) As Boolean
    
    If ArcTblXxxxx(thisXxxxx, whenArchived) = False Then
        MsgBox "Archive Failed!", vbInformation, "ERROR ArcTblXxxxx"
        CopyToArchive = False
        Exit Function
    End If
    
    If ArcTblM2M(thisXxxxx, whenArchived) = False Then
        MsgBox "Archive Failed!", vbInformation, "ERROR ArcTblM2M"
        CopyToArchive = False
        Exit Function
    End If
    
    'otherwise, copy was successful
    CopyToArchive = True

End Function

Private Function DeleteXxxxxRec(thisXxxxx As Long) As Boolean
    
    Dim wksp As DAO.Workspace
    Dim dabs As DAO.Database
    Dim strSQL As String
    On Error GoTo err_DeleteXxxxxRec
    
    Set wksp = DBEngine(0)
    Set dabs = wksp.OpenDatabase("", False, False, gloStrConn)
    
    strSQL = "USE Zzzzzzzz DELETE FROM tblYyyyyyyyXxxxxM2M WHERE tblYyyyyyyyXxxxxM2M.IDXxxxx = " & thisXxxxx & "; "
    
    dabs.Execute strSQL, dbSQLPassThrough
    
    
    strSQL = "USE Zzzzzzzz DELETE FROM tblXxxxx WHERE tblXxxxx.IDXxxxx = " & thisXxxxx & "; "

    dabs.Execute strSQL, dbSQLPassThrough

    
    DeleteXxxxxRec = True

exit_DeleteXxxxxRec:
    Set dabs = Nothing
    Set wksp = Nothing
    Exit Function
    
err_DeleteXxxxxRec:
    DeleteXxxxxRec = False
    MsgBox "ERROR " & Err.Number & ": " & Err.Description, vbCritical, "DeleteXxxxxRec" & thisXxxxx
    Resume exit_DeleteXxxxxRec
    
End Function

 

[weilerdo]

Thanks Guys

Thank you to everyone for their help I will try these things and see what I can do. Thanks again

 

[still_rookie]

a simpler way, that doesn't require any VB coding, is :

make a Yes/No box in your employee table and make two queries, 1 with the criteria for the Yes/No set to Yes, and the other set to No.

So when you click on the Yes/No button on the form the Yes (meaning either left the company or the ones that are at the company currently - depends upon how you label the field) will put all records into the Yes query and vice verca.

Instead of making two different tables you could make these two queries and use them ...?

Another plus point of this is, that when you select the Yes/No button on the form, it is immidiately transfered into the Yes, or No query.

And if you want to make it more professional, make a search for the ones with Yes, or the Ones with No.

 

[Rai]

Filter

Dont know if you have sorted this out yet but hte way o do that tyoe of thing in my DB is to put a field like mentioned before with a yes no answer and then when you have done that, create a button to open the form filtering out all the Yes answers.

and you could also have another form that filters out all the no answers so you can view all termed Employees.

regards,
Rai