Cannot Call Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon
I'm working on a Maintenance log and have a switchboard/dashboard (more information than commands). One of my tabbed pages is PMs coming up. Well, I have a GO button that calls up the "Assign PM to Technician" form. THAT form isn't running properly when SUBMIT is clicked. Can someone help? I'll explain in comments intertwined. Thank you in advance

Public Sub Submit_Click(
' This section sets up the AREA ID (Building, Plumbing, Storage, Mobile Equipment, Conveyor, etc - based on what the
' Equipment type is
Dim strare
strarea = DLookup("[Area]", "tblEquipment", "TypeDesignator = '" & Left(Forms!frmPMs2WOs.Loc, 1) & "'"

' This section creates the request record in the Request Table and assigns it a value and technician from the form
' Unfortunately, classey(), the function called gives me an error message of 'Undefined function "Classey" in expression'
' The function can be found after the END SUB statement
DoCmd.RunSQL "Insert into tblrequests (classe, WOID, loc, act, division, reqstatus, assignedto, AssTS, PMDate, TimeSt) values (classey(),right(Forms!frmPMs2WOs.PMID,5), Forms!frmPMs2WOs.loc, 'PM', Forms!frmPMs2WOs.divn, 'Assigned', Forms!frmPMs2WOs.Technician,now(),date(),now())
End Su

Public Function classey() as strin
classey = DLast("[AreaDesc]", "tblrequests", "loc = Forms!frmPMs2WOs.loc"
End Functio

Thank you for your help
Derek
 
Derek Wittman said:
Good afternoon,
I'm working on a Maintenance log and have a switchboard/dashboard
(more information than commands). One of my tabbed pages is PMs
coming up. Well, I have a GO button that calls up the "Assign PM to
Technician" form. THAT form isn't running properly when SUBMIT is
clicked. Can someone help? I'll explain in comments intertwined.
Thank you in advance!

Public Sub Submit_Click()
' This section sets up the AREA ID (Building, Plumbing, Storage,
Mobile Equipment, Conveyor, etc - based on what the ' Equipment
type is.
Dim strarea
strarea = DLookup("[Area]", "tblEquipment", "TypeDesignator = '" &
Left(Forms!frmPMs2WOs.Loc, 1) & "'")

' This section creates the request record in the Request Table
and assigns it a value and technician from the form. '
Unfortunately, classey(), the function called gives me an error
message of 'Undefined function "Classey" in expression'. ' The
function can be found after the END SUB statement.
DoCmd.RunSQL "Insert into tblrequests (classe, WOID, loc, act,
division, reqstatus, assignedto, AssTS, PMDate, TimeSt) values
(classey(),right(Forms!frmPMs2WOs.PMID,5), Forms!frmPMs2WOs.loc,
'PM', Forms!frmPMs2WOs.divn, 'Assigned',
Forms!frmPMs2WOs.Technician,now(),date(),now())"
End Sub


Public Function classey() as string
classey = DLast("[AreaDesc]", "tblrequests", "loc =
Forms!frmPMs2WOs.loc")
End Function

Thank you for your help!
Derek
Click to expand...

Where is the function defined? It must be in a standard module, not the
form's module, and the name of the module must not be the same as the
name of the function.
 
I think the syntax should be:

classey = DLast("[AreaDesc]", "tblrequests", "loc = '" &
Forms!frmPMs2WOs.loc & "'")

-----Original Message-----
Good afternoon,
I'm working on a Maintenance log and have a
Click to expand...
switchboard/dashboard (more information than commands).
One of my tabbed pages is PMs coming up. Well, I have a
GO button that calls up the "Assign PM to Technician"
form. THAT form isn't running properly when SUBMIT is
clicked. Can someone help? I'll explain in comments
intertwined. Thank you in advance!
Public Sub Submit_Click()
' This section sets up the AREA ID (Building,
Click to expand...
Plumbing, Storage, Mobile Equipment, Conveyor, etc -
based on what the
' Equipment type is.
Dim strarea
strarea = DLookup
Click to expand...
("[Area]", "tblEquipment", "TypeDesignator = '" & Left
(Forms!frmPMs2WOs.Loc, 1) & "'")
' This section creates the request record in the
Click to expand...
Request Table and assigns it a value and technician from
the form.
' Unfortunately, classey(), the function called
Click to expand...
gives me an error message of 'Undefined
function "Classey" in expression'.
' The function can be found after the END SUB statement.
DoCmd.RunSQL "Insert into tblrequests (classe, WOID,
Click to expand...
loc, act, division, reqstatus, assignedto, AssTS, PMDate,
TimeSt) values (classey(),right(Forms!frmPMs2WOs.PMID,5),
Forms!frmPMs2WOs.loc, 'PM', Forms!
frmPMs2WOs.divn, 'Assigned', Forms!
frmPMs2WOs.Technician,now(),date(),now())"
End Sub


Public Function classey() as string
classey = DLast("[AreaDesc]", "tblrequests", "loc = Forms!frmPMs2WOs.loc")
End Function

Thank you for your help!
Derek
.
Click to expand...
 
Derek Wittman said:
Thanks, Dirk. I've tried from a specific module as well as from the
form's code and got the same result. Now, I have it from a separate
module. I know it shouldn't be as hard as I am making it.

Thanks again!
Derek

----- Dirk Goldgar wrote: -----

Derek Wittman said:
Good afternoon,
I'm working on a Maintenance log and have a
Click to expand...
switchboard/dashboard > (more information than commands). One
of my tabbed pages is PMs > coming up. Well, I have a GO button
that calls up the "Assign PM to > Technician" form. THAT form
isn't running properly when SUBMIT is > clicked. Can someone
help? I'll explain in comments intertwined. > Thank you in
advance! >> Public Sub Submit_Click()
' This section sets up the AREA ID (Building, Plumbing,
Click to expand...
Storage, > Mobile Equipment, Conveyor, etc - based on what the '
Equipment > type is.
Dim strarea
strarea = DLookup("[Area]", "tblEquipment", "TypeDesignator =
Click to expand...
'" &> Left(Forms!frmPMs2WOs.Loc, 1) & "'") >> ' This section
creates the request record in the Request Table > and assigns it
a value and technician from the form. ' > Unfortunately,
classey(), the function called gives me an error > message of
'Undefined function "Classey" in expression'. ' The >
function can be found after the END SUB statement. >
DoCmd.RunSQL "Insert into tblrequests (classe, WOID, loc, act, >
division, reqstatus, assignedto, AssTS, PMDate, TimeSt) values >
(classey(),right(Forms!frmPMs2WOs.PMID,5), Forms!frmPMs2WOs.loc,
'PM', Forms!frmPMs2WOs.divn, 'Assigned', >
Click to expand...
Forms!frmPMs2WOs.Technician,now(),date(),now())" > End Sub
Public Function classey() as string
Click to expand...
classey = DLast("[AreaDesc]", "tblrequests", "loc =
Forms!frmPMs2WOs.loc")
End Function
Thank you for your help!
Click to expand...
Derek
Click to expand...

Where is the function defined? It must be in a standard module,
not the form's module, and the name of the module must not be
the same as the name of the function.
Click to expand...

Have you verified that the function itself works? With frmPMs2WOs open,
if you press Ctrl+G and enter this in the Immediate Window ...

?classey()

.... do you get the correct result?
 
It seems to me that the value generated by your classey()
function will be the same for all records generated by
your SQL statement, so rather than run it for each
appended record, just run it once and place the value in
your statement (I've done the same with your object
references).

eg)

Dim frm as Form
Dim strClasse as String, strWOID as String
Dim strLoc as String, strDivision as String
Dim strAssignedTo as String
Set frm = Forms!frmPMs2WOs
strClasse = classey()
strWOID = Right(frm!PMID,5)
strLoc = frm!loc
strDivision = frm!divn
strAssignedTo = frm!Technician
strSQL = "INSERT INTO tblRequests (" _
& "classe, WOID, loc, act, division, reqstatus, " _
& "assignedto, AssTS, PMDate, Timest) " _
& "VALUES('" & strClasse & "','" & strWOID & "','" _
& strLoc & "'PM','" & strDivision & "','Assigned','" _
& strAssignedTo & "',Now(),Date(),Now())"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

To specifically answer your question, your Classey()
function needs to be in a separate module, other than the
form's module, to be available to queries. Once you got
past that though I'm pretty sure you'd hit the 'Too few
parameters' error where you'd need to create a temporary
QueryDef object to run the query and specify its
parameters prior to executing it. It's easier to do what
I've outlined above. Good luck :)
-----Original Message-----
Good afternoon,
I'm working on a Maintenance log and have a
Click to expand...
switchboard/dashboard (more information than commands).
One of my tabbed pages is PMs coming up. Well, I have a
GO button that calls up the "Assign PM to Technician"
form. THAT form isn't running properly when SUBMIT is
clicked. Can someone help? I'll explain in comments
intertwined. Thank you in advance!
Public Sub Submit_Click()
' This section sets up the AREA ID (Building,
Click to expand...
Plumbing, Storage, Mobile Equipment, Conveyor, etc - based
on what the
' Equipment type is.
Dim strarea
strarea = DLookup
Click to expand...
("[Area]", "tblEquipment", "TypeDesignator = '" & Left
(Forms!frmPMs2WOs.Loc, 1) & "'")
' This section creates the request record in the
Click to expand...
Request Table and assigns it a value and technician from
the form.
' Unfortunately, classey(), the function called gives
Click to expand...
me an error message of 'Undefined function "Classey" in
expression'.
' The function can be found after the END SUB statement.
DoCmd.RunSQL "Insert into tblrequests (classe, WOID, loc,
Click to expand...
act, division, reqstatus, assignedto, AssTS, PMDate,
TimeSt) values (classey(),right(Forms!frmPMs2WOs.PMID,5),
Forms!frmPMs2WOs.loc, 'PM', Forms!
frmPMs2WOs.divn, 'Assigned', Forms!
frmPMs2WOs.Technician,now(),date(),now())"
End Sub


Public Function classey() as string
classey = DLast("[AreaDesc]", "tblrequests", "loc = Forms! frmPMs2WOs.loc")
End Function

Thank you for your help!
Derek
.
Click to expand...
 
Elwin said:
It seems to me that the value generated by your classey()
function will be the same for all records generated by
your SQL statement, so rather than run it for each
appended record, just run it once and place the value in
your statement (I've done the same with your object
references).

eg)

