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:
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:
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 FunctionBefore 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:
