Access 2010 Query or Event Procedure Issue?

[justphilip2003]

The Gallery db and a text version of the Carving Table are attached. The enclosed Event Procedur beyond the isolate area of record fetching are not in any semblance of good order; I have been changinging things trying to reslove problems. TIA Phil

[June7]

If you want to Debug.Print the value of a variable, don't include the variable within quotes:
Debug.Print pathname

There is one record with no CarvingNR value. There are some duplicate CarvingNR values in Carving Table. How will users know which one to select from the combobox? Suggest including the JPEG_ID in the RowSource.

Since the query filter parameter is under IDCarv, the combobox properties should be:
RowSource: SELECT IDCarv, CarvingNR, JPEG_ID FROM [Carving Table] ORDER BY CarvingNR;
ColumnCount: 3
ColumnWidths: 0";0.5";0.5"
BoundColumn: 1

Odd, I can't get the combobox to allow typing entry.

IDCarv is autonumber primary key. Users normally have no reason to be aware of autonumber primary key as it should have no meaning to them.

Revise code for the combobox AfterUpdate event:
CalVer = 1
Me.Requery
JPEG_Hold = JPEG_Id
MsgBox "JPEG name = " & JPEG_Hold & " and CarvingNR value = " & Me.Select_CrNr.Column(1)
Sr_Message = "**" & JPEG_Hold & "**"
Call JPEG_Display

You should run Debug Compile - it will expose error: Clear_Scpreen

You posted the db as a 2003 (mdb) version but you show 2010 in your profile. Do you need this db to be compatible with Access 2003? If you want to use 2010 functionality, all of this code related to retrieving image can be replaced with a simple function:

Function GetPath()
GetPath = CurrentProject.Path
End Function

Then in the Image control ControlSource property:
= GetPath() & "\Gallery_Pics\" & [JPEG_ID] & ".jpg"

[justphilip2003]

I wish to thank you for helping me to relearn Access VBA coding! Two picture files were *.tif files and that caused some mysterious can't find messages. The duplicate numbers are all “0” and they have been assigned out of range 2### numbers. The blank CarvingNr should be 12 and data similar to 13; that too has been fixed. The select statement was as you describe and it did not work any different than this code; the current code appears to work but should I revert back to your suggested code? When the combobox freezes, you must go to the Event build screen and press reset. Most of the code in Private Sub Select_CrNr_AfterUpdate() was used for debugging to show values at various stages. The two necessary lines are “JPEG_Hold = JPEG_Id” and “Call JPEG_Display”; this was done to freeze the query search at the current record and allow the “Private Sub SeeMore_Click()” display other views of the carvings (v2, v3, etc.). I also cleaned up the “Private Sub SeeMore_Click()” and it works as it should. Now if I could only resove the query issue. I have been running debug but I apparently overlooked the misspelled tag. I thought this db was converted to 2010, I am using “pathname = CurrentProject.Path” in “Private Sub JPEG_Display()”; are they different codes? I was wondering if the path function should be in the Form OnOpen event procedure, it would only need be executed once or should I leave it where it is?
I repaired the blank table entry, corrected the misspelled tag and tested the code. I selected combobox value “8” the logic displayed carving “58”, which has IDCarv =1. Same outcome for all combobox selections. TIA Phil

[June7]

I did the edits I described and the combobox AfterUpdate code works properly. I did do some edits on my post, make sure you followed the final version.

I also tested the 2010 functionality and that works. Use this option and I expect issues with the image display will go away.

The duplicate numbers were not all "0". There were duplicate CarvingNR values for 95, 96, 111, etc, each showing a v1 and v2 jpg. I handled this by including JPEG_ID in the combobox RowSource.

[ssanfu]

In addition to June's comments.........

I selected combobox value “8” the logic displayed carving “58”,

I noticed that too. I selected different carving numbers (after modifying the combo box row source) and the value in the message box was always 58.
Since there is not a control named "CarvingNR", the value is coming from the form record source.
When the form is opened, the first record in the form record source query is "IDCarv" = 1 and "CarvingNR" = 58.
The "TITLE" control is always "YODA". You are always at record "1 of 146". (Look at the navigation buttons at the bottom of the form)

After you select a record from the combobox "Select_CrNr", the after update event code executes. But nowhere in that code are you changing the current record to the IDCarv record selected in the combo box.
See: http://allenbrowne.com/ser-03.html

Or you could set a filter to just display the one record.



In the combo box after update event code you have:

JPEG_Hold = JPEG_Id

the value of JPEG_ID is coming from the first record of the form record source(query) since there is not a control named "JPEG_ID".
You also have that same line in the Sub JPEG_Display() code. (It is not needed)


Also, in the Sub JPEG_Display() code, you have:

    If Dir(File_Path) = "" Then                       'null indicating not found

NULL is not the same as an empty string (""). I would use

    If Len(Trim(Dir(Me.File_Path))) = 0 Then           'indicating not found

(I added "Me." since "File_Path" is a control on the form)


I also would not use module-level variables. I would pass variables (like JPEG_ID) to the sub:

Private Sub JPEG_Display(JPEGID As String)

You would call the sub using something like:

   Call JPEG_Display(JPEG_Hold)

-------
These CarvingNR numbers also have duplicates:
95
96
111

[June7]

One of the edits I did was to uncomment Me.Requery line. I will check everything again first chance I have (my home laptop died this morning) but as far as I could tell, all worked correctly last night after the edits.

[ssanfu]

For me, the code would run to where the picture was supposed to open - but I didn't add any pictures...

Looking at the form, it always stayed at "Record 1 of 146."

Nowhere did(do) I see either of these in the after update code:

Me.bookmark =

or

Me.Filter = "[fieldName] = " & Me.Select_NR
Me.FilterOn =true

Maybe I am missing something??

[June7]

The form has a parameterized query as RecordSource. The parameter is reference to the combobox. The AfterUpdate requeries the form.