Dim frm as Form
Dim strClasse as String, strWOID as String
Dim strLoc as String, strDivision as String
Dim strAssignedTo as String
Set frm = Forms!frmPMs2WOs
strClasse = classey()
strWOID = Right(frm!PMID,5)
strLoc = frm!loc
strDivision = frm!divn
strAssignedTo = frm!Technician
strSQL = "INSERT INTO tblRequests (" _
& "classe, WOID, loc, act, division, reqstatus, " _
& "assignedto, AssTS, PMDate, Timest) " _
& "VALUES('" & strClasse & "','" & strWOID & "','" _
& strLoc & "'PM','" & strDivision & "','Assigned','" _
& strAssignedTo & "',Now(),Date(),Now())"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

To specifically answer your question, your Classey()
function needs to be in a separate module, other than the
form's module, to be available to queries. Once you got
past that though I'm pretty sure you'd hit the 'Too few
parameters' error where you'd need to create a temporary
QueryDef object to run the query and specify its
parameters prior to executing it. It's easier to do what
I've outlined above. Good luck :)
Click to expand...

No, I don't think he'll have that problem using RunSQL -- that comes
from using the DAO Execute method, but the Access RunSQL method resolves
form references in the query string automatically. I also don't think
that a function expression that doesn't involve any of the fields in the
query will be called for every record; generally, such invariant
expressions are evaluated only once, and the result is substituted in
every record.

