Return field name into array where field is true

[The_Doc_Man]

Leyton01: I am going to chime in on this one with a thought or two. You are seemingly afraid of / are daunted by the idea using VBA for this, and perhaps I get that. However, there are some trivial ways to do this if you aren't afraid of getting your feet wet. But before we can give advice on the best mechanism, I didn't see this in skimming through the discussion, so either I missed it (not a surprise with these old eyes) or nobody thought to ask it.

You didn't discuss whether you needed to keep a "score" of the issues that passed as well as the issues that failed. That will guide the optimum table structure. If you are ONLY interested in failed issues, a "sparse" table will be useful because then you can drive it from the checkbox "click" event. If you have to track "passing" issues - OR if you have to come back and later "uncheck" one of those issues - then a "sparse" design won't help.

By "sparse," I specifically mean a design for which you don't store anything you don't have to. IF you are not interested in either going back to uncheck something OR give an overall compliance score, then you only need to make a list of the failed issues. That list of failed issues is SHORTER (smaller) than the list of ALL issues, which is why "sparse" data lists are often preferable.

Can you clarify that aspect of your requirements for us?

 

[Deleted member 147267]

Yes you did.

No I did not. I said "If" meaning it was hypothetical - better read it again. But someone in the other thread did say as much.

You have rejected the logical, efficient interface requested by the customer,

Again, no. Strictly hypothetical from the standpoint that for example IF someone wanted you to create tables like spreadsheets (flat files) would you do it? I would have pm'd you on this rather than risk hijacking this thread, but I feel that some of the statements I made obviously require clarification. I won't belabor this further if you intend to continue.

 

[MajP]

It is a dilemma. If you are not skilled in vba and Access it is often easier to make a non normalized db so that you can get a useable UI. The problem with that is this may make getting the data loaded easily, but doing anything with it becomes very hard. I believe you are better off properly designing the db and then asking for help on the UI.
So I believe I concur with Glaxiom on this issue. Even though there may not be a native Access control to facilitate user input you are better off making a normalized DB and then trying to figure out a user interface. Unfortunately Access is extremely primitive in what it offers for UI. IMO it has barely been upgraded since 1995 so you will have to use code to do non standard things. Modern applications have datagrids, treeviews, listviews, and other controls that facilitate data entry.
Anyways here is a home grown version that allows a checklist as you describe. The concept is pretty standard and it is the same as you would do if you use a multi select listbox as a control. You can definitely build your desired UI and still have a normalized table structure.
However, I concur with Micron. If you are not willing to dip into vb then leverage the native controls and be limited to what they provide. I often see novices coming up with what I call Rude Goldberg solutions. They are so convoluted that they are far better off using the KISS principle.
But if you are willing to dip your toes into VBA we can help build the UI you want with a normalized data structure.

 

[Leyton01]

The_Doc_Man: not afraid of VBA, I was just basing my concerns on the idea that I would not be able to use continuous docs (to display 'virtual' records) and instead would have to use a flat form with each check box requiring an event (that is a lot of mucking around). I was also concerned about the overhead of looping through all the controls on the page to set the correct checkbox state every time the user changed the record. (I can see this can be achieved in other ways now though).

To answer your other question - I just need to score the fails - a pass is assumed. A sparse table design was the ultimate aim. In the other thread I had elaborated on the proposed DB design and it had an "issues" table which only stored a single issue related to that particular document, if it existed.

MajP: This example is fantastic. It shows that it can be done using minimal vba and is elegant in its execution.

However (and this is a big however) the continuous doc is constrained to one specific row height and cannot be dynamically resized based on the textbox within. There is great variability in the length of the description of the problem with some very long (Issue 11 in the example) or very short (Issue 2). If all problems had to be at least 3 lines long it would result in significant amounts of white space. The power and ease of using a continuous doc reduces its customisability.

I may have to bite the bullet and do a hybrid model where I use a flat form and set an event for each checkbox to run the appropriate SQL. It pains me to do such a thing, and not just because of the work but it is neither elegant nor efficient.


I did solve the original question on this thread (how to get the column names of true values) late on Friday and will post the result when I am back at my machine. Nice to be able to solve it, even if I don't end up using it.

 

[isladogs]

