Would anyone share some code?

[Guest]

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.

 

[Joseph Meehan]

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.

I am going to do some guessing here, but it sounds to me that you need
to change your table design.

I would suggest one table for visits and in that table you would have
your "Inoculation Date" field and an "Office field"

A separate table for patients along with their demographic data

No separate history table as you can find the most recent date for the
individual if you like, or all of them.

 

[Guest]

I still need a way to save the old dates as they are replaced with current
dates

 

[Joseph Meehan]

I still need a way to save the old dates as they are replaced with
current dates

I guess I did not explain it well enough. The "Visits" table would
contain ALL the visits. The complete history. Then when you wanted to see
just the last one, it could be picked from that table.

I an not sure what you want to do, but I think I would want to use
subforms. That way I could pick the patient and in the sub form show all of
their visits with the most recent on the top, scrolling thought if needed to
see older ones if they all did not fit. Depending on your need there could
be two list (Inoculation Date and Office Visit Date) (BTW try not to use the
term "Date" in the real name you use as it may be a reserved name and could
cause problems.)

So you could display something like this


____________________________

Name: Joseph Meehan
Address: 741 Main Street
Anytown Mi 84563
------------------------------------

Visits: Inoculations
******* *************

12/01/05 -
11/15/04 11/15/04
11/01/04 11/01/04
08/04/04

_______________________

 

[Guest]

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

 

[Joseph Meehan]

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

We can't hold that against you. We can't all be lucky.

 

[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.

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.