Sorry folks, Access World will not accept my posts

[MajP]

BlueSpruce, I don't do anything special with MVFs. I store information in lookup tables that are managed using their forms. Like with everything else in my databases, the goal is to create reports and to make verbal suggestions to users (TTS).

MVFs do the first well because they display multiple attributes in columns. For example, if I want to see all the diseases that a person has then an MVF in a form will allow me to select all of their diseases for every member of a family unit, and it will show the disease names using commas or some other punctuation mark. The report would then show them in a column, so it will be easier for the user to read the information. Checkboxes make selecting one or more diseases easy for the user.
This doesn't mean that there aren't other ways to do this. I am only saying that I like using MVFs. They require no code, and they are intuitive to use for attribute information.
Also MVFs in reports expand downward automatically when directed to do so.
This works well for both my forms and reports because my tables have many fields. I optimize for space on both the monitor and on paper. Some of my tables have 10 MVFs. I place them strategically on forms and reports. Unlike most developers, I don't need to manipulate attributes in MVFs. My reports are designed to be documents, not lists of data.
To let users edit information in MVFs, I use forms for my MVFs. I label those forms as lfrm to distinguish them from main forms and subforms. This way Access groups and sorts my forms and other objects using assigned prefixes. I also give the objects similar names. So for one table, say Diseases, the table is named tblDiseases, the lookup table is named lkuDiseaseType, the main form is frmDiseases, and the form to edit the lookup table is lfrmDiseaseType, the main report is rptDiseases, and so on.
The best thing about using MVFs for me is reusing them multiple times. So the list of diseases can be used to describe a person's health, used with good foods to eat, and with bad foods to avoid. I can also use the list with foods that have been recalled by the government and producers. This is my approach to normalization, but I am not sure that that is what it is. By the time I am finished, MVFs will allow for Many-to-Many-to-Many relations.

I think there are two big differences where MVFs are fine and where you most likely will get into problems down stream.

I call it a "Simple List" or a "Complex List". You seem to be suggesting "simple lists" and thus not a big deal to go with MVF. But you really need to think about the potential limitations now. And since you do not know where this is going, you can paint yourself into a corner.

1. In the Simple List, the data you are choosing to put in your MVF comes from a table with few fields. Mostly it is just a value. This list does not hold any foreign keys to other related tables. Other tables do not have foreign keys to this list. There are no fields such as dates, currencies, or other values that you may need to do queries against. So a list of products, Disease types where you are just concerned in showing the choices. If all you are ever going to do is show these choices and query against these choices then using MVF is fine

2. In the Complex List the table you are pulling from has a lot of information to include foreign keys to other tables or other tables referencing this list. Lets assume DiseaseType has the following fields
tblDiseaseType
- DiseaseName
-DiseaseDescription
-DiseaseCategory_FK ' foreign key to disease category table
- DiseaseCricality_FK ' foreign key to criticality table
- PercentPopulationAffected ' numeric
-RiskPercentage ' numeric

and a related table
tblOrgansAffected 'references tblDiseaseType
- DiseaseTypeID_FK ' a foreign key to the disease table
- OrganID_FK ' a foreign key to the organs table

Because I am tracking that level of detail it tells me I probably want to do things like answer
- Show all family members with diseases that are high risk and all organs affected
- Show family members with a disease risk over 70%
- Count total family members with a disease in the parasitic infection category that are high criticality

The above questions are trivial in a standard design; the above questions are near impossible to answer with an MVF in the mix.

So it takes some hard thought of the downstream effects and what I will need to do with the data.
You say that your "requirements" are continually changing and do not know what functionality you will need. If I am 100% confident a simple list of choices is all the future holds then knock yourself out with MVF if you understand the other limitations. But if you think you want to do more with the data in the list table then just show and query the choices, you are better off putting the work in now. Sometimes a simple list starts to grow because you decide you want to do more with the related data.

 

[The_Doc_Man]

I would think Dakota could PM an admin to see if they can override the blocking?

Tell you what I'll do... I'll post this so we see about blocking.

@Jon - members @DakotaRidge and @BlueSpruce have asked the question of whether it is possible within the forum software for an admin to:

(a) block a specific user's ability to post to a specific thread BUT
(b) allow the same user to post to other threads AND
(c) allow other users to still post to the specific thread in question.

Can you answer this question?

Follow-up: If it IS possible, was this ability used on member DakotaRidge?

 

[DakotaRidge]

I will just point out that we recommend not MVFs but Combo Boxes based on a separate lookup table. Comparing the two, the MVF has a hidden infrastructure and thus is harder to maintain, particularly if you need to add or remove entries in the underlying table. BUT... otherwise, they take up the same amount of space. If I'm going to store code 12 and "XYZ", the only difference is whether I can see the name of the definition table. There is no difference in the space used. But that one little difference - a visible name for the supporting table - allows me to more easily DO things with the underlying data.

Your comments in post #22, middle section, talk about the advantages of MVFs, NONE OF WHICH are distinct from ordinary combo-box/table behavior. (They can't be, since it is the same mechanism.) The combo box, if you build it with a wizard, does not require you to engage in coding. You can control the nature of the drop-down behavior. You can get the combo to show multiple columns. You keep on showing us a distinction based on no difference. The REAL difference is that the MVF implied table, being hidden, is harder to work with when changes are needed. And there is the perceptual difference that hides how much you use that feature. I believe that each MVF creates its own hidden table, which means that even if you have the chance to re-use a set of values, you won't - potentially leading to duplicated data when sharing via combo/table setups on the forms could easily re-use data commonly looked up, via data sharing techniques. And heavens forfend that you should ever have two MVFs representing the same thing but with different codes stored for selection for the same values in two different hidden tables.

That's not how I use MVFs. All of mine use a visible, not a hidden, table. I call them lookup tables because the MVFs use them. Other tables, forms, and reports can also use them. Again, the big advantage, IMHO, is how the attributes look in forms and reports. I usually set the formatting in forms to System Separator, but to New Line in reports. If a user wants to change the settings, they are right there in the Property Sheet. That way, the user can have it their way and doesn't need me.

I have tried using Combo Boxes, and they create similar or identical looks. I have used MVFs so long that they are second nature for me. I would never tell someone to use MVFs if they knew Combo Boxes.

Here's a screenshot from the database that I am working on now. The Selling Stores MVF is open. A lookup table populates the MVF. The small Access "A" icon opens a form that contains information about grocery stores in Colorado and Florida where my family shops. The normal number of attributes (stores) that MVFs use is 16. I use the Property Sheet anytime I need more. The maximum is 100. I have never needed more than 30 attributes in an MVF.

I don't like seeing those down carets in MVF, so I hide them. All I need is a shape having the same background color as the form's background. In this case, white. As soon as I click in the MVF, the shading in the shape disappears. I occasionally change the background color of MVFs. And if I don't like the width of an MVF, I just drop into Design mode and change it. These are all things that family members can do if they don't like my design.

I should also say that I use MVFs and other Access tools to reduce the amount of typing that a person needs to do. The only field in the form below that a user really needs is Cost and PlantID. They should type in Notes, but that information is optional. Cost is needed to tell a user how much he/she spent on recalled products. A related report will tell the user how much he/she spent on doctor and hospital bills because they ate contaminated food. That form can also be used to track cremation and funeral costs. Remember, this database is for personal finances.

Food recalls relate to finances. For each recall, there can be one or more financial impacts depending on how many family members are affected. The impact to a multi-generational family can be significant because contaminated food affects small children and the elderly more than middle-age adults. This form will eventually use speech (TTS) to announce the affects of food contamination. TTS may also tell a user that a critical piece of information is missing from a form.

 

[MajP]

I will just point out that we recommend not MVFs but Combo Boxes based on a separate lookup table. Comparing the two, the MVF has a hidden infrastructure and thus is harder to maintain, particularly if you need to add or remove entries in the underlying table. BUT... otherwise, they take up the same amount of space. If I'm going to store code 12 and "XYZ", the only difference is whether I can see the name of the definition table. There is no difference in the space used. But that one little difference - a visible name for the supporting table - allows me to more easily DO things with the underlying data.

Your comments in post #22, middle section, talk about the advantages of MVFs, NONE OF WHICH are distinct from ordinary combo-box/table behavior. (They can't be, since it is the same mechanism.) The combo box, if you build it with a wizard, does not require you to engage in coding. You can control the nature of the drop-down behavior. You can get the combo to show multiple columns. You keep on showing us a distinction based on no difference. The REAL difference is that the MVF implied table, being hidden, is harder to work with when changes are needed. And there is the perceptual difference that hides how much you use that feature. I believe that each MVF creates its own hidden table, which means that even if you have the chance to re-use a set of values, you won't - potentially leading to duplicated data when sharing via combo/table setups on the forms could easily re-use data commonly looked up, via data sharing techniques. And heavens forfend that you should ever have two MVFs representing the same thing but with different codes stored for selection for the same values in two different hidden tables

I am with @DakotaRidge because the above is completely misleading. You cannot simply replace an MVF with a combo.
You cannot replace a MVF with a combobox. It requires
- Child table
- Subform
- Combobox in the subform

In regards to real estate a subform is going to take up way more space. So in that way they are definitely not comparable.
The MVF will display the related record selections concatenated with a comma. A subform will show multiple rows.

 

[MajP]

Unfortunately this takes some understanding of code. But you can get all the benefits of a MVF control and still normalize the data.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

 

[DakotaRidge]

I think there are two big differences where MVFs are fine and where you most likely will get into problems down stream.

I call it a "Simple List" or a "Complex List". You seem to be suggesting "simple lists" and thus not a big deal to go with MVF. But you really need to think about the potential limitations now. And since you do not know where this is going, you can paint yourself into a corner.

1. In the Simple List, the data you are choosing to put in your MVF comes from a table with few fields. Mostly it is just a value. This list does not hold any foreign keys to other related tables. Other tables do not have foreign keys to this list. There are no fields such as dates, currencies, or other values that you may need to do queries against. So a list of products, Disease types where you are just concerned in showing the choices. If all you are ever going to do is show these choices and query against these choices then using MVF is fine

2. In the Complex List the table you are pulling from has a lot of information to include foreign keys to other tables or other tables referencing this list. Lets assume DiseaseType has the following fields
tblDiseaseType
- DiseaseName
-DiseaseDescription
-DiseaseCategory_FK ' foreign key to disease category table
- DiseaseCricality_FK ' foreign key to criticality table
- PercentPopulationAffected ' numeric
-RiskPercentage ' numeric

and a related table
tblOrgansAffected 'references tblDiseaseType
- DiseaseTypeID_FK ' a foreign key to the disease table
- OrganID_FK ' a foreign key to the organs table

Because I am tracking that level of detail it tells me I probably want to do things like answer
- Show all family members with diseases that are high risk and all organs affected
- Show family members with a disease risk over 70%
- Count total family members with a disease in the parasitic infection category that are high criticality

The above questions are trivial in a standard design; the above questions are near impossible to answer with an MVF in the mix.

So it takes some hard thought of the downstream effects and what I will need to do with the data.
You say that your "requirements" are continually changing and do not know what functionality you will need. If I am 100% confident a simple list of choices is all the future holds then knock yourself out with MVF if you understand the other limitations. But if you think you want to do more with the data in the list table then just show and query the choices, you are better off putting the work in now. Sometimes a simple list starts to grow because you decide you want to do more with the related data.

Thanks, Pete. All I want to do with MVFs is show lists of attributes. I agree that a more analytical application may need other tools. This is why my Finance and Health database already has 450 tables and forms. The forms look like the above screenshot. The forms are related to other forms and reports. Some of them use queries to answer questions that you proposed here, while others just allow the user to enter information and display it. Most of the forms aggregate data such as costs and dates. It depends.

I always address ease of data entry, complete information reporting, and decision support. For the first, I try to reduce typing as much as possible. For the second, I try to show as much information, not data, in a document. For the third, I aggregate data using Access aggregate functions and I use information from the literature and internet when I understand its significance.

I am continuously compiling information about foods and medicines. A food that helps one disease may not be good for a person with different disease. And a recommended way to manage money for a young person may not be good for a senior citizen. I admit that I am far from having a handle on the differences in age, health, race, and other factors that impact my family. But my database is a combination of Quicken and EHR/PHRs.

Again, my second question on AW was about cleaning sensitive information from the database. My family members will not want to share certain information with others. How can I give them one command button that will delete all sensitive records across the entire database? That's my challenge. I don't want anyone to have to run a hundred queries. Thanks, that just gave me an idea. I can use a macro to run a batch of queries that uses a click event. Now I just need to inventory all the tables with Is-Sensitive fields. Thanks all.

 

[MajP]

How can I give them one command button that will delete all sensitive records across the entire database?

If I was going to do it for something this big, I would use the description field and mark all fields

Now I would loop all tables
create a recordset
Identify the fields that are sensitive
Delete using a delete query since I know the name and field.

This would be very short. Did someone provide that yet? I can send the procedure if not.

 

[DHookom]

I don’t believe @DakotaRidge has ever stated the full contents of what is contained in the post. Was there an attachment, image, link, script,…?

 

[DakotaRidge]

That sounds like the reason. @DakotaRidge posted several screenshots in that long thread, and only has 34+ posts to date. Attaching images to posts requires providing the URL to the images, or just drag/drop? I would think Dakota could PM an admin to see if they can override the blocking?

I still don't understand why @DakotaRidge cannot post anything to those two specific threads he initiated, but can post to others with images included. The 100 postings threshold has nothing to do with it

BlueSpruce, I suspect AW counts the number of posts that a person submits to AW and if that number is not above a certain threshold, then the system blocks new posts to a thread. I can start new threads, and I can respond to other threads. I just can't go over 100 posts until I become a member of the club.

This is unfortunate because I am locked out of two conversations that I started. The Access experts here have provided many excellent comments that I cannot respond to individually, and I would very much like to thank each of them for helping me.

Sometimes they write that I ignore their comments, but frequently that is not true. There are two things that I am not interested in doing: 1) splitting a database and 2) stopping my use of MVFs. I have no interest in splitting, knowing that it will cause problems for my users in the future. And I have tried in the past few days to explain why I use MVFs. I am glad to see that some here understand the benefits that they offer users and creators (which is what I call myself because I am not a developer, sensu stricto).

I also don't code. I don't want code in my applications because it will require that my users learn to code if they need to make changes to the application. I figure that folks who have created macros in Word and Excel will be able to add macros to Access. They won't be afraid of Access because I won't say code in my user manual. That won't always be the case, but hopefully it will work. I assume that because I used a Word macro to create the text-to-speech code in my applications two decades ago. Word macros are like VBA, and this gave me confidence in my knowledge of code. I can now pick up a textbook on Access programming and read several pages. I use a little code in my applications but very little. When I got started with Access, I had to use code just to open a form. I haven't needed to do that in years. Most operations in my datrabases do not require code. Moving objects on a form does. Reading text aloud does. Having Access drive my car does.

 

[DakotaRidge]

I don’t believe @DakotaRidge has ever stated the full contents of what is contained in the post. Was there an attachment, image, link, script,…?

That's true Duane. Here's the text that I tried to post and it was blocked. I saved it to Word. I was responding to Pat. I made a few edits today.



Thanks a million, Pat. I will use your comments to improve my applications. But some of what you are directing me to do is over my head.

There's only one thing that I will never implement, and that's splitting a database into a FE and a BE. I have a friend who will do things like that for me. I just can't get my head around doing it.

Normalization is also difficult for me to understand. I consider the data that are needed to describe a feature, task, expense, or disease, and I go from there.

I really do not have a menu item named Walmart. I have a Walmart form because I visit the store once or twice a week. By tracking those expenses, I have discovered that I spend about $160 a week at Walmart. I rarely shop at King Soopers and Safeway, even though they are about the same distance from my house. I shopped at CostCo every week until Covid hit. Distinguishing between these stores is a way for me to estimate how much I waste on food purchases by not shopping at Walmart.

My fields do not use numbers, but the names of some of my forms do. The numbers in my form names tell me that I used different graphics as I created a set of related forms. Like yesterday when I placed images of four squirrels, and spirals into my new food recall forms. The main forms have names like frmRecall2, frmRecall3, and frmRecall4. And then the subforms have names like subfrmRecall2, subfrmRecall3, and subfrmRecall4. This naming convention lets me create and update main forms and subforms to my heart's content.

It would be great to avoid making mistakes, but I can't do so until I build out the database more. And I need to the system to throw errors that tell me that I have made a mistake. Working with Access for about 25 years now, I have only seen two or three errors in one of my databases. The last one was when MS did something to the MVF wizard. They fixed the problem in short order.

I think that the reason that I don't see lots of errors is that I don't use code unless I find a useful piece on the internet. I have a difficult time with code on the net because it is often several pages worth.

Pretty soon the system will block me out of this conversation, so watch for me going dark.

 

[MajP]

The following code clears all fields in all tables in the datbase that are identified as sensitive in the description field. If the field is required it alerts you that it cannot clear it.

Private Sub cmdDelSensitive_Click()
  Dim rtn As Long
  Me.txtOut = Null
  rtn = MsgBox("This will delete all sensitive data from your DB. Make sure you have a backup before attempting. Do you want to continue?", vbCritical + vbYesNo, "Delete Data")
  If rtn = vbYes Then
    ClearSensitive
  End If
 
End Sub

Public Sub ClearSensitive()
  Dim tdf As TableDef
  Dim rs As Recordset
  Dim fld As Field
  Dim strSql As String
  Dim strOut As String
  For Each tdf In CurrentDb.TableDefs
    
    If Left(tdf.Name, 1) <> "~" And Left(tdf.Name, 4) <> "MSYS" And Left(tdf.Name, 4) <> "Copy" Then
      strOut = strOut & "TABLE: " & tdf.Name & vbCrLf
      Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
      For Each fld In rs.Fields
        If DoesFieldPropertyExist(fld, "Description") Then
          If Left(fld.Properties("Description"), Len("Sensitive")) = "Sensitive" Then
              If fld.Required = True Then
                MsgBox "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required.", vbCritical
                strOut = strOut & "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required." & vbCrLf
               Else
                 strOut = strOut & "-- Clearing Sensitive Field: " & fld.Name & vbCrLf
                 strSql = "Update " & tdf.Name & " Set " & fld.Name & " = Null"
                 'strOut = strOut & "----" & strSql & vbCrLf
                 CurrentDb.Execute strSql
               End If
          End If
        End If
      Next fld
      strOut = strOut & vbCrLf
    End If
    
  Next tdf
  Me.txtOut = strOut
End Sub
Function DoesFieldPropertyExist(fld As DAO.Field, propName As String) As Boolean
    On Error GoTo ErrorHandler
    Dim p As DAO.Property

    ' Attempt to access a standard property (e.g., DataUpdatable)
    ' This will generate an error if the property doesn't exist for this field type
    Dim temp As Variant
    temp = fld.Properties(propName).Value
    DoesFieldPropertyExist = True
    Exit Function

ErrorHandler:
    ' If an error occurred, it means the property was not found directly
    ' Now, check for user-defined properties
    For Each p In fld.Properties
        If p.Name = propName Then
            DoesFieldPropertyExist = True
            Exit Function
        End If
    Next p

    DoesFieldPropertyExist = False ' Property not found
End Function

The tables starting with Copy still have data in those fields. To run this again Copy the copies and rename those so they do not start with the word copy. Or add some tables and mark certain fields sensitve.

 

[DakotaRidge]

The following code clears all fields in all tables in the datbase that are identified as sensitive in the description field. If the field is required it alerts you that it cannot clear it.

Private Sub cmdDelSensitive_Click()
  Dim rtn As Long
  Me.txtOut = Null
  rtn = MsgBox("This will delete all sensitive data from your DB. Make sure you have a backup before attempting. Do you want to continue?", vbCritical + vbYesNo, "Delete Data")
  If rtn = vbYes Then
    ClearSensitive
  End If
 
End Sub

Public Sub ClearSensitive()
  Dim tdf As TableDef
  Dim rs As Recordset
  Dim fld As Field
  Dim strSql As String
  Dim strOut As String
  For Each tdf In CurrentDb.TableDefs
   
    If Left(tdf.Name, 1) <> "~" And Left(tdf.Name, 4) <> "MSYS" And Left(tdf.Name, 4) <> "Copy" Then
      strOut = strOut & "TABLE: " & tdf.Name & vbCrLf
      Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
      For Each fld In rs.Fields
        If DoesFieldPropertyExist(fld, "Description") Then
          If Left(fld.Properties("Description"), Len("Sensitive")) = "Sensitive" Then
              If fld.Required = True Then
                MsgBox "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required.", vbCritical
                strOut = strOut & "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required." & vbCrLf
               Else
                 strOut = strOut & "-- Clearing Sensitive Field: " & fld.Name & vbCrLf
                 strSql = "Update " & tdf.Name & " Set " & fld.Name & " = Null"
                 'strOut = strOut & "----" & strSql & vbCrLf
                 CurrentDb.Execute strSql
               End If
          End If
        End If
      Next fld
      strOut = strOut & vbCrLf
    End If
   
  Next tdf
  Me.txtOut = strOut
End Sub
Function DoesFieldPropertyExist(fld As DAO.Field, propName As String) As Boolean
    On Error GoTo ErrorHandler
    Dim p As DAO.Property

    ' Attempt to access a standard property (e.g., DataUpdatable)
    ' This will generate an error if the property doesn't exist for this field type
    Dim temp As Variant
    temp = fld.Properties(propName).Value
    DoesFieldPropertyExist = True
    Exit Function

ErrorHandler:
    ' If an error occurred, it means the property was not found directly
    ' Now, check for user-defined properties
    For Each p In fld.Properties
        If p.Name = propName Then
            DoesFieldPropertyExist = True
            Exit Function
        End If
    Next p

    DoesFieldPropertyExist = False ' Property not found
End Function

The tables starting with Copy still have data in those fields. To run this again Copy the copies and rename those so they do not start with the word copy. Or add some tables and mark certain fields sensitve.

Thanks, Pete.

I think this is exactly what I was looking for. I will add a few Is Sensitive fields, back up my application, and do a test.

I would never have been able to create this code myself. This will help my family members share their version of the database with other family members and be confident that they aren't sharing sensitive or embarassing financial and health information. There isn't a problem with information from the internet, but some folks may not want their A1C or hemoglobin numbers to be shared.

Now, if I can only finish this critter before I croak.

Thanks again.

 

[DHookom]

If you haven't split out the data tables from the other objects, how do you intend to provide updates to your users? When you send them a new application, it will overwrite everything they have added in terms of records? Is your application read-only?

On a side note, it seems like a waste of time to maintain a lists of recalled products and the specifics. This is constantly changing information so attempting to maintain it locally seems like a horrible waste of time. Anyone needing information can open any browser and search for "recent food recalls" and get up-to-date information in an instant.

I do wish the posting rules were published somewhere, what could it hurt and it would certainly end some confusion.

 

[DakotaRidge]

The following code clears all fields in all tables in the datbase that are identified as sensitive in the description field. If the field is required it alerts you that it cannot clear it.

Private Sub cmdDelSensitive_Click()
  Dim rtn As Long
  Me.txtOut = Null
  rtn = MsgBox("This will delete all sensitive data from your DB. Make sure you have a backup before attempting. Do you want to continue?", vbCritical + vbYesNo, "Delete Data")
  If rtn = vbYes Then
    ClearSensitive
  End If
 
End Sub

Public Sub ClearSensitive()
  Dim tdf As TableDef
  Dim rs As Recordset
  Dim fld As Field
  Dim strSql As String
  Dim strOut As String
  For Each tdf In CurrentDb.TableDefs
   
    If Left(tdf.Name, 1) <> "~" And Left(tdf.Name, 4) <> "MSYS" And Left(tdf.Name, 4) <> "Copy" Then
      strOut = strOut & "TABLE: " & tdf.Name & vbCrLf
      Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
      For Each fld In rs.Fields
        If DoesFieldPropertyExist(fld, "Description") Then
          If Left(fld.Properties("Description"), Len("Sensitive")) = "Sensitive" Then
              If fld.Required = True Then
                MsgBox "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required.", vbCritical
                strOut = strOut & "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required." & vbCrLf
               Else
                 strOut = strOut & "-- Clearing Sensitive Field: " & fld.Name & vbCrLf
                 strSql = "Update " & tdf.Name & " Set " & fld.Name & " = Null"
                 'strOut = strOut & "----" & strSql & vbCrLf
                 CurrentDb.Execute strSql
               End If
          End If
        End If
      Next fld
      strOut = strOut & vbCrLf
    End If
   
  Next tdf
  Me.txtOut = strOut
End Sub
Function DoesFieldPropertyExist(fld As DAO.Field, propName As String) As Boolean
    On Error GoTo ErrorHandler
    Dim p As DAO.Property

    ' Attempt to access a standard property (e.g., DataUpdatable)
    ' This will generate an error if the property doesn't exist for this field type
    Dim temp As Variant
    temp = fld.Properties(propName).Value
    DoesFieldPropertyExist = True
    Exit Function

ErrorHandler:
    ' If an error occurred, it means the property was not found directly
    ' Now, check for user-defined properties
    For Each p In fld.Properties
        If p.Name = propName Then
            DoesFieldPropertyExist = True
            Exit Function
        End If
    Next p

    DoesFieldPropertyExist = False ' Property not found
End Function

The tables starting with Copy still have data in those fields. To run this again Copy the copies and rename those so they do not start with the word copy. Or add some tables and mark certain fields sensitve.

Pete, you forced me to look up your full name in one of Colin's YouTube posts. I don't like using a person's code without crediting him or her in my database. Fortunately, I watched one of your recent presentations to Access Europe that Colin posted. Thanks again.

 

[MajP]

Pete, you forced me to look up your full name in one of Colin's YouTube posts. I don't like using a person's code without crediting him or her in my database. Fortunately, I watched one of your recent presentations to Access Europe that Colin posted. Thanks again.

I post a lot of code as examples. It is not fully tested or error checked. You definitely want to test this thoroughly.

 

[DakotaRidge]

Now I understand what AWF wants to prevent with its rule. I am seeing all sorts of scam on this site. I may have to cut the cord.

 

[Mark_]

There's only one thing that I will never implement, and that's splitting a database into a FE and a BE. I have a friend who will do things like that for me. I just can't get my head around doing it.

