Calculate 1 of 2 fields for Dates or Hours? 1

[misscrf]

I have 3 fields on a form:
Start Date/Time
End Date/Time
Hours Length

When a user is on the form, they naturally tab to the Start Date/Time first. On click of that field a mini calendar form pops up for them to choose the date and time.

The same mini form pops up for the End Date/Time.

Because of the information these users have, they will typically have maybe 1 of these pieces of information now, and 1 more later.

We cannot control which they enter first, but once any 2 of those are filled out, we want the calculate the 3rd based on the 2 that are entered. Make sense?

So, if...

1) Start Date/Time is filled out, and End Date/Time is filled out, then we need to calculate Hours Length between those two.

2) Start Date/Time is filled out, and Hours Length is filled out, then we need to calculate End Date/Time as the Start Date/Time plus Hours Length.

3) End Date/Time is filled out, and Hours Length is filled out, then we need to calculate Start Date/Time as the End Date/Time minus Hours Length.

I want to have this form auto update which ever field can calculate at any time. We store the information in fields so we can run reports later and not have to worry about all of these calculations.

Any ideas how I can do this, please?

I'm hoping this will be a fun challenge!!!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Can you provide the code that opens the minidatetime form? Can we assume this form is being opened acDialog?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

not very fancy:

Private Sub dtDateStart_Click()
DoCmd.OpenForm "frmMiniDateTime"
End Sub


Private Sub dtDateEnd_Click()
DoCmd.OpenForm "frmMiniDateTime"
End Sub

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You might want to try open the forms acDialog and then run the afterupdate:

Private Sub dtDateStart_Click()
   'I think acDialog is in the correct place
   DoCmd.OpenForm "frmMiniDateTime" , , , , acDialog
   flgChange = 1
   Call CalcMissing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Whats the name of the form calling the calendar?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

frmDealContracts

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, I changed my start and end date click code to this:

 DoCmd.OpenForm "frmMiniDateTime", acDialog
   flgChange = 1
   Call CalcMissing

acDialog needs to be right after the form.

Now when I click on the field, I get a run-time error 94, invalid use of null. It points to a line in the calcmissing function:

Public Function CalcMissing()
   Dim BinVal As Byte

   'Note Binary Bit Positions: dtDateStart=1, dtDateEnd=2, intLength=4
   BinVal = Abs(IsDate(Me.dtDateStart) = True) + _
            Abs(IsDate(Me.dtDateEnd) = True) * 2 + _
            Abs(IsNumeric(Me.intLength) = True) * 4
            
   'Set BinVal by deleting sought value from all three!
   If BinVal = 7 Then 'All data present but edit was made
      If flgChange = 1 Or flgChange = 2 Then
         Me.intLength = Null
         BinVal = 3
      ElseIf flgChange = 4 Then
         Me.dtDateEnd = Null
         BinVal = 5
      End If
   End If
  ---debug highlights line below of flgChange=Null---- 
   flgChange = Null
         
   'Calculations Here
   If BinVal = 3 Then 'calculate hours
      Me.intLength = DateDiff("h", Me.dtDateStart, Me.dtDateEnd)
   ElseIf BinVal = 5 Then 'calculate dtDateEnd
      Me.dtDateEnd = DateAdd("h", Me.intLength, Me.dtDateStart)
   ElseIf BinVal = 6 Then 'calculate dtDateStart
      Me.dtDateStart = DateAdd("h", (Me.intLength * -1), Me.dtDateEnd)
   End If

End Function

Any thoughts?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

p.s. The minidatetime form still opens but opens in a datasheet view instead of a form view. Wierd!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The acDialog is not right after the form name. It should be following the 5th comma. acFormDS (datasheet) has the same value as acDialog. They are both constants that equal 3.

Its theAceman1's code but these lines will cause an error since a Byte data type can't be set to Null.

   Private flgChange As Byte

   flgChange = Null

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

misscrf . . .

We have a navigational problem here as the [blue]OnChange[/blue] events won't trigger when your dates are updated by the calendar. The same for [blue]AfterUpdate[/blue] we already know. I had used the [blue]OnChange[/blue] in the origional code not thinking of the calendar.

Before getting to the above, lets make necessary program changes:
[ol][li]Cut [blue]Private flgChange As Byte[/blue] out of the declaration section of the form and paste it in the declarations section of a module in the modules window and change [blue]Private[/blue] to [green]Public[/green]:

[blue]Public flgChange As Byte[/blue]

[/li]
[li]In the following code remove the lines in [red]red[/red] and replace the error line with that in [green]green[/green]:

[blue]   If BinVal = 7 Then
      If flgChange = 1 Or flgChange = 2 Then
         [red][b]Me.intLength = Null[/b][/red]
         BinVal = 3
      ElseIf flgChange = 4 Then
         [red][b]Me.dtDateEnd = Null[/b][/red]
         BinVal = 5
      End If
   End If
   
   [green][b]flgChange = 0[/b][/green][/blue]

[/li]
[li]your click evens should be:

[blue]Private Sub dtDateStart_Click()
   flgChange = 1
   DoCmd.OpenForm "frmMiniDateTime", , , , , acDialog
   Call CalcMissing
End Sub

Private Sub dtDateEnd_Click()
   flgChange = 2
   DoCmd.OpenForm "frmMiniDateTime", , , , , acDialog
   Call CalcMissing
End Sub[/blue]

Special note here: make sure the [blue]PopUp[/blue] & [blue]Modal[/blue] properties of frmMiniDateTime are set to Yes![/li][/ol]
BTW ... thanks [blue]dhookom[/blue] for the headsUp.

The sequence of events should be:
[ol][li]Click a date and open frmMiniDateTime with DoCmd.OpenForm.[/li]
[li]Since the form is open [blue]Modal[/blue], the code stops runing on the DoCmd.OpenForm line.[/li]
[li]This is important: [blue]Your calendar now only needs to return a date![/blue] No other code other than closing frmMiniDateTime.[/li]
[li]frmMiniDateTime closes and the code continues to after DoCmd.OpenForm executing [blue]Call CalcMissing![/blue].[/li]
[li][blue]Done![/blue][/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[misscrf]

Aceman and dhookom you guys rock!!! That worked!! I tested it out and it did exactly what it should. I am so psyched!!!!

misscrf

It is never too late to become what you could have been ~ George Eliot