Leyton
Rather than use a 'hybrid' form (as you call it) and to help keep the design simple & efficient (as in MajP's example), consider using a zoom box type feature so that users could click on a textbox to see all the text

For example, see the first two screenshots in this post:
https://www.access-programmers.co.uk/forums/showpost.php?p=1613073&postcount=2

 

[MajP]

However (and this is a big however) the continuous doc is constrained to one specific row height and cannot be dynamically resized based on the textbox within. There is great variability in the length of the description of the problem with some very long (Issue 11 in the example) or very short (Issue 2). If all problems had to be at least 3 lines long it would result in significant amounts of white space. The power and ease of using a continuous doc reduces its customisability.

Yes, it may reduce the customisabilty but drastically increases the flexibility. If you want to be easy and customize-able you could simply do an unbound form. You will be able to format it any way you want. The issue will be that every time you change or add an issue you have to recode the form. If the issues never change or very infrequently then this may not be an problem. No different then the format you proposed originally, you have to redesign for any question change.

One simple option would be to make it like a wizard where each issue is on a single form view. You then scroll through the issues selecting them. You would be able to format as needed.

 

[Leyton01]

Just for completeness here is the code I used to get the name of the "true" fields. I set the array to be the maximum number of fields, then include only the true field names and redim it to that number.

This was just hacked together late on a Friday and I need to clean it up especially around the integers (I skip the first 2 fields as these are ID fields). This is not the final product but I probably won't improve it if going in the other direction.

Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim varFields() As Variant
Dim i As Integer
Dim j As Integer

Set mydb = CurrentDb
strSQL = "SELECT * FROM " & strTableName & " WHERE DocID = " & DocID
Set rs = mydb.OpenRecordset(strSQL, dbOpenForwardOnly)

ReDim varFields(rs.Fields.Count - 1)
j = 0
If rs.RecordCount = 1 Then
  For i = 2 To (rs.Fields.Count - 1)
    If rs.Fields.Item(i).value Then
        varFields(j) = rs.Fields.Item(i).Name
        j = j + 1
    End If
  Next
  If j = 0 Then
    ReDim varFields(0)
    varFields(0) = "No Issues"
    GoTo Cleanup
  Else
    ReDim Preserve varFields(j - 1)
  End If
Else
    ReDim varFields(0)
    varFields(0) = "Does not Exist"
    GoTo Cleanup
End If

Cleanup:
rs.Close
Set rs = Nothing
Set mydb = Nothing

 

[MajP]

I do not get it. So you fill an array with values from a recordset. What is the point and what will you do with the array? Seems like a waste of time. In VBA you cannot bind an array to anything. If you want to display this in a different format, then should be using an ADO in memory (disconnected) recordset IMO.

 

[Leyton01]

This was for the original problem where the database had been set up so that the table had all the issues listed for each document as Y/N columns. This array could be used to convert a list of individual document issues to a textbox, email or document that has the description of those issues.


eg the table (old design):

DocID | Issue100 | Issue101 | Issue102 | Issue103 | 
5677  |    []    |    [X]   |    [X]   |    []    |

Would return an array of (Issue101, Issue102) - this can be used to create an email based off the Issue List table with a full description -
Dear XXXX
Your document has the following issues:
{Issue101 long description}
{Issue102 long description}
You can correct the document by:
{Issue101 remedy description}
{Issue102 remedy description}
Thanks!

This is the way the database had already been set up but I will be changing the design to an improved structure.

I placed the answer to the question here in case someone was looking for a solution for a similar problem. It is not refined, it is not good design but answers the original question.

 

[MajP]

I see. Thanks.

 

[MajP]

I am not of fan of unbound forms if it can be done, bound. Understand that it will not be very flexible, but will be very customize-able. Implementing your design unbounded would be very easy. It is very similar to the other form I provided in logic. You read from the normalized table and push into your controls the previous selections. Then write to your normalized table. The entire code is

Option Compare Database
Option Explicit

Private Sub Form_Current()
  LoadChecks
End Sub
Public Sub LoadChecks()
  Dim strSql As String
  Dim rs As DAO.Recordset
  Dim I As Integer
  Dim ctrl As Access.Control
  'Add the users selections
  If Me.NewRecord Then
    'If new record set them all to false
    For Each ctrl In Me.Controls
      If ctrl.ControlType = acCheckBox Then ctrl.Value = False
    Next ctrl
    Exit Sub
  End If
  
  strSql = "Select * from tblUserSelections where UserID_FK = " & Me.UserID
  Set rs = CurrentDb.OpenRecordset(strSql)
  'Put the selection ID in the checkbox tag
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acCheckBox Then
      I = CInt(ctrl.Tag)
      rs.FindFirst "selectionID_FK = " & I
      If Not rs.NoMatch Then
        Me.Controls("chk" & I).Value = True
      Else
        Me.Controls("chk" & I).Value = False
      End If
    End If
  Next ctrl
  Me.Refresh
End Sub

Private Function CheckUncheck()
  If Me.ActiveControl Then
    AddSelection
  Else
    RemoveSelection
  End If
End Function

Public Sub AddSelection()
   Dim strSql As String
   Dim UserID As Long
   Dim SelectionID As Long
   UserID = Me.UserID
   SelectionID = Me.ActiveControl.Tag
   strSql = "Insert into tblUserSelections (UserID_FK, SelectionID_FK) VALUES (" & UserID & ", " & SelectionID & ")"
   CurrentDb.Execute strSql
End Sub
Public Sub RemoveSelection()
   Dim strSql As String
   Dim UserID As Long
   Dim SelectionID As Long
   UserID = Me.UserID
   SelectionID = Me.ActiveControl.Tag
   strSql = "Delete * from tblUserSelections WHERE USERID_FK = " & UserID & " AND SelectionID_FK = " & SelectionID
   CurrentDb.Execute strSql
End Sub

You could use the form that you posted previously, just make it unbound and update the tag and name properties. Here is my example.

This concept would be useable if you have a relatively small amount of items and these items do not change or change very infrequently. Each change requires form redesign.

 

[Leyton01]

Thanks again MajP

I was going through your database example again today and liked the way it used a "virtual table" and mirrored the form to the table. My only concern with this was that when I did have to make changes (which are infrequent as you have guessed), I have to make changes to the table plus the form to make sure they match. This would add an extra table for each of the document types (even if it is just table which is used temporarily). I have coded it today without binding just to make sure the proof of concept works.

I agree that unbound is not the best - already when the form was first created the checkboxes show the "no state" filled-in box style instead of being empty. I have to set a default value to get around this. I can't set a value of an unbound checkbox.

What I have coded today and it appears to work (this also highlights the labels of checked boxes in blue):

Private Sub Form_Current()
Dim strSql As String
Dim ctrlname As String
Dim rs As DAO.Recordset
Dim ctrl As Control

For Each ctrl In Me.Controls
    If  ctrl.ControlType = acCheckBox Then
        ctrl.DefaultValue = False
        ctrl.Controls(0).ForeColor = 0
    End If
Next ctrl

strSql = "SELECT * from tblDocumentIssues WHERE DocID = " & Me.DocID
Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    ctrlname = rs!IssueID & "chk"
    Me.Controls(ctrlname).DefaultValue = True
    Me.Controls(ctrlname).Controls(0).ForeColor = vbBlue
    rs.MoveNext
  Loop

End Sub


Public Sub AddSelection()
Dim strSql As String
Dim strIssueID As String

'Remove the "chk" from the name
strIssueID = Left(Me.ActiveControl.Name, 11)

strSql = "INSERT into tblDocumentIssues (DocID, IssueID) VALUES (" & Me.DocID & ", '" & strIssueID & "')"
CurrentDb.Execute strSql

Me.ActiveControl.Controls(0).ForeColor = vbBlue

End Sub

Public Sub RemoveSelection()
Dim strSql As String
Dim strIssueID As String

'Remove the "chk" from the name
strIssueID = Left(Me.ActiveControl.Name, 11)

strSql = "Delete * from tblDocumentIssues WHERE DocID = " & Me.DocID & " AND IssueID = '" & strIssueID & "'"
CurrentDb.Execute strSql

Me.ActiveControl.Controls(0).ForeColor = 0
   
End Sub

Private Sub Ctl102_101_101chk_Click()
If Me.ActiveControl.value Then AddSelection Else RemoveSelection
End Sub

 

[Leyton01]

I did have to ask - I am guessing I should be using

rs.close
set rs = nothing

whenever I open a recordset (such as in the Form_current above)?

 

[MajP]

I was going through your database example again today and liked the way it used a "virtual table" and mirrored the form to the table. My only concern with this was that when I did have to make changes (which are infrequent as you have guessed), I have to make changes to the table plus the form to make sure they match. This would add an extra table for each of the document types (even if it is just table which is used temporarily). I have coded it today without binding just to make sure the proof of concept works

In the first example where I used the subform, the point was that this would not require code, table, or form redesign in order to add or change records. But as you pointed out it is not as customizeable. If document types have a different set of issues then I would add in the issue table a field for document type. My guess is that you may actually need a many to many table if issues are shared across document types. You definitely would not need multiple tables for each document type. Now if it was me and I was going to try to maintain this, I would probably make code to help build the forms. It would add the correct amount of checks, name them correctly, tag them correctly, temporarily place them, and provide the correct labels. Then I just have to clean up the size of the labels, and do some movement for aesthetics .



You would build your different forms using a query but would have a single table. The unbound form design suffers from the above problems. Whatever way you go the main point is that you are better off with a normalized table structure and with some code and trickery you can usually come up with a non normalized presentation that is more user friendly.

As per your other question, I will stir up some controversy, but read this first

https://docs.microsoft.com/en-us/ar...en-are-you-required-to-set-objects-to-nothing


IMO (based on facts) regardless of what you read or heard there is almost no reason to ever set an object variable to nothing. People on this forum and others say all them time that you have to, but if asked they can not explain why or their answers are just wrong. There are a few times when you should, but it is never the case that people site. The referenced post explains those rare times. People will say that it is just good coding practice. If writing unnecessary code without understanding of what it does is good practice then I guess, sure. So the logic is in the .1% chance you will need it, it is better to do it every time just in case. Personally, I prefer to understand the .1%. In all of my coding I know of a handful of cases where I had to set an object variable to nothing and these were all in composite custom classes or automation of other applications.

The author describes it as Cargo Cult Programming

When I see code like this, the first thing I think is cargo cult programmer. Someone was told that the magic invocation that keeps the alligators away is to put a banana in your ear and then set objects to Nothing when you're done with them. They do, and hey, it works! No alligators!

As for closing a recordset, there are more times you should as compared to setting an object to nothing. If working with an external datasource I will close the connection. But closing a local access recordset is also mostly unnecessary. I have left thousands of thousands of recordsets open and have let code execution take care of it without the world ending.

My other guess is that many people are too lazy to learn the rare cases where you would need to close a recordset and set an object to nothing. So the easier solution is to tell everyone you have to do it all the time. If it makes you more comfortable then have at it, just understand about 99.9% of the time it does nothing.

 

[Leyton01]

Ok it is all working as expected with the new database structure - I now have a single table with just the DocID and the associated issue that goes with. I have created a form with full descriptions and this adds and removes records from this table appropriately.

In the spirit of optimising the database can I ask what is the best way to retrieve the issue descriptions using the list of possible issues? I have to retrieve all possible issues related to a DocID (there may be none) and look those up in the tlkpIssueMatrix to retrieve the description.

I could see a number of ways of doing this and went with creating a string which was used for an SQL statement as such (this places the descriptions in a textbox for display, no declarations included):

Me.txtCurrentDocIssues = ""

intIssueCount = DCount("[ID]", "tblDocumentIssues", "[DocID] = " & Me.DocID)

If intIssueCount = 0 Then
    Me.txtCurrentDocIssues = "No issues identified/not audited"
Else
    strSql = "select * from tblDocumentIssues Where DocID = " & Me.DocID
    Set rs = CurrentDb.OpenRecordset(strSql)
    Do While Not rs.EOF
     strIssueList = strIssueList & "'" & rs!IssueID & "',"
     rs.MoveNext
    Loop
    'remove the trailing comma
    strIssueList = Left(strIssueList, Len(strIssueList) - 1)
     rs.Close

    strSql = "select * from tlkpIssueMatrix Where IssueID IN (" & strIssueList & ")"
    Set rs = CurrentDb.OpenRecordset(strSql)
    
    Do While Not rs.EOF
     strCurrentDocIssues = strCurrentDocIssues & rs!DescinSOP & vbCrLf
     rs.MoveNext
    Loop
    Me.txtCurrentDocIssues = strCurrentDocIssues
End If

 

[MajP]

Cant you simply make a query that joins tblIssueMatrix and tblDocumentIssues by issueID filtered on DocID?

 

[Leyton01]

Yes that would make more sense. I couldn't see the forest for the trees.

 

[Galaxiom]

I was going through your database example again today and liked the way it used a "virtual table" and mirrored the form to the table.

Nothing "virtual" about the table. It uses what is usually referred to as a temporary table and the unbound design requires a lot of code to work. (Although the table is actually permanent, the records in it are temporary.)

My only concern with this was that when I did have to make changes (which are infrequent as you have guessed), I have to make changes to the table plus the form to make sure they match. This would add an extra table for each of the document types (even if it is just table which is used temporarily).

Exactly. For me this aspect would be a deal breaker. Practical design is extensible by adding records, not reconstructing table and forms. You have compromised important core functionality to gain trivial window dressing at the cost of complexity and what will ultimately prove to be expensive development when any modification is required.

It is a pity you didn't look harder at the example I linked. Sure it can't deal with different size issue descriptions because of the continuous forms construction but it has many practical advantages and its operation is incredibly simple.

The different size descriptions could have been dealt with using a separate single form to display the detailed descriptions.

Also note that a subform structure can easily be made to look like everything part of the one form by making the control borders transparent.

 

[MajP]

It is a pity you didn't look harder at the example I linked. Sure it can't deal with different size issue descriptions because of the continuous forms construction but it has many practical advantages and its operation is incredibly simple.

@Glaxiom
To be clear my first demo provided Leyton all of what you describe. It does basically what that link does, simple, efficient and flexible. My second demo was an unbound option, but I provided all the caveats that this will be hard to maintain and only benefits from more customization at the expense of flexibility and maintainability.

Leyton will have to weigh user experience vs ease and flexibility, but I feel I have provided sufficient information for him to weigh this decision. Not sure if I could provide more foot stomping. If I knew I would be around to do the updates, the issues are highly unlikely to change, the requirements would not expand then I would consider the unbound option. If the above was uncertain I would not.