Best way to "Wrap your head around it" is to think of programs like Notepad. Notepad lets you open another file, edit it, save it. Notepad can create a new text document. Notepad DOES NOT save the data in itself, so if you put in a new copy of Notepad you don't loose everything you've written down.

ACCESS does the same thing. The program ACCESS reads from and writes too your .accdb file. If you get a new copy of ACCESS it doesn't get rid of all of your existing databases. If you don't split, your end users will get really tired of having to put everything back in each time you send out an update. If you NEVER plan to update this may not be a problem, but your description is you constantly make changes.

 

[MajP]

@DakotaRidge,
I read the requirement wrong. I assumed you wanted to clear certain fields, not certain records. So that solution will not help.
If you are deleting records, let me do a new demo. Disregard that.
If you add an "IsSensitive" field then this code should work sort of

Public Function ClearSensitiveRecords() As Boolean
    On Error GoTo ClearSensitiveRecords_Error
  Dim tdf As TableDef
  Dim rs As Recordset
  Dim fld As Field
  Dim strSql As String
  Dim strOut As String
  For Each tdf In CurrentDb.TableDefs
    
    If Left(tdf.Name, 1) <> "~" And Left(tdf.Name, 4) <> "MSYS" And Left(tdf.Name, 4) <> "Copy" Then
      Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
      If HasSensitiveField(rs) Then
        rs.Filter = "isSensitive = True"
        Set rs = rs.OpenRecordset
        If Not rs.EOF Then
          
          rs.MoveLast
          rs.MoveFirst
          If rs.RecordCount > 0 Then
            strOut = strOut & "Attempting to delete " & rs.RecordCount & " records from table: " & tdf.Name & vbCrLf
            strSql = "delete * from [" & tdf.Name & "] WHERE isSensitive = true"
            rs.Close
            Debug.Print strSql
            CurrentDb.Execute strSql, dbFailOnError
          End If
      End If
      strOut = strOut & vbCrLf
      
    End If
    End If
    
  Next tdf
  Me.txtOut = strOut
    
    On Error GoTo 0
    Exit Function

ClearSensitiveRecords_Error:
    If Err.Number = 3200 Then
      MsgBox "Could not delete from " & tdf.Name & " since it has a related records. "
      strOut = strOut & "DELETE FAILED FOR RELATED RECORDS" & vbCrLf
      Resume Next
    Else
       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ClearSensitiveRecords, line " & Erl & "."
    End If
End Function

Public Function HasSensitiveField(rs As DAO.Recordset) As Boolean
  Dim fld As DAO.Field
  For Each fld In rs.Fields
    If fld.Name = "IsSensitive" Then
      HasSensitiveField = True
      Exit Function
     End If
  Next fld
End Function

However, if you enforce referential integrity you cannot delete records that have related records unless you set cascade deletes.
So you can delete fields or records with the two examples.

 

[DakotaRidge]

Mark,

My approach now is to send the database to family members on USB drives when I am happy with it. I will continue development but I don't plan to send it out for several months or a year after that. I may ask family to send me their USB drives so I can copy the new database for them. They can opt out if they want. Splitting could be a good thing because all I would need to do is send folks the FE. Thanks.

My challenge will be to teach folks to use Access when they have never used a database. Some of them don't use computers for anything other than email, and they only check email once a week.

I suspect I will need to have a two-page description of every form/report in the application. But I am hoping that the graphics and built-in speech will reduce the amount of work that I will need to do on the user manual. Wish me luck.

.

 

[Mark_]

I suspect I will need to have a two-page description of every form/report in the application. But I am hoping that the graphics and built-in speech will reduce the amount of work that I will need to do on the user manual. Wish me luck.

You will DEFINITELY want to reduce the thousands of reports and forms you have then. 2 pages EACH? For a printed manual you'll be contributing to the deforestation of the planet!