I don't disagree with your recommended revision, though -- that's the
way I'd do it, except that I'd use CurrentDb.Execute with the SQL
string, instead of RunSQL. But I don't think Derek's code is going to
be as inefficient as you think. There must be some reason the classey()
function isn't being recognized, though.
 
Also, the Insert statement should look like:

DoCmd.RunSQL "Insert into tblrequests (classe, WOID,
loc, act, division, reqstatus, assignedto, AssTS, PMDate,
TimeSt) values ('" & classey() & "',right(Forms!
frmPMs2WOs.PMID,5),
Forms!frmPMs2WOs.loc, 'PM', Forms!
frmPMs2WOs.divn, 'Assigned', Forms!
frmPMs2WOs.Technician,now(),date(),now())"


Chris Nebinger

-----Original Message-----
I think the syntax should be:

classey = DLast("[AreaDesc]", "tblrequests", "loc = '" &
Forms!frmPMs2WOs.loc & "'")

-----Original Message-----
Good afternoon,
I'm working on a Maintenance log and have a
Click to expand...
switchboard/dashboard (more information than commands).
One of my tabbed pages is PMs coming up. Well, I have a
GO button that calls up the "Assign PM to Technician"
form. THAT form isn't running properly when SUBMIT is
clicked. Can someone help? I'll explain in comments
intertwined. Thank you in advance!
Public Sub Submit_Click()
' This section sets up the AREA ID (Building,
Click to expand...
Plumbing, Storage, Mobile Equipment, Conveyor, etc -
based on what the
' Equipment type is.
Dim strarea
strarea = DLookup
Click to expand...
("[Area]", "tblEquipment", "TypeDesignator = '" & Left
(Forms!frmPMs2WOs.Loc, 1) & "'")
' This section creates the request record in the
Click to expand...
Request Table and assigns it a value and technician from
the form.
' Unfortunately, classey(), the function called
Click to expand...
gives me an error message of 'Undefined
function "Classey" in expression'.
' The function can be found after the END SUB statement.
DoCmd.RunSQL "Insert into tblrequests (classe, WOID,
Click to expand...
loc, act, division, reqstatus, assignedto, AssTS, PMDate,
TimeSt) values (classey(),right(Forms!frmPMs2WOs.PMID,5),
Forms!frmPMs2WOs.loc, 'PM', Forms!
frmPMs2WOs.divn, 'Assigned', Forms!
frmPMs2WOs.Technician,now(),date(),now())"
End Sub


Public Function classey() as string
classey = DLast("[AreaDesc]", "tblrequests", "loc = Forms!frmPMs2WOs.loc")
End Function

Thank you for your help!
Derek
.
Click to expand...
.
Click to expand...
 
Wow... sometimes I get no response whatsoever, and sometimes (like this), it seems everyone's got some suggestions (which I REALLY appreciate). I had a few errors going on simultaneously - unfortunately, run-time errors don't provide a LIST, just one at a time. Putting the function in a separate module was one of the key ingredients I needed. Complete success has been achieved on this function (thanks to all!). I also will certainly start using variables to point to my "collection.object.control"s - if nothing else, it will tidy up my SQL statements. I had thought about that yesterday on the way home from the office. Not only will it neaten it up, but it would coincide with commenting and make the code readable to my successor (whenever that happens)

Further, I really only want to insert one line at a time. The form's module I'm working on is to convert a scheduled Preventative Maintenance on the 'board' to an actual Work Order assigned to a technician. I really only want to do one at a time. Besides, any logic I include for multiple assignments may prove to be buggy with some of the folks who end up using this thing. Some would think before they clicked anything. Some are of the mindset (?) of ready, shoot, aim..

Thanks again
Dere

----- Dirk Goldgar wrote: ----

Elwin said:
It seems to me that the value generated by your classey(
function will be the same for all records generated b
your SQL statement, so rather than run it for eac
appended record, just run it once and place the value i
your statement (I've done the same with your objec
references)
Dim strClasse as String, strWOID as Strin
Dim strLoc as String, strDivision as Strin
Dim strAssignedTo as Strin
Set frm = Forms!frmPMs2WO
strClasse = classey(
strWOID = Right(frm!PMID,5
strLoc = frm!lo
strDivision = frm!div
strAssignedTo = frm!Technicia
strSQL = "INSERT INTO tblRequests ("
& "classe, WOID, loc, act, division, reqstatus, "
& "assignedto, AssTS, PMDate, Timest) "
& "VALUES('" & strClasse & "','" & strWOID & "','"
& strLoc & "'PM','" & strDivision & "','Assigned','"
& strAssignedTo & "',Now(),Date(),Now())
DoCmd.SetWarnings Fals
DoCmd.RunSQL strSQ
DoCmd.SetWarnings Tru
function needs to be in a separate module, other than th
form's module, to be available to queries. Once you go
past that though I'm pretty sure you'd hit the 'Too fe
parameters' error where you'd need to create a temporar
QueryDef object to run the query and specify it
parameters prior to executing it. It's easier to do wha
I've outlined above. Good luck :
Click to expand...

No, I don't think he'll have that problem using RunSQL -- that come
from using the DAO Execute method, but the Access RunSQL method resolve
form references in the query string automatically. I also don't thin
that a function expression that doesn't involve any of the fields in th
query will be called for every record; generally, such invarian
expressions are evaluated only once, and the result is substituted i
every record

I don't disagree with your recommended revision, though -- that's th
way I'd do it, except that I'd use CurrentDb.Execute with the SQ
string, instead of RunSQL. But I don't think Derek's code is going t
be as inefficient as you think. There must be some reason the classey(
function isn't being recognized, though
 
Back
Top