Help Creating a Function

[3239]

Hello,

I am trying to create a function that sets the record source of a form by passing a query to the function. I am a beginner in writing functions and don't how to accomplish this task.
Here is a snippet of the code that I am trying to turn into a function.

Thanks.

Public Sub GetRequisitions()


Dim frm As Form
Set frm = Forms!POCreator




Select Case frm.cboDepartment.Value

Case "ADMIN" 

frm.RecordSource = "ADMIN_REQ"
frm.xRequisition.RowSource = "ADMIN_REQ"
frm.xReqItems.RowSource = "ADMIN_REQ_DETAILS"
frm.xRequisition.Enabled = False
frm.POID.Enabled = True
frm.POID.SetFocus



Case "AHYD"

frm.RecordSource = "AHYD_REQ"
frm.xRequisition.RowSource = "AHYD_REQ"
frm.xReqItems.RowSource = "AHYD_REQ_DETAILS"
frm.xRequisition.Enabled = False
frm.POID.Enabled = True
frm.POID.SetFocus


Case "BCW"

frm.RecordSource = "BCW_REQ"
frm.xRequisition.RowSource = "BCW_REQ"
frm.xReqItems.RowSource = "BCW_REQ_DETAILS"
frm.xRequisition.Enabled = False
frm.POID.Enabled = True
frm.POID.SetFocus


Case "CENTRAL SUPPLY"
frm.RecordSource = "CENTRALSUPPLY_REQ"
frm.xRequisition.RowSource = "CENTRALSUPPLY_REQ"
frm.xReqItems.RowSource = "CENTRALSUPPLY_REQ_DETAILS"
frm.xRequisition.Enabled = False
frm.POID.Enabled = True
frm.POID.SetFocus





End Select




End Sub

 

[PWise]

how about

Public function GetRequisitions()
......
....
....
end function

 

[joelflorendo]

Why do you want to change this to a function? A function is usually used to return a single value.

For example this function returns the sum of 2 and 2

Function AddTwo()
  AddTwo = 2 + 2
End Function

A sub is usually used to run some kind of process like you have here in setting recordsources for multiple forms and setting other values as well.

You could set up what you were asking for by doing something like this:

Sub SetRecordSource()
   Set frm = Forms!POCreator
   frm.Recordsource = GetRecordsource(frm.cboDepartment.Value)
End Sub

Function GetRecordSource(qry as String) as String
   Select Case qry
      Case "ADMIN"
          GetRecordSource="ADMIN_REQ" 'return the query name
      Case etc....
   End Select
End Function

But the problem with this setup is that you want to be able to change a multiple things when a user makes a choice on the form and functions will only return one value at a time. It seems like your choice of using a Sub is appropriate.

 

[NXMold]

Private Sub cboDepartment_AfterUpdate
	GetRequisitions Me
End Sub

or if you want to handle errors,

Private Sub cboDepartment_AfterUpdate
	if GetRequisitions(Me) = false then
		msgbox "error"
	end if
End Sub

Public Function GetRequisitions (frm as Form) as boolean

GetRequisitions = false 'False means function did not complete

Select Case frm.cboDepartment.value

Case "ADMIN"
	frm.recordsource = "ADMIN_REQ"
	...rowsource...etc
	GetRequisitions = true 'Function worked 	

Case "Other"
	...

End Select

End Function

 

[SkipVought]

Hi,

Here's a typical function that I use, that returns a value from a query...

Function [b]GetNomen[/b](sPN As String) As String
'SkipVought/2006 Mar 7/999-999-9999
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
':this function returns nomenclature for a given part number
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "dwprod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT PM.Nomen_201"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FRH_MRP.PSK02101 PM"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE PM.PARTNO_201 like '" & Trim(sPN) & "%' "

    Debug.Print sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst
[b]
    GetNomen = rst("NOMEN_201")
[/b]
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[3239]

Thanks everyone. I am going to try your suggestions and let you know how it went.

 

[NXMold]

In fact, looking at your post, I would clean it up a bunch since it seems so consistent. Use the value from the combo box as the query name prefix.

Public Function GetRequisitions (frm as Form) 

Dim strDept as String

strDept = frm.cmeDepartment.Value

frm.RecordSource = strDept & "_REQ"
frm.xRequisition.RowSource = strDept & "_REQ"
frm.xReqItems.RowSource = strDept & "_REQ_DETAILS"
frm.xRequisition.Enabled = False
fr,requery 'Might be required to refresh the new record/rowsource
frm.POID.Enabled = True
frm.POID.SetFocus

End Function