Solved Label not displaying properly after selecting a record (1 Viewer)

ZeRoX99

New member
Local time
Today, 17:04
Joined
Jun 18, 2021
Messages
7
Hello,

I apologize in advance if this is not the correct place to post my issue. Wasn't sure if it was a VBA related issue or just a form related issue.

I just started a bit of VBA coding a few days ago so I'm relatively new to it and coding in general. I've written some small code which I'm sure some veterans should be able to easily grasp. Unfortunately, I'm running into an issue and one of my features is not working as expected. I'm kind of stumped and really at a total loss. There's got to be something I'm not seeing and maybe someone else can point out for me.

I just want my code to work right now. Not trying to optimize it or anything. And again, I'm new to VBA so I'm not going to know the very advanced stuff. Just want it simple.

Note: I am using Access 2007

This is a long read so thanks to those who take their time to read it in advance! Any advice is much appreciated! Ask any additional questions or request more pieces of the code if necessary.


Background Information:
I have a table of people in which I created a form that shows their picture. I have 1 column dedicated to the file path of where their picture is located locally for reach record. I named that column "Photo". Some records have multiple photos. I have created a photo counter that displays two numbers; 1st number tells me which photo is currently showing (aka the file path currently set in the "Photo" column of that record) and a 2nd number (let's call this the total) tells me how many pictures they have associated to that record. All the pictures are formatted with their name and a number (eg. BobJoe1.jpg, BobJoe2.jpg, etc.).


What I am attempting to achieve:
When I move from record to record, I want that 2nd number to display the correct total number of pictures associated with that record. The code runs when focus from one record changes to another record.

Example:

If I select Mary Sue (who has only 1 picture), I want that 2nd number (total) to display '1'.

If I select Bob Joe (who has a total of 5 pictures), I want that 2nd number (total) to display '5'.


The Problem:
For whatever reason, this is what's currently happening. When I initially open the form and select the record for Bob Joe (taking the example from above), it correctly shows 5 for the total. However when I select, Mary Sue, the total stays at 5, which is incorrect because she only has 1 picture. No matter how many different records I select after that, it stays at 5.

The same thing happens in reverse as well. When I select Mary Sue right after opening the form. The total is 1. Ok, fine. That's correct. But when I select Bob Joe, the total does not change to 5 as expected. And again, every record I select after that, the total is always 1.

So as far as I can tell, it only seems to work on the first record I select immediately after I open the form, but stops working when I select different records after the initial one.

I've tried to figure it out myself for the past day or two on why this is happening. I tried putting MsgBox codes to figure out the flow of data, but I just don't see what the issue is (if there is any). The flow seems to be fine unless I'm not seeing something. I've tried scouring the internet for people with similar issues and most of what is being advised is to use Me.Paint. I have tried adding this but it doesn't seem to do the trick.

As far as I can tell, the calculation of the various variables is correct as I have used Breakpoints and the Watch Item tool to go through the code line by line. It's just displaying the proper number after moving from record to record that's the issue.

Some things I've noticed while attempting to troubleshoot:
I've just noticed something unusual while I was testing in Layout view of the form which is probably tied to my main problem. So immediately after I open the form, I select a record (Bob Joe), that I know has 5 photos, the caption will update properly. Then I select another record (Mary Sue), that I know has 1 photo, the caption will update but it will overlap numbers with the previous record I selected. In other words, I see both the number 1 and number 5 for that label like it's been written on top of each other. But if I select the label control once, the caption is the correct number as indicated in the property sheet section. Click once more, the previous number disappears and the number is properly displayed.

Here's what it looks like through each step:
I've also tried something a little different, by adding Me.Label32.Visible = False to see if it will properly make that label disappear after running through the code. Not surprisingly, it does not properly make the label invisible. However, if I switch from Form View to Layout View, the label will become invisible. And when I switch back to Form View, the label stays invisible.

Me.Repaint does not seem to solve this as I already have it in my code. This is, of course, while in Form View.


What the Photo counter looks like on the form:

Screenshot of entire form (with data white'd out):

Visualized Flow of Data (which I believe is how it is flowing):


Code that should be relevant to my issue:

Module:

I have a macro that automatically executes this module upon opening the database. This is the module where I establish the global variables and do a little bit of initialization:
Code:
Option Compare Database
Option Explicit

Public num1 As Integer
Public num2 As Integer
Public str1 As String
Public str2 As String
Public strCombined As String

Public Function intVar()
    num1 = 1
    str1 = "C:\Pictures\"
 
End Function


Before you read the code below:
Label29
is the current selected Photo number
Label32 is the Total Photo number (this is the label that's giving me issues)
I added in separators with '-----' to make it easier to read here. It's not in the actual code itself.

Additional code:
Code:
'Executes upon selecting a record. Checks if Photo column contains any data.
Disables/Enables "Add Photo Filepath" button based on results. Also sets
default filepath for 1st picture based on results.

Private Sub Form_Current()

'If False (has data) and...

If IsNull([Photo]) = False Then

'... is set to either "?" or "N/A" values then disable button

    If [Photo] = "?" Or [Photo] = "N/A" Then
        Me.Label32.Caption = 1
        Me.Command30.Enabled = False
        Me.Repaint
        Me.Refresh
     
 
'... is not set to either "?" or "N/A" values, then disable button and put
in default filepath for 1st picture

    Else
     
        Me.Command30.Enabled = False
        str2 = Replace([Full Name], " ", "")
        strCombined = str1 & str2 & "1.jpg"
     
        Me.Photo.Value = strCombined
        Call checkLastPicture
        Me.Repaint
        Me.Refresh
     
    End If

'If True (has no data), then enable button and set total Photo # (Label32) = 1

Else

    Me.Command30.Enabled = True
    Me.Label32.Caption = 1
    Me.Repaint
    Me.Refresh
 
End If
End Sub
---------------

'Used for checking if record has multiple Photo files upon selecting it

Public Sub checkLastPicture()
        'num2 set to 1 so that it begins checking with Photo file #1
    
        num2 = 1
     
        'Uses FileExists function to check if file exists and return value of
either True or False. If True, add 1 to num2 and check if the next numbered
file exists.

    Do While FileExists(strCombined) = True
        num2 = num2 + 1
        str2 = Replace([Full Name], " ", "")
        strCombined = str1 & str2 & num2 & ".jpg"
     
        'If False, subtract 1 from num2 and update Photo # (Label32)

        If FileExists(strCombined) = False Then
         
            num2 = num2 - 1
            Me.Label32.Caption = num2
            Me.Repaint

        Exit Do
        End If
    Loop
End Sub
----------------

'To check if file exists and return value of either True or False

Public Function FileExists(filepath As String) As Boolean
Dim TestStr As String
    TestStr = ""
    On Error Resume Next
        TestStr = Dir(filepath)
        On Error GoTo 0
        If TestStr = "" Then
            FileExists = False
        Else
            FileExists = True
        End If

End Function
---------------

'Executes everytime you update a record and move to a different record.
Initalizes num1 and Photo # (Label29). Resets the Label29 counter back to 1
when moving to a different record

Private Sub Form_AfterUpdate()
    Call intVar

    Me.Label29.Caption = num1
    Me.Label32.Caption = num2
    Me.Repaint

End Sub
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
13,727
can you check if replacing your checkLastPicture will work for you:
Code:
'Used for checking if record has multiple Photo files upon selecting it

Public Sub checkLastPicture()
        'num2 set to 1 so that it begins checking with Photo file #1
      
        Dim sCombi As String
        Dim sFile As String
        
        str2 = Replace([Full Name], " ", "")
        sCombi = str1 & str2 & "*.jpg"
        sFile = Dir$(sCombi)
        num2 = 0
        Do Until Len(sFile) = 0
            num2 = num2 + 1
            sFile = Dir$
        Loop
        
        Me.Label32.Caption = num2
        
        'num2 = 1
        
        
        'Uses FileExists function to check if file exists and return value of
        'either True or False. If True, add 1 to num2 and check if the next numbered
        'file exists.

        
    'Do While FileExists(strCombined) = True
    '    num2 = num2 + 1
    '    str2 = Replace([Full Name], " ", "")
    '    strCombined = str1 & str2 & num2 & ".jpg"
    '
    '    'If False, subtract 1 from num2 and update Photo # (Label32)
'
    '    If FileExists(strCombined) = False Then
    '
    '        num2 = num2 - 1
    '        Me.Label32.Caption = num2
    '        Me.Repaint
'
    '    Exit Do
    '    End If
    'Loop
End Sub
 

ZeRoX99

New member
Local time
Today, 17:04
Joined
Jun 18, 2021
Messages
7
can you check if replacing your checkLastPicture will work for you:
Code:
'Used for checking if record has multiple Photo files upon selecting it

Public Sub checkLastPicture()
        'num2 set to 1 so that it begins checking with Photo file #1
     
        Dim sCombi As String
        Dim sFile As String
       
        str2 = Replace([Full Name], " ", "")
        sCombi = str1 & str2 & "*.jpg"
        sFile = Dir$(sCombi)
        num2 = 0
        Do Until Len(sFile) = 0
            num2 = num2 + 1
            sFile = Dir$
        Loop
       
        Me.Label32.Caption = num2
       
        'num2 = 1
       
       
        'Uses FileExists function to check if file exists and return value of
        'either True or False. If True, add 1 to num2 and check if the next numbered
        'file exists.

       
    'Do While FileExists(strCombined) = True
    '    num2 = num2 + 1
    '    str2 = Replace([Full Name], " ", "")
    '    strCombined = str1 & str2 & num2 & ".jpg"
    '
    '    'If False, subtract 1 from num2 and update Photo # (Label32)
'
    '    If FileExists(strCombined) = False Then
    '
    '        num2 = num2 - 1
    '        Me.Label32.Caption = num2
    '        Me.Repaint
'
    '    Exit Do
    '    End If
    'Loop
End Sub

While the calculation seems to work, the issue with the proper display of that label remains the same, unfortunately.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
13,727
maybe:

Me.Label32.Caption = num2 & ""
Me.Refresh
 

ZeRoX99

New member
Local time
Today, 17:04
Joined
Jun 18, 2021
Messages
7
maybe:

Me.Label32.Caption = num2 & ""
Me.Refresh

Changed your code to add the above. Same issue.

I know the caption value is set correctly cause when I switch to Layout View, the label suddenly updates properly, and when I click on the Label, the caption value shows the correct number (in the Property sheet).

Isn't this such a strange issue? I almost feel like it's not even the code that's the issue
 

bastanu

AWF VIP
Local time
Today, 14:04
Joined
Apr 13, 2010
Messages
859
Maybe replace the label with a text box (Locked=True and Enable=False it so it can't get the focus) and set its value to num2:
Me.txtLabel=num2

Cheers,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
13,727
check if the label does not touch/overlays with another control.
is the label Grouped? remove the grouping (arrange->remove layout)
 

ZeRoX99

New member
Local time
Today, 17:04
Joined
Jun 18, 2021
Messages
7
Maybe replace the label with a text box (Locked=True and Enable=False it so it can't get the focus) and set its value to num2:
Me.txtLabel=num2

Cheers,
Doesn't a textbox add a new column of data to the table? Or am I wrong about that?
 

ZeRoX99

New member
Local time
Today, 17:04
Joined
Jun 18, 2021
Messages
7
check if the label does not touch/overlays with another control.
is the label Grouped? remove the grouping (arrange->remove layout)
Nope, it doesn't appear to be grouped. The Remove Layout button is greyed out. I even moved the label away from other controls. See image:

 

bastanu

AWF VIP
Local time
Today, 14:04
Joined
Apr 13, 2010
Messages
859
No, the new new textbox will be unbound (its control source will be empty) and you just populate it programmatically to act as a label.

Cheers,
 

ZeRoX99

New member
Local time
Today, 17:04
Joined
Jun 18, 2021
Messages
7
No, the new new textbox will be unbound (its control source will be empty) and you just populate it programmatically to act as a label.

Cheers,
So I tried adding an unbound textbox and changing a little bit of code to accommodate it. It appears that the number does correctly update as I move from record to record. However I noticed it created another column of data in my bottom table. Although when I open that original table in a separate window, I notice that that column of data is not there so it seems to be a temporary thing. In the end, I just made that column hidden.

I might just go with this solution as nothing else seems to be working. Not elegant but works nonetheless. It's only for personal use anyways.

I appreciate the advice!
 

bastanu

AWF VIP
Local time
Today, 14:04
Joined
Apr 13, 2010
Messages
859
Yes, the textbox would appear as column in a datasheet form (which is "tablelike") but does not affect the bound table in any way, so just hide it.

Cheers,
 

Users who are viewing this thread

Top Bottom