how-to block ads
|
|
Uniqs:
193 |
Share Topic
 |
 |
|
|
|
| [Access] Filtering Records I have a database with records that can be marked as Current, Suspended, Withdrawn or Completed. The record can't be more than one type at a time. Records can also be from different years.
I want to be able to filter the records I see, first for a particular year (that's easy, selected from a dropdown box) and to be able to see ANY combination of Current, Suspended, WIthdrawn or Completed records (ie just Current, or Current and Withdrawn, or Withdrawn Suspended and Completed etc.) within that year. I also want it to be done quickly and easily with toggle buttons.
I have two choices for storing the info on the records. I can have four fields (Cu,Su,Wi,Co) and using toggle buttons can enter a "-1" in any one of them. Alternatively, using the Option Group I can have ONE field that contains 1,2,3 or 4 for Cu, Su, Wi or Co respectively.
What I would like is for my filter "page" to have four toggle buttons (Cu, Su, Wi, Co) which are toggled "on" to select the appropriate records. I can get the buttons to work, but I don't seem to be able to write a query that gives me what I want.
I know the "four separate fields" solution looks clunky, but I couldn't figure out how to use the "1 to 4" number in conjunction with a combination of buttons, and four separate fields looked like it might be easier (though that isn't working either for some reason)
Help!!! | | |
|
| Sent you a PM. | |
| Thanks cscottm...
I seem to be making a habit of giving up on working something, posting here...and then figuring it out the very next second.
One thing I realised helped me; the build query field in access is limited to 255 characters. As soon as I realised that I'd have to run VB code instead and build an stLinkCriteria statement, it all kind of fell into place.
Basically, I grouped four toggle buttons in an option box on the database form, and had the same four toggle buttons but not in an option box on my form for doing the filter. I set the optionvalue statements for each button to 1,2,3, and 4, and because the default values for toggle buttons are either "" or -1, I put some code at the start of the filter code to change any button to the appropriate value, and then at the end to change it back again (since the form stays open in the background).
Here's the code;
Private Sub Go__Click()
On Error GoTo Err_Go__Click
If T16 = -1 Then T16 = 1 Else T16 = 0
If T17 = -1 Then T17 = 2 Else T17 = 0
If T18 = -1 Then T18 = 3 Else T18 = 0
If T19 = -1 Then T19 = 4 Else T19 = 0
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Student Details"
stLinkCriteria = "([Cohort]='" & Me![Combo4] & "' AND [Statuscode]=" & Me![T16] & ") OR ([Cohort]='" & Me![Combo4] & "' AND [Statuscode]=" & Me![T17] & ") OR ([Cohort]='" & Me![Combo4] & "' AND [Statuscode]=" & Me![T18] & ") OR ([Cohort]='" & Me![Combo4] & "' AND [Statuscode]=" & Me![T19] & ")"
'MsgBox stLinkCriteria, vbOKOnly
DoCmd.OpenForm stDocName, , , stLinkCriteria
If T16 = 1 Then T16 = -1 Else T16 = 0
If T17 = 2 Then T17 = -1 Else T17 = 0
If T18 = 3 Then T18 = -1 Else T18 = 0
If T19 = 4 Then T19 = -1 Else T19 = 0
Exit_Go__Click:
Exit Sub
Err_Go__Click:
MsgBox Err.Description
Resume Exit_Go__Click
End Sub
T16 through T19 are my toggle buttons on the filter form, and statuscode is the source field for the option box on the database form. Cohort is the "year" field.
Works like a charm... | |
|
