Parsing Minutes

[twildt]

I have a text box on a form. This form is built off just 1 table.

The form field in question is feeding the "minutes" field of the table.

The table data contains minutes such as 15, 60, 240, etc.

The form however I need it to display, or allow the users to enter data in h:nn format or decimal such as either 1:30 or 1.5

I have this unbound text box with the following Before and After event procedures:

Private Sub txtMinutesWorked_AfterUpdate()
    txtMinutesWorked = FormatMinutesAsHMM([MinutesWorked])
    
End Sub
Private Sub txtMinutesWorked_BeforeUpdate(Cancel As Integer)
Dim vResult As Variant
    vResult = ParseMinutes(txtMinutesWorked)
    If IsEmpty(vResult) Then
        Cancel = True
    Else
        MinutesWorked = vResult
    End If
End Sub

The form itself has the following Current event procedure applied:

Private Sub Form_Current()
    txtMinutesWorked = FormatMinutesAsHMM([MinutesWorked])
End Sub

Those event procedures are using this code from my custom public module:

Option Compare Database
'Format Minutes
Public Function FormatMinutesAsHMM(MinutesValue As Variant) As String
If IsNumeric(MinutesValue) Then
    FormatMinutesAsHMM = Format(MinutesValue \ 60, "0") & ":" & Format(MinutesValue Mod 60, "00")
Else
    FormatMinutesAsHMM = ""
End If
End Function
'Parse Minutes
Public Function ParseMinutes(vTime As Variant) As Variant
Dim h As Long, m As Long, aTime As Variant
On Error GoTo ProcErr
  aTime = Split(vTime & "", ":")
  Select Case UBound(aTime)
    Case -1 ' no data entered - return null with no error
      ParseMinutes = Null
    Case 0 ' no colon found
      If Not IsNumeric(aTime(0)) Then Err.Raise 5 ' hours not numeric
      If aTime(0) < 0 Then Err.Raise 5 ' hours negative
      ParseMinutes = CLng(aTime(0) * 60)
    Case 1 'exactly one colon
      If Not IsNumeric(aTime(0)) Then Err.Raise 5 ' hours not numeric
      If aTime(0) <> "" & CLng(aTime(0)) Then Err.Raise 5  ' hours not whole number
      If aTime(0) < 0 Then Err.Raise 5 ' hours negative
      If Not IsNumeric(aTime(1)) Then Err.Raise 5 ' minutes not numeric
      If aTime(1) <> "" & CLng(aTime(1)) Then Err.Raise 5  ' minutes not whole number
      If aTime(1) < 0 Then Err.Raise 5 ' minutes negative
      If aTime(1) >= 60 Then Err.Raise 5 ' minutes 60 or more
      ParseMinutes = aTime(0) * 60 + aTime(1)
    Case Else ' more than one colon
      Err.Raise 5 ' invalid
  End Select
ProcEnd:
  On Error Resume Next
  Exit Function
ProcErr:
  ParseMinutes = Empty
  If Err = 5 Then
    MsgBox "Invalid time", vbExclamation
  Else
    MsgBox Err.Description, vbExclamation
  End If
  Resume ProcEnd
End Function

How can I make this become data entry? My problem is I have the form set as Data Entry=Yes, but this code isn't allowing me to enter data, only allows me to view/parse the data if its already existing. But as you know, with Data Entry=Yes my form loads on a new record so there isn't any data to view yet.

I need this form text box to be setup for data entry. The user will put in either 1:30 or 1.5, and in turn when they hit the save button it will turn that into 90 for the table.

Thank you in advance,
Tom

[ItsMe]

You could display your form in a subform and build custom controls to navigate, edit, addnew, etc.

[ssanfu]

I'm not totally sure what you are attempting... I think you want the control "Me.txtMinutesWorked" to be empty when you save the current data and want to enter a new record.

I am guessing "txtMinutesWorked" is an unbound text box and "MinutesWorked" is a bound control that is hidden.


"Private Sub Form_Current()" doesn't appear to fire when the form is in Data Entry mode.
Comment out "Private Sub Form_Current()" and try adding

Private Sub Form_AfterInsert()
    Me.txtMinutesWorked = ""
End Sub

BTW, your function "ParseMinutes" does not allow an entry of "1:05". This block of code
(I prefer the block form syntax to the single line form)

         If aTime(1) <> "" & CLng(aTime(1)) Then
            Err.Raise 5  ' minutes not whole number
         End If

compares aTime(1) (which ="05") to CLng(aTime(1)) (which = 5) and raises an error.

So the minutes from "01" through "09" will error. (Maybe not a big problem)

[orange]

How about stepping back from the nitty gritty and tell us WHAT all of this represents and how it fits together.

Did you write the vba code? Did you work through any debugging scheme to check the code?
Were you aware of the issues Steve has raised?

[twildt]

Thanks guys, actually my code all of a sudden seems to be working. I am trying to figure out what I did/changed to make it work.

Yes, txtMinutesWorked is an unbound textbox that is on my form.

The form looks something like this:

Name:
Date:
Work Task:
Hours:
Description:

Save and Continue Button:

What I am trying to accomplish is in that "Hours" field. This is the unbound txt box. I want the users to be able to insert 1:30 or 1.5 and have my code convert that to minutes for the table data. The table field is called "MinutesWorked". Its data type is Number.

PS: I only want 15 minute increments and in the format h:nn entered into that unbound text box.

Maybe when I opened up my "Save and Continue" button macro code and closed it and saved it that is what changed. Maybe not. I have no clue. But it's working now...

I do have another issue though now that this is working. If I don't figure it out soon I will start a new post since it has to do with displaying this data and SUMing up the [MinutesWorked] data in a query.

[orange]

Glad you have it working.