Sub to Function

[bymarce]

I have the following sub on a form and I want to convert it to a function so
I can call it from several forms. When I try to do this, it opens the form
but doesn't fill in the value that was not in list. I've tried to move the
NewData and Response declarations down in to the sub but that doesn't work.
Thanks.
Marcie

Private Sub Property_NotInList(NewData As String, Response As Integer)
Dim strSQL As String, x As Integer
x = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If x = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[Stefan Hoffmann]

hi,

I have the following sub on a form and I want to convert it to a function so
I can call it from several forms.

You can also call a Sub form other forms. The key is the access
modifier. Simply change it to

Public Sub Property_NotInList(parameterList)
End Sub

Then you can call it as

Forms("yourLoadedForm").Property_NotInList parameterList

When I try to do this, it opens the form
but doesn't fill in the value that was not in list. I've tried to move the
NewData and Response declarations down in to the sub but that doesn't work.

This makes no senses for me. Can you give us a better, more complete
example?


mfG
--> stefan <--

 

[Dorian]

You cannot convert it to a function.
Call a function from inside the Sub.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[JimBurke via AccessMonster.com]

Are you saying that you have several forms which all have a combo box called
Property that is from the Properties table? If that's the case, I think what
you want is a public function that is in a code module, rhater than in any of
the form modules. You still need the NotInList event in each form obviously.
First, your NotInList event in each form would look something like this:

Private Sub Property_NotInList(NewData As String, Response As Integer)

If AddNewProperty(Property, NewData) then
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Then in a seperate code module something like this:

Public Function AddProperty(byref property as combobox, byval NewData as
string) as boolean
On Error GoTo HandleError

Dim strSQL As String, answer As Integer

answer = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If answer = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
AddProperty = True
Else
AddProperty = False
End If

Exit Function

HandleError:
AddProperty = False
do whatever you want here if there is an error

End Function

I think that's something like what you're looking for if I understand you
correctly. I have no way of testing that code myself.

I have the following sub on a form and I want to convert it to a function so
I can call it from several forms. When I try to do this, it opens the form
but doesn't fill in the value that was not in list. I've tried to move the
NewData and Response declarations down in to the sub but that doesn't work.
Thanks.
Marcie

Private Sub Property_NotInList(NewData As String, Response As Integer)
Dim strSQL As String, x As Integer
x = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If x = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[Steve Sanford]

IMPORTANT!!!!!!

Be aware that "Property" and "Properties" are reserved words in Access and
shouldn't be used as object names.

For a list of reserved words for Access and SQL, see:

http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Are you saying that you have several forms which all have a combo box called
Property that is from the Properties table? If that's the case, I think what
you want is a public function that is in a code module, rhater than in any of
the form modules. You still need the NotInList event in each form obviously.
First, your NotInList event in each form would look something like this:

Private Sub Property_NotInList(NewData As String, Response As Integer)

If AddNewProperty(Property, NewData) then
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Then in a seperate code module something like this:

Public Function AddProperty(byref property as combobox, byval NewData as
string) as boolean
On Error GoTo HandleError

Dim strSQL As String, answer As Integer

answer = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If answer = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
AddProperty = True
Else
AddProperty = False
End If

Exit Function

HandleError:
AddProperty = False
do whatever you want here if there is an error

End Function

I think that's something like what you're looking for if I understand you
correctly. I have no way of testing that code myself.

I have the following sub on a form and I want to convert it to a function so
I can call it from several forms. When I try to do this, it opens the form
but doesn't fill in the value that was not in list. I've tried to move the
NewData and Response declarations down in to the sub but that doesn't work.
Thanks.
Marcie

Private Sub Property_NotInList(NewData As String, Response As Integer)
Dim strSQL As String, x As Integer
x = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If x = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

 

[bymarce]

Thanks!! That's exactly what I'm trying to do and it worked!

Are you saying that you have several forms which all have a combo box called
Property that is from the Properties table? If that's the case, I think what
you want is a public function that is in a code module, rhater than in any of
the form modules. You still need the NotInList event in each form obviously.
First, your NotInList event in each form would look something like this:

Private Sub Property_NotInList(NewData As String, Response As Integer)

If AddNewProperty(Property, NewData) then
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Then in a seperate code module something like this:

Public Function AddProperty(byref property as combobox, byval NewData as
string) as boolean
On Error GoTo HandleError

Dim strSQL As String, answer As Integer

answer = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If answer = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
AddProperty = True
Else
AddProperty = False
End If

Exit Function

HandleError:
AddProperty = False
do whatever you want here if there is an error

End Function

I think that's something like what you're looking for if I understand you
correctly. I have no way of testing that code myself.

I have the following sub on a form and I want to convert it to a function so
I can call it from several forms. When I try to do this, it opens the form
but doesn't fill in the value that was not in list. I've tried to move the
NewData and Response declarations down in to the sub but that doesn't work.
Thanks.
Marcie

Private Sub Property_NotInList(NewData As String, Response As Integer)
Dim strSQL As String, x As Integer
x = MsgBox("Do you want to add this Property to the list?", vbYesNo)
If x = vbYes Then
strSQL = "Insert Into Properties ([Property]) values ('" & NewData &
"')"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub