Filter Form & Table Update (1 Viewer)

Nip351

New member
Local time
Today, 07:34
Joined
May 31, 2017
Messages
8
If you are familiar with Pokemon (Go) then read on, if not, start from Part II as this may be too much or irrelevant info.

Part I - If you are familiar with Pokemon (Go)

I have a growing functionality database for Pokemon Go that does quite a bit. Right now, Im trying to figure out how I can update candy values for all "Starter" Pokemon and later just update all related Pokemon to which I have acquired. For example, if I have Pichu with 5 candies I want to be able to filter by use of a single button, then scroll through and only see Pichu (not Pikachu or Raichu) and update its candy, as well as all other starter Pokemon. Once I complete the entire roster of starters I wanted this value to update for all related Pokemon (all non-Starters) that I have acquired. I set my own FamilyID and SubFamilyID values as I needed to assign values for all Pokemon to be able to group them as well as identify starters and children. See example on the bottom for a example of some of the records.

Part II - If you are not familiar with Pokemon (Go)

I have a Main Form that list the primary fields for each record. Each record can be related to another (up to five times), if related at all. I wanted to know a way I could update a single field with a new value and then it would update all related records with that same value for the same field.

For example, records Apple, Banana and Pear are all related. This is identified under the FamilyID field as 1. Cherry, Orange are related as 2. Coconut, sadly, is alone, but identified as 3. Additionally each record also has a subfamily ID, which simply increments by 1, so Apple, banana and Pear are 1,2,3 respectively and Coconut will be 1since its the only record in the family series and Cherry, Orange will be 1,2 respectively.

Since Coconut has no family members it will be excluded (on the update portion) because I won't include any subfamily of 1 since that is what will be updated bu the users, so eventually we will only update where subfamilyid <>1 and InPokedex =TRUE, well get to InPokedex later.

Two things need to happen.

1. We need to filter the form to only show where subfamilyID = 1 and where InPokedex = TRUE (Inpokedex True means we have it) - By enabling a filter we are only scrolling through records we have and that we want to update. For the users sake, Id like this to be a button that just says "Show Starters" and it will filter for records that have a SubFamilyID = 1 and display in the current form, if possible.

2. Once all updates are done on the Starters, we need to update the others OnHand values where their familyID = familyID of the record we updated with a subfamilyid =1 and where the InPokedex=True. It would be Update tblMain set OnHand = [OnHand] where familyID=[FamilyID] and subfamilyid <>1 and InPokedex = True.

For Part I or II, this is the data for a dozen records out of 251. Idnum is the PK. These are unfiltered.
Idnum OnHand FamilyID SubFamilyID InPokedex
1 93 1 1 TRUE
2 93 1 2 TRUE
3 93 1 3 FALSE
4 22 2 1 TRUE
5 22 2 2 TRUE
6 22 2 3 TRUE
7 105 3 1 TRUE
8 105 3 2 TRUE
9 105 3 3 FALSE
10 66 4 1 TRUE
11 66 4 2 TRUE
12 66 4 3 FALSE

Filtered:
Showing SubfamilyID = 1 and InPokedex = TRUE (These are the records the user will update OnHand values, once completed the onhand values will be updated to match these with with same FamilyID where the SubFamily <>1 and where InPokedex=TRUE.
Idnum OnHand FamilyID SubFamilyID InPokedex
1 93 1 1 TRUE
4 22 2 1 TRUE
7 105 3 1 TRUE
10 66 4 1 TRUE

I hope this makes sense. This is in Microsoft Access 2002.

Are you into Pokemon Go and want to see what this generated for reports as well as the main form, check the screen-shots out at: drive.google.com/drive/folders/0Bzml8R4b8NaweFNCSDBpR2pvLW8?usp=sharing
 

Nip351

New member
Local time
Today, 07:34
Joined
May 31, 2017
Messages
8
I figured this out using a Command Button and some code.

I added a command button to the form, edited the On Click property and added the following:
Code:
Private Sub cmdFilterBySelection_Click()
On Error GoTo Err_Handler

Screen.PreviousControl.SetFocus
Me.Filter = "SubFamilyID = 1"
Me.FilterOn = True

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Sub

Now I need to set the On DoubleClick property to remove the filter and at the same time, update the single command button to display "Show Starters" and when single clicked (On Click event) display "Show All".

Hopefully this is possible to prevent two buttons for such a small function, but much needed functionality.
 

Minty

AWF VIP
Local time
Today, 11:34
Joined
Jul 26, 2013
Messages
10,354
You don't need to use the double click. If at the beginning of your code you check to see if the filter is already on, then simply turn it off and reset it, if not do what you are already doing.

You can also change the Button caption to indicate "Filter On" or "Reset Filter" .
Simples - one button - two functions.
 

Nip351

New member
Local time
Today, 07:34
Joined
May 31, 2017
Messages
8
Works great!

As mentioned above, the default Caption for this button is Show Starters and a single click filters all Starters (subfamily = 1) and changes the Button Caption to Show All. A double click sets the filter off and changes the button Caption back to Show Starters. The following code was used for the On Double Click Event:

Code:
Private Sub cmdFilterBySelection_DblClick(Cancel As Integer)
On Error GoTo Err_Handler

cmdFilterBySelection.Caption = "Show Starters"

Screen.PreviousControl.SetFocus
Me.Filter = ""
Me.FilterOn = False
Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler

End Sub

Hope this helps someone.
 
Last edited:

Users who are viewing this thread

Top Bottom