sql and public functions

[greg]

Would someone be able to help with this please?

I am trying to create a public function that returns the
value of an sql statement. I have created a module
called "basUtilities" and I want to place a public
function in this module "NextNum()" which returns the
value of the sql statement below. I want to call this
function from a macro using setvalue. I have a
form "frmPOType" which is has the combo box named "Type".
The form is based on the table "tblPOType". There are two
fields in this table, "fldPOType" and "fldNextNum"

What I am wanting to see happen is: When someone opens
the form, selects the POType, then executes the macro, the
function would then retrieve the selected Type from the
form, then concante with equivilent fldNextNum from the
table tblPOType, and return that value to be used
however...

All I have been able to come up with is:

Public Function NextNum() As String

Dim PONum As String

PONum = "SELECT [fldPOType] & [fldNextNum] AS PONum" & _
"FROM tblPOType WHERE (((tblPOType.fldPOType)=[Forms]!
[frmPOType]![Type]));"

End Function

thank you
greg

 

[Dan Artuso]

Hi,
Public Function NextNum() As String

Dim strNum As String
Dim strPOType As String

strPOType = Forms!frmPOType!Type

strNum = DLookup("[fldNextNum]","tblPOType", _
"fldPOType ='" & strPOType & "'"

NextNum = strPOType & strNum

End Function

 

[greg]

Thank you Dan, it works perfect!

greg (who dreams of one day being able to walk all by
himself)

 

[Dan Artuso]

you're welcome

--
HTH
Dan Artuso, Access MVP

Thank you Dan, it works perfect!

greg (who dreams of one day being able to walk all by
himself)

Public Function NextNum() As String

Dim strNum As String
Dim strPOType As String

strPOType = Forms!frmPOType!Type

strNum = DLookup("[fldNextNum]","tblPOType", _
"fldPOType ='" & strPOType & "'"

NextNum = strPOType & strNum

End Function