Basic database read function for breakout scripts

[Scott.Pearce]

Thought someone might find this handy. This is a function that reads from the database and returns a result, or any error message that occurred.

You can change this to your liking (ie. to read different database fields) and use it for database reads from breakout scripts.

I recommend placing function declarations after the "End Sub" of the "Form Loaded" breakout of the module you are working with.

Code: Select all

Function GetInventoryWeight(DatabaseObject, InventoryID, Weight, rtnErrorModule, rtnErrorString)

   GetInventoryWeight = True

    With DatabaseObject
        rHwnd = .StatementOpen(.ConnectionJiwaRead1, , , 1)
       
        If GetInventoryWeight = True Then
            SQL = "SELECT TOP 1 Weight " _
                & "FROM IN_Main " _
                & "WHERE " _
                & "InventoryID = " & .FormatChar(InventoryID)
           
            If .ExecuteSelect(CInt(rHwnd), SQL, True) = True Then
                If .FetchRow(CInt(rHwnd)) = True Then
               Weight = .GetData(CInt(rHwnd), 1)
            Else
                   rtnErrorModule = "GetInventoryWeight"
               rtnErrorString = "Inventory item with InventoryID of '" & InventoryID & "' not found."
               GetInventoryWeight = False            
                End If
            Else
                rtnErrorModule = "Database.ExecuteSelect"
            rtnErrorString = .ErrorMessage
            GetInventoryWeight = False
            End If
            .StatementClose CInt(rHwnd)
        End If
   End With
End Function


A call to this function would look something like this:

Code: Select all

If GetInventoryWeight(PurchaseOrderObject.Database, PurchaseOrderObject.OrderLines(LineKey).InventoryID, lWeight, lErrorModule, lErrorMessage) = False Then
   MsgBox lErrorModule & vbCrLf & lErrorMessage
Else
   MsgBox lWeight
End If