Solved Automated search for Database Corruption Assistance

[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

 

[Pat Hartman]

but once I open either form, the switchboard closes

That is because I almost never allow two forms to be active at one time. It tends to confuse the user. So, when one form opens another, it passes in its name as the OpenArgs so the called form knows what form to return to (this resolves any problem that might arise if a form might be opened by multiple calling forms) and then hides itself setting the visible property to false.

If you don't like this behavior, you can remove or comment out the code involved.

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

You can hard code anything. The issue comes when you change your mind or need to add another object between two existing objects. ALL the example switchboards are bound to the original [Switchboard Items] table but the table includes additional columns to control security. I did make the exception to increase the number of items from 8 to 12. If you want more than 12, you have to add the additional controls using the same control naming pattern so the code won't break and then change the value of ONE variable from 12 to whatever.

In the database you are looking at are three samples. Two, use hardcoded buttons. This is of course limiting but it does allow you to always assign the exit to 12 or whatever is the highest number. the third option uses a continuous subform. This is of course the most flexible option but the exit will be the last item in the list rather than specifically item # 12. The sample database also includes a form that lets you manage the [Switchboard Items]. Once I changed the max items from 8 to 12, that prevents you from using the internal switchboard manager because it is limited to 8 items ONLY. The other switchboard samples database uses security and in that case, the table driven model lets you make custom switchboards based on security levels. That way Sam sees one list of options, Suzie sees a different one and Pat sees everything because she is the master of the universe This version also includes a completely custom switchboard that lets you customize the buttons also.

Using data to power the switchboard eliminates the need to modify forms to add or change options. You update the table which is ever so much easier and is the reason that Access is so much better at managing data than Excel is.

 

[Marshall Brooks]

Switchboard close code - That is pretty clever. I like it for a single-form only application. I wasn't seeing where the code was to hide it, but I found it now.

You can hard code anything. The issue comes when you change your mind or need to add another object between two existing objects.

I guess it was a more basic question - i.e. does having it set up that way gain me anything over just creating a form named Switchboard that opens with the database on startup. The table to edit seems unintuitive to me. If it need to add anything, I just go to design view and shift the buttons there.

 

[Gasman]

The table to edit seems unintuitive to me. If it need to add anything, I just go to design view and shift the buttons there

So you have never created data driven systems?

Think of your combos, your listboxes, hell even the data itself.
If you have a flag to change the sort order, then no need to change the code. If you have a field to change the field(s) to order by.
Just depends on how much you want to data drive the system.

Need a new button?, add a record.
Your way, change the form.

Even if I created my own switchboard form and did not use the builtin version, it would still be data driven.

 

[Marshall Brooks]

No - not really data driven systems - other than as you said, the data itself.

created my own switchboard form and did not use the builtin version

Okay - here's a basic question. What is meant by the builtin version?

I went to another website and it mentioned a switchboard manager. I added that to the toolbar and started working through it and it added a table like Pat Hartman's named tblSwitchboardItems, but it never created a form that I could see or any way to load the switchboard.

I copied Pat's Large Buttons form into my database, and it would work, except I want more than 12 buttons, I want them somewhat grouped, and I want some hidden depending on conditions.

Now I'm creating my own form, but I'm not sure if there are any disadvantages to this - other than what you explained above.

 

[Gasman]

Well the form is already created for you. No work involved unless you want to tweak it, like I did for security levels.

Been ages since I created one though.

https://www.youtube.com/results?search_query=access+switchboard

In 2007 the switchboard manager is in Database Tools.

 

[CJ_London]

Agree with Colin - too many questions for one thread - start a new thread for each question

. You aren't the only member of the forum.

Agree but many members won’t want to get involved with your frenzied approach

 

[Pat Hartman]

What is meant by the builtin version?

Access comes with a Switchboard form and table. They used to be visible on the ribbon but they are now hidden. You have to add the developers tab to see them.

All three of my samples are based on the original Switchboard form. The hardcoded list version is based on the last VBA version from MS with two changes - 1. 12 items instead of 8, 2. the additional option of opening a main form in DS view which the original code could not do. The big button version, is a modification of this version. The code behind it is identical. It even has both a button and a text box but the text box is hidden under the enlarged buttons. The continuous form version is based on the last version of the switchboard prior to A2007 which had been converted to macros. I removed all the macros and reverted the form to using VBA.

except I want more than 12 buttons,

I explained to you how to do that. in an earlier post. But I'll tell you again, 1. change ONE variable from 12 to 532 or whatever. 2. Add the additional 520 or whatever buttons and be careful to adhere to the naming standard used by the original 12 buttons. You are still thinking hard coded and not data driven. I've been creating applications for 50 years. I have lots of scars from all the mistakes I've made. I've spent weeks of my life (over the 50 years) making and fixing hard coded menus. Believe me, if I thought that hard-coded switchboard/menus were the way to go I would not have bothered to build a sample to show you how to do it wrong. So, I, personally, think that data driven is the right way for a switchboard. Rather than grouping and futzing with the button display, think about a button dropping down to a sub menu. If you do it the hardcoded way, you WILL at some point sooner or later realize that. If your app is small and will stay small, you can make it "prettier" by using a custom layout. If you value substance over form and respect your employer's cost investment, you won't waste their money doing "pretty" for the sake of "pretty" when "functional but still nice looking" is almost free.

and I want some hidden depending on conditions.

Again, you can write code and spend development time doing this OR, you can look at the version of the switchboard samples that support a log in. You can use that security model and by using queries with criteria, the custom switchboard or the continuous form switchboard (rather than the one with Hard coded buttons) will show each user only what they are authorized to see. If you want to show/hide the buttons depending on authorization, you will find that the menu looks silly as gaps occur for the hidden functions. That will mean you need to change how the form works and figure out how to make the missing teeth look better.

It is important to understand what is substance and what is fluff and spend your time on things that provide a good ROI which is solid validation code and security as well as efficient processing and flexibility. Table driven is an objective to always strive for since it provides the most flexibility with the least amount of programming effort to create and maintain. Think of this as you would a combo. If you have two options that will never change (bet you thought gender would never change), it doesn't make much difference. But if you have 20 and there is the slightest possibility of change, then a table is called for.

I'm sorry you didn't find the switchboard maintenance form obvious. The upper subform is the menus and the lower form is the items on any given menu, including the link to another menu.

 

[Marshall Brooks]

Okay - per CJ London's suggestion, this thread will only be for database corruption. Any questions I have about user interface improvements will be made in new separate threads.

I'm thinking (like everybody else told me) that our issues are not really related to database corruption.

To recap - 25-Jul-2023 I revised the front end to only open from the mapped U:\ drive or the users desktop. 31-Jul-2023 I revised the front end to remove datasheet view.

We haven't had any crashes (inconsistent state, database can't be read) errors since that time, but it was an intermittent error before.

