VBA sending string error

[rpaulson]

Some SQL reading from access

Function ReadFromAccess2(sqlString As String, fieldName As String) As String
...

Sub Populate_from_Current_Invoice()

    Dim fieldName As String
    Dim tableName As String
    Dim custID As Integer
    Dim sqlString As String
    Dim result As String
 
    ' Set the field name, table name, and customer ID
    tableName = "Customer"
    custID = 5346  ' Example customer ID, you can set this dynamically as needed
      

'this works
    fieldName = "City"
    Range("A1") = ReadFromAccess2("SELECT " & fieldName & " FROM " & tableName & " WHERE Custid=" & custID, fieldName)
 
'this does not
     Range("A1")  = ReadFromAccess2("SELECT " & fieldName & " FROM " & tableName & " WHERE Custid=" & custID, "City")

I plan on using the a lot and would like the above 1-liner to work. (Error: Item cannot be found in the collection corresponding to the requested name or ordinal.)

Any Ideas?
-Ross

 

[6StringJazzer]

ReadFromAccess2 is your function and you haven't shown the code for it, so we have no idea how it is using the fieldName argument.

Theoretically passing in a string literal should work the same as passing a String variable with the same value, but to diagnose this we have to see all of the code in ReadFromAccess2, plus any other Subs or Functions that it calls. For example, does ReadFromAccess2 try to change the value of fieldName?

 

[rpaulson]

sorry for delay,

entire code for for the function below.

Function ReadFromAccess2(sqlString As String, fieldName As String) As String 'this will return a single record field to a cell

    On Error GoTo ErrorHandler
   
    Dim conn As Object
    Dim rs As Object
    Dim connectionString As String
    Dim result As String
   
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Ross\Desktop\CHat GPT Code\Invoice.accdb;"
   
  
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
   
    conn.Open connectionString
    rs.Open sqlString, conn
   
    If Not rs.EOF Then
        If IsNull(rs.Fields(fieldName).Value) Then
            result = ""
        Else
            result = rs.Fields(fieldName).Value
        End If
    Else
        result = ""
    End If
   
    rs.Close
    conn.Close

    Set rs = Nothing
    Set conn = Nothing
   
    ReadFromAccess2 = result
    Exit Function
   
ErrorHandler:
    result = "Error: " & Err.Description
    ReadFromAccess2 = result
End Function

 

[6StringJazzer]

I don't see anything in that code that would cause this error. When the error occurs, does debug mode show you what line of code causes the error? If so which one? If not I would step through the code with F8 to pin down exactly where the error is occurring. However, that may or may not help.

But again, from what I've seen so far, theoretically this error shouldn't occur. Unfortunately without your Access database I'm unable to test it myself.

 

[Alex Blakenburg]

I set up the data in Access and using that exact same code it works fine for me which can confirms Jeff's theory that the code looks fine.
I had thought that using fieldName for different purposes might have caused an issue but it doesn't seem to be impacting it.
I also though maybe it needed ByVal especially since fieldName is being used by the main procedure for one thing but in the function for something else but it doesn't seem to make any difference either.
Function ReadFromAccess2(sqlString As String, ByVal fieldName As String) As String

 

[rpaulson]

omg,
I think i found the problem.

I was using fieldName 2 times.

Range("A1")  = ReadFromAccess2("SELECT " &  fieldName & " FROM " & tableName & " WHERE Custid=" & custID, "City")

needs to be

Range("A1")  = ReadFromAccess2("SELECT " & "City" & " FROM " & tableName & " WHERE Custid=" & custID, "City")

Sorry for wasting everyone time.

-Ross

 

[Alex Blakenburg]

Glad you found a way to make it work. Thanks for letting us know.

 

[bobsan42]

I cannot see an immediate problem and also am a bit confused which part throws an error.
However - one suggestion:
You only select one record and one field. So you don't need the field's name as a separate argument.
you can use this:

rs.Fields(0).Value

Basically ReadFromAccess2 needs only the SQL string as an argument

 

[rpaulson]

Txs bobsan42, that made thing a lot easier.

 

[6StringJazzer]

omg,
I think i found the problem.

Oh, I see. Yes, in the second example that doesn't work, it is using fieldName but it omits setting it

    fieldName = "City"

so that fieldName is the null string. As many times as I read this, I didn't realize it.