Forum Widgets
Latest Discussions
Select Query values not showing in report - MS Access
I'm probably missing something simple, but.. I have a Table that has 4 field (plus key), Company ID, Join Date, Quit Date, Notes. This tracks agency membership I made a select query, with 2 IIF statements, and 2 conversions. My Datasheet view shows correctly, but when i put the fields on a report, YRSCALC is empty, and doesn't sum, which affects Grand Total. I have tried every type of formatting i can think of.. Select Query: SELECT tblJOINQUIT.[Company ID#], tblJOINQUIT.[Join Date], tblJOINQUIT.[Quit Date], tblJOINQUIT.Notes, Sum(IIf([Quit Date] Is Null,"0.00",DateDiff("m",[Join Date],[Quit Date]))) AS NumOfMonthsCalc, Sum(IIf([Quit Date] Is Not Null,"0.00",DateDiff("m",[Join Date],Now()))) AS NumOfMonthsCurr, [NumOfMonthsCalc]/12 AS YRSCALC, [NumOfMonthsCurr]/12 AS YRSCURR FROM tblJOINQUIT GROUP BY tblJOINQUIT.[Company ID#], tblJOINQUIT.[Join Date], tblJOINQUIT.[Quit Date], tblJOINQUIT.Notes; Report View: Help??
Access changing lettercase - redux
This has probably been beaten to death, but I'm fuming, so here goes. I want Microsoft to DO SOMETHING to help. Access has the highly annoying habit of changing lettercase on identifiers, seemingly at random. This is a real pain when using add-ins like MSAccessVCS, which makes it possible to use git for source code control. Here' what I've deduced is happening: There exists a dictionary/symbol-table containing every identifier used in a project. The symbol table contains ALL identifiers, including those defined by MSAccess, referenced libraries, and your code. Searching that table is case-insensitive, but it stores the canonical version of the identifier with case preserved. Normally, when you type an identifier that already exists in the table, the VBA editor "corrects" the lettercase of your entry to match the table's entry. The extremely annoying part is that sometimes that last step works in reverse. You type an existing identifier, but with a different lettercase. Instead of correcting what you typed to match the table, it instead updates the table with the version you typed, and decides that's the way future and existing identifiers should be spelled. Here's a recent example. I tried to create a class module constructor (Class_Initialize) but typed it "class_initialize" implicitly expecting that the VBA editor would "fix" it. Instead, it updated the symbol table and set the canonical lettercase to the all-lowercase version... and then proceeded to change the lettercase on the Class_Initialize() method in all my class modules. It has now decided the canonical lettercase is "class_initialize" and that's what it "corrects" to from now on. Of course, when I did the next MSAccessVCS export, git showed a bunch of unwanted (but cosmetic) changes. This pollutes the changeset and makes source control more difficult. THIS. IS. A. BUG. My workaround is, when starting a checkin, I go through the list of changes and group all such changes into one commit called "VBA Artifacts". But come on, this shouldn't be necessary. At least let us know WHY this happens, and give us some control of the process. A way to specify/correct the canonical casing would help.
Changed systems 2/3 Working
I am not an Access Professional, and I inherited a system that was on a PC that is no longer viable. I built the new PC and named it exactly the same as the old one to mitigate any conflicts, and it is half working. We have a large database and 3 associated programs that add data and export data from that database. The 2 programs for adding data are working flawlessly with no issues. The program for exporting data is giving me error 3044 that there is no valid path, when I upload an Excel file to it to output a different Excel file. I do not understand how the path can be valid for the 2 programs for inputting data are working, but not the program for exporting data, when it's the same path to the same database. All I did was copy and paste the existing database and all its files into the same file location on the same named PC. Any Assistance is appreciated. The database is on a Windows 11 PC.
Linking Access to Excel does not work
I have created a simple Access that is connected to a linked table to an Excel file as a data source. I can run the Access without any problems. It accesses the data and everything is fine. But when my colleague wants to run the Access, she gets the following error message "Microsoft Access “C:\Users\User\ [Source Path] .xlsx” is not a valid path. Please ensure that the path is entered correctly and that you are connected to the server where the file is located." We use OneDrive/SharePoint in our company and the colleague has access to the folder where the Excel and Access files are located. But it doesn't work. I think it's the path, as it seems to be local. But I don't understand why. This Access in turn serves as a source for another Excel (the target file). There it has a similar error message: ‘’Microsoft Excel [DataFormat.Error] “ [File Name] .accdb” is not a valid path. Please ensure that the path is entered correctly and that you are connected to the server where the file is located." I can open and run both the Access and the target Excel without any problems. Thanks a lot in advance. I appreciate your help a lot.
Msaccess Enhanced Message Box - Office update issue
This Code has been working for 10 years. Then, an Office update causes multiple instances of the form to remain open. If I revert to 16.0.17531.20120 all is well. This creates multiple instances: ' Create the MessageBox Dim F As New Form_frmEnhancedMessageBoxFormDialog This is supposed to close the form. ' cleanup Set F = Nothing Every time a message is displayed, a new instance of the form is opened, and after the user responds, it is hidden. The cleanup does not work. I tried unloading and closing the form but they are not part of the forms collection that can be referenced. I waited to see if a Microsoft update would fix the problem, but so far, no luck. Has anyone experienced the same issue? The code came from here: https://datenbank-projekt.de/projekte/improved-enhanced-message-box-ms-access However, a request for paid help was unanswered. After spending a morning with ChatGPT trying various fixes that did not work, I wondered if any other developers had any ideas or perhaps had experienced a similar issue. Thanks, Finlay McMillan '----------------------------------------------------------------------------- ' Plain Text Replacement for the standard MsgBox '----------------------------------------------------------------------------- Public Function Box(ByVal Prompt As String, _ Optional ByVal Buttons As VbMsgBoxStyle = vbOKOnly, _ Optional ByVal Title As String, _ Optional ByVal HelpFile As String, _ Optional ByVal HelpContextId As Long, _ Optional ByVal ButtonDelay As Long = -1, _ Optional ByVal AllowBeep As Variant, _ Optional ByVal AllowCopyToClipboard As Variant, _ Optional ByVal AllowSaveToFile As Variant, _ Optional ByVal LabelButton1 As Variant = Null, _ Optional ByVal LabelButton2 As Variant = Null, _ Optional ByVal LabelButton3 As Variant = Null, _ Optional ByVal NoStrEsc As Variant, _ Optional ByVal DismissID As Variant = Null, _ Optional ByVal AutoCloseSec As Long = 0, _ Optional ByVal DefaultButton As Long = 0, _ Optional ByVal BGColorButton1 As Long = -1, _ Optional ByVal BGColorButton2 As Long = -1, _ Optional ByVal BGColorButton3 As Long = -1, _ Optional ByVal BoxIsModal As Boolean = True, _ Optional ByVal BoxIsOnTop As Boolean = True, _ Optional ByVal DelayShow_Countdown As Boolean = False) _ As VbMsgBoxResultEx ' If the dialog was previously dismissed, don't display anything at all If (Buttons And 7) = 0 And GetDismissIDValue(DismissID) Then Box = VbMsgBoxResultEx.vbOK Exit Function End If ' Create the MessageBox Dim F As New Form_frmEnhancedMessageBoxFormDialog F.ParenthWnd = GetParentWindowHandle() F.Title = Title F.Buttons = Buttons F.HelpFile = HelpFile F.HelpContextId = HelpContextId F.ButtonDelay = IIf(ButtonDelay <= 0, DefaultButtonDelay, ButtonDelay) F.AllowBeep = IIf(IsMissing(AllowBeep), DefaultBeepAllowed, AllowBeep) F.AllowCopyToClipboard = IIf(IsMissing(AllowCopyToClipboard), DefaultCopyToClipboardAllowed, AllowCopyToClipboard) F.AllowSaveToFile = IIf(IsMissing(AllowSaveToFile), DefaultSaveToFileAllowed, AllowSaveToFile) F.SavedTextFileFolder = DefaultSavedTextFileFolder F.IsRichText = False F.LabelButton1 = LabelButton1 F.LabelButton2 = LabelButton2 F.LabelButton3 = LabelButton3 F.DismissID = DismissID F.AutoCloseSec = IIf(IsMissing(AutoCloseSec), DefaultAutoCloseSecDelay, AutoCloseSec) F.DefaultButton = IIf(IsMissing(DefaultButton), DefaultDefaultButton, DefaultButton) F.BGColorButton1 = IIf(BGColorButton1 < 0, DefaultBGColorButton1, BGColorButton1) F.BGColorButton2 = IIf(BGColorButton2 < 0, DefaultBGColorButton2, BGColorButton2) F.BGColorButton3 = IIf(BGColorButton3 < 0, DefaultBGColorButton3, BGColorButton3) F.BoxIsModal = IIf(IsMissing(BoxIsModal), DefaultBoxIsModal, BoxIsModal) F.BoxIsOnTop = IIf(IsMissing(BoxIsOnTop), DefaultBoxIsOnTop, BoxIsOnTop) F.DelayShow_Countdown = IIf(IsMissing(DelayShow_Countdown), DEFAULT_DELAYSHOW_COUNTDOWN, DelayShow_Countdown) If IIf(IsMissing(NoStrEsc), DefaultNoStrEsc, NoStrEsc) Then F.Prompt = Prompt Else F.Prompt = UnEscStr(Prompt, toHtml:=False) End If ' Make it visible and wait for the user until we get the result Box = F.ShowModal() ' Keep the last result just in case the user needs it again later m_Result = Box ' Set focus back to the parent form On Error Resume Next WinAPISetFocus F.ParenthWnd On Error GoTo 0 ' cleanup Set F = Nothing
BUG REPORT MS ACCESS: F5 Key in VBA Editor Behaves Like F8 – Code Runs Line by Line After Breakpoint
Issue Summary: In the VBA Editor (VBE) of Microsoft Access (Microsoft 365, 32-bit), I'm experiencing a critical issue during debugging: When I enter break mode (e.g., hit a breakpoint) and press F5 (or click “Continue”), the code does not resume execution normally — instead, it continues line by line, exactly as if I were pressing F8. This completely breaks normal debugging flow. Things I Tried (none fixed the issue): Compact and Repair database. Ran Access with the /decompile switch. Created new, clean databases. Reinstalled Office 365 completely. Ran both Quick Repair and Online Repair. Used Office Deployment Tool to downgrade to older versions. Cleared .exd, vbaProject.bin, and temp VBA cache files. Verified it's not a keyboard or hardware issue. Disabled OneDrive sync (though my Documents folder is still redirected). Checked VBE-related DLLs (VBE7.DLL), and user registry entries. Same result on other PCs in my workplace. ✅ What Did Work (But Not a Practical Solution): I created a new Windows user account, re-ran Access there, and F5 worked normally again, even using the same Access application where the issue originally occurred. This confirmed that the bug is profile-specific, likely tied to a corrupted configuration or setting in the original Windows user environment. ❌ Why That’s a Problem: Creating a new Windows profile is not viable in a production/development environment: I use licensed OCX controls (Viscom) tied to my current user profile — reactivation would require additional purchases. Other tools are already configured. Recreating the full environment is costly and time-consuming. 🧠 How I Discovered This: When I purchased a new laptop and did a clean install of Windows and Office, the issue did not occur at all, even when running the same Access database file. That’s when I realized it was tied to something in the Windows user profile. With help from ChatGPT, I identified that the issue is likely due to corrupt user-level configuration of the VBA editor, which is not easily reset or exposed to users. 🔎 Possible Causes: Corrupt configuration in %APPDATA%, registry, or synced files. VBA/VBE state incorrectly cached. Debugger logic affected by user-specific flags or crash residue. 📢 Request to Microsoft: Please consider providing: A tool or command to reset all VBE/VBA user settings (without creating a new user account). Documentation on which files or registry keys may affect debugging behavior. An investigation into why F5 acts like F8 and what triggers this silent corruption. Thanks four your help
