Clear out data (3 Viewers)

[DakotaRidge]

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

Pete, my forms are a mix of data, calculated values, graphics (maps), and speech. The aggregated values in the main forms use data in the subforms,

There are boxes that use conditional formatting. For example, if a person's A1C test result is over 7 a box turns red. If the A1C is low then the box will also turn red. And if the A1C is near the low or high end of the normal range for his/her age and race, the box will turn yellow.

The idea is to encourage the person to pay more attention to hos/her test results when they are out of range or near the top or bottom.

Also, if medical test results change over time, boxes will change color. That tells the person that their meds are working or not. If they are not working, the person may use one of the actions suggested by the database. Suggestions come from the medical literature, and the person can edit them.

Other boxes count the number of checkboxes on a subform that are true and the number that are false.

To keep from having to use numerous If-Then statements, I use multiple forms because the measures are different. Take utilities, for example, water is sold where I live in gallons, electricity is sold by the kilowatt hour, and gas is sold in another unit. I could use If-Then to display units on each Utilities form/report, but I prefer using different forms and reports. That way the aggregated information is contextual, the way people think about their expenses.

So each form also shows the cost of water, gas, and electricity each month. Some forms show the amount of a utility used by the month, for the quarter, and for the year. These are simple calculations done on the form. Other expenses like dairy deliveries, groceries, and gas and oil are tracked the same way.

This is why I think I need so many forms. It is not just a matter of showing different graphics. It is also about showing the current analytics at the same time.

 

[DakotaRidge]

@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.

Sorry Pete, I don't touch Google. I would remove it from my phone, but it is the OS.

 

[Isaac]

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.

HIPAA doesn't even apply to you, you're not a covered entity
Just more BS

 

[DakotaRidge]

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

Sorry about that BlueSpruce. Thanks for all of your help. I very much appreciate everything you have written. As I have said repeatedly, this application is for a special group of people. They are not techies.

 

[AccessBlaster]

Sorry about that BlueSpruce. Thanks for all of your help. I very much appreciate everything you have written. As I have said repeatedly, this application is for a special group of people. They are not techies.

Why not empty the data and allow MajP to exam it privately?

 

[NauticalGent]

It took 219 posts to get to this:

View attachment 120917

I cant do that. This like watching Howard Stern - I HAVE to stick around to see what he says next...

I may need a 12-step program...? Nah...

 

[DakotaRidge]

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.

Thanks, Pete. I had to reply to so many posts this morning that I forgot yours.

Folks wanted to beat me up for many reasons, I always try to thank everyone. Unfortunately, they don't know what I accept and what I save for later.

I actually don't reject anything. But I just don't have the programming skills to implement all the suggestions.

I have no idea why some folks are so much for splitting. I read occasionally in UGs that someone is not using a split database. Those people never get beat up the way I get.

I understand why developers do not use MVFs. I use them a lot in simple ways. You have taught me more about them in the past two weeks than I learned in almost two decades. Thank you.

I will try to add your code above to my database as soon as I can. I understand about 70 percent of it.

 

[The_Doc_Man]

This thread reminds me of the Woody Allen movie Sleeper - specifically reminding me of the comment that watching old Howard Cosell interviews was like a form of torture. Or What's Up, Tiger Lily - where in one scene a protagonist says "I'd call you a sadistic sodomistic necrophile, but that would be beating a dead horse." This thread is the "gift" that keeps on giving.

 

[MajP]

I read occasionally in UGs that someone is not using a split database. Those people never get beat up the way I get.

Not everyone needs a split db, but your specific scenario warrants it (almost. If the tables were settled).
1. You absolutely need to split a db if you are in a multi user db where people are simultaneously working with the same data.
2. Or where you want users to be able to work on their database but you plan to push out changes to the forms, reports, queries, and code. This way they can continue to work without interruption while you can develop enhancements and fixes. Then push them the update.
However in your case that is not really the scenario because you plan to continue to modify tables as well.

 

[MajP]

Sorry Pete, I don't touch Google. I would remove it from my phone, but it is the OS.

You can send email, snail mail, or use another file sharing app if you like.

 

[Pat Hartman]

When I have looked at the panes created by developers, I never see any of them working for me. With hundreds or thousands of forms, and hundreds or thousands of reports in a database, I need customized navigation panes.

What you NEED is to normalize your schema so you don't have THOUSANDS of forms that do the same thing. And the best way to handle a TOC is just that. The high level is finance, human medicine, pet medicine, etc. Whatever sections you have. And then each of those drills down. The entire TOC is held in a table so if you want to duplicate items or move them around, you have total control and you don't have to write a single piece of code or design a new form for the new view. I really don't know how many times we have to explain this.

I have built medical applications for clients that used ICD 9 and then we moved to ICD 10. That was a royal PITA to convert our existing medical records because ICD 10 was much more detailed than ICD 9 so I had to work with the client to create a conversion that would get the best possible ICD 10 out of our existing ICD 9's. It is all done with conversion tables. My job was trivial because it was controlled by tables and queries and no code was ever required. The users had a harder job because they had to actually think and use their medical logic to make the conversion. I started by pulling in the entire universe of ICD 9 codes that they were currently using. Then I narrowed it down to active clients and that is what they started with. That gave them the ability to make very accurate conversion tables because if they had 1 client who had ICD xxx, they could convert xxx to the specific, detail, ICD 10 code for that client which is something that a generic conversion could never have accomplished. You probably have a few dozen codes you need to worry about and you can help your friends and relatives to drill down to the actual ICD 10 code they need and otherwise just load the full ICD 10 table and not worry about an automated conversion. I'm guessing that no one who will be using this app has a freaking clue what this last paragraph was even talking about so I can tell you that you are over thinking this by a mile. Concentrate on the day to day details of managing a disease. Concentrate on blood pressure, pulse rate, sugar levels, weight, drug usage, food intake, etc. Then on doctor's visits, drugs (with notes on ill effects), surgeries, vaccines, etc. and you won't be needing thousands of anything.

nteresting, I was getting ready to go back out in public last month when I had a talk with my neighbor. He told me that one of his friends attended an event in the city recently. The person came down with Covid and died. Another friend told me someone else he knows came down with the disease. I don't know if that person died or not. I have a friend in California who has had Covid twice. His cognitive abilities have been seriously impacted.

1. Surgical masks do not prevent virus' from penetrating. N95 masks block out 95% of airborne particles. But virus' are teeny-tiny and fall in the 5% that even the best masks don't block. The only time a virus particle would be trapped by the N95 mask is if it were contained within a water droplet. Do your own research. Always read the "settled" science on masks prior to 2020. Read the settled science based on the various viral epidemics we've had during the past century since the Spanish Flu. Read Fauci's opinion on masks, etc. before he decided that he could control the world. Would you put up a chain link fence around your property to keep out mosquitos? That's the equivalent of using a N95 mask.
2. There is no way to tie COVID to a particular outing. The incubation period is up to a week so you cannot tie it to an event.
3. Most of my friends took at least the initial vaccine because we're all old and have comorbidities. The friends who have taken the most "boosters" are the only people I know who have had COVID at all and the more boosters, the more likely they are to have subsequent infections. None of the children/grandchildren of people I could influence subjected their child to a vaccine that did not have any long term testing associated with it. Let us not forget Thalidomide and other drugs that have caused serious problems years after the fact. Even the early results of COVID testing revealed that young people were not in danger of dying from COVID. And since the vaccine didn't prevent you from contracting the disease, there was no point in taking an untested vaccine for anyone under the age of 70. There was much less jeopardy with developing a natural immunity. Once it became clear that the government distribution plan for the original COVID vaccine was to prioritize those people most in jeopardy of catching the disease (medical workers) rather than those people most in jeopardy of dying from it (the elderly and the ill), I almost didn't even take the original vaccine. WHY? What did the government know that i didn't know? WHY were they prioritizing the people more likely to be exposed to the disease rather than the people who would be more likely to die if they caught it?

I know that some people are more susceptible to diseases than others.

100% true and yet where are the genetic studies? Why do some people keep getting COVID over and over again? The "scientists" don't want to answer either of these questions and so they aren't being studied. I watched a documentary a few years ago about a group of people who were studying the black death. They were concentrating on finding towns in the UK where there were no deaths or few deaths. They were studying the genes of the townsfolk from the families in towns where few or no people died with the general population. They isolated some gene where if one of your parents carried the gene, you might get the disease but would probably survive but if both parents passed you a gene so you had two of them, you were almost certain to never contract the disease. Why aren't we using the genealogy databases to study this (and other) diseases?

 

[DakotaRidge]

Not everyone needs a split db, but your specific scenario warrants it (almost. If the tables were settled).
1. You absolutely need to split a db if you are in a multi user db where people are simultaneously working with the same data.
2. Or where you want users to be able to work on their database but you plan to push out changes to the forms, reports, queries, and code. This way they can continue to work without interruption while you can develop enhancements and fixes. Then push them the update.
However in your case that is not really the scenario because you plan to continue to modify tables as well.

Pete,
Number 1 does not apply at all. My brother lives in northern Florida, I have a sister in Miami, a son in Denver, a brother in Southern California and one in Massachusetts. They won't be using the same datasets. The database is probably the same as MS Money.

Number 2 is about the same situation. If someone in a household comes down with another disease, they will have to mail me their .Accdb file so I can add the disease, symptoms, meds, etc. to it. Or I may be able to tell the person how to copy and paste objects over the phone.

Most, if not all of the forms in the Personal Finance section are structured so the user can add other expenses and sources of income to that section. The Grocery domain, for example, lets the user add new stores with their locations and value propositions. Value propositions can be discounts on certain products such as gasoline, and they can be delivery of products and afterhour services.

 

[MajP]

Number 2 is about the same situation. If someone in a household comes down with another disease, they will have to mail me their .Accdb file so I can add the disease, symptoms, meds, etc. to it. Or I may be able to tell the person how to copy and paste objects over the phone.

If the references tables are not editable and you manage what goes in the lists, then the reference tables could stay in the front end but then you cannot enforce referential integrity.
I then would then keep the source reference tables in the front end, but when the user gets a new front end it updates their reference tables from the authoritative tables. Then the users always have the updated lists without requiring to recover the databases and manually update them.

 

[DakotaRidge]

If the references tables are not editable and you manage what goes in the lists, then the reference tables could stay in the front end but then you cannot enforce referential integrity.
I then would then keep the source reference tables in the front end, but when the user gets a new front end it updates their reference tables from the authoritative tables. Then the users always have the updated lists without requiring to recover the databases and manually update them.

Some lookup tables can remain in the FE. It depends on whether or not the household needs to update them or not.

If the information in a lookup table comes from the internet, then that table needs to be updated. WIth thousands of diseases and new ones being discovered all the time, lkuDiseases needs to be updatable, but not until someone in that family gets the disease. I will need to think about where to put lkuDiseases, in FE or BE? All 1,000 images will be in the FE because they are in the forms.

This is why I don't want to split yet. I can't make these decisions until the database is finished and I have defined everything. There's no problem with forms and reports.

 

[MajP]

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

FYI, you do not have a relationship diagram, you have an image of default query joins.
There is no referential integrity enforced on any of those relationships. So what's the point? This diagram only serves to set the default join type and all are inner joins.

 

[Pat Hartman]

Some lookup tables can remain in the FE. It depends on whether or not the household needs to update them or not.

No, they can't. You are not understanding the purpose of the split between application objects and data. If/when you make this split. EVERY table that includes data that can be updated by the user, gets put in the BE. NEVER in the FE - because, you want to be able to completely replace the FE and not have to worry about clobbering ANY user data. In fact, the ONLY tables that ever live in the FE are tables that YOU make to control things like the menus. The user cannot change these tables because he does not add new objects to the application. Only YOU add new objects. Therefore, all tables in the FE are those with data controlled by the developer.

The sooner you split, the less trouble you will have going forward. Or never split and see how quickly your app dies. You are not going to want to be in the business of managing user data and that is exactly what will happen if you don't split. This is not a "perfection" thing. It is a totally practical solution for managing an application that will be used by more than just yourself. MS has always recommended this split as far as I know but they recommend it for technical reasons such as to minimize the potential for corruption not practical reasons such as allowing each user to have a separate set of data that is not disturbed when you change the application objects. Both are valid.

 

[Pat Hartman]

There are boxes that use conditional formatting. For example, if a person's A1C test result is over 7 a box turns red. If the A1C is low then the box will also turn red. And if the A1C is near the low or high end of the normal range for his/her age and race, the box will turn yellow.

If you properly tablize the various values for test ranges, it makes no difference whatsoever what the test is measuring. You find the row with the range that contains the value of the A1c, Blood Pressure, pulse rate, sugar level, etc. and the row value tells you whether or not to highlight the control. You make the table, enter the data and then EVERY SINGLE CONTROL uses that same table to determine the color for the control. You do this ONCE, not hundreds of times. THIS IS WHAT NORMALIZATION and proper design is all about. Do something ONCE and use it for a thousand different things. You can new tests simply by adding rows to tables. NOTHING ELSE has to change. This is very similar to my table maintenance mini-app in concept except the data is more complex and the result is going to be the color you want to make a particular control.

The more types of things you can define with a single table, the less work your app requires over all.

 

[Pat Hartman]

@DakotaRidge I don't want to divert this thread to COVID but on the news just now was the results of recent studies that are blaming the proliferation of new COVID boosters on the mutation of COVID and the fact that the more boosters you get, the more variants of COVID you are sickened by and probably the more likely you are to die from one of them. Eventually, if you are sickened by enough COVID mutations, one of them is going to get you. So, yes, if you are compulsively getting every new booster that comes down the pike, you should be very worried about dying from COVID. But hiding away in your house isn't going to save you unless you never open your door to anyone.

 

[DakotaRidge]

FYI, you do not have a relationship diagram, you have an image of default query joins.
There is no referential integrity enforced on any of those relationships. So what's the point? This diagram only serves to set the default join type and all are inner joins.


View attachment 120921

You are right Pete. There's very little value in a graphic like this. I rarely look at these things. Years ago, I used Visio. But I really didn't use it to the max either.

There's too much to these technologies for me to understand. So I do what I can to build useful applications, knowing that I am just scratching the surface. I thank folks on AWF and those who were on UA for comments. My favorite way to learn is to study screenshots of forms and reports. I try to attend all the Access User Groups meetings that I can. I attended local meetings for about 15 years.

 

[DakotaRidge]

@DakotaRidge I don't want to divert this thread to COVID but on the news just now was the results of recent studies that are blaming the proliferation of new COVID boosters on the mutation of COVID and the fact that the more boosters you get, the more variants of COVID you are sickened by and probably the more likely you are to die from one of them. Eventually, if you are sickened by enough COVID mutations, one of them is going to get you. So, yes, if you are compulsively getting every new booster that comes down the pike, you should be very worried about dying from COVID. But hiding away in your house isn't going to save you unless you never open your door to anyone.

Thanks, Pat. I just talked to my brother in Florida about all the stuff that is happening in the world. He mentioned multiple shootings, multiple hurricanes, widespread flooding and fires. Being a volcanic geologist, I mentioned the likely eruption in Alaska.

As you know, I track cases of Covid and food recalls, it is never ending.

When you read those reports, you have to consider who is saying things. There's always two sides. I put new locks on my front door last month to keep me inside more. They are working

Stay Safe!