It's easy when you know how...

[Tezcatlipoca]

...and unfortunately I don't know how, which is where you lovely people come in.

I'm trying to create a form which contains a number of list or combo boxes (never really understood the difference between the two), that allows users to select a number of different filters, then click a button and have all records in the database display with those filters in place.

Now I need each combo or list box to contain data from a certain field. Since new information is constantly being added, I cannot realistically type in the data to be displayed myself, as I'd need to update it almost constantly. I therefore elected to have Access gather the data from each field automatically. The problem is that when I create a list or combo box to do this, it lists every single occurance of information in the field (so if I'm working on a field that contains surnames, it lists the same surname multiple times). What I want, is for it to list every piece of data in the field, but to only list multiple occurances of the same data once (so my name field may well contain 376 Joe Bloggs', for example, but the list/combo box should only have Joe Bloggs appearing once).

Secondary to this, is there some sort of null value that can be added to the list in a list/combo box to tell Access not to bother with that filter? I'm thinking that a user may well select something - whether intentionally or accidentally - and then need to clear that selection so Access doesn't filter it when the button is pressed.

 

[bradcccs]

You will need to "Group by" in your rowsource to avoid multiple listings.

You may want to check out Beef's examples here:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=49333


Brad

 

[dcx693]

To try and answer your questions:
1 - the differences between listboxes and combo boxes: combo boxes can "drop down" list boxes are permanently "open"; you can select multiple values from listboxes that are setup to do so not with combos; you can enter new entries into combo boxes that have been set up to do so not with list boxes.

2- I would not use "group by" (and therefore a "totals query") to prevent multiple listings in my list or combo boxes. When editing the rowsource for your combo or listbox, all you need to do is to go to the View menu, choose Properties, to see the query propery sheet, go to the line that says "unique values" and select yes.

3 - A popular technique is adding (n/a) or "<Alll>" to a list to tell Access not to bother filtering on that combo box. I'll see if I can find some examples of how to do that and post them here.

 

[Tezcatlipoca]

Apologies if I'm sounding impatient, but any luck with tracking down those examples, dcx693? I've had a search myself, but can't find what I'm after.

 

[Rich]

Article ID: Q128881

 

[Tezcatlipoca]

Firstly, thanks for the advice, Rich, it was much appreciated.

Hokay, still a few problems, most notably with this eliminating multiple records. I'm following dcx693's directions on point 2, but don't see a 'unique values' line anywhere...umm....on any screen.

The 'group by' method example seems to bring up tabled results, whereas what I really need is for the user to set however many filters they want, then click a 'Go', and have the resulting records filtered in the form of a form (pun not intended).

I'm assuming - perhaps wrongly - that I'm going to need to base a query on my form where the filters are set, and a final form on that query. The latter two jobs I can do; the establishing of a working form where filters are set, I am hopeless at.

Any help is gratefully accepted!

 

[Rich]

Open the query for the combo box in design view, double click on a blank section of the grid to open the property sheet, select Unique Values

 

[Tezcatlipoca]

Ah! There it is! Thanks again, Rich. The only thing I have noted though, is that I can alter the Unique Values to 'yes', but in testing the form which contains the combo boxes, they still list all of my records (i.e. multiple occurences of data appears multiple times).

Apart from even attempting to add this process to my database, what am I doing wrong?

 

[dcx693]

Tezcatlipoca, sorry I've been away from this thread. What is the SQL code for the query that is feeding into the combo box that is still giving you multiples? If you're not sure how to look at the SQL, go to your form in design view, double-click on the combo box, it should bring up the property sheet for it, to the right of the rowsource line, you'll see the drop-down arrow and a three dot thingee, click on the three dot thingee, that will bring you into the query builder for combo box rowsource. Now click on the View menu, and choose SQL View.

Your SQL code should start off with "SELECT DISTINCT".

 

[Rich]

Base the form on a query and use the same method to get unique records

 

[Tezcatlipoca]

The SQL in questions reads:

SELECT coredata.Name
FROM coredata;


'coredata' being the table holding the main database records, 'name' being one of the fields I'm trying to set up a filter for.

It's obvious to me that it's currently just reading everything in that table's field, instead of filtering out duplicate records. The problem however, as I stated before, is that I changed the 'Unique Values' row to 'yes', but I still get the same SQL and same result.

I suspect that once I can get just one field of this query working properly (and, by extension, one combo box on the form), then the rest will fall into place. Should I perhaps by putting anything into the 'critera' sections of my query?

 

[Tezcatlipoca]

Ah, I see where I was going wrong and now have a fully working first combo box, so thanks for all your help guys.

My problem now is that I've written up a form (coredata4) which is to display the filtered records, based on the query. The form with the combo box on has 'Table/Query' in its RowSourceType and 'coredata Query1' (the name of my query I've just set-up) in its RowSource.

Currently I can open up the form with the combo box on it and use the combo box to select any of the - now unique - names from the 'Name' field. However, clicking on my 'search' button (which has the command to open 'coredata4' attached to it), causes a blank window to pop up and Access to freeze (resolvable only by CTRL+ALT+DEL and shutting down Access).

If I double click on 'coredata4' as a form, it pops up as it should do, but with only the 'Name' field filled (and every other field containing '#?Name'. It's obvious why it's doing this, since I only have Name selected in my query, but if I set my query to show other fields (as it should do for the user), Access just adds those fields to the Unique Value SQL. So, how do I tell Access to only treat certain fields as having unique values, and, if I can't, how do I display a form with only a certain field filtered uniquely?.

 

[dcx693]

What is the recordsource for the form? And what is the rowsource for the combo box? It sounds like you may have set them to the same thing.

 

[Tezcatlipoca]

Ah, yes they are both bound to 'coredata Query1'. Since the combo box takes its data from the query, I'm assuming it is setup correctly, which brings me neatly to the question "What the hell should the form be taking data from?"

 

[dcx693]

Generally, the form should be taking it's data from the "full set" of data that you ever want to display on the form. The combo box should take only that data that needs to ever be in the combo box. As you can see, they are rarely the same set of data.

 

[Tezcatlipoca]

Ok, I've got my combo box [comboname) on my combobox selection form (formfilter) bound to my query (coredata query1) in its RowSource.

The query forces the combobox to list all data from the Name field in my main record table (coredata), uniquely by using the code:

SELECT DISTINCT [coredata].[Name]
FROM coredata;

On FormFilter is a button which has the following code attached:

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "coredata4"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub



The end form (coredata 4) - the one that should display the filtered records - currently has its RecordSource bound to the main data table, coredata.

At the moment, I can open up 'formFilter', browse the unique records, and select any one I wish. When I click on my 'Go' button ('command13' in the code above), it opens the end form with all the records displayed. I realise why it is doing this; what I don't realise is where the code that tells this form to display only those records filtered by the combobox should go, nor what format that code should take.