CODEWITHSUNDEEP
vb6
KaurSukh
KaurSukh

Reputation: 13

Function returns empty result

I am getting empty msgBox when i call the function . Have a look on code as bellow

Public Function Custom(ByVal TableName As String, _
    ByVal EmployeeCode As String, ByVal FieldName As String, ByVal DataToCheck As String, _
    Optional ByVal CodeFieldName As String = Empty, Optional ByVal CodeFieldValue As String = Empty) As Boolean

Dim lstrSQL1 As String
Dim lrsTemp1 As ADODB.Recordset

lstrSQL1 = " Select  " & FieldName & "  from  " & TableName & " Where ID_CARD_NO =" & DataToCheck & ""
'MsgBox (lstrSQL1)

If Len(Trim$(CodeFieldName)) <> 0 And Len(Trim$(CodeFieldValue)) <> 0 Then
   lstrSQL1 = lstrSQL1 & " AND " & CodeFieldName & " <> '" & CodeFieldValue & "'"


End If

Set lrsTemp1 = cObjDBConn.ExecuteSQL(lstrSQL1)

If lrsTemp1 Is Nothing Then
    Custom = False
ElseIf Not (lrsTemp1.BOF And lrsTemp1.EOF) Then
    Custom = True
ElseIf lrsTemp1.RecordCount = 0 Then
    Custom = False
Else
    Custom = False
End If

If lrsTemp1.State = adStateOpen Then lrsTemp1.Close
Set lrsTemp1 = Nothing

Exit Function

ErrorHandle:

    Custom = False


End Function

The calling code is here:

If gobjValidation.Custom(fstrTableName, gEmployeeCode, "EMPLOYEE_CODE", _
                Trim$(TxtIDcardNo.text)) = True Then
                MsgBox (gEmployeeCode)
                Call MessageBox("This ID Number is already existing for another  employee. Cannot enter duplicate number!Using By Employee Code:" & gEmployerCode & " ", OKOnly, Information, DefaultButton1, Me.Caption)
                sstInformationTab.Tab = 0

Upvotes: 0

Views: 96

Answers (1)

Eric Walker
Eric Walker

Reputation: 1052

As @Arvo said, you need to make youre EmployeeCode variable ByRef, then assign it a value in your function Custom()

Public Function Custom(ByVal TableName As String, _
    **ByRef EmployeeCode As String**, ByVal FieldName As String, ByVal DataToCheck As String, _
    Optional ByVal CodeFieldName As String = Empty, Optional ByVal CodeFieldValue As String = Empty) As Boolean

Dim lstrSQL1 As String
Dim lrsTemp1 As ADODB.Recordset

    lstrSQL1 = " Select  " & FieldName & "  from  " & TableName & " Where ID_CARD_NO =" & DataToCheck & ""
    'MsgBox (lstrSQL1)

    If Len(Trim$(CodeFieldName)) <> 0 And Len(Trim$(CodeFieldValue)) <> 0 Then
       lstrSQL1 = lstrSQL1 & " AND " & CodeFieldName & " <> '" & CodeFieldValue & "'"
    End If

    Set lrsTemp1 = cObjDBConn.ExecuteSQL(lstrSQL1)

    If lrsTemp1 Is Nothing Then
        Custom = False
    ElseIf Not (lrsTemp1.BOF And lrsTemp1.EOF) Then
        Custom = True
        **lrsTemp1.MoveFirst**
        **EmployeeCode = lrsTemp1.Fields("EMPLOYEE_CODE")**
    ElseIf lrsTemp1.RecordCount = 0 Then
        Custom = False
    Else
        Custom = False
    End If

    If lrsTemp1.State = adStateOpen Then lrsTemp1.Close
    Set lrsTemp1 = Nothing

Exit Function

ErrorHandle:
    Custom = False
End Function

The double asterisks are just to highlight the changes I made to your original code.

Upvotes: 1

Related Questions