Solved Retrieve columns from a table based on a list box that contains field names (1 Viewer)

[Redbeard3456789]

Quite a tricky one.

I have a listbox that contains a variable list of field names
I have imported a table and I now want to retrieve only specific columns from that table where the field names appear in the listbox
I am selecting the field names from this listbox using sql using .items selected and this correctly loops through and selects the listbox items selected.

I can't work out how to retrieve only those columns in the table where the field names in the table match the names selected from my listbox.the list box

I would greatly appreciate some assistance
Malcolm
'

 

[The_Doc_Man]

A native Access table has several parts that it inherits from the DAO library. Besides the associated recordset, it also has an Index collection and another collection called Fields. Each field defined in the table includes a name that can be used for field selection, as Table-name.Fields("name-of-field") which might be helpful to you. This may help you. However, when you say you want to retrieve only the selected fields, HOW you do that may be tricky since Access works better if you tell it ahead of time what it is you are seeking. Making things dynamic becomes a matter of whether the retrieval method supports accessing the .Fields collection.

Fields collection (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

IF you were using a DAO recordset based on that table, the Fields collection is also available and can be used to retrieve named fields from the recordset. Other methods of retrieval? ... not 100% certain. Check the linked article.

By the way, that was your first post. Hello, Malcolm, and welcome to the forum.

 

[cheekybuddha]

I am selecting the field names from this listbox using sql using .items selected and this correctly loops through and selects the listbox items selected.

I can't work out how to retrieve only those columns in the table where the field names in the table match the names selected from my listbox.

Where do you wish to display your data?

Build your SQL in VBA and assign it as the RecordSource of a form, or the Row Source of a listbox, or open a recordset to manipulate in code.

 

[CJ_London]

On my phone so code in principle In your vba might be something like

for each itm in listbox.items
    On error resume next
    If itm.selected then
       If currentdb.tabledefs(itm.value)then sqlstr = sqlstr & “,” & itm.value
      On error goto 0
End if
Next itm

syntax is probably not quite correct but you should get the idea

 

[arnelgp]

Dim itm As Variant
Dim fld As String, sql As String
For Each itm In Me.yourListbox.ItemsSelected
    fld = fld & "[" & Me.yourListbox.ItemData(itm) & "], "
Next
If Len(fld) <> 0 Then
    fld = Left$(fld, Len(fld) - 2)
    sql = "SELECT " & fld & " FROM [yourTableName];"
End If
Debug.Print sql

 

[CJ_London]

@arnelgp - how does that meet to OP's requirement?

how to retrieve only those columns in the table where the field names in the table match the names selected from my listbox.

 

[cheekybuddha]

@CJ_London

The OP said the listbox contains the field names, so Arnel's suggestion seems spot on, no?

 

[CJ_London]

well, if it is the field names of the table in question yes.

My interpretation of the list was it contained generic field names e.g. the user selects productcode, qty, price

the table however only contains qty and price, it's version of productcode is called something else. So the OP was looking for a solution

to retrieve only those columns in the table where the field names in the table match the names selected

Perhaps I'm over thinking it

 

[cheekybuddha]

My interpretation of the list was it contained generic field names e.g. the user selects productcode, qty, price

the table however only contains qty and price, it's version of productcode is called something else. So the OP was looking for a solution

Ah! Makes sense.

Perhaps I'm over thinking it

Possibly. The simpler interpretation also made sense to me.

Need clarification from the OP.

 

[CJ_London]

OP also said

I am selecting the field names from this listbox using sql using .items selected and this correctly loops through and selects the listbox items selected.

So I assumed (perhaps wrongly) the sql string was already being created as required

 

[jdraw]

RedBeard3456789,
When you return, could you please review the thread and clarify the requirements. As you can see, there have been several guesses as to what you were trying describe. Often better to provide a sample of input and expected result/output.
Your question and responses may be helpful to someone else.