Join two Code Function

How to join the 2(two) code funtions below?
Thanks
Public Function PRO_NAME(ProCode) As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from STOCK where PROCODE='" & ProCode & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            PRO_NAME = ""
        Else
            PRO_NAME = !PRONAME
        End If
        .Close
    End With
    Set rs = Nothing
End Function
Public Function Disc_A(ProCode) As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from STOCK where PROCODE='" & ProCode & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            Disc_A = "0"
        Else
            Disc_A = !DiscA
        End If
        .Close
    End With
    Set rs = Nothing
End Function

Open in new window

Whing Dela CruzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hello ERWIIN,

What is it that you want the new function to return?

Regards,

Patrick
0
DhaestCommented:
You need to return multiple parameter, you can easily use an array for this:

You can call it now like this:
dim myVal() as string
myVal = Merged("YourCode")
myVal(0) will contain the proname, myVal(1) will contain the DiscA
Public Function Merged(ProCode) As String()
dim Merge() as string
ReDim Merge(1 To 2)
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "select * from STOCK where PROCODE='" & ProCode & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF = True And _
            .EOF = True Then
            merge(0) = ""
            merge(1) = "0"
        Else
            merge(0) = !PRONAME
            merge(1)  = !DiscA
        End If
        .Close
    End With
    Set rs = Nothing
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Assuming Dhaest was correct about what you wanted (i.e., an array with the two former results), this code
fixes a couple of syntax errors and optimizes the SQL statement.
Public Function Merged(ProCode As String
    
    Dim Merge(1 To 2) as string
    Dim rs As ADODB.Recordset
    Dim sql As String     
    Set rs = New ADODB.Recordset
    sql = "SELECT TOP 1 PRONAME, DiscA FROM STOCK WHERE PROCODE='" & ProCode & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
        If .BOF And .EOF Then
            Merge(1) = ""
            Merge(2) = "0"
        Else
            Merge(1) = !PRONAME
            Merge(2)  = !DiscA
        End If
        .Close
    End With
    Set rs = Nothing
    
    Merged = Merge
    
End Function

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DhaestCommented:
@matthewspatrick: Thanks for correcting the syntax errors (I don't have visual studio 6 here and I'm very busy programming c#)
0
Patrick MatthewsCommented:
Dhaest,

No worries :)

ERWIN: if that revised code does what you're looking for, be sure to remember Dhaest's contribution.  If that
is not what you had in mind, then please clarify your request.

Regards,

Patrick
0
Whing Dela CruzAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.