Solved Listbox to returns non-empty query fields (1 Viewer)

theferd

Registered User.
Local time
Today, 13:24
Joined
Dec 23, 2019
Messages
42
I have a QueryA that lists 5 fields from TableA, lets call them Fields1-5. On FormA I have a button that changes the SQL criteria of QueryA such that only one row of TableA is returned. I want to make a listbox that populates based on the fields that are currently present in QueryA. Simply putting the SQL into the listbox rowsource only returns Field1.

I know how to cleanup empty rows in a listbox so the goal here is to simply list the contents of Fields1-5. What would be the most efficient way to go about this?
 

Isaac

Lifelong Learner
Local time
Today, 10:24
Joined
Mar 14, 2017
Messages
5,915
I want to make a listbox that populates based on the fields that are currently present in QueryA. Simply putting the SQL into the listbox rowsource only returns Field1.
Then your combination of:

- the SQL for the rowsource
- the columncount property
- the columnwidths property

is wrong
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:24
Joined
Oct 29, 2018
Messages
17,158
Hi. Not sure I follow, did you want to list the field names or the field contents of QueryA in your Listbox?
 

theferd

Registered User.
Local time
Today, 13:24
Joined
Dec 23, 2019
Messages
42
Hi. Not sure I follow, did you want to list the field names or the field contents of QueryA in your Listbox?
I want to list field contents, that way it will return blank fields if they are blank on TableA. I was thinking this could be done using the SQL for the query somehow or manually assigning listbox rows to each field in QueryA since it will be the same size query every time. But I havent figured out the details on making this work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:24
Joined
Oct 29, 2018
Messages
17,158
I want to list field contents, that way it will return blank fields if they are blank on TableA. I was thinking this could be done using the SQL for the query somehow or manually assigning listbox rows to each field in QueryA since it will be the same size query every time. But I havent figured out the details on making this work.
Okay, so can you show us an example of what you mean, using data? What do you want the Listbox to look like based on different results of your query? This will help us figure out how to make it work.
 

theferd

Registered User.
Local time
Today, 13:24
Joined
Dec 23, 2019
Messages
42
Code:
SELECT Components.Field1, Components.Field2, Components.Field3, Components.Field4, Components.Field5
FROM Components
WHERE Components.PartNumber = 'abc123';
This is the SQL that defines QueryA. It will return the row corresponding to abc123. This part number has content in Fields1-3 but not 4 and 5.

Fields 1-5 are hyperlinks to different quality documents.

So if I press the button on FormA, the listbox will show Field 1's contents, Field 2, and Field 3. I do not require that links work in the listbox, only that the listbox return fields with content such that I can tell from a glance if this part number has this type of quality document.
 

Isaac

Lifelong Learner
Local time
Today, 10:24
Joined
Mar 14, 2017
Messages
5,915
If you don't see field4 and field5 in your listbox then, as I first mentioned in post2, you need to check your widths and columncounts properties.
 

theferd

Registered User.
Local time
Today, 13:24
Joined
Dec 23, 2019
Messages
42
If you don't see field4 and field5 in your listbox then, as I first mentioned in post2, you need to check your widths and columncounts properties.
I regrettably did not see your comment in my frustration. Setting columncounts to 5 did in fact show all fields.

Though I am not as familiar with using columns in a list box. Is it possible to transpose columns into a single row? alternatively I do not know how to remove the empty columns. I am thinking that hiding this first listbox and creating a second one that references it would be the way to go about it.
 

Isaac

Lifelong Learner
Local time
Today, 10:24
Joined
Mar 14, 2017
Messages
5,915
For transposing, do you just mean making 3 columns into 1 for example? If so do it in your query: NewField: [field1] & [field2] & [field3]

What do you mean by removing the empty columns? I thought you wanted to see those columns to see whether they had a document or not
 

theferd

Registered User.
Local time
Today, 13:24
Joined
Dec 23, 2019
Messages
42
So my original vision was to have the columns of QueryA into rows of the listbox on FormA. These would list all the documents that existed, so three documents in the case of part number "abc123". It wouldnt need to list all 5 columns since all it would show is 3. Functionally showing all columns/rows would provide the same result, but just for aesthetics an empty column/row would just take up space.


In the end I did manage to get the columns into rows using your suggestion. From there I know how to remove empty rows from a listbox. Thanks.
 

Isaac

Lifelong Learner
Local time
Today, 10:24
Joined
Mar 14, 2017
Messages
5,915
Hmm, interesting. FWIW, from a user perspective, I think it would be very confusing to see a listbox where, it normally has (let's say), 4 columns. But whenever column 4 is blank, then it only shows 3 columns (if you could even figure out a way to do that).

Hopefully I'm misunderstanding - and I'm glad you got something working. Cheers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:24
Joined
Oct 29, 2018
Messages
17,158
So my original vision was to have the columns of QueryA into rows of the listbox on FormA. These would list all the documents that existed, so three documents in the case of part number "abc123". It wouldnt need to list all 5 columns since all it would show is 3. Functionally showing all columns/rows would provide the same result, but just for aesthetics an empty column/row would just take up space.


In the end I did manage to get the columns into rows using your suggestion. From there I know how to remove empty rows from a listbox. Thanks.
So, if I am understanding this correctly, I am thinking you should be able to use a UNION query for your SQL statement. For example:
SQL:
SELECT Field1 AS Document FROM Components WHERE PartNumber="abc123"
UNION SELECT Field2 FROM Components WHERE PartNumber="abc123"
UNION SELECT Field3 FROM Components WHERE PartNumber="abc123"
Hope that helps...
 

Users who are viewing this thread

Top Bottom