VBA to set a filter AND go to a specific record (without filtering further)

[cricketbird]

I am trying to code a button that switches between two nearly identical forms for a touchscreen interface. The content is identical, but one layout (form) is for left-handed folks and the other is for right-handed folks. I'm trying to make it so that clicking the button toggles from one form to the other. It should be seamless to the user and keep them at the same record they were on.

I can open the form using a filter, which filters to a subset of records specific for this user/workstation (this is working fine). But, I want to jump to the specific record the user was on when they clicked the toggle button so the swap does not break their workflow. Somehow I cannot get this to work. It always jumps to the "first" filtered record instead of the same record as on the original form.

Any suggestions?

-CB

Button on "Leftie" form:

DoCmd.OpenForm "TouchScreen-RIGHTIE", acNormal, , "WorkstationID = " & Me.CmbPickTable, , , Me.DIETID
Me.Visible = False

Code module of "Rightie" form:

Private Sub Form_Open(Cancel As Integer)

DoCmd.SelectObject acForm, , True
   If Not IsNull(Me.OpenArgs) Then
       With Me.RecordsetClone
           .FindFirst "DIETID = " & Me.OpenArgs
           If Not .NoMatch Then
               Me.Bookmark = .Bookmark
               Me.OpenArgs = Null
           End If
       End With
    End If

Call HideToolbars
End Sub

 

[theDBguy]

Hi. Which of those two code is giving your the problem? If it's the Leftie form, then perhaps you simply need to copy the Open event code from the Rightie form into the Leftie form. Have you tried that?

 

[cricketbird]

Hi. Which of those two code is giving your the problem? If it's the Leftie form, then perhaps you simply need to copy the Open event code from the Rightie form into the Leftie form. Have you tried that?

Sorry - I should have said. They both have the same code (differing only with the appropriate form name, of course).

 

[theDBguy]

Sorry - I should have said. They both have the same code (differing only with the appropriate form name, of course).

Okay, so which form is giving you the problem of going to the first record? Both of them? If so, are you able to post a sample db demonstrating the problem? It might be easier to trace the problem if we have something to test with.

 

[Pat Hartman]

Use the WHERE argument rather than the FILTER argument.

Is WorkstationID the ID for the unique record? (I doubt it)

You need to use the exact ID of the record you want to open to.

"SomePKname = " & Me.SomePKname rather than the Workstation ID

 

[cricketbird]

Thanks for looking into this. Both forms are problematic. Attached is an example db. When you toggle back and forth, you don't stay on the same diet.

 

[cricketbird]

Use the WHERE argument rather than the FILTER argument.

Is WorkstationID the ID for the unique record? (I doubt it)

You need to use the exact ID of the record you want to open to.

"SomePKname = " & Me.SomePKname rather than the Workstation ID

DIETID is the unique id for the record on either form.

So, it should FILTER by tableID (get all the diets for Table 1, for example), but jump to the record for "DIETID".

In other words, the user should be able to use next/previous arrows (in navigation box in my example db, but with nice big buttons on the real db) to go back and forth among the different diets at their table. If I filter by DIETID, then they only see one diet.

 

[Pat Hartman]

You will need code in the form's load event to position to the record passed by the OpenArgs if you want to use the filter option.

Personally, I never use filters. Mostly because the majority of my BE's are ODBC and even if they start as Jet/ACE, they always have the option of being upsized. Once they are upsized, filters defeat the purpose of upsizing to SQL Server or other RDBMS.

Most of the time, the user knows what record he wants to interact with so he can pick from a combo. Sometimes he needs to search. In that case, I build forms with multiple search criteria and I use the criteria as the where clause for a query and load a subform. The user can then manually filter using the integrated functionality of the DataSheet view. Then the user can double click on a row to open the detail edit form for the record.

 

[theDBguy]

Thanks for looking into this. Both forms are problematic. Attached is an example db. When you toggle back and forth, you don't stay on the same diet.

Hi. I'm not sure I understand what you're trying to do, but I gave it a shot.

 

[isladogs]

