Editing field creates new record instead of replacing

[cochese]

I'll try my best to describe what I need to do. I'm sure this is covered many places, but I have a hard time coming up with good search keywords.

I have two tables. Table A holds employee records (id, first & last name, position, etc), and Table B holds the employee's rate history (id, rate, date given). In Table A the employee id is the primary key, and in Table B the employee id is foreign key with an auto number the primary key. The two tables share a 1 to Many relationship.

The rate is separated from the employee record table because I need to keep a history of each rate increase, and thus allow me to run a query and see all the different rate changes any given employee has had.

There is an employee Form (based off Table A) where all edits should be done, including changes to the employee's rate. So when a user navigates to an employee the user sees all the basic employment information, along with the most *recent* rate (that is being fed from Table B). If any edits to the basic employment information are made the changes replace what was in Table A automatically, while changes to the employee's rate needs to add a new record in Table B with the date given.

I have a Query that returns the most recent rate given (1 result) based off the id field of the employee Form. I have a Subform on the employee form which displays the query's results. However, when the rate is changed it changes it in Table B and doesn't add a new record like I would need. Also, the edit doesn't change the Date Given field of the record.

I guess I could work around this with a new form pop up to add a new rate and then requery the Subform...but it's not the method I prefer.

Thoughts? Help?

Thank you.

 

[vbaInet]

Creating a form to handle Table A should be easy for you so I think your main question is how to handle Table B right?

Well, have you considered using a subform and disallowing edits? So you restrict them from making changes but you allow them to click a new button to add a new entry?

If you don't like the idea and still want to go ahead with your plan here's how your form could look like:

---------------------
Main form - Table A
---------------------

------------------------------------------------
Unbound controls - Values fed from Subform B
------------------------------------------------
^
|
-----------------------------
Subform - Table B - Hidden
-----------------------------

Link the subform to the main form via the usual Master/Child fields. The Record Source of Subform B will be a query that uses the Max() function on the Date/Time field. Notice I mentioned a Time part too because you will need that.

 

[Beetle]

How about just using unbound controls with DLookup in the control source to display the most recent Rate and/or DateGiven. You could place these either somewhere on the main form, or in the header section of the subform, etc. Then, base the subform on Table B (or a simple query thereof) and set the Data Entry property of the subform to true so only new records can be added.

 

[CBrighton]

You could just add a command button to the subform which moves to a new record.

 

[vbaInet]

You could just add a command button to the subform which moves to a new record.

Not when you are aggregating

 

[CBrighton]

I assume the aggrgation you are talking about is the use of a Max() function?

Could you not just sort by date & return the top 1 record instead?

 

[vbaInet]

You could do. Even better

 

[vbaInet]

With CBrighton's approach you will have to:

1. Cancel the subform's Before Update event
2. Save the new values into variables
3. Create a new record
4. Set the controls to the values of the variables and Save.

 

[CBrighton]

I was thinking more of locking the fields until the command button was clicked, then the command button would unlock the rate textbox and move the subform to a new record where the user could input the new rate (assuming the date field has a default value of Now() / Date() and the FK is grabbed by the link).

Or using an unbound subform and a DAO / ADO recordset in VBA.

Most problems have so many possible solutions.

 

[vbaInet]

That was the approach I mentioned in my first post, but cochese mentioned he wants ...

If any edits to the basic employment information are made the changes replace what was in Table A automatically, while changes to the employee's rate needs to add a new record in Table B with the date given.

So what would happen is the edits are not overriden but in the background a new record is created. No create new or unlock button.

 

[CBrighton]

I meant the subform unbound, but I agree after loking at your post again it's basically what you sugggested with unbound fields on the main form & a hidden bound subform.

Heck, you could forget the subform entirely and have a single unbound control which pulls the value via VBA. Disable the field and put a command button next to it with the caption "Update" or "Amend", etc. Clicking on that would run an append query with the ID fk field & date field referring to the ID pk field on the form & Now() respectively and the rate field set to "[Enter new rate]". Then requery the form via VBA.

That way the query would prompt for the new rate, create a new record & display the latest rate.