subform record is not changing key field

Hi -
On my app I have a form/subform group and they are (almost) correct. The situation is that I need to show a different image on each of the subform records and they are always showing the same (and initial) image.

Please check below the screenshot

Screen-Shot-2018-02-11-at-11.50.37.png
The keys to the records are OK and I am using the "ON CURRENT" event to modify the source images according to the key.

I did put a break point (and debug.print) on the code to change the image and, surprisingly it is always showing the initial record (30001). So it looks that I should not be using the "ON CURRENT".

The related coed is:

    q03A_imagemDef.SQL = strSetSQL
    Set q03A_imagemSet = LifeLingerDB.OpenRecordset("tblimagem", dbOpenDynaset)
    With q03A_imagemSet
        If .RecordCount = 0 Or (.EOF And .BOF) Then
            ' no record
            Call logMe("Form_Current - frmImagemNovaThumbNail", "end")
            .Close
            Set q03A_imagemSet = Nothing
            Exit Sub
        Else
            imgSnapShot = !imgOLE
            frm_csxI_imgScreenShot.ReadBinary2 imgSnapShot
            Me.frm_imgScreenShot.PictureData = Me.frm_csxI_imgScreenShot.PictureData
            .Close
            Set q03A_imagemSet = Nothing

        End If
    End With

and, beforehand, I do set the select, in this manner:

    ' locate current idImage OLE
    strSetSQL = "SELECT "
    strSetSQL = strSetSQL & "tblimagem.IDpaciente, "
    strSetSQL = strSetSQL & "tblimagem.IDExame, "
    strSetSQL = strSetSQL & "tblimagem.IDimagem, "
    strSetSQL = strSetSQL & "tblimagem.imgSelect, "
    strSetSQL = strSetSQL & "tblimagem.imgOLE, "
    strSetSQL = strSetSQL & "tblimagem.imgTipo, "
    strSetSQL = strSetSQL & "tblimagem.imgLesao, "
    strSetSQL = strSetSQL & "tblimagem.imgNotas "
    strSetSQL = strSetSQL & "FROM tblimagem "
    strSetSQL = strSetSQL & "WHERE ((tblImagem.IDpaciente = "
    strSetSQL = strSetSQL & Nz(Forms![_intData].frm_IDpaciente, "000000") & ")"
    strSetSQL = strSetSQL & " AND "
    strSetSQL = strSetSQL & "(tblimagem.IDExame = "
    strSetSQL = strSetSQL & Nz(Forms![_intData].frm_idExame, "000000") & ") "
    strSetSQL = strSetSQL & " AND "
    strSetSQL = strSetSQL & "(tblimagem.idImagem = "
    strSetSQL = strSetSQL & Me.frm_idImagem & ") "
    strSetSQL = strSetSQL & ");"
    Set q03A_imagemDef = LifeLingerDB.QueryDefs("q03A_imagem")
    q03A_imagemDef.SQL = strSetSQL
    Set q03A_imagemSet = LifeLingerDB.OpenRecordset("tblimagem", dbOpenDynaset)

the actual Key is

Me.frm_idImagem

which is different on each record (the yellow text box) but keeps the same in the SELECT

can anyone help?
João serras-pereiraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can use the method (and code) described here:

Show pictures directly from URLs in Access forms and reports
João serras-pereiraAuthor Commented:
Hi Gustav -
Thanks for the hint but I am afraid that it won't work. I am using images stored in OLE objects on the DB. It looks that I am wrongdoing something on the form  query or using a wrong event.
For instance, I can't understand why the current event is fired so many times (at least 6, with only 2 records (!)

BTW: I am using your code with  remarkable success on other app. Great code! Thanks for it.
Gustav BrockCIOCommented:
OK, but can't you just bind the image control to that field?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

João serras-pereiraAuthor Commented:
Nothing shows :(

I need to

1. select the record using a separate query (the subform is dependent on Q03, so, inside the "on current" even I prepare a second query, just to select the actual "current " OLE field, based on a control in the form (the yellow one -  frm_idImagem). This new query is Q03A, and the output should look okay, but it is not as Me. frm_idImagem is kept with the value of the very first record on the Q03 record set (the current event is fired 7 times (!) - I did put a debug.print just for it; I would expect to fire only 2 times one per record in q03.

After selecting the proper record, I need to put the OLE inside csxImage and then, from it, in a standard image control.

It looks cumbersome (and is) but it is like that for 3 reasons:

1. the OLE refuses to display on the standard image control without this pass
2. csXImage, which I bought, in an access form (64bit) looks having a bug (Simon is on it) - it alters the control frame size without any one asking for it.
3. I need to make some changes in the image and csXImage is really Ok for the needs

so, back to the question: for the time being I am stuck to this workaround approach - but really, I amd wrongdoing something in the form.
John TsioumprisSoftware & Systems EngineerCommented:
Take a look here for an approach...although if you want flexibility the best solution is using an ActiveX control like ListView or MSFlexgrid
João serras-pereiraAuthor Commented:
Thanks for the hint but...
I went though the sample db and read the conversation (somehow the sample is not showing any images, bout I went through the source and 'got the idea.

But my problem of showing an image is solved. what is not solved is the display of the continuous form as it keeps showing the wrong OLE as the query is incorrect (something is wrong in the ON CURRENT...
Gustav BrockCIOCommented:
But since you seem to use the csXImage control, why not use that for displaying the images?
João serras-pereiraAuthor Commented:
Hi Gustav -
I can't. There is something in the control as I told before:

1. the OLE refuses to display on the standard image control without this pass
2. csXImage, which I bought, in an access form (64bit) looks having a bug (Simon is on it) - it alters the control frame size without any one asking for it.
3. I need to make some changes in the image and csXImage is really Ok for the needs
Gustav BrockCIOCommented:
OK. Then, I guess, your option is to extract the picture to a file, and then retrieve the file for display.
Clumsy, yes, but should be possible.
João serras-pereiraAuthor Commented:
Well... but could not the problem be in my code? After all is always selecting ONLY THE FIRST RECORD....
Gustav BrockCIOCommented:
It looks like you retrieve a picture at the OnCurrent event.
But if the picture control is unbound, all records will show the same picture.
The picture control has to be bound for each record to show individual pictures.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
João serras-pereiraAuthor Commented:
Well...
Thinks are improving.. but not much
For the moment, I totally forgot the OLE approach and came back to the old file download approach. Now what happens is the following:

1. On LOAD

Shows ALL images equal

Screen-Shot-2018-02-11-at-16.14.55.png
2. after selecting  the second record so it fires the "ON CURRENT"

Screen-Shot-2018-02-11-at-16.14.48.png
I am using ONLY the  standard image control and my complete code for the ON CURRENT is:

Private Sub Form_Current()

    Dim strFileName As String
    Dim LifeLingerDB As DAO.Database
    Dim q01_pacienteSet As DAO.Recordset
    Dim q01_pacienteDef As QueryDef
    Dim q02_exameSet As DAO.Recordset
    Dim q02_exameDef As QueryDef
    Dim q03_imagemSet As DAO.Recordset
    Dim q03_imagemDef As QueryDef
    Dim q03A_imagemSet As DAO.Recordset
    Dim q03A_imagemDef As QueryDef
    Dim strSetSQL As String
    Dim bUpdate As Boolean
    Dim Twat As Date
    
    Dim nFileNum As Integer
    Dim byteData() As Byte
    Dim varStatus As Boolean
    
    Dim imgSnapShot As Variant


    Dim varImg As Variant

'On Error GoTo errorTrap

    Call logMe("Form_Current - frmImagemNovaThumbNail", "start")
    strFileName = "c:\lifelingerIIimg\[" _
             & Format(Me.frm_IDpaciente, "000000") _
             & "." _
             & Format(Me.frm_idExame, "000000") & _
             "." _
             & Format(Me.frm_idImagem, "000000") _
             & "].jpg"
            Me![frm_imgScreenShot].Picture = strFileName
    Call logMe("Form_Current - frmImagemNovaThumbNail", "end")
    Exit Sub
        
errorTrap:
    MsgBox "[Form_Current - frmImagemNovaThumbNail]." & "[" & Err.Number & "].[" & Err.Description & "]"
    Call logMe("Form_Current - frmImagemNovaThumbNail", "errorTrap:" & "[Form_Current - frmImagemNovaThumbNail]." & "[" & Err.Number & "].[" & Err.Description & "]")
    Resume Next
 
End Sub

Open in new window


so I am really wrongdoing something as it does not change just th current image but ALL the images....
João serras-pereiraAuthor Commented:
Hi Gustav -
Thanks for the help. It is working with external files and, I have better fish to fry than losing my mind on OLE. I'll wait for Simon's correction but, the workaround also works for him as I am having an invisible  csXimage performing the required functionality, and showing the output in the image control...
Gustav BrockCIOCommented:
You are welcome.
I'm confident that Simon can fix it.
João serras-pereiraAuthor Commented:
yeah... And I am a bit eager to abandon ms/access and move to a more independent multi-platform web based IDE.

What would you recommend?
Gustav BrockCIOCommented:
Well, that certainly depends.
There are many factors, including non-technical as audience, time, and budget as well as own preferences and skills.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.