I'm left handed but in 25 years of using Access forms, I've never felt that they were laid out to suit right handed people or that I was being disadvantaged in some way. That is also true for touchscreen devices
So I don't understand the point of this. Do explain

 

[cricketbird]

@isladogs

I'm left handed but in 25 years of using Access forms, I've never felt that they were laid out to suit right handed people or that I was being disadvantaged in some way. That is also true for touchscreen devices
So I don't understand the point of this. Do explain

We make specialized diets at different workstations (8 prep stations total). Each workstation has 100-200 diets that they make daily using a touchscreen mounted above the table. They use the "next" (and occasionally "previous") buttons to navigate from diet to diet. If the buttons are located on the opposite side of their body from their dominant hand, then they either have to use their weaker hand to hit "next", or they reach their dominant hand across their body to the other side of the screen, often with a knife in their hand. Both of these are ergonomically not good and potentially dangerous. It makes sense to have leftie and rightie versions. We also have a large print version for folks who need that too. This makes work safer and accessible to everyone.

 

[cricketbird]

Hi. I'm not sure I understand what you're trying to do, but I gave it a shot.

Thank you so much for looking into it - I really appreciate your time.

It's still not working quite right. If I select Table Two and navigate (using the navigation arrows at the bottom) to diet 7, then when toggling, I should still see only records for Table Two and still be viewing Diet 7.

In your example, when I go to Table Two/diet 7 and toggle, I end up on diet 7, but filtered to just diet 7 (or maybe linkid 12, which is also not correct). I can no longer navigate to the other diets on Table Two. I still need it filtered to Table Two.

Thank you.

 

[theDBguy]

Thank you so much for looking into it - I really appreciate your time.

It's still not working quite right. If I select Table Two and navigate (using the navigation arrows at the bottom) to diet 7, then when toggling, I should still see only records for Table Two and still be viewing Diet 7.

In your example, when I go to Table Two/diet 7 and toggle, I end up on diet 7, but filtered to just diet 7 (or maybe linkid 12, which is also not correct). I can no longer navigate to the other diets on Table Two. I still need it filtered to Table Two.

Thank you.

Yes, that's the part I wasn't understanding. I'll take another look.

 

[isladogs]

Thanks for the explanation as to why you do this.

A suggestion to simplify things
1. Have a table which stores user preferences (left/right)
2. In your main menu form, add code which automatically opens the correct version of each form based on the user preference.
OR
3. in each form, add code to the load event to move the relevant controls to the left/right side as appropriate.
Then you would only need one version of each form

 

[theDBguy]

Thank you so much for looking into it - I really appreciate your time.

It's still not working quite right. If I select Table Two and navigate (using the navigation arrows at the bottom) to diet 7, then when toggling, I should still see only records for Table Two and still be viewing Diet 7.

In your example, when I go to Table Two/diet 7 and toggle, I end up on diet 7, but filtered to just diet 7 (or maybe linkid 12, which is also not correct). I can no longer navigate to the other diets on Table Two. I still need it filtered to Table Two.

Thank you.

Okay, hopefully this is closer to what you wanted.

 

[arnelgp]

you may also try this.

 

[cricketbird]

Okay, hopefully this is closer to what you wanted.

Thank you! This worked! I'm still figuring out why using linkid works when using dietid didn't, but this gave me a functional result.

 

[cricketbird]

you may also try this.

Thank you! Very interesting solution!

 

[cricketbird]

Thanks for the explanation as to why you do this.

A suggestion to simplify things
1. Have a table which stores user preferences (left/right)
2. In your main menu form, add code which automatically opens the correct version of each form based on the user preference.
OR
3. in each form, add code to the load event to move the relevant controls to the left/right side as appropriate.
Then you would only need one version of each form

Great idea! I immediately went and implemented this for two other forms that I was having similar issues. It won't work for this particular form for various reasons, but I am definitely going that route in other places. Thank you! Not sure why that never occurred to me but it is a beautiful solution

 

[theDBguy]

Thank you! This worked! I'm still figuring out why using linkid works when using dietid didn't, but this gave me a functional result.

When you try to navigate to a record, it works better if you use a unique value, which usually means the primary key.