Would anyone share some code?

J

Jeff C

I have two "Date" fields.."Innoculation Date" and "Office Visit Date". I
have created an additional related "History" table for each of these fields.
When a new date is entered in either of the fields I want the old date to
move to the related history table. Would anyone be able to share the code
needed and where to enter it in the form control...or field control on the
form? Thank you.
 
J

Jeff C

I guess I was running the race in the wrong direction (probably cause I'm not
Irish)
 
M

margaret bartley

I have two "Date" fields.."Innoculation Date" and "Office Visit Date". I
have created an additional related "History" table for each of these fields.
When a new date is entered in either of the fields I want the old date to
move to the related history table.
Click to expand...

Assuming the field names are the same in your datatables and your history
table, and are the same as your form fields:

In the General section of your form:

sub MoveToHistory
dim strSQL$, OldDate as date, strField$
OldDate=me.activecontrol.oldvalue
strField=me.activecontrol.name
strSQL="Insert into [History] ([" & strfield & "]) select #" &
format(OldDate,"m/d/yy") & "#;"
DoCmd.runsql strsql
end sub

(The only reason I used the variables OldDate and strField was for debugging
purposes, they are really not necessary)

Put the following in the BeforeUpdate event of your date field:

MoveToHistory


If you want the MoveToHistory action to take place when a Command button is
pushed, rather than when the user leaves the date fields, you may have to
store the old date in a hidden field when the form is loaded, and use the
hidden, original data, depending upon how you've set up your form actions.
 
Top