Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: Newbie seeks Advice Function errors after conversion to ADP

    The Function below is a VBA Module which Works in Access Accdb


    Function MyLookUp(SQLstring As String) As String
    Dim objDB As DAO.Database
    Set objDB = CurrentDb
    Dim Value As String
    Dim rsLookUp As DAO.Recordset
    Dim rsLookUpField As DAO.Field
    Set rsLookUp = objDB.OpenRecordset(SQLstring)

    Do While Not rsLookUp.EOF
    For Each rsLookUpField In rsLookUp.Fields
    Value = rsLookUpField.Value
    Next
    rsLookUp.MoveNext
    Loop

    MyLookUp = Value

    End Function


    This Function is Used to return value from following VBA Code

    Dim VarMyvar As Integer

    VarMyvar = MyLookUp("SELECT Count(Servers.[Unique Server ID]) AS [CountOfUnique Server ID]FROM Servers INNER JOIN [LinkHigh Level Servers Pivot] ON Servers.[Unique Server ID] = [LinkHigh Level Servers Pivot].[Unique Server ID];")

    I did some research, and made the changes shown below to reflect the change to ADODB


    References = Microsoft ActiveX Data Objects 6.0 Library


    Function MyLookUp(SQLstring As String) As String
    Dim objDB As ADODB.Connection
    Set objDB = CurrentProject.Connection
    Dim Value As String
    Dim rsLookUp As ADODB.Recordset
    Dim rsLookUpField As ADODB.Field
    Set rsLookUp = objDB.OpenRecordset(SQLstring)

    Do While Not rsLookUp.EOF
    For Each rsLookUpField In rsLookUp.Fields
    Value = rsLookUpField.Value
    Next
    rsLookUp.MoveNext
    Loop

    MyLookUp = Value

    End Function


    This seemed to resolve the initial "Object variable or With block variable not set" error message.
    However I now get an "Arguments are of the wrong type,are out of acceptable range, or are in conflict with one another" messsage.

    Any help greatly appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try changing variables that are dimmed as a 'string' or 'integer' to a 'variant' and troubleshoot. Sometimes large values (ie. such as: 34342352354323) if defined as an integer will return the error you described.

    ex:
    Dim VarMyvar As Integer

    to

    Dim VarMyvar As Variant
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •