List Box Help

[brittaink]

All,
I am trying to resolve a problem that I have been thinking about for a while. I have a table of assets containing about 15 columns of data. One of my columns [system_registration_name] holds the data type of the PC for example:
COMPUTER-NET-00001
COMPUTER-NET-00002
Are for Networked

COMPUTER-PC-00001
COMPUTER-PC-00002
Are for Laptops

I would like to have a list box in my form that shows all of the assets. But then either with a radio button or combo box make the row source for the list box change. I could do this by manually creating queries that do it. But I would also like it so the user using the form can combine options like:
Only Laptops
Only Networked
All Assets

With other options like
In Ascending Bldg order
In Descending Bldg Order
Asc Room Number
Desc Room Number
Site
etc.

It seems like the only way to do this is to create every single different type possible as queries and setting that as the row source via radio buttons.

Does anyone have any alternate idea's for me.

Thank You

Keith

 

[neileg]

Couple of points rather than a comprehensive answer.

1) Holding what amounts to three pices of data in one field is a bad design. You would be well advised to split this into three fields. You can join these fields together anytime you need to using a query or in the data source of a form control.

2) You can use the value in a form control as a criterion in a query. You can use a combo box so that the user can select the value they need. Then you refer to this control in the criterion of your query using this syntax - Forms!MyFormName!MyControlName. This way you don't need multiple queries, just one.

 

[brittaink]

Firther assistance

Neil
Thank you for the reply. Other columns in the data do stipulate the type of asset that it is for example if [system_reg_name] = COMPUTER-NET-00001 then another column in my table will be a lookup from another table, the value for column [Networked] would be value of 1. This is linked to tblListNetworked where [id]=1 (autonumber), and [Type]= "Networked" (Text)

I thought that it might be easier to link it through the registration name though if I had multiple criteria. eg. it may be networked but also a laptop or desktop.

The thing you said about the combo box I tried. You may be able to tell me if I did this wrong but I created a combo box on my form called cmbFilter.
I set the values as:

[id] (Text) [Filter] (Text)
Like "COMPUTER-NET*" "Shows all Networked"
Like "COMPUTER-PC*" "Shows all Laptops"
Like "COMPUTER*" "Shows All"

Then I set the bound column as column 1 [id]

I made the query and typed in the criteria Forms![frmITMSAssets]![cmbFilter]

This always brought 0 results in my list box though.

Thank You

 

[gemma-the-husky]

the name/code of the asset is just that, i would have thought. although in your case it follows a strict format, its still probably not thebest way to sort/extract data, unless there is no alternative

given that you have an asset type code, it would be easier to use that
so your filter/selection criteria is effectivley assettype=combobox value.

access handles numerical joins like this, far easier than string manipulation anyway

----------
in your case, your conbobox is actually returning the id number of the row (the BOUND column - normally

 

[neileg]

Neil
Thank you for the reply. Other columns in the data do stipulate the type of asset that it is for example if [system_reg_name] = COMPUTER-NET-00001 then another column in my table will be a lookup from another table, the value for column [Networked] would be value of 1. This is linked to tblListNetworked where [id]=1 (autonumber), and [Type]= "Networked" (Text)

I thought that it might be easier to link it through the registration name though if I had multiple criteria. eg. it may be networked but also a laptop or desktop.

Yes, but you shouldn't store three pieces of data in one field.

The thing you said about the combo box I tried. You may be able to tell me if I did this wrong but I created a combo box on my form called cmbFilter.
I set the values as:

[id] (Text) [Filter] (Text)
Like "COMPUTER-NET*" "Shows all Networked"
Like "COMPUTER-PC*" "Shows all Laptops"
Like "COMPUTER*" "Shows All"

Then I set the bound column as column 1 [id]

I made the query and typed in the criteria Forms![frmITMSAssets]![cmbFilter]

This always brought 0 results in my list box though.

Thank You

The combo should hold a list of the valid values onlynot the full syntax of the criterion. The query criterion will then be:
LIKE Forms!MyForm!MyControl & "*"

 

[brittaink]

One More Bit

Neil,
Thank You for you help. You have actually inspired me to do something slightly different with my list box. Using your CORRECT bit of code with the & sign etc. The list box will now display results that I want. I now have a text box in my form called txtFilter. In here I would put Something like "COMPU" and it will find anything that is like "COMPU*". The only problem that I have is that I have done this for all fields. For example Bldg has txtBldgs where I could stipulate only Bldg 44 and it would recieve all results like "44*". Not all of my fields contain a value though and this is where it does not work correctly. if any of the fields do not contain a value then the record does not get shown. I have entered the code LIKE Forms!MyForm!MyControl & "*" or is null. This will then display all records but when you enter search criteria like bldg 44 it will show all in bldg 44 and all without a value.

Any Idea's

Keith

 

[neileg]

It's doing what you ask it to do. The 'or is null' will match any records with a null value in that field. What did you want it to do?

 

[brittaink]

What I Need it to do

I would like it to show all records when the box is empty but if I type "Comp" in the text box, then only show records that start with "Comp". What is happening at the moment is that it shows all records but when I type in "comp" it shows all records with a null value or that start with "comp". I know that it is doing what I have asked but I was wondering if anyone knows what I could type in my criteria to only show what I want

Any Idea's

Keith

 

[neileg]

Take out the 'or is null'.

 

[brittaink]

Sorry I didn't explain myself too great

I have my list box containing 8 columns. Underneath each column I have a well placed text box. Under column 1 (List Box) I have Txt1 (Text box). Under column 2 I have txt2 etc. For the criteria for each column in my query I have LIKE Forms!MyForm!MyControl & "*"

This allows me to type COMP in txt1, and it eliminates all records that do not have COMP in the column

I can then go to column 5 (Building Number) and in txt5 I can type 11. This will then eliminate all records that do not have COMP in Column 1 AND 11 as the building number.
I am using this as a filter and it works brilliantly.

The only problem I have is that if ANY of the records do not have a value in a box then the record does not get shown
eg. Laptop 1 has no building number so the record does not get shown when all txt boxes are blank. Also Computer 7 does not have a network label so it does not get shown in the query.
To get around this I added to the end of the citeria "or is null". This then means that when all the boxes are blank ALL records are shown.
The problem then is that when I type in txt5 (BUILDING) "11", it will show all records where the building number contains "11" and all records where the building number is blank. I was looking more for something that
When all txt boxes are blank ALL RECORDS WILL BE SHOWN
When a txt box contains data ONLY RECORDS WITH THAT CRITERIA ARE SHOWN

Sorry for the poor explaination first time

Any Help appreciated

Keith

 

[neileg]

No, I understood what you meant. You are correct that Like "*" won't return null values. But if you add 'or is null' this will return the null values regardless of the criteria. The easiest way round this is to make sure you don't have nulls. Make the default value of the field "" ( a zero length string) if the field is text or 0 if it is numeric. That way you will find that Like "*" will return all records.