Function to pass value to text box

[vttech]

Option Compare Database
Option Explicit
Dim RawTime As String
Dim RawHour As String
Dim RawMinutes As String
Dim AMPM As String
Dim RealTime As Variant


Private Sub cmdOK_Click()

RawHour = Forms!frmRealTime!cboHour
RawMinutes = Forms!frmRealTime!cboMinutes
AMPM = Forms!frmRealTime!cboAMPM

RealTime = TimeValue(RawHour & ":" & RawMinutes & AMPM)

Debug.Print RealTime

End Sub

I created a Form that has three combo boxes

cboHour Row Source 1;2;3;4;5;6;7;8;9;10;11;12
cboMinutes Row Source 00;15;30;45
cboAMPM AM;PM


I want to create a function that will pass the Real Time value to a text box

example:

a text box named txtStartTime

and lets say the function name is fRealTime()

So that a user can for example place the statement below in the On_Click event
=fRealTime([txtStartDate])

Need some thoughts/ideas how to accomplish this




Newbie in search of knowledge

 

[CaptainD]

Do you understand how functions work?

You pass in information and it does what you want to that information and passes it back. You set the datatype for what goes in as well as the datatype that is passed back.

Here is an example:

Private Function dblReturnValue(iNumberToMultiply as Integer) as Double
    '"As Double" sets the datatype for dblReturnValue
    'Now Work with the information passed in, in this case  iNumberToMultiply

dblReturnValue = iNumberToMultiply * 4
End Function

To use the function, put this in a click event for a command button:

Dim iSomeNumber as Integer
iSomeNumber = txtTextBox1.Text
If iSomeNumber = 0 Then 
  MsgBox "Need a number", vbOKonly
  txtTextBox1.SetFocus
  Exit Sub
Else
  txtTextBox2.Text = dblReturnValue(iSomeNumber)
End If

txtTextBox2 should now show the value in txtTextBox1 * 4

 

[MajP]

Public Function RealTime(intRawHour As Integer, intRawMinutes As Integer, strAMPM As String) As Date
  RealTime = TimeValue(intRawHour & ":" & intRawMinutes & strAMPM)
End Function

=RealTime([cboHours],[cboMinutes],[cboAMPM])

 

[vttech]

OK I am basically trying to wrap a form in a function

Standard Module Named DisplayTime

Public CompleteTime As Variant

Public Function GetTime() As Variant

Dim strRawHour As String
Dim strRawMinutes As String
Dim strAMPM As String

DoCmd.OpenForm "frmGetTime", , , , , acDialog


CompleteTime = TimeValue(Forms!frmGetTime!cboHour & ":" & Forms!frmGetTime!cboMinutes & Forms!frmGetTime!cboAMPM)

DoCmd.Close acForm, "frmGetTime"

GetTime = CompleteTime

End Function

frmGetTime

Private Sub cmdOK_Click()

CompleteTime = GetTime()

Debug.Print CompleteTime

End Sub

But know I get the error
"Run-Time error '2450'

Microsoft Office Access can't find the form 'frmGetTime' referred to in a macro expression or visual basic code


But the Debug.Print CompleteTime is showing the correct time chosed in the immediate window


what am I doing wrong??

Newbie in search of knowledge

 

[MajP]

This does not do anything

DoCmd.Close acForm, "frmGetTime"

Since it is in dialog, code execution stops until you close the form. The form has to be already closed before you tell it to close again. That is probably the error. You are telling it to close a closed form.

 

[vttech]

I tried commenting out

DoCmd.Close acForm, "frmGetTime"

but I still get the error when I X out of "frmGetTime"

Newbie in search of knowledge

 

[MajP]

Where does the code break? That would help. I can not see any other problems.

 

[vttech]

The code it breaking when ever I try to close the dialog box

I basically have no way of close the "frmGetTIme" form without getting that error

Newbie in search of knowledge

 

[adalger]

Public CompleteTime As Variant

Public Function GetTime() As Variant

Dim strRawHour As String
Dim strRawMinutes As String
Dim strAMPM As String

DoCmd.OpenForm "frmGetTime", , , , , acDialog

At this point, code stops until you close your dialog.

CompleteTime = TimeValue(Forms!frmGetTime!cboHour & ":" & Forms!frmGetTime!cboMinutes & Forms!frmGetTime!cboAMPM)

You now refer to controls on the dialog you just closed.

 

[MajP]

Public CompleteTime As Variant

Public Function GetTime() As Variant
DoCmd.OpenForm "frmGetTime", , , , , acDialog
GetTime = CompleteTime
End Function

From the form

private form_close()
CompleteTime = TimeValue(Me.cboHour & ":" & Me.boMinutes &
me.cboAMPM)
end sub

 

[vttech]

I changed up the code to

Public CompleteTime As Date



Public Function GetTime(txtbox As Control) As Date

DoCmd.OpenForm "frmGetTime", , , , , acDialog

txtbox = CompleteTime

End Function

Private Sub cmdOK_Click()

CompleteTime = Forms!frmGetTime!cboHour & ":" & Forms!frmGetTime!cboMinutes & Forms!frmGetTime!cboAMPM

DoCmd.Close acForm, "frmGetTime"

Debug.Print CompleteTime

End Sub

This worked great but know I have a formatting issue, I am trying to figure out.

Instead of getting results like 7:15:00 PM
How do I just get 7:15 PM?





Newbie in search of knowledge

 

[MajP]

You can format it
format(completetime,"Medium Date")
This is now a string, no longer a date.