Filtering fields to a list box after searching for a record 1

[Magnetar]

Hello, I posted the following question this afternoon,on the 'Microsoft: Access Forms' thread ( - 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!).


Basically I want to do the EQUIVALENT of:

"SELECT DISTINCTROW [Q].[field] FROM [Q] WHERE [Q].[FileNo] = Forms![frmQ].[FileNo]"


Please, Please help!!

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

 

[BoxHead]

Mag,

I'm reading it differently than Rod.

I think you're real close here:

"SELECT DISTINCTROW [Q].[field] FROM [Q] WHERE [Q].[FileNo] = Forms![frmQ].

[FileNo]

"

Replace the field reference [FileNo](above, in red) to the name of the control that the user enters their search string into.

On the AfterUpdate event of that field, run

NameOfYourListBox.Requery

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[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

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

 

[Magnetar]

To Boxhead (John - Tekkie User)

Many thanks Boxhead!

This seems to have worked a treat ( - so far!)
My code as embedded within the Row Source of my List Box was as follows:


SELECT DISTINCTROW qryCaseSummaryTable.Country
FROM qryCaseSummaryTable
WHERE
(((qryCaseSummaryTable.FileNo)=[Forms]![frmCaseSummary].[FileNumberSearch]));


(I didn't realise I could reference a form control from within a query, so to speak!).


What it is,.. my user needs to view certain countries pertaining to a record (ie 'FileNo'), upon entering the relevant FileNo into the text box for searching, (ie 'FileNumberSearch').


On the After Update [Event Procedure] of the 'FileNumberSearch' textbox, I placed the following code, as per your suggestions:

Private Sub FileNumberSearch_AfterUpdate()
'
' Update the list box for the nex FileNo search
'
Countries.Requery 'where 'Countries' = name of List Box

End Sub


Many many thanks for your time/help yesterday.

Kind regards

Magnetar