Passing value from form into public function

G

Greg

I have a public function where I am calculating time difference. Function is
working ok however I would like to display critical message after specific
time difference displaying critical message along with ticket number that
needs update

My function:
Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Ticket: " & " " & [ticket#]
...............

MsgBox "Please update Issue: ", vbCritical, "Time for Update "


How can I pass correct ticket number to be display in this message.

With line: smsg = " Please update Ticket: " & " " & [ticket#]
I am getting error message [ticket#] -- External name no defined

I tried me.[ticket#] but i get invalid use of me keyword

Thanks
 
T

Tom Wickerath

Hi Greg,
I tried me.[ticket#] but i get invalid use of me keyword
Click to expand...

The Me keyword is only valid in class modules associated with forms and
reports. You cannot use this keyword in stand-alone modules. You need to pass
the Ticket number into the procedure as a parameter. Something like this:

Option Compare Database
Option Explicit

Public Function NotifyUser(TicketNo As String) As Boolean
On Error GoTo ProcError

Dim smsg As String

smsg = " Please update Ticket: " & TicketNo

MsgBox smsg, vbCritical, "Time for Update "
NotifyUser = True

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure NotifyUser..."
NotifyUser = False
Resume ExitProc
End Function


Try it from the Immediate Window, which you can open by pressing <Ctrl><G>:
Enter the following command, and then press the <Enter> key:

notifyuser "123456"

If your TicketNo field is numeric, then make the appropriate substitution in
the parameter typecast. Something like:

Public Function NotifyUser(TicketNo As Integer) As Boolean
or
Public Function NotifyUser(TicketNo As Long) As Boolean

and test in the Immediated Window in a similar manner, except without
enclosing the parameter in quotes: notifyuser 123456

You can call this function from code in a form's class module, like this:

NotifyUser "123456" or
Call NotifyUser("123456")


Note that in this line:
smsg = " Please update Ticket: " & " " & [ticket#]

You could have used this, instead, where an extra space is added after the
colon:
smsg = " Please update Ticket: " & [ticket#]

Also, I'd like to encourage you to not use special characters, such as the #
sign, in the names of anything that you assign a name to in Access:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Greg said:
I have a public function where I am calculating time difference. Function is
working ok however I would like to display critical message after specific
time difference displaying critical message along with ticket number that
needs update

My function:
Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Ticket: " & " " & [ticket#]
..............

MsgBox "Please update Issue: ", vbCritical, "Time for Update "


How can I pass correct ticket number to be display in this message.

With line: smsg = " Please update Ticket: " & " " & [ticket#]
I am getting error message [ticket#] -- External name no defined

I tried me.[ticket#] but i get invalid use of me keyword

Thanks
Click to expand...
 
G

Greg

Tom thanks a lot for your response however I am testing condition in this
module and I want to display this message as a reminder only if specific
condition is met ( i my case if there is no update in 1 hour. Here is my
function:

Public Function ElapsedTimeString(Date1 As Date, _
Date2 As Date) _
As String

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Remedy Ticket: "

If IsNull(Date1) = True Or _
IsNull(Date2) = True Then Exit Function

interval = Date2 - Date1
days = Fix(CSng(interval))
hours = Format(interval, "h")
Minutes = Format(interval, "n")
seconds = Format(interval, "s")

str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(Minutes & seconds <> "00", ", ", " "))

str = str & IIf(Minutes = "0", "", _
IIf(Minutes = "1", Minutes & " Minute", _
Minutes & " Minutes"))

If seconds > 30 Then Minutes = Minutes + 1
If Minutes > 59 Then hours = hours + 1: Minutes = 0
If hours >= 1 Then
MsgBox "Please update Remedy Ticket: ", vbCritical, "Time to Update Remedy"
ElapsedTimeString = IIf(str = "", "0", str)
End If
ElapsedTimeString = IIf(str = "", "0", str)
End Function


Any ideas how i can display Ticket #? Thanks

Tom Wickerath said:
Hi Greg,
I tried me.[ticket#] but i get invalid use of me keyword
Click to expand...

The Me keyword is only valid in class modules associated with forms and
reports. You cannot use this keyword in stand-alone modules. You need to pass
the Ticket number into the procedure as a parameter. Something like this:

Option Compare Database
Option Explicit

Public Function NotifyUser(TicketNo As String) As Boolean
On Error GoTo ProcError

Dim smsg As String

smsg = " Please update Ticket: " & TicketNo

MsgBox smsg, vbCritical, "Time for Update "
NotifyUser = True

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure NotifyUser..."
NotifyUser = False
Resume ExitProc
End Function


Try it from the Immediate Window, which you can open by pressing <Ctrl><G>:
Enter the following command, and then press the <Enter> key:

notifyuser "123456"

If your TicketNo field is numeric, then make the appropriate substitution in
the parameter typecast. Something like:

Public Function NotifyUser(TicketNo As Integer) As Boolean
or
Public Function NotifyUser(TicketNo As Long) As Boolean

and test in the Immediated Window in a similar manner, except without
enclosing the parameter in quotes: notifyuser 123456

You can call this function from code in a form's class module, like this:

NotifyUser "123456" or
Call NotifyUser("123456")


Note that in this line:
smsg = " Please update Ticket: " & " " & [ticket#]

You could have used this, instead, where an extra space is added after the
colon:
smsg = " Please update Ticket: " & [ticket#]

Also, I'd like to encourage you to not use special characters, such as the #
sign, in the names of anything that you assign a name to in Access:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Greg said:
I have a public function where I am calculating time difference. Function is
working ok however I would like to display critical message after specific
time difference displaying critical message along with ticket number that
needs update

My function:
Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Ticket: " & " " & [ticket#]
..............

MsgBox "Please update Issue: ", vbCritical, "Time for Update "


How can I pass correct ticket number to be display in this message.

With line: smsg = " Please update Ticket: " & " " & [ticket#]
I am getting error message [ticket#] -- External name no defined

I tried me.[ticket#] but i get invalid use of me keyword

Thanks
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Mark,
Any ideas how i can display Ticket #?
Click to expand...

This is just a quickie answer, since I need to leave for an Access group
meeting in a couple of minutes.

You need to somehow make the function aware of the Ticket number, whether by
passing it in as an optional parameter (as the example in my previous reply
shows), or using a domain aggregrate function, such as DLookup, to grab this
value, or perhaps opening up a recordset to read this value.

Where is this information currently available? Is it in a table?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


Greg said:
Tom thanks a lot for your response however I am testing condition in this
module and I want to display this message as a reminder only if specific
condition is met ( i my case if there is no update in 1 hour. Here is my
function:

Public Function ElapsedTimeString(Date1 As Date, _
Date2 As Date) _
As String

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Remedy Ticket: "

If IsNull(Date1) = True Or _
IsNull(Date2) = True Then Exit Function

interval = Date2 - Date1
days = Fix(CSng(interval))
hours = Format(interval, "h")
Minutes = Format(interval, "n")
seconds = Format(interval, "s")

str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(Minutes & seconds <> "00", ", ", " "))

str = str & IIf(Minutes = "0", "", _
IIf(Minutes = "1", Minutes & " Minute", _
Minutes & " Minutes"))

If seconds > 30 Then Minutes = Minutes + 1
If Minutes > 59 Then hours = hours + 1: Minutes = 0
If hours >= 1 Then
MsgBox "Please update Remedy Ticket: ", vbCritical, "Time to Update Remedy"
ElapsedTimeString = IIf(str = "", "0", str)
End If
ElapsedTimeString = IIf(str = "", "0", str)
End Function


Any ideas how i can display Ticket #? Thanks

Tom Wickerath said:
Hi Greg,
I tried me.[ticket#] but i get invalid use of me keyword
Click to expand...

The Me keyword is only valid in class modules associated with forms and
reports. You cannot use this keyword in stand-alone modules. You need to pass
the Ticket number into the procedure as a parameter. Something like this:

Option Compare Database
Option Explicit

Public Function NotifyUser(TicketNo As String) As Boolean
On Error GoTo ProcError

Dim smsg As String

smsg = " Please update Ticket: " & TicketNo

MsgBox smsg, vbCritical, "Time for Update "
NotifyUser = True

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure NotifyUser..."
NotifyUser = False
Resume ExitProc
End Function


Try it from the Immediate Window, which you can open by pressing <Ctrl><G>:
Enter the following command, and then press the <Enter> key:

notifyuser "123456"

If your TicketNo field is numeric, then make the appropriate substitution in
the parameter typecast. Something like:

Public Function NotifyUser(TicketNo As Integer) As Boolean
or
Public Function NotifyUser(TicketNo As Long) As Boolean

and test in the Immediated Window in a similar manner, except without
enclosing the parameter in quotes: notifyuser 123456

You can call this function from code in a form's class module, like this:

NotifyUser "123456" or
Call NotifyUser("123456")


Note that in this line:
smsg = " Please update Ticket: " & " " & [ticket#]

You could have used this, instead, where an extra space is added after the
colon:
smsg = " Please update Ticket: " & [ticket#]

Also, I'd like to encourage you to not use special characters, such as the #
sign, in the names of anything that you assign a name to in Access:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Greg said:
I have a public function where I am calculating time difference. Function is
working ok however I would like to display critical message after specific
time difference displaying critical message along with ticket number that
needs update

My function:
Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Ticket: " & " " & [ticket#]
..............

MsgBox "Please update Issue: ", vbCritical, "Time for Update "


How can I pass correct ticket number to be display in this message.

With line: smsg = " Please update Ticket: " & " " & [ticket#]
I am getting error message [ticket#] -- External name no defined

I tried me.[ticket#] but i get invalid use of me keyword

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Greg

Tom thanks a lot

varX = DLookup("[Ticket Description]", "Remedy Tickets", "[Ticket #] =" _
& Forms![Remedy Tickets]![Remedy Tickets Table subform]![Ticket #])
smsg = " Please Update Ticket # --- " & varX
MsgBox smsg, vbCritical, "Time to Update Remedy Ticket"

It is working.

Tom Wickerath said:
Hi Mark,
Any ideas how i can display Ticket #?
Click to expand...

This is just a quickie answer, since I need to leave for an Access group
meeting in a couple of minutes.

You need to somehow make the function aware of the Ticket number, whether by
passing it in as an optional parameter (as the example in my previous reply
shows), or using a domain aggregrate function, such as DLookup, to grab this
value, or perhaps opening up a recordset to read this value.

Where is this information currently available? Is it in a table?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


Greg said:
Tom thanks a lot for your response however I am testing condition in this
module and I want to display this message as a reminder only if specific
condition is met ( i my case if there is no update in 1 hour. Here is my
function:

Public Function ElapsedTimeString(Date1 As Date, _
Date2 As Date) _
As String

Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Remedy Ticket: "

If IsNull(Date1) = True Or _
IsNull(Date2) = True Then Exit Function

interval = Date2 - Date1
days = Fix(CSng(interval))
hours = Format(interval, "h")
Minutes = Format(interval, "n")
seconds = Format(interval, "s")

str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(Minutes & seconds <> "00", ", ", " "))

str = str & IIf(Minutes = "0", "", _
IIf(Minutes = "1", Minutes & " Minute", _
Minutes & " Minutes"))

If seconds > 30 Then Minutes = Minutes + 1
If Minutes > 59 Then hours = hours + 1: Minutes = 0
If hours >= 1 Then
MsgBox "Please update Remedy Ticket: ", vbCritical, "Time to Update Remedy"
ElapsedTimeString = IIf(str = "", "0", str)
End If
ElapsedTimeString = IIf(str = "", "0", str)
End Function


Any ideas how i can display Ticket #? Thanks

Tom Wickerath said:
Hi Greg,

I tried me.[ticket#] but i get invalid use of me keyword

The Me keyword is only valid in class modules associated with forms and
reports. You cannot use this keyword in stand-alone modules. You need to pass
the Ticket number into the procedure as a parameter. Something like this:

Option Compare Database
Option Explicit

Public Function NotifyUser(TicketNo As String) As Boolean
On Error GoTo ProcError

Dim smsg As String

smsg = " Please update Ticket: " & TicketNo

MsgBox smsg, vbCritical, "Time for Update "
NotifyUser = True

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure NotifyUser..."
NotifyUser = False
Resume ExitProc
End Function


Try it from the Immediate Window, which you can open by pressing <Ctrl><G>:
Enter the following command, and then press the <Enter> key:

notifyuser "123456"

If your TicketNo field is numeric, then make the appropriate substitution in
the parameter typecast. Something like:

Public Function NotifyUser(TicketNo As Integer) As Boolean
or
Public Function NotifyUser(TicketNo As Long) As Boolean

and test in the Immediated Window in a similar manner, except without
enclosing the parameter in quotes: notifyuser 123456

You can call this function from code in a form's class module, like this:

NotifyUser "123456" or
Call NotifyUser("123456")


Note that in this line:
smsg = " Please update Ticket: " & " " & [ticket#]

You could have used this, instead, where an extra space is added after the
colon:
smsg = " Please update Ticket: " & [ticket#]

Also, I'd like to encourage you to not use special characters, such as the #
sign, in the names of anything that you assign a name to in Access:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a public function where I am calculating time difference. Function is
working ok however I would like to display critical message after specific
time difference displaying critical message along with ticket number that
needs update

My function:
Dim interval As Double, str As String, days As Variant
Dim hours As String, Minutes As String, seconds As String
Dim smsg As String
smsg = " Please update Ticket: " & " " & [ticket#]
..............

MsgBox "Please update Issue: ", vbCritical, "Time for Update "


How can I pass correct ticket number to be display in this message.

With line: smsg = " Please update Ticket: " & " " & [ticket#]
I am getting error message [ticket#] -- External name no defined

I tried me.[ticket#] but i get invalid use of me keyword

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time difference and If function 4
Refreshing main form from the subform 1
Populating main form after changing selection 0
Move local File to a shared drive,this not work on 2 & 3 athattach 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Problems with code 11
Invalid use of New Keyword 1
Unable to send email in HTML format 1

Top