Clear out data (3 Viewers)

[AccessBlaster]

If the goal is to keep it family-friendly less is better. Complexity is the enemy here.

 

[MajP]

I just don't see how it can be done considering that I have ten personalities, three races, and three generations of users in five states to deal with. Just dealing with extroverts is a real challenge for me.

Sounds to me you need to write the books, because I have never seen these topics addressed anywhere as being important in database development.
For your development:
"Designing Access Applications for Different Personalities"
"Race based Access Database Design and Development" sounds controversial
"Impacts on Age and Generations in Database Design"
"Building Specialized Database Applications for People living in Different Regions"

 

[isladogs]

...considering that I have ten personalities ... to deal with.

Are those split?

 

[BlueSpruce]

Almost 200 posts into this, he/we are no closer to a solution to the original question. When he was given an answer to it, there was no follow-thru on his part to actually try it. The developers here tried (in vain) to save him from himself but he seems to take delight in explaining how his unique and wonderful personality somehow insulates him from the same fate his last endeavor suffered.

Yeah, Chatty is the right tool for him...

That being said, Mods and Admins: PLEASE do not close this thread. Rubber-necking is how I am entertained...

Agreed, it is what it is

 

[DakotaRidge]

The only way you can have that many forms is that most of these forms are all doing the same thing. Maybe a different sort order, or filter, or visible controls etc. That is why I can rebuild this application with 20 different forms and modify them at runtime. If OP says they are not are all unique then it is a lie.
Can you modify them at runtime using macros? Maybe a little. I can probably write some working code using only one finger to type and closing both eyes. However, I chose not to handicap myself that severely. That seems really painful to attempt. To me using only macros would be equivalent to trying to build an application, but only typing with my eyes closed. The question is not can it be done, it is who in the hell would want to.

The only way you can have that many forms is that most of these forms are all doing the same thing. Maybe a different sort order, or filter, or visible controls etc. That is why I can rebuild this application with 20 different forms and modify them at runtime. If OP says they are not are all unique then it is a lie.
Can you modify them at runtime using macros? Maybe a little. I can probably write some working code using only one finger to type and closing both eyes. However, I chose not to handicap myself that severely. That seems really painful to attempt. To me using only macros would be equivalent to trying to build an application, but only typing with my eyes closed. The question is not can it be done, it is who in the hell would want to.

Pete, only 20 forms? I have half that number of full-page maps now. The maps show food recalls, Covid trends, and state tax policies. You'll have to tell me how you can get more than 80 domains onto just 20 forms/reports.

Can you show me a couple of health data forms, say for managing diabetes and kidney disease data. I am sure a few of your health databases have examples. Thanks.

 

[MajP]

Pete, only 20 forms? I have half that number of full-page maps now. The maps show food recalls, Covid trends, and state tax policies. You'll have to tell me how you can get more than 80 domains onto just 20 forms/reports.

Can you show me a couple of health data forms, say for managing diabetes and kidney disease data. I am sure a few of your health databases have examples. Thanks.

Send me your database, and I promise you I can reduce it by 95%. PM me and put it in a google drive. Since it is so poorly designed I may struggle to make heads or tails of it initially, but I am confident I can radically reduce it. I may have exaggerated. It may take 40 tables and forms max.
As I said you do not have 80 domains. Maybe 10. You just have replication of the same information that could be dynamically tailored for the display.

 

[MajP]

Almost 200 posts into this, he/we are no closer to a solution to the original question.

Not true. I solved this a long time ago, but lost in the noise.

FYI. I wrote none of this code, and let AI do it.

1. Add an Is_Sensitive field to all tables so that you can mark records as sensitive.

Sub LoopThroughUserTablesAddSensitve()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    ' Set the database object to the current database
    Set db = CurrentDb

    ' Loop through each TableDef object in the TableDefs collection
    For Each tdf In db.TableDefs
        ' Check if the table is a user-defined table (not a system or temporary table)
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            ' Print the name of the user-defined table to the Immediate Window
            ' Debug.Print tdf.Name
            AddBooleanField (tdf.Name)
        End If
    Next

    ' Clean up objects
    Set tdf = Nothing
    Set db = Nothing

End Sub

Sub AddBooleanField(TableName As String)

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field

    ' Set a reference to the current database
    Set db = CurrentDb

    ' Set a reference to the table where you want to add the field
    ' Replace "YourTableName" with the actual name of your table
    Set tbl = db.TableDefs(TableName)

    ' Create a new field object
    ' dbBoolean corresponds to the Yes/No data type in Access
    Set fld = tbl.CreateField("Is_Sensitive", dbBoolean)

    ' Append the new field to the table's fields collection
    tbl.Fields.Append fld

    ' Optional: Set a default value for the new field
    ' fld.DefaultValue = "No" ' Sets the default to False

    ' Clean up objects
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing

    'MsgBox "Boolean field 'Is_Senstive' added to 'YourTableName' successfully.", vbInformation

End Sub

2. Tag records as sensitve.

Run the code to delete all records in all tables that are sensitive.

Sub LoopThroughUserTablesDeleteSensitive()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    ' Set the database object to the current database
    Set db = CurrentDb

    ' Loop through each TableDef object in the TableDefs collection
    For Each tdf In db.TableDefs
        ' Check if the table is a user-defined table (not a system or temporary table)
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            ' Print the name of the user-defined table to the Immediate Window
            ' Debug.Print tdf.Name
            If TableHasField(tdf.Name, "Is_Sensitive") Then DeleteSensitiveRecords tdf.Name
           
        End If
    Next

    ' Clean up objects
    Set tdf = Nothing
    Set db = Nothing

End Sub
Function TableHasField(ByVal TableName As String, ByVal fieldName As String) As Boolean
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
   
    Set db = CurrentDb
   
    On Error GoTo Err_Handler ' Enable error handling
   
    ' Get the TableDef object for the specified table
    Set tdf = db.TableDefs(TableName)
   
    ' Iterate through the fields collection
    For Each fld In tdf.Fields
        If fld.Name = fieldName Then
            TableHasField = True ' Field found
            Exit Function
        End If
    Next fld
   
    TableHasField = False ' Field not found
   
Exit_Handler:
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
   
Err_Handler:
    ' Handle the error if the table does not exist
    If Err.Number = 3265 Then ' Item not found in this collection (table doesn't exist)
        MsgBox "Table '" & TableName & "' not found.", vbCritical
        TableHasField = False
    Else
        MsgBox "An error occurred: " & Err.Description, vbCritical
    End If
    Resume Exit_Handler
End Function
Sub DeleteSensitiveRecords(TableName As String)

    On Error GoTo DeleteSensitiveRecords_Error
    Dim db As DAO.Database
    Dim strSQL As String

    ' Set the current database
    Set db = CurrentDb

    ' Construct the SQL DELETE statement
    ' Replace "YourTableName" with the actual name of your table
    strSQL = "DELETE FROM [" & TableName & "] WHERE Is_Sensitive = True"

    ' Execute the SQL statement
    ' dbFailOnError ensures that an error is raised if the operation fails
    db.Execute strSQL, dbFailOnError

    ' Clean up
    Set db = Nothing
   
    MsgBox "Sensitive records deleted successfully from " & TableName, vbInformation

   
    On Error GoTo 0
    Exit Sub

DeleteSensitiveRecords_Error:
    If Err.Number = 3200 Then
      MsgBox "Could not delete sensitive records from " & TableName & " because related records exist and referenitial integrity is enforced."
     
    Else
      MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteSensitiveRecords, line " & Erl & "."
      Debug.Print TableName & "  " & vbCrLf & strSQL
    End If
End Sub

I added a little error checking to tell you if it cannot delete a record that has related child records.

 

[NauticalGent]

Not true. I solved this a long time ago, but lost in the noise.

I said as much in my post - a solution was offered but no action taken.

Send me your database...

@DakotaRidge - simply put, that is the deal of the century. If you do not take him up on this, then your ignorance will have attained a level that can not be measured with modern technology.

 

[MajP]

Can you show me a couple of health data forms, say for managing diabetes and kidney disease data. I am sure a few of your health databases have examples

I have posted thousands of examples of access databases, queries, and code examples. Just start searching. How about you post 1 real database example?
These are kind of health and medicine related ones

Solved - Can I hide controls in a record in Detail View

A simple question, with perhaps a simple answer. In the detail section of a form I have a checkbox and based on whether this checkbox is checked or not, I would like to show or hide other controls (Textbox, Checkbox etc) on the same record. Is this possible? When it comes to a textbox I can...

www.access-programmers.co.uk

Combo's [Edit from Record] help

Hello Gurus; I am almost finalizing my project, is not perfect but is mine thanks to the help of many of you, but I have an issue and I don't know what to do: Here is the part of the form: The user adds all those records in the ADD RECORD Form, that part is working fine. Before those records...

www.access-programmers.co.uk

Solved - Link Text Box to another Text Box

HiAll, I have a form that contains a drop-down text box with the values "Bad, Acceptable, Good, V Good" and I have another drop-down text box that contains "illumination is very low, illumination is low, illumination is acceptable" and so on. what I am trying to do is when I select "Bad" in...

www.access-programmers.co.uk

Filter Form with multiple Unbound combo box and dates

Dears; I need your support, as I have a form with multiple un-bound combo-box and need to filter the form based on the user selection. as i tried below but not work Properly. and will not filer the form. so can you guide me how to solve this, File attached. Private Sub filterThisForm2() On...

www.access-programmers.co.uk

Solved - Help to build a treeview

Bom dia. Primeiramente peço desculpas pela linguagem. Como alguns de vocês sabem, sou médico e estou tentando desenvolver um APP em MS access para o meu dia a dia. Existem duas classificações em nosso meio, que são: 1) a Classificação Internacional de Doenças versão 11 (...

www.access-programmers.co.uk

Somewhere I did a very involved pet management Db for a vet but cannot find that.

 

[MajP]

One way I would get rid of a lot of tables is to combine all the MVF lookup tables into a single lookup table.
This demo is a bunch of single select comboboxes, but the same would work with MVFs.

Solved - How to quickly duplicate Tables & associated subforms within a main form

Another quick question. Data validation best practice. How would you control a Combo box to show a known list of values? Do I create a table for each combo box? Most need to be lists which are occasionally edited by an end user In excel I had them all in 1 sheet as Named Tables Most/all of...

www.access-programmers.co.uk

 

[DakotaRidge]

Agreed, it is what it is

Actually, the only advice that I have not taken so far is to split the database and use more code. I see no need to use more code because macros do everything that I need to do. The only code that is needed for the speech. I have had that for almost two decades.

And no one so far has mentioned how they achieve DKNF or even BCNF. Without seeing my database, no one knows how normalized my tables really are. Here's a very small part of the relationship map. It is compressed, meaning that very few field names are shown. The map shows some of the main tables (tbl) and some of the lookup tables (lku).

I tried once to create a relationship map for my database with 4,000 tables, and Access locked up after about an hour. Once again, I design databases using the 5 Ws.

 

[MajP]

I have half that number of full-page maps now. The maps show food recalls

I make one form and load the images dynamically.
Something like this where you can show multiple ship floor plans and "geo locate" tasks to complete.

Solved - User made command button that can be moved with mouse?

haha! We got plenty of liferafts! Thanks MajP, is working good. Attached is idea of the jobs pane. I think when a new dot is moved to the plan/deck then it would ideally open up a new record/job for this new dot that has been added, this would highlight as a new job in the jobs detail pane...

www.access-programmers.co.uk

 

[DakotaRidge]

Are those split?

Colin, they probably are not because I use MBTI to define them.

 

[MajP]

Without seeing my database, no one knows how normalized my tables really are.

About 80% of that image is lookup tables. As I said when you send me the db I will crush them all down into one single table.

 

[MajP]

The other thing I will do when you send me the database is to combine the rest of the tables I see into a single Expenses table. Pretty sure most ERP do not have an expense table for doctors, car repairs, restaurants.
So from your image I will end up with two maybe three tables. Heck I have 17 more tables left to keep within my promised 20.

 

[DakotaRidge]

Yes I know that. That's why I'm asking him what HIS plan actually is, since relinking is 'out of the question'.

About 80% of that image is lookup tables. As I said when you send me the db I will crush them all down into one single table.

Sorry, Pete, I can only send this database to family members via the US mail or by hand delivery. It has sensitive information. Some of the data falls under HIPAA.

 

[MajP]

@DakotaRidge,
No problem, I already solved that issue. When ready I will send to a link to a google drive where you can upload the database, and I will start to slowly whittle it down. Just delete the sensitive records using the provided code. To make it understandable you need to leave in sufficient data. It is hard to follow the intent if you delete everything.

 

[MajP]

In the mean time you can simply delete out all the data and send me the empty db. I can at least see the forms and proposed tables. The relinking is not an issue as long as your users always put the BE in the same location as the FE. That is easy to ensure. You have an application Folder and inside you have both the front end and back end in the folder. Also you will probably need an Attachments and Images folder for holding those things.
Looking forward to getting started. I am sure it will take me a while, but I am sure I can get it down to a very reasonable size probably 95% reduction of objects. Unfortunately I will be providing vba not macros since macros are a waste of time.

 

[BlueSpruce]

Actually, the only advice that I have not taken so far is to split the database

That's it, I'm done with trying to help you. This has been a big waste of time, I'm out of this...

 

[BlueSpruce]

It took 219 posts to get to this: