Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

I expected to have a question on the quick Access Toolbar - some of it could be useful, but I don't want a duplicate button (even grayed out), and it seems silly to have a VBE button with an .accde database. True, they only see "The project is not viewable", but they see all the module names and that tends to make you think something is broken.

However, I found DoCmd.ShowToolbar "Ribbon", acToolbarNo does a lot more than I expected, it not only hides the menu, it also hides the QAT and changes the title bar, which is what I wanted to do.

But I would prefer if the title bar stayed red, instead of gray - i.e. instead of:

I'd prefer to see:

Is there a way to do this?

Alternately, would there be a way to get rid of the title bar, but have a bar on my form with the database title and minimize, restore, and maximize buttons for the application?

 

[isladogs]

You've mentioned me a couple of times in recent posts though I'm not sure what points need answers.
If I miss anything let me know:
1. If you hide the ribbon completely, AFAIAA the title bar appearance cannot be changed.
2. Yes you can get rid of the title bar completely. See the 'Fill Entire Screen' option in my article linked in post #125. Or use borderless forms with the Access interface hidden.
3. AFR = automatic form resizing. The built in navigation form doesn't play nicely with AFR for reasons explained in one of my articles.
I create my own nav forms instead so I can control their behaviour and use AFR.
See the emulated navigation form in my example AFR app supplied with this series of articles

Automatic Form Resizing 1

Use automatic form resizing (AFR) to ensure that forms and their controls are automatically resized and repositioned for any screen size & resolution. It can also be used to add a zoom feature to Access forms

www.isladogs.co.uk

 

[Marshall Brooks]

@isladogs - Thanks answer anything and everything!!!

I'm trying to fix a lot of things at one shot, so I'm getting in deep water fast ...

I wasn't aware of automatic Form Resizing - it looks good - I'll try it.

Q1 - Does tabbed windows stay with the database FE. For example, if I set my database up to use Tabbed Windows and another user had changed it to use Overlapping Windows, when they download the new version, does it use Tabbed Windows for them, like I set it, or does it use Overlapping windows for them since they had this selected previously for this file name?

Questions on the Nav Pane replacement:

Since I am not using AFR, I have my forms mainly designed to work at 1920x1080. They would probably work at lower resolution with scroll bars and they would probably work at higher resolution and just be smaller visibly. 1920x1080 seems to be fairly standard for us.

Q2 - What really is different in CAI between frmNavigation and frmNavigationEmulated? I know they have different footers and headers, but ...

Q3 - It isn't really necessary, but I like the minimize Navigation Pane option. I'm assuming I could add a button on the FRAToggle and it would change the width of FRAToggle and the left Position of FSubNavigation - Do I also need to set the width of FSubNavigation since I want it to use the entire screen area?

Q4 - I currently have the database open with FrmFormA. With the new code, I would have it open with frmNavigationEmulated. Would I then call FrmFormA from the load event of frmNavigationEmulated, or just another line in the startup code (I'm assuming from frmNavigationEmulated since it becomes a subform.

Q5 - Follow-up to Reply #129 - Would it be possible to do everything from frmNavigation Emulated - i.e. hide the title bar, have a heading bar as part of the form with the file name and path and with minimize, restore, and maximize buttons?

Thanks in advance!

 

[Marshall Brooks]

New question. I downloaded the shift Key bypass code from here: https://www.access-programmers.co.uk/forums/threads/disable-shift-key.51479/page-2#post-1442733

It seems to be working as expected once I open the database twice - as mentioned in several other locations - for example: https://www.isladogs.co.uk/improve-security-2/index.html#SB

Is there a way to avoid the double-open?

It seems like (if anyone knew about this), they could open the front end whenever I updated it and hold down the shift key and change some of the settings (although without access to the VBA code, I'm not sure how much they could change.)

 

[Marshall Brooks]

@isladogs - Follow-up to Q5. I see how in CAI, you show and hide the App Interface (and the title bar). In Reply #130, you mentioned borderless forums.

In CAI, your main form always shows and the button hides or shows the Access Interface.

I copied modDatabaseWindow module from CAI to my database and set my main form borders to "None", and added functions with
SetAccessWindow (SW_HIDE)
and
SetAccessWindow (SW_SHOW)
And it works, but it is hiding the entire Access app, including my form, not just the title bar. How do I hide only the title bar?

 

[Marshall Brooks]

I'm not getting very far with my Nav Pane Replacement form ...

I tried running AFR with my existing form. I got an error on the ReSizeForm Me line ("Sub or Function Not Defined"), even though I copied the modResizeForm module over and I see the function in that code.

I copied the frmNavigationEmulated form to my database and I get an error ("Sub or Function Not Defined") on the On Open Event, even though it is only one line and I commented out the Form Open and Form Load code.

 

[Marshall Brooks]

Made some progress going the other way - i.e. I made a copy of CAI, linked it to my backend, copied some of my forms over to it and changed the buttons on frmNavigationEmulated to call my forms and that works, although it doesn't use the entire window even if I maximize the form. Gives me an idea that what I want is possible, though ...

 

[isladogs]

You appear to be trying to do several fairly advanced things at the same time. Rarely a good idea.
You have also asked so many questions that I don't have time to respond properly as I'm busy with my own projects.

My advice would be to focus on one task at a time.
Study the article(s) for that feature, carefully review the code in the supplied example apps and make sure you understand what each part does.
Only then should you import code into your own app.
When you have one feature working, then go onto the next item.

 

[Marshall Brooks]

I have a new issue unrelated to the development work that I am doing now. When I rebuilt the backend, I removed three tables that were not used or needed any more (and were not accessed by the front end, but existed in the backend. My corruption search code looks like this:

Function DB_Corruption_Check()
    ' https://access-excel.tips/access-vba-loop-through-all-tables/
    ' https://www.access-programmers.co.uk/forums/threads/using-vba-to-search-all-tables-in-a-database.234890/#post-1198193
    Dim Found As Boolean
    Dim tdf As DAO.TableDef
    Dim sTable As String
    Found = False
    For Each tdf In CurrentDb.TableDefs
        If Not (tdf.Name Like "MSys*") Then
            sTable = tdf.Name
            Call SearchTable(sTable, "###", Found)
        End If
    Next
    If Found = False Then
        Box "### Not Found"
    End If
End Function

When I run it now, I get "Run-time error '3078':" the Microsoft Access database engine cannot find the input table or query '<tablename that I deleted>'. Make sure it exists and that it's name is spelled correctly.

How do I tell the backend that this table doesn't exist or how do I remove it from .TableDefs?

 

[Marshall Brooks]

For some reason, I can't see @Gasman 's reply (I got it in my e-mail), but deleting the links from the front end worked perfectly. I forgot the tables were linked in the front end b/c we never access them.

 

[Marshall Brooks]

You appear to be trying to do several fairly advanced things at the same time. Rarely a good idea.

Agree and Disagree. I have a vision for how I want this to work and appear. It is easier and more productive to find a way that gets me close to a solution than to spend several hours on one approach and then find out that that approach won't work.

You have also asked so many questions that I don't have time to respond properly as I'm busy with my own projects.

Fair enough. You aren't the only member of the forum. (Granted, most of what I am working on now are areas that you have the most expertise in). If I don't ask here, I'm simply stumbling on my own and tripping over my own feet and trying to figure out things that someone else can solve with one reply. I'll try to prioritize future questions.
***
I made significant progress this morning. To recap:

• Yesterday, I fixed an error in my User Lockout code that now allows me to work on the database (based on the database being opened from a specific location, even if I have other users locked out.
• I know how to use tabbed window - not sure if that setting stays with the database, but from what I can tell, it does.
• I figured out how to disable the Shift Key bypass - with it disabled unless the database is opened from a specific location.
• I know how to hide the Access Navigation Panel and block F11 to restore it. (Not implemented yet, but I know how to do it.)
• I figured out how to hide the ribbon and toolbars. Not crazy about how it makes the title bar look, but I can't live with it, and I'm sure there is a solution.
• I abandoned the idea of using frmNavigationEmulated and bring that into my database and modifying it and the idea of adding my forms and into CAI and working that way. I ended up just adding my own navigation form based on this website: https://www.webucator.com/article/how-to-create-a-navigation-form-in-microsoft-acces/ It is pretty much working. There is a lot to do, but I think I know how to modify it to work the way I want. Essentially, at this point it is more functional than the Access Navigation Pane, and it allows me to hide the Access Navigation Pane. Still to do:
  • I can't see how to have tabbed windows with labels on the subforms, but I want to get away from this.
  • Everything is cropped, and there is a lot of white space at the top. I think this will be an easy adjustment.
  • Right now the navigation buttons are blue and the selected form is light blue. I want to change this to the active form is green, other forms that are loaded (and behind the active form) are light blue and unloaded forms are gray. I think I can do this, but I found an issue.
  • I want to add additional buttons on the form for some of my macros and some admin functions - the buttons will be hidden for most users. I think this can be done fairly easily.
  • There is a header on the form which I am thinking I can convert to a "psuedo Title Bar".

***
Issues to be resolved (by Priority):

• I don't quite understand how the form is working apart from how I used to operate with the built-in Navigation pane. From what I can tell, if I click on a button on the new frmNavPane that I created, the form load event runs, but the form Activate code DOES NOT RUN. Three examples:
  • Previously, the database opened what I will called frmFormA on startup. Now the database opens frmNavPane on startup and frmFormA is shown as the subform of frmFormA. I'm not sure how it knows frmFormA should be opened first, other than it was the first form in the list.
  • I added "MsgBox "I'm here"" in the FormLoad even of frmFormA and when I select FormA from the button on frmNavPane, I see the MsgBox.
  • I added "MsgBox "I'm here." To the FormActivate event of frmFormA and when I select FormA from the button on the frmNavPane, I DO NOT see the MsgBox. My code looks like this:
    

    Private Sub Form_Activate()MsgBox "I'm Here."
    If MultiForm = False Then
    Dim intx As Integer
    Dim intCount As Integer
    intCount = Forms.Count - 1
    Screen.MousePointer = 11
    For intx = intCount To 0 Step -1
    If Forms(intx).Name <> "FormA" And Forms(intx).Name <> "frmLogoutTimer" Then
    DoCmd.Close acForm, Forms(intx).Name
    End If
    Next
    Screen.MousePointer = 1
    End If
    End Sub

    If a public (global) variable named MultiForm is false, then it closes all the other forms except one hidden background form. If this code were running, it should close frmNavPane as that is not the form in question. I'm not sure how to change the code to: frmNavPane.SubformPanel.Activate - close all other subforms of FrmNavPane. There doesn't appear to be ANY VBA code for the new frmNavPane.
• When I displayed the forms with the standard NavPane, I could right-click on the form and select Close. (I still can). When it is loaded as a subform. I don't see any way to close it - other than opening a new form. I might be able to add a close button on the button that loads the form or add a right-click menu here - otherwise, this negates any advantage to showing multiple forms.
• As mentioned in Reply #133 - I don't know how to hide the Access Title bar and leave frmNavPane open. I know it can be done b/c CAI does it, but I can't make it work. - But I can live without it.
• I don't know how to get AFR working - but I found a spot where the code wouldn't compile, so I can probably figure that out. It would have been useful for us many years ago. We had one users running at 1650x900. Now most everyone is 1920x1080 or better and in Win10, you can use scaling to make everything readable.

All help appreciated!!!

 

[Marshall Brooks]

New issue - I will HAVE to get AFR working with the new form. Since my forms are now subforms, the footer doesn't show at the bottom of the window. Either i have white space at the bottom of the form, or I have to scroll down to see the status bar and what record number I am viewing.

I might be abandoning the Navigation Form idea. I can't seem to make the form more narrow than 2.4583 - not sure why. I can't add command buttons to the Navigation Control (I can in Design View, but they aren't visible in form view. I can't make the Navigation Control shorter than the subform so that I can add a form with buttons below it.

Time to restart!!!

 

[Gasman]

For some reason, I can't see @Gasman 's reply (I got it in my e-mail), but deleting the links from the front end worked perfectly. I forgot the tables were linked in the front end b/c we never access them.

Sorry. I deleted the post after rereading your post.
I had said 'Have you deleted the links', but then you said they were not used from the FE, so that would not be of much use.

 

[Marshall Brooks]

Nope - your post was spot-on and got me back in business again. The links weren't USED in the front end and I even forgot they were there, but they were there and that was causing the issue.

I'm now considering a switchboard approach, but I don't know how to make the switchboard work with multiple forms open. In other words:

As I understand it, my Switchboard would have buttons labelled Form A, Form B, Form C. I click the button and Form A opens and the switchboard closes. I close Form A and the switchboard comes back up. If I want to have BOTH Form A and Form C open, how do I get back to the Switchboard to open Form C?

Or am I not understanding it correctly?

 

[Gasman]

Yes, you are not understanding it correctly.
You can open as many forms as you like (subject to memory etc).
The switchboard remains open.

If you want to allow just the one form, then you need to work at it.
You also need to work out if the forms should be open at the same time?

 

[Marshall Brooks]

Okay - let me back up a bit ...

Your example is using Tabbed windows with show display Tabs. I used to use that and it works okay. I might have to stick with it.

Typically, I only have one form open at a time - when you select a different form, the form activate event closes all other forms. I have a global variable that allows more than one form to be open at a time. The reason for this is that I also sort the forms on open. So if I am planning to need to update Record 76 on Form A and Record 285 on Form C, I can keep both forms open and switch between records. Otherwise, I have to search for the record I want when I switch between forms. I would prefer to go to tabbed windows and not show display tabs b/c it maximizes the screen real estate, but if I do that, I can't tell how many forms are open behind the active form.

I would like to get rid of the navigation pane (and/or switch it for something more functional.) The main reason for this is to prevent direct access to my tables. I have the tables hidden and I don't think most of our users know how to do it, but all they have to do is select Navigation Pane Options and show hidden items and the tables are visible. I don't really want to deep hide the tables.

Ideally, I would like a form on the left hand side that is always visible and then any form that I select with it will open to the right of it. The left hand form should have buttons to open the other forms and buttons to run macros (some buttons will be visible or hidden depending on the user or the file location).

I could use suggestions on the best way to achieve this!!!

 

[Marshall Brooks]

Question for @isladogs

I was trying to get AFR working. My monitor resolution is 1920*1200. When I open form 800x600 in the AFR Database, it looks like this:

I created a new blank database and copied the 800x600 template form over to it. I also copied ALL of the modules from AFR over to it. Your page said the code only needed the one line in the form load event and the ModResizeForm module, but I got errors about missing functions when I open it that way.

When I open the 800x600 form in the new blank database, I see this:

I had display document tabs selected above, but it looks the same with them turned off:

The forms look the same in design view in either database.

What do I need to do/change?

 

[Gasman]

I just used tabbed windows as that was one of the dbs that I used a switchboard for.
I just used the standard switchboard but with access levels.
@Pat Hartman has posted a switchboard several times that allows more entries.
I see no reason why floating forms would not work just as wrll.

 

[Marshall Brooks]

@Gasman - Thanks - the form would work as well with floating (overlapping windows). I was preferring to use it with tabbed windows with the tab labels hidden. If I have that, the switchboard disappears behind the active form, so if the Nav Pane is hidden, there is no way to get back to it.

I am using (testing/playing around with) @Pat Hartman 's Swithchboard Big Buttons form, so I'll ask the questions to her - although anyone else can answer:

• I have the first two buttons of the switch board set up to open my (obfuscating) Forms Form A and Form B. That works, but once I open either form, the switchboard closes and I have to use the Nav Pane to get it open again (which is a problem if my goal is to hide the nave pane.) I can't figure out why it is closing looking at the code. (I have multiple forms enabled and I can open Form A and Form B from the Nav Pane, so it isn't that the form activation code is closing the switchboard).
• What is special about a switchboard panel? (What I mean is the panel is limited to 12 buttons and each button can perform any of 9 actions and the form uses a table to read the button labels and target files. This seems to be standard practice. But I figured out that I can go to design view in the form and add any number of buttons that perform any action and have them visible.) What does using a switchboard get me over just created any form named frm_Switchboard and adding as many buttons as I want doing whatever I want?
• The switchboard window seems to open in it's own window in my database - although in the demo database it opens as a pop-up, and it's width seems to be set at 8.5833 inches and cannot be changed. I'm not sure this matters, but why is this?

Bottom line, it works but I'm not seeing an advantage to this over doing something like the following:

• Create a form that loads at startup. The form will have approximately 14 buttons as follows:
  • 7 buttons for selecting the main forms. If I can do it, I might color-code these buttons to show if the form is loaded or not. I might make them toggle buttons so that clicking the button opens the form and clicking it again closes the form if it is open. (Nevermind - if I need to enable tab labels so I can get back to the switchboard, I can't have forms open behind active forms so this won't matter.
  • One button will toggle single-form and multi-form by calling VBA macros.
  • Two buttons will be admin buttons for me to show the Access Nav Pane and the Access Ribbon/Toolbar. These buttons will not be visible unless the database is opened from my development folder - controlled with an If-Then clause on Form Load.
  • Four Buttons will run reports from the database. I don't want everyone doing this, so these would be visible by myself and one other users - controlled by a If-Then clause for username in the Form Load event.

I'm not trying to be argumentative, but I'm not seeing what the switchboard offers that the above option does not.

 

[Gasman]

Well it is only a form.
I used my 7 buttons to drill down to another form.
So I might have
Employees
Sales
Commisions
Payroll
Reports & Queries
Administration
DB Maintenance
Exit