Delete a record using a form that is not tied to a table. 1

[PlumDingo]

Hi,

I have the following database:

Employees
Scheduled Vacation
Total Vacation Allowed

as well as some other tables that don't really affect this operation. Each of them are related by the Employee ID number either as a primary or foreign key. I cannot enforce referential intergrity on the Scheduled Vacation table because an employee may not necessarity schedule vacation when they are supposed to thus causing issues as that their employee Id is not in the table. In fact, I don't mind leaving this information in this table until the time comes to start a new year so I guess this table is not affected by this action either.

Anyway, what I am trying to do is that when an employee leaves a location, that the clerk can pull up an Employee Delete form and thus type in the Employee ID number and delete that record from the table Total Vacation Allowed and Employees. Before the action is completed. I want a box asking the person to confirm that they want to delete this employee from the file. Then if is possible, I would like for the deleted record to go into another table called Former Employees and store the relevant information there that is combined from both tables. That way if the employee ever comes back to that location or something needs to be looked up it will be in the database.

I hope that this makes some sense.

France
Just trying to get by.

 

[sillysod]

First Copy your main table and paste its as Employees History

you could then use a SQL DELETE and INSERT statement to insert the record into the history table and then delete it from the main table

After you write the SQL statement and assign it to a string variable then use the docmd as follows

'declare variables
dim strInsertSQL as string
dim strDeleteSQL as string
dim EmployeeID as string

strInsertSQL = "INSERT INTO [Employee history] SELECT [Employees].* FROM [Employees] WHERE ((([Employees].EmployeeID)= " & "'" & employeeID "'));"

strDeleteSQL = "DELETE [Mailing List].*, [Mailing List].MailingListID FROM [Mailing List] WHERE ((([Mailing List].MailingListID)= " & "'" employeeid"' ));"


'prompt user for employee id, alternatively you could assign this value based on a textbox value etc

employeeID = inputbox("Enter Employee ID")

'turn off access's warning messages
docmd.setwarnings false]
docmd.runsql(strInsertSQL)


' the employee record has been appended to the history table so we can now delete the original record from main table
docmd.runsql(strDeleteSQL)


docmd.setwarnings true

 

[PlumDingo]

THank you so much. It works!! I had to do some tweaking to check and make sure that the Employee ID actually exists but the basic concept really helped me out.

France
Just trying to get by.

 

[FancyPrairie]

One thing you should consider is wrapping your code in a transaction. Otherwise, you could corrupt your data. For example, suppose you issue the command to insert the data into the history table, then delete the record from the Total Vacation Allowed table. But before you can issue the command to delete the record from the Employees table, something happens (i.e. program aborts, system crashes, whatever).

In this case your tables are no longer in sync. Here's basically what you want to do (note the BeginTrans, CommitTrans, and the RollbackTrans commands):

Function DeleteEmployee()

    Dim cnn As ADODB.Connection
    
    Dim bolBeginTrans As Boolean
    
    On Error GoTo ErrHandler
    
    bolBeginTrans = False
    
    If (MsgBox("Do you want to delete the Employee ID?", vbYes + vbNo + vbQuestion) = vbYes) Then
        Set cnn = CurrentProject.Connection
            
        cnn.BeginTrans
        bolBeginTrans = True
        
        cnn.Execute "insert into Employee History..."
        cnn.Execute "Delete * from Total Vacation Allowed ..."
        cnn.Execute "Delete * from Employees..."
        
        cnn.CommitTrans
    End If
    
ExitFunction:

    Exit Function
    
ErrHandler:

    MsgBox Err.Number & vbCrLf & Err.Description
    If (bolBeginTrans) Then cnn.RollbackTrans
    Resume ExitFunction
    
End Function

Another thing you might want to consider is to archive your records (rather than writing them to a history table). For example, create another database that contains 2 tables: Total Vacation Allowed and Employees (these tables have the same structure as the 2 tables you currently have. Note they also have the same name). This database is your archive database. Instead of inserting the records into a history table insert them into the 2 archive tables.

Now I assume you have a form where the user can enter Employee information (tied to the table Employees) and I assume you have another form where the user can enter the data into the Total Vacation Allowed table. Finally, I assume you have a report that prints the data from the 2 tables. If so, you could simply have a button (or menu item or something) that when selected, the user can alternately view and/or report on either the archived data or live data. You use the same forms and reports for both sets of data (live and archive). So basically, when the user selects the button, your program simply relinks to the tables in the arhived database. Your forms and reports are now reporting archive data. When they select the button again, your program relinks to the live database and now your forms and reports are reporting live data. Remember, your forms and reports don't change. You simply change the links to where the tables reside (live or archive). You might also consider adding a label on your forms/reports so when the user selects the archived database, the labels are made visible and displays something like "Editing Archived Data".

Finally, since you already have the code to archive the records, you could use the same code to unarchive records if necessary.

To summarize:
1. Create an archive database and import just the structure of your 2 tables and give the 2 tables the same names as the tables in your live database.
2. Create a function that archives/unarchives the data (already done)
3. Create a function that relinks the 2 tables to either the live database or archived database

I have explained how to do this in another thread. So if your interested in how this works, let me know and I will find the thread.

 

[PlumDingo]

Thank you so much for this information Fancy. After reading your post, I decided to give it a try. I realize a couple of things. In trying to make my database make sense, I was going to get rid of the idea of the history. I realize that what I really need a history on is not the Employee and what vacation they were allowed but what Vacation was scheduled. Here is where I am right now.

I have a form called Employee that the user uses to enter information on new employees and another form used to edit information. I do not have a from to enter Total Vacation because I am still trying to figure out how this information can be calculated automatically and then entered into the table once a year when the user hits an update button and calculated automatically when a new employee is entered. Anyway, when I delete the employee from the table, I thought that referential intergrity would automatically delete information in the related tables, but it does not. This makes me have to write a seperate line for each. That is fine because I am going to try your suggestion with the transaction so that I don't have any problems. Regardless of what happens, I want to archive one year's worth of vacation information. So if we are in 2004, a user can look back at 2003 and see what an employee took, how much they were alotted and how much they carried over. I have the information on the Vacation Scheduled in a table called Vacation Scheduled. It consists of the fields:

Sch ID primary key
Employee ID
Date
Hours Scheduled

The Total Vacation Allowed table tells the user how much vacation a user has earned. It has the following fields:

Employee Number primary key
Seniority Date
Hours Earned
Carry Over Hours

In the archives I would want these two tables, I guess, refelcting the employees still on location at the end of the year so that a user can tell what an employee took if their is a dispute on their vacation schedule or we need to look something up.

Any help would be greatly appreciated.

France
Just trying to get by.

 

[PlumDingo]

Does anyone have an idea how to make the above work. I could really use some help.

France
Just trying to get by.

 

[FancyPrairie]

What part is not working?

 

[PlumDingo]

First, I have given up on trying to automatically calculate the vacation information based on an employee's work history. What I having trouble with is storing the old information so that I can use that information to popoulate this years information. Am I making any sense?

France
Just trying to get by.