JMongi
Active member
- Local time
- Today, 10:10
- 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:
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:
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:
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.
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:
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.