• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Have data in textbox inserted into two tables or give me another solution

Here is what I have so far:

One form that allows the addition of a new employee. All the fields are associated with a single table.

One form that allows for the editing of most of the information about that employee.

The client wants to be able to keep track of every time that employees status changes ("Temp Full Time";"Temp Part Time";"Permanent Full Time";"Permanent Part Time") and every time thier pay changes.

So my idea was that when the information is entered into the "Add Employee" form, the information in the employee status and pay rate text boxes would be duplicated into another table. Kind of like an audit log. Then every time the data was modified in the "Edit Employee" form It would have a record for the date effective and the new status or payrate.

Hope this makes sense.

Thanks in advance for your help.
0
princeservice
Asked:
princeservice
  • 4
  • 3
  • 3
2 Solutions
 
PatHartmanCommented:
There are a number of ways to make an audit log.  A very simple solution is to use a shadow table for each main table.  The shadow table has two (or possibly three) additional columns.  It needs an autonumber primary key for uniqueness.  It needs a timestamp to log when the record was added (both of these can be handled by table level defaults).  The third column is needed only if your main table doesn't keep track of who made a particular change.  If it does that already, then the column will already be logged.  If it doesn't, you'll need to include it when you log the record.

The code to insert the record goes into the AfterUpdate event of the form and it is just an append query that copies the record you just added (you'll need the autonumber PK of the record in question).
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you really need an audit table for this? Assuming an employee can only have a single "status" at a time, then just keep an EmployeeStatus table, and add a new record each time that status changes - just don't remove old records when you do.
0
 
princeserviceAuthor Commented:
Scott - That is exactly what I want and was what I was trying to describe, but I can't think of how to do it. How can I make a single form put data in multiple tables.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd think you could do this most easily with a Subform based on an EmployeeStatus table. So your Employee table (and form) would remain unchanged, and you'd create a second table for EmployeeStatus, then create a Form based on that Table. Embed the new form in your Employee form, and make sure that Access correctly sets the Master/Child link fields. You could then record your Employee Status with that subform.

There are some challenges to this approach - for example, if you don't want users to modify or update existing records, you'll have to take action to prevent that - but it's definitely doable.
0
 
PatHartmanCommented:
You make a single form put data in multiple tables using a query as I described in my original post.  Whether you want the data to be in the same table or a separate table, the technique is the same.  The bound form updates the "master" and an append query logs the new or the old values.  

I prefer to log the new value since it is simpler to program.  When you elect to log the old value, you actually need to create variables to hold all the columns, then in the AfterUpdate event of the form, you take the data from the variables and construct an append query.  You have to save the values because once the AfterUpdate event fires, the OldValue property is set to the new value.  If you log the change in the BeforeUpdate event, you have no assurance that the save isn't going to be cancelled and if it is, you need to delete the history record you just created.

If you log the "new" value, you end up with the new value twice, once as part of the history set and once as the active record.  This also simplifies queries if you are using the two table method.  A query that gets "history" automatically includes the current record without having to involve a second table and do a union.  When I use the one table solution, I use a flag to distinguish the "current" record from "history" records.  Again, this is to simplify queries since the "current" record will have a different date on every record which makes it harder to simply retrieve the "current" values.  So, each client (or whatever) has one current record and multiple history records.  The query for the maintenance form includes this flag in its selection criteria so that it only retrieves current records.  If you want to look at history, you can do it with filters or I would use a different form since it is safer.  History can't be updated so it is simply a matter of having a visual.  You won't need validation code behind the form.
0
 
princeserviceAuthor Commented:
I seem to have come up with a solution to my problem that so far is perfect. After doing a little research I decided to use an unbound form instead of a bound form ::gasp::

It seems to give me a whole lot more control.
0
 
PatHartmanCommented:
It seems to give me a whole lot more control.
It doesn't.  Make sure you have crossed all the t's and dotted all the i's because you no longer have Access doing stuff for you behind the scenes.  Once you understand form events, you have all the control you need.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree with Pat regarding the need to be very, very careful when dealing with unbound forms. Access is very good at handling data; most novice programmers are not. I have created several applications that use unbound forms, and it is quite a bit of work to get things right.

Not that I'm implying you're a novice programmer, but asking the question you did would seem to indicate that you're still in a bit of a learning curve with Access.
0
 
princeserviceAuthor Commented:
A lot of work and coding seems to be the biggest argument for unbound forms. I am keeping every form in the database bound accept one. I understand how much easier it is but I couldn't get it to work with the bound form. It was too complicating to try to describe exactly what I wanted and why.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A lot of work and coding seems to be the biggest argument for unbound forms.
To me, improper data handling is the single biggest argument against using unbound form, not the amount of work - that's just a byproduct.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now