Option buttons value big issue

[Kovacs]

Hi everybody
In order to filter records in the main listbox, I have an option frame which has 16 option buttons.
Apparently Access is reading the option button value 11 as both 11 and 1, the value 12 as 12, 1, and 2, the value 13 as 13, 1 and 3, etc.....
So when I use the button 11, the records are filtered currectly, but they are filtered the same way if I use the button 1. Same thing, when I use the button 12, the records are filtered currectly, but they are filtered the same way if I use the button 1 and the button 2. And so on.....

Sure this is a newbie question, but.......
What am I doing wrong?

 

[pr2-eugin]

I am pretty sure that this is not Access's fault.. 16 Option button by itself quiet bizarre, is it possible you could post a stripped version of DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[Kovacs]

Thank you Paul for your fast reply. I've uploded a new database made by scratch just for sample. The issue, as you can see, is exactly the same: Access reads a value "12" as 12, 1 and 2; a value "13" as 13, 1 and 3 and so on.

The database is originally made for "antiquities", so I have a lot of categories for all the possible items (paintings, prints, cloths, etc). In total they are 16.
My task was to filter the items by category, using option buttons in a frame.
The user can choose the Category using a combox. When the combobox text changes, the proper "category value" is stored in a non-visible field called Catvalue.

I know it would be better to use a combox to filter records, but I really don't know how to do it!

Please keep in mind that I am a very beginner, that's why you will find a very poor code.. lol, but it works, except for this thing. I cannot believe Access works that way (you can't use a double digit value), so must be something wrong....

 

[namliam]

Like "*" & [forms]![Form1]![Frame1] & "*"

Assuming you put into the Frame1 the value 1, will find
1
10
21
212
anything containing a 1

If you are using category as a number field why "hurt" yourself by actually making it a text field and if you are looking for an exact value, why use a LIKE?

Access is doing exactly as you are telling it too...

 

[pr2-eugin]

The Problem is in your Query1, you have used

Like "*" & [forms]![Form1]![Frame1] & "*"

as the criteria, thus 1 would read *1* , which means if there is a 1 in the Category everything will be pulled across.. 1 or 101 or 700000000188888 or 44588966523661. So change the criteria to..

[forms]![Form1]![Frame1]

That should sort it out.

 

[Kovacs]

Ok guys of course you were right, the problem was in the query.. but once I've changed it, the query doesn't returns all the records, only the filtered ones.
In fact when I open the form, the listbox is empty, then the records appear only if I click on a category button (giving the proper result, this time).
Same thing, the reset button doesn't work anymore, since the query can't return all the records.

A soution will be to set a default option button on the form load (the first one), so the listbox can't be empty.... but what if I want to show all the records? And what about the reset button?

Thank you for your time!

 

[namliam]

The Problem ..

Like "*" & [forms]![Form1]![Frame1] & "*"

*Geez* that echo

 

[pr2-eugin]

How about..

[COLOR=Red][B]Private Sub Form_Load()[/B][/COLOR]
    [COLOR=Blue][B]Me.List1.RowSource = "SELECT Table1.ID, Table1.Serial, Table1.Title, Table1.Director, Table1.Release, Table1.Category, Table1.Catvalue FROM Table1"[/B][/COLOR]
    Me.List1 = Me.List1.ItemData(1)
    Me.box1.SetFocus
End Sub

[B][COLOR=Red]Private Sub Frame1_Click()[/COLOR][/B]
    [COLOR=Blue][B]Me.List1.RowSource = "Query1"[/B][/COLOR]
    varDisable = Me.List1
    Me.List1.Requery
    Me.List1 = Me.List1.ItemData(1)
    Me.box1.SetFocus
    If Me.List1.ListCount = 0 Then
        Me.Editrecord.Enabled = False
    Else
        Me.Editrecord.Enabled = True
    End If
End Sub

[COLOR=Red][B]Private Sub Reset_Click()[/B][/COLOR]
    [COLOR=Blue][B]Me.List1.RowSource = "SELECT Table1.ID, Table1.Serial, Table1.Title, Table1.Director, Table1.Release, Table1.Category, Table1.Catvalue FROM Table1"[/B][/COLOR]
    Me.box1 = ""
    Me.box2 = ""
    Me.Frame1 = Null
    Me.List1.Requery
    Me.List1 = Me.List1.ItemData(1)
    Me.box1.SetFocus
    If Me.List1.ListCount = 0 Then
        Me.Editrecord.Enabled = False
    Else
        Me.Editrecord.Enabled = True
    End If
End Sub

 

[namliam]

You -on average- want to filter the form, not the query...

see (for example) this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=253291&highlight=filter+form
To see how that might work...

 

[Kovacs]

I really appreciate your help. Obviously I need to improve my skills here
Hope this will help others beginners to understand how to approach some basic stuff.
Regards