Re: Filtering fields to a list box after searching for a record

[Magnetar]

Hello, I posted the following question this afternoon, ( - which is urgent now!). Could anyone help/advise please!?
Would be much appreciated!


I wonder if someone could advise on the following:

I need to display certain field values within a list box, having searched for the relevant record which include those fields. To explain more clearly:

Underlying query, Q for example, has records X, Y Z.

Underlying query attached to form, gives results:

X field A
X field B
X field C

Y field D
Y field A
Y field B

Z field E
Z field F
Z field A

The user enters and searches for required record, Y say, which (I would like!) should display all relevant fields in list box, as follows:

field D
field A
field B

Everything works find, - except the list box.

Using the SQL builder for the Row Source of my list box, as follows, displays ALL the fields for each of the above records X, Y and Z:

"SELECT DISTINCTROW [Q].[field] FROM [Q]"

I want to filter for and hence display ONLY those fields pertaining to record Y. Can this be achieved using SQL, or will I have to write more VBA at this stage? (I want to avoid using v Basic if I can!).


Cheers Magnetar

 

[tunsarod]

If I understand you, you need a listbox which contains A to F (6) columns. You then need to arrange for only those columns to be visible that are relevant to the record being currently displayed by the form that the list box is on. So for one record columns 1, 3 and 4 of the list box might be relevant, while for another record columns 2, 4 and 6 might be relevant.

Assuming I have understood you correctly I suggest the following idea will work. Might be a bit clumsy but I think it will do the trick. I cannot think of any way to set the column appearance with SQL.

Create a new text field in the underlying table.

Name it 'listboxwidths' or whatever you like.

Next, for each record, enter into the new field the sizes of the columns of the list box as you want them to appear for that record. eg. "0;1.5;0;2;1;0" bearing in mind that a zero (0) will result in a cloumn being hidden. I put double quotes round the numbers to show that you store these numbers as a text string.

Now in the form's Current Event place the following code adjusting for the actual names that you have used for the list box and the new field:

Private Sub Form_Current()
Me.List0.ColumnWidths = listboxwidths
Me.Form.Repaint
End Sub


I haven't actually tried it but I think it'll work and will re-format the listbox for each record to show only the columns you want to see.

Good luck.

Rod Griffiths

 

[Magnetar]

To Rod Griffiths
Hello Rod

Thanks for your response. However what is needed is just ONE column
displaying field with D, A and B ONLY for Y, i.e. I do not want to view all the other values pertaining to X and Z, say, eg for:

FileNo FieldLetter (<- these are field headings in qry)

X Field A
X Field B
X Field C
Y Field D
Y Field A
Y Field B
Z Field E
Z Field F
Z Field A

That is, on searching for FileNo: Y, I only want to view FieldLetters: D, A
and B within my listbox, (- NOT all other FieldLetters pertaining to X and
Z as well!). Any further ideas? I'd be very pleased to hear from you/any one else on this one!

Cheers
Magnetar