Sorry folks, Access World will not accept my posts

[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!

 

[BlueSpruce]

I will just point out that we recommend not MVFs but Combo Boxes based on a separate lookup table...

So for the sake of helping @DakotaRidge, I created a prototype app that uses a multiselect list box for storing employee certifications in an MVF.

***I tried attaching the accdb to this post, and it attached, or didn't, I dunno. Guess I need 100 posts to do that?***

Can someone show @DakotaRidge and me how you can use a multiselect list box to store selected values in a normal child table, one record per value?

I imagine a vba public function that accepts arguments can be reused to accomplish the above?

 

[DakotaRidge]

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

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

Similarly, a person may not want a spouse to know that he has kidney disease. He could give his spouse a copy of the database after deleting certain records about his kidney problem. Or he may want to delete alcohol purchases from the system to hide that he is back on the wagon.

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!

 

[DakotaRidge]

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

Similarly, a person may not want a spouse to know that he has kidney disease. He could give his spouse a copy of the database after deleting certain records about his kidney problem. Or he may want to delete alcohol purchases from the system to hide that he is back on the wagon.

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

Similarly, a person may not want a spouse to know that he has kidney disease. He could give his spouse a copy of the database after deleting certain records about his kidney problem. Or he may want to delete alcohol purchases from the system to hide that he is back on the wagon.

Yes, Mark. The last time that I worked on the manual for my proposal management system, the manual was more than 400 pages. That was several years ago. For each form, there was a screenshot and a page of text. For my Finance and Health manual, no one will print it out in hardcopy. I don't have to worry about that.

 

[MajP]

Can someone show @DakotaRidge and me how you can use a multiselect list box to store selected values in a normal child table, one record per value?

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

You can do this, but no matter how much I tried I could not think of a way to make this completely generic. So this will always require the user to do some coding. You will see that area in the example where you would have to write the insert and delete query.

 

[MajP]

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

post 45 shows the solution now to delete selected records.

 

[BlueSpruce]

You can do this, but no matter how much I tried I could not think of a way to make this completely generic. So this will always require the user to do some coding. You will see that area in the example where you would have to write the insert and delete query.

Yes, I was hoping somehow a generic func or sub could be built and reused that takes parameters, such as Me.Control and other objects, and construct dynamic queries. Then @DakotaRidge can put that module in all his apps and call it wherever he uses lst's. Would also have to figure out for reports, how to fake displaying concat values on the same line separated with delimiters.

 

[DakotaRidge]

So for the sake of helping @DakotaRidge, I created a prototype app that uses a multiselect list box for storing employee certifications in an MVF.

***I tried attaching the accdb to this post, and it attached, or didn't, I dunno. Guess I need 100 posts to do that?***

Can someone show @DakotaRidge and me how you can use a multiselect list box to store selected values in a normal child table, one record per value?

I imagine a vba public function that accepts arguments can be reused to accomplish the above?

View attachment 120720View attachment 120721View attachment 120722

BlueSpruce, I would ike to see both a form and a report for the same recordset. Here's a pair that I have in my Finance and Health database for pet care activities. When I opened the lookup form, I noticed that it has 126 items. That indicates that an MVF can have more than 100 items, which is what I have read in the literature. I have no idea now what the maximum number of attributes is if you use lookup tables in MVFs the way that I do.

The form.

The report.

 

[BlueSpruce]

That indicates that an MVF can have more than 100 items, which is what I have read in the literature. I have no idea now what the maximum number of attributes is if you use lookup tables in MVFs the way that I do.

There's no artificial limit for number of values stored in tables. The only limit you have to worry about is the 2GB accdb size limit. However, I wouldn't use a multi select list box for more than a dozen or so values. Instead, I would use a combo box so as I start entering characters into the cbo, the list in the dropdown filters down to the desired values. I thrive on providing my users a streamlined experience to minimize the number of keystrokes they have to enter, find the desired info in the shortest amount of time, and provide them the best visual displays.

 

[Gasman]

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

You can do this, but no matter how much I tried I could not think of a way to make this completely generic. So this will always require the user to do some coding. You will see that area in the example where you would have to write the insert and delete query.

Could you not have a table that defines what fields are used and what to be set with?
Then just read for a particular table and command and build the strings?

tblSQL
SqlID
SqlTable
SqlCmd
SqlField
SqlSetTo

 

[Gasman]

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

And I think that is complete rubbish.
Willing to be proved wrong though.?
@Jon

 

[Jon]

And I think that is complete rubbish.
Willing to be proved wrong though.?
@Jon

You are correct @Gasman, there is no such system.

In many cases, the user has quoted a previous reply in their response and that quoted section has a link in it. This often flies under the radar of new users here because they didn't type out the link themselves, but they still included a link because it was in the quoted section.

 

[Jon]

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?

No facilities for this in the software.

 

[BlueSpruce]

You are correct @Gasman, there is no such system.

In many cases, the user has quoted a previous reply in their response and that quoted section has a link in it. This often flies under the radar of new users here because they didn't type out the link themselves, but they still included a link because it was in the quoted section.

Ahaaa!... Heads up, @DakotaRidge

 

[Gasman]

So for the sake of helping @DakotaRidge, I created a prototype app that uses a multiselect list box for storing employee certifications in an MVF.

***I tried attaching the accdb to this post, and it attached, or didn't, I dunno. Guess I need 100 posts to do that?***

Can someone show @DakotaRidge and me how you can use a multiselect list box to store selected values in a normal child table, one record per value?

I imagine a vba public function that accepts arguments can be reused to accomplish the above?

View attachment 120720View attachment 120721View attachment 120722

***I tried attaching the accdb to this post, and it attached, or didn't, I dunno. Guess I need 100 posts to do that?***

DB is attached?

Very neat. How is it done?