Solved How to determine the individual record of a continuous form? (1 Viewer)

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
This might be a bit long, thanks for your patience. I'm providing it so you know my approach, even if my approach needs to change.

On one of my very first forms I incorporated some code from the internet that opens a windows folder based on the value of a textbox in a continuous form. I added comments so that I could learn what the sub was doing in case I wanted to use the same concepts in the future. It works great. Since the sub is called on the click event of the textbox used to supply the value, everything is rainbows and butterflies because Access know which individual record value to pass to the sub based on the click providing the appropriate focus. I tweaked the suggested code to meet the needs of that particular form. Here is the code:

Code:
Private Sub UnitNum_Click()
   Dim Shex As Object
   Dim frmList As Form
   Dim UnitNum As Control
   Dim Loc As String
   Dim Loc2 As String
   Dim tgtfile As String
   Dim tgtfile2 As String
'Set object definitions
   Set frmList = Forms("JeffAvailability")
   Set UnitNum = frmList.UnitNum
   Set Shex = CreateObject("Shell.Application")
'Pull the unit number and set the folder location
   Loc = UnitNum.Value
   tgtfile = "\\KCI-Cambridge\Kingsly Master Files\1 - UNIT FILES\" & Loc & "\"
   'Debug.Print tgtfile
  
   If Not Dir(tgtfile, vbDirectory) = vbNullString Then 'make sure folder exists
        Shex.Open (tgtfile)
   Else
        'Check if a unit file without year prefix exists
        Loc2 = Mid(Loc, 4)
        tgtfile2 = "\\KCI-Cambridge\Kingsly Master Files\1 - UNIT FILES\" & Loc2 & "\"
        Debug.Print tgtfile2
        If Not Dir(tgtfile2, vbDirectory) = vbNullString Then 'make sure folder exists
            Shex.Open (tgtfile2)
        Else
            MsgBox "The Unit File Does Not Exist"
        End If
   End If
  
End Sub

So, in my current form, I'm trying to do something similar so that the user can click on an inserted image that is repeated in each record of the continuous form. Then a value from that record can be extracted and passed as a criteria to a query to be used as the recordsource for another form. The code works in general. A value is extracted and the query (as a placeholder for my form) is opened. However, the value extracted is based on the last focus, which is NOT updated to the continuous form row which contains the image that was clicked since it has no direct connection to the underlying record. I made the sub public as I assumed it needed to be to pass info to a different query/form. Here is the code:

Code:
Public Sub Image13_Click()
'Create variables and objects
    Dim frmList As Form
    Dim ProjID As Control
    Dim Proj As String
'Set object definitions
    Set frmList = Forms("FrmProjList")
    Set ProjID = frmList.ProjID
'Pull the project name ID and assign to variable
    Proj = ProjID.Value
    
    Debug.Print Proj
'Launch Project Task Form
    DoCmd.OpenQuery QueryName:="QryTaskTracker"

End Sub

I also discovered, that his whole VBA sub is likely unnecessary as I can directly chose ProjID as a criteria in my query. It actually works too. But, it has the same issue. The value of ProjID when the query runs is based on the last focus which is not updated to the needed record based on the click on the image. Here is the query design:

Query2.PNG


I gave you this long-winded background because sometimes I don't use the right terminology and I hoped this would make it clear what I was trying to do and what isn't working correctly. The main crux of the matter is how do I either: set the focus to the right row in the continuous form on the click event of the image or determine in some way which row the image is tied to and use that to extract the appropriate value. Thanks for taking the time to read through and thanks for any help!

P.S. A separate issue that would be nice to address after this important issue is sorted is...I couldn't find a way to use my variable from subroutine in the query. I'd like to tackle that idea/syntax later so that I know the right way to do that (if possible) in the future.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,357
Hi.

1. You can replace the image with a button or overlay a transparent button on top of the image

2. You can assign the value of your variable to a TempVar and then use the TempVar in your query
 

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
@ the DBguy - I'll circle back to #2 in minute...

How does replacing the image with a button help me? I was about to try that anyway but I couldn't think of why that would be different?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,357
@ the DBguy - I'll circle back to #2 in minute...

How does replacing the image with a button help me? I was about to try that anyway but I couldn't think of why that would be different?
An image control cannot receive focus, but a button can.
 

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
Wow, it's that simple. Thank you!
It works great now. I put a transparent button over my image and left the ProjID in the query itself.

However, I'd like to learn how to do this via option #2 for use in the future. What is the TempVar and how do I use it as you stated?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,357
Wow, it's that simple. Thank you!
It works great now. I put a transparent button over my image and left the ProjID in the query itself.

However, I'd like to learn how to do this via option #2 for use in the future. What is the TempVar and how do I use it as you stated?
In your code, you could add something like:
Code:
TempVars!Proj=Me.ProjID.Value
Then in your query, you could use a criteria like:

TempVars!Proj
 

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
Ok. That seems simple enough. Let me make sure I understand the concept. Using a TempVar is like a bridge between a VBA variable and something that can actually be used by Access components as a variable outside of the VBA environment.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,357
Ok. That seems simple enough. Let me make sure I understand the concept. Using a TempVar is like a bridge between a VBA variable and something that can actually be used by Access components as a variable outside of the VBA environment.
Correct, but you can also use TempVars in macros.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:08
Joined
Feb 19, 2002
Messages
42,970
the value extracted is based on the last focus
Once a control has lost focus, a different record might be current.

You need to use the Click event of the image or the dbl-click if that makes more sense. Focus will be on the correct record if you use the correct event.
 

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
the value extracted is based on the last focus
Once a control has lost focus, a different record might be current.

You need to use the Click event of the image or the dbl-click if that makes more sense. Focus will be on the correct record if you use the correct event.
The current solution works even if it lacks a bit of elegance. I'll have to experiment and see. The code was in the click event of the image, but that didn't get focus on the correct record. Although, if what theDBguy says is true "an image control cannot receive focus" than it doesn't matter what event on the image control triggers my code as it will never receive focus and thus not have the focus needed by the child form being launched.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:08
Joined
Feb 19, 2002
Messages
42,970
The image has a click event so it can get the focus. Maybe you're using a different control type
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,357
if what theDBguy says is true "an image control cannot receive focus"...
Should be easy enough to verify. Create a button and try setting the focus to the Image control when you click that button, and see what happens. For example:
Code:
Me.ImageControlName.SetFocus
 

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
The only events triggers are: Click, Double-Click, Mouse Up, Mouse Down, and Mouse Move.
Here is the form:
FrmProjList.PNG


At the time of testing I had visible fields (now hidden) with the ProjID I need to pass to the query to get the proper task/hours. If I had the cursor in one of those fields (say the first record) and then clicked on the Edit Hours icon for the 3rd record, the query would pull the task/hours for the the 1st project not the 3rd project because the click did not claim the appropriate focus. When I added the transparent button and moved the code to it's click event, everything worked fine.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:08
Joined
Oct 29, 2018
Messages
21,357
The only events triggers are: Click, Double-Click, Mouse Up, Mouse Down, and Mouse Move.
Here is the form:
View attachment 89024

At the time of testing I had visible fields (now hidden) with the ProjID I need to pass to the query to get the proper task/hours. If I had the cursor in one of those fields (say the first record) and then clicked on the Edit Hours icon for the 3rd record, the query would pull the task/hours for the the 1st project not the 3rd project because the click did not claim the appropriate focus. When I added the transparent button and moved the code to it's click event, everything worked fine.
Hi. You could also save one control by simply replacing the image control with your button and assign the same image to the button's Picture property (of course, it won't be transparent anymore). Just a thought...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:08
Joined
Feb 19, 2002
Messages
42,970
Wow, Image doesn't change focus to the row on which you click. I can understand why the control itself doesn't get the focus but it should move focus to a different row. And it's even worse than that. I added a button to the main form and clicking on the button on the main form runs the code but doesn't force the subform record to save. Edit one of the subform records and watch the record selector when you press the mf button. Then edit the subform record and click into the tablename field on the mf. Wow again.

All I can say is use a command button and put an image on that rather than using an image control.
 

Attachments

  • ClickEvent.zip
    471.4 KB · Views: 576

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
Now if only a command button could be made as pretty as an image and we wouldn't even have needed this thread! :)
Of course, a transparent button over an image works, but is less elegant of a solution.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:08
Joined
Sep 12, 2006
Messages
15,613
Now if only a command button could be made as pretty as an image and we wouldn't even have needed this thread! :)
Of course, a transparent button over an image works, but is less elegant of a solution.

It can. Just browse to the picture you want in the button/format/picture (bmp or ico file, I see)
 

JMongi

Active member
Local time
Today, 06:08
Joined
Jan 6, 2021
Messages
802
Unless I am missing something, unfortunately, the resizability (word?) of the image within the button doesn't lend itself to being useful. Plus, making the button transparent makes the image transparent as well. That's why I said AS pretty. You could use an image with a command button but you can't make it AS pretty as using an image.
 

Users who are viewing this thread

Top Bottom