Late on Thursday, 3-Aug-2023, I exported the backend tables to text files and re-imported them. Everything seemed to be working well. I ran the Excel Validation records after I did this since I reset the PK numbering and the reports sort on that. (Otherwise the reports would show hundreds of errors where the PK values did not match.)

Tuesday - I did a compare of the Excel validation reports (So a compare between last Thursday night and last Monday night. Only one table had errors:

• One record was shown as added to the table - i.e. it had an entry in the current report (Monday the 7th at 6:45 P.M.) and not in the prior report (Thursday the 3rd at 5:37 P.M.) Theoretically, this could happen and not be an issue. Other people CAN add records to the database and they wouldn't necessarily notify me, but this isn't a recent record. I checked our backups from 1-Aug-2023 and it was in that report. That report shows it was added November 1st, 2022. It's POSSIBLE it got deleted after 1-Aug and added back before 4-Aug (it got difficult to keep up with), but it should have been in the Thursday version of the database. I checked the exported .csv files and the copy of the BE after I imported the .csv files and it is in both files. THAT makes me somewhat question or export/validation routine, but it is simply a query of 4 fields from the table exported to Excel. I don't see any rhyme or reason for it dropping a record, and especially for it doing it Thursday but not doing it the following Monday. That's not my primary concern, though:
• One record was shown as deleted from the table. This was a fairly recent record also - it was shown as added July 24, 2023. I searched and it was not in the database. It was not in Monday night's backup. It WAS in Thursday nights backup. So sometime on Monday, it got deleted somehow. That IS concerning.

Questions:

• What are the possible methods of deleting a record from an Access table? The only methods I know are Datasheet View of the form and right-click Delete, or from the table directly by doing the same thing (or via VBA code). DS View is blocked, tables are hidden, VBA is not accessible and I don't think I've told any of the users how to do this. (But you don't know what you don't know. For all I know, Shift-Del might delete a record and I have never tried that.)
• What can be done to prevent a record being deleted? (Back in Reply #55, @Pat Hartman mentioned added code to the BeforeDelete and BeforeDeleteConfirm events, but she never said what to add.)

Thanks in advance!

 

[Pat Hartman]

What are the possible methods of deleting a record from an Access table?

The delete button is active in all form views, not just DS. If you don't want people to ever delete rows, you can just set the AllowDelete property to No.

@Pat Hartman mentioned added code to the BeforeDelete and BeforeDeleteConfirm events, but she never said what to add.

I guess you didn't look at the sample database I referenced. I can't give you code because I have no idea how you are handling security. Here's some code from the sfrmDetailItems form in the switchboard with login sample.

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    If Forms!frmLogin!txtDeleteSecurityLevel >= Forms!frmSwitchboard!sfrmSwitchboard!DeleteSecurityLevel Then
    Else
        MsgBox "You are not authorized to Delete data on this form.", vbOKOnly
        Cancel = True
        Me.Undo
        Exit Sub
    End If

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    If Forms!frmLogin!txtAddSecurityLevel >= Forms!frmSwitchboard!sfrmSwitchboard!AddSecurityLevel Then
    Else
        MsgBox "You are not authorized to add data on this form.", vbOKOnly
        Cancel = True
        Me.Undo
        Exit Sub
    End If
    
    If Me.Parent.sfrmMenuItems.Form!SwitchboardID & "" = "" Then
        MsgBox "Please select a Switchboard before trying to enter Items.", vbOKOnly
        Me.Undo
        Cancel = True
        Exit Sub
    End If
    Me.SwitchboardID = Forms!frmUpdateMenu!sfrmMenuItems.Form!SwitchboardID
    If Me.txtIncrement & "" = "" Then
        Me.txtIncrement = 1
    End If
    Me.ItemNumber = Nz(DMax("ItemNumber", "[tblSwitchboardItems]", "SwitchboardID = " & Me.SwitchboardID), 0) + Me.txtIncrement
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Forms!frmLogin!txtEditSecurityLevel >= Forms!frmSwitchboard!sfrmSwitchboard!EditSecurityLevel Then
    Else
        MsgBox "You are not authorized to update data on this form.", vbOKOnly
        Cancel = True
        Me.Undo
        Exit Sub
    End If

    If Me.txtItemText & "" = "" Then
        MsgBox "Item text is required.", vbOKOnly
        Cancel = True
        Me.txtItemText.SetFocus
        Exit Sub
    End If
    If Me.cboCommand & "" = "" Then
        MsgBox "Command is required", vbOKOnly
        Cancel = True
        Me.cboCommand.SetFocus
        Exit Sub
    End If
    If Me.cboCommand = 6 Then
    Else
        If Me.cboArgument & "" = "" Then
            MsgBox "Argument is required", vbOKOnly
            Cancel = True
            Me.cboArgument.SetFocus
            Exit Sub
        End If
    End If
    
    If Me.txtAddSecurityLevel & "" = "" Then
        Me.txtAddSecurityLevel = 0
    End If
    If Me.txtEditSecurityLevel & "" = "" Then
        Me.txtEditSecurityLevel = 0
    End If
    If Me.txtViewSecurityLevel & "" = "" Then
        Me.txtViewSecurityLevel = 2
    End If
    If Me.txtDeleteSecurityLevel & "" = "" Then
        Me.txtDeleteSecurityLevel = 0
    End If
End Sub

As I look at the sample, not all forms have the validation code so I will update the sample. I must have stopped in the middle when I was building the example.

 

[Marshall Brooks]

@Pat Hartman - Thanks!!! (I am listening and learning, whether it seems like it or not.)

The delete button is active in all form views, not just DS. If you don't want people to ever delete rows, you can just set the AllowDelete property to No.

And there is is - clear as day "Allow Deletions = Yes" I need to release a new FE today, before I worry about the other changes.

I guess you didn't look at the sample database I referenced. I can't give you code because I have no idea how you are handling security. Here's some code from the sfrmDetailItems form in the switchboard with login sample.

Yes, I did look at the sample database. It wasn't clear to me where the beforedelete and beforedelete confirm code was located. What I saw was mostly talking about the switchboard and about event logging. There was a LOT more to it that I didn't look into, but as you mentioned, not all of the forms were updated.

Three followup questions:

• I'm the only one that should be deleting records from the database. I'm comfortable with going to the tables in the backend to delete records. The tables are hidden on the nav pane and the nave pane is going to be hidden in the next version of the FE, but I can either access the tables through a different front end that I maintain, or I can enable the nav pane myself through the switchboard with buttons that will only be visible to me. With the constraints above, do I still need to add code to the form's BeforeDelete event, or is turning off allow deletions sufficient?
• If I only want myself to be able to delete records, I assume the If statement could just be:
  If Environ("Username") = "<myusername>" Then
  correct?
• In reply #55, you mentioned changes to both the BeforeDelete events and BeforeDeleteConfirm events, but above you only show code for the BeforeDelete events? Do I need to update both (assuming from the first question that I need to update either one), and if so, do I use the same code for both?

Thank you again!!!

 

[Pat Hartman]

If you implement a type of security similar to what you see in the code I posted, you can assign delete permission to yourself but not to anyone else. The posted code is from three different events handling three different user actions - delete, add, update. The View permissions are checked in the current event .

I made some updates to the security to make it more consistent.

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

 

[Marshall Brooks]

@Pat Hartman - Thank you, but you keep answering questions other than the ones I asked. The reason I keep asking is b/c I think I know how things work, but obviously, things don't always work the way I think they do.

I released an updated version of the FE yesterday. The updated version has "Allow Deletions - No" in the properties of all forms.

As I understand it - nobody (including me) can delete any record from the front end. (Other than technically for this version of the front end, they could, if they knew how, change the taskbar to show hidden items, select the tables, open a hidden table, and delete the record from there).

I'm not sure if the above is a true statement?

If the above is true, I'm not sure I need to add/edit each form's beforeDelete event? I seems somewhat superflous/overkill if deletions aren't even allowed in the form properties.

You previously mentioned changing each form's BeforeDeleteConfirm event, but you haven't mentioned that again or whether any changes are required for it?

As far as the other events:

• Everyone needs Edit Access. We have some employees in other departments who would like read-only Access to the database, but don't necessarily need real-time information. For them, we export the database data to Excel nightly and give them access to the Excel files. (It is useful to see how I could prevent edits, though.)
• Currently, everyone has add record privileges. I don't want to restrict that at this time. I probably add over 90% of the new records. Most of the team doesn't want to bother or doesn't trust themselves to add records. If a record is added by someone else, I should see it on my weekly validation reports and I can edit or correct it at that time. (Again, it is useful to see how to limit it if that becomes necessary.)

Thanks to all for the assistance.