Autofill Date Fields

[PJHAction]

When I fill in a date field call one, how can I have field two, three and four filled in automatically based on date one. I do not want to use Action Query, they will affect my complete table!

Pete

 

[JeffAlton]

Do you want to populate the other fields with the same date in field 1?
If so create a new table and name it tblOne.
create fields One, Two, Three, Four in tblOne.
Create new form and Name it Form1.
Drag the four fields from the field list box to Form1.
Click on the text box for field one, open the properties box and click on the other tab and rename the four text boxes to:
txtOne
txtTwo
TxtThree
TxtFour
click again on txtOne and on the event tab.
click once in the "on exit" option, now click on the ...
and cut and paste the following code between
"Private Sub txtOne_Exit(Cancel As Integer)" and End Sub

Forms!Form1!TxtTwo = Forms!Form1!txtOne
Forms!Form1!txtThree = Forms!Form1!txtOne
Forms!Form1!txtFour = Forms!Form1!txtOne

Now when you type a date in the first box and tab out the other three are updated with the value in field one. This will only affect the current record that you are viewing in your form.

 

[PJHAction]

Trying the solution given worked great on the txt field. What I have is a field called "Dateasgn" (formated mm-ddd-yyyy), I would like a field called "Phase1" to equal "dateasgn" and "Phase2" to equal "phase1 + 28 days".

Pete

 

[danvlas]

Are you going to edit those values at a later time?
If not, adding such fields and values has no meaning, eats resources and induce redundancy.
You should calculate the values through queries or expressions.
Create an unbound textbox on your form and set its control source to:
= [Phase1] + 28
then see what happens if you change the date in [Phase1]

However, if you will eventually change those values independantly of the basic date, disregard my post.

Jeff: Exit event of the control is a bad choice for this. Merely tabbing through it would trigger writing to the record, even though it would be the same value. That record is unnecessarily locked in that case.
AfterUpdate would have been better (combined with some data checking)

Just some thoughts...



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[PJHAction]

All: Points made are valid, lets set up the example. This is a dateabase for a special education school. We will use the field "Dateasgn" and the date started school. The student will start their first phase on the date assigned, with this in mind: I need the field "Phase1" to be automatically filled in from "Dateasgn" when a date is entered, then in 28 days they should start a new phase of training. Field "Phase2" is automacally filled in by adding in 28 days to the "Phase1" which is equal to "Dateasgn".

End result: I will type in the "Dateasgn", the "Phase1" and "Phase2" needs to be filled in that record. Reports are genertated so we know when each student should go to the next phase.

Pete (PJHAction)

 

[willir]

Pete

Has JeffAlton may have answered part of your question using ...

Forms!Form1!TxtTwo = Forms!Form1!txtOne
Forms!Form1!txtThree = Forms!Form1!txtOne
Forms!Form1!txtFour = Forms!Form1!txtOne


And Daniel's comment about whether or not you plan to allow the subsequent dates to be changed. One of the rules for "Normalization" suggests not to store a calcualted field.

From your description, with the exception of holidays, vacation and illness, the date fields will be static.

With the information provided, you may consider...

me.Dateasgn is a field on your form where you enter the date


Storing only the Dateasgn value

me.Phase1 field
Control source (properties, data tab)
= [Dateasgn] + 28

me.Phase2 field
Control source (properties, data tab)
= [Dateasgn] + 56


If you actually want to store the phase 1 and 2 dates, you will need to add some logic...

me.Dateasgn field
After Update event

if not isnull(me.Dateasgn) then
me.Phase1 = me.Dateasgn + 28
me.Phase2 = me.Dateasgn + 56
end if


Storing Dateasgn, Phase1 and Phase 2 dates
Or you could put similar code for the "after update" event for me.phase1 if you want to add the the 28 days to phase2 to capture any changes you make in this field.

Richard

 

[PJHAction]

Thanks for the input, this is what I figured..

Me.phase1date = Dateasgn
Me.phase2date = DateAdd("d", 28, [Dateasgn])
Me.phase3date = DateAdd("d", 45, [Dateasgn])
Me.phase4date = DateAdd("d", 150, [Dateasgn])

Pete