Variable Field name for Select query or split columns

[DStevens]

Hey all!

I have a dilema: I'm essentially building a tooltip for an object based on data found in a table. I have the following function to query for the data:

Public Function getItemTip(ByRef col As String, ByVal item As String)
    Dim query As String
    query = "SELECT ITEMS." & col & " FROM ITEMS WHERE (((ITEMS.ITEM_NAME)='" & item & "'))"
    Dim MyDB As DAO.Database, record As DAO.Recordset, MyList As String
    
    Set MyDB = CurrentDb
    Set record = MyDB.OpenRecordset(query)
    Do Until record.EOF  ' Loop trough the table
        If (Not IsNull(record(col))) Then
            MyList = MyList & ";" & record!col
        End If
        record.MoveNext
    Loop
    MyList = Mid(MyList, 2)
    
    record.Close
    MyDB.Close
    
    getItemTip = MyList
End Function

It's called like so:

Dim item, base, tmp as String
item = "Test Item"
base = getItemTip("ITEM_BASE", item)
complete = getItemTip("ITEM_COMPLETE", item)
...

[I]OBJECTNAME.[/I]ControlTipText = formatTip(base, complete)

Public Function formatTip(ByVal base As String, ByVal complete As String)
    Dim tip As String

    ' it'd be nice to bold the items in the quotes
    tip = "Base: " & base & vbCrLf & "Completion: " & complete
    formatTip = tip
End Function

The formatTip function formats the values into a readable tip.

My question is this: Is there a way to have a variable field name in the "Do Until record.EOF" loop? If not, is there a way to query all the data for a particular item then split it so it's readable in the format tips function?

I hope this makes sense.

 

[MarkK]

Is there a way to have a variable field name in the "Do Until record.EOF" loop?

Isn't the field name in the loop already variable?

is there a way to query all the data for a particular item then split it so it's readable in the format tips function?

What is 'all the data' and what does it mean to 'split' it?

What is wrong with what you have here? Are you getting specific errors or are there specific failures that you haven't mentioned?

 

[DStevens]

Isn't the field name in the loop already variable?

Yes but it gives me an "Item not found in this collection." error on the [MyList = MyList & ";" & record!col] code. To the best of my knowledge, this error means the field was not found. I could more than likely be wrong though.

What is 'all the data' and what does it mean to 'split' it?

Here's what the table looks like:

ITEM_ID | ITEM_NAME | ITEM_BASE | ITEM_COMPLETE
1         Test        5266        98%

I want to get all the data for a particular record found in a row and "split" it into pieces so it would look something like this after the format:

Base: 5266
Completion: 98%

 

[boblarson]

Incorrect syntax when referring to the col variable:

MyList = MyList & ";" & record!col

should be

MyList = MyList & ";" & record.Fields(col)

You can't use the bang with a variable as it is looking for the literal field name col then.

 

[DStevens]

Incorrect syntax when referring to the col variable:

MyList = MyList & ";" & record!col

should be

MyList = MyList & ";" & record.Fields(col)

You can't use the bang with a variable as it is looking for the literal field name col then.

Yeah I changed that awhile back but ended up nuking my code and takinga different route. I just formatted the tips in the loop via if statement comparing the field name.

I cannot figure out how to get

query = "SELECT ITEMS." & col & " FROM ITEMS WHERE (((ITEMS.ITEM_NAME)='" & item & "'))"

to work properly if the item has a ' mark in it (such as "Sona's Peak")