pulling field names from table

[murfeezlaw]

I'm not sure if this is possible but i'm looking to create a query to pull the field names from a table if the values are true.




For example each field is a Y/N field. I'd like to build a query that would pull the field name from the table if the value is true, in ascending order. So the query would pull


CG0001_1207
CG0033_1207

ID	CA0188_0399	CG0001_1204	CG0001_1207	CG0033_1204	CG0033_1207
21284	FALSE	FALSE	TRUE	FALSE	TRUE

[pbaldy]

It does not look like the table is normalized. You will have no end of difficulty this way, including this query. What do the fields represent? They should almost certainly be records in a related table rather than fields.

[ssanfu]

You might try creating a crosstab query, then use the crosstab as the source for another query to select only the True values.

---------
Having said that, it looks like your table structure is incorrect - it is not in 3NF.
I would set it up like this:

ID	Item	Available
21284	CA0188_0399	False
21285	CG0001_1204	False
21286	CG0001_1207	True
21287	CG0033_1204	False
21288	CG0033_1207	True

Then it is a simple matter to create a query to return the Items that are available (True).

[murfeezlaw]

The field names are names of forms. The database is pretty old and i was hoping not to change too much of the design to do this. I need to print the field names and the matching form description. The code below does this with no problem but I need to sort the field names in ascending order before I print them on the Word Document. I thought I could pull the field names from the table and then sort them. Then print them.

I'm all ears for a better solution

For Each ctrl In Me![sfmStateForms].Form.Controls
        varCheckBoxTagProp = ctrl.Tag
            If TypeOf ctrl Is CheckBox And varCheckBoxTagProp = "Mandatory" Then
                If ctrl.Value = True Then
                                    
                    Set dbs = CurrentDb
                                            
                    varControlName = ctrl.Name
                                            
                    strSQL = "SELECT FormDescription, FormName, ControlName FROM tblFormDescriptions WHERE ControlName = '" & varControlName & "'"
                                            
                    Set rst = dbs.OpenRecordset(strSQL)
        
                    varFormDescription = rst!FormDescription
                    varFormName = rst!FormName
                    
                    objWord.Selection.TypeText "  " & varFormName & vbTab & Left(varFormDescription, 79) & vbCr
                    
                End If
            End If
        Next

[murfeezlaw]

i could also write the values to a temporary or not temporary table before printing them. my VBA isn't that strong tho. i'm not sure how to create a table object and write to it w VBA

[murfeezlaw]

You might try creating a crosstab query, then use the crosstab as the source for another query to select only the True values.

---------
Having said that, it looks like your table structure is incorrect - it is not in 3NF.
I would set it up like this:

ID	Item	Available
21284	CA0188_0399	False
21285	CG0001_1204	False
21286	CG0001_1207	True
21287	CG0033_1204	False
21288	CG0033_1207	True

Then it is a simple matter to create a query to return the Items that are available (True).

i see where you're going w that. I thought about that too. That would require some pretty big changes to the system that i'm not prepared to make right now.

[ssanfu]

i could also write the values to a temporary or not temporary table before printing them. my VBA isn't that strong tho. i'm not sure how to create a table object and write to it w VBA

I modified your code snippet to write to a temp table first. THIS IS UNTESTED CODE!! Try this on a COPY of your database.

Start by creating a table named "TmpFormDesc" with two text fields - "FormName" & "FormDesc"
Then look at the following code.

The gold text is basic declarations. You should already have these
The blue is what I added.

Option Compare Database  'should always have these two lines
Option Explicit


Public Sub Form2Word()
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset

   Dim strSQL As String
   Dim varCheckBoxTagProp As String   'These I added to create the sub - you should already have them
   Dim varControlName As String
   Dim varFormName As String
   Dim varFormDescription As String

'--------beg modified code---------
   Set dbs = CurrentDb    '<<- only needs to be set once

   'clear the temp table
   strSQL = "Delete * FROM TmpFormDesc;"
   dbs.Execute strSQL, dbFailOnError

   'fill the tmp table with form names and descriptions
   For Each Ctrl In Me![sfmStateForms].Form.Controls
      varCheckBoxTagProp = Ctrl.Tag
      If TypeOf Ctrl Is CheckBox And varCheckBoxTagProp = "Mandatory" Then
         If Ctrl.Value = True Then

            varControlName = Ctrl.Name

            strSQL = "SELECT FormDescription, FormName, ControlName "
            strSQL = strSQL & " FROM tblFormDescriptions"
            strSQL = strSQL & " WHERE ControlName = '" & varControlName & "';"
            Set rst = dbs.OpenRecordset(strSQL)

            varFormDescription = rst!FormDescription
            varFormName = rst!FormName

           'insert into the tmp table
            strSQL = "INSERT INTO Account ( FormName, FormDesc ) "
            strSQL = strSQL & " VALUES ('" & varFormName & "', '" & varFormDescription & "');"
            dbs.Execute strSQL, dbFailOnError

         End If
      End If
   Next
   rst.Close

   'open a sorted recordset
   strSQL = "SELECT FormName, FormDesc"
   strSQL = strSQL & " FROM TmpFormDesc"
   strSQL = strSQL & " ORDER BY FormName"
   Set rst = dbs.OpenRecordset(strSQL)

   'check if there are records returned.
   If Not (rst.BOF And rst.EOF) Then
      rst.MoveLast
      rst.MoveFirst
      Do While Not rst.EOF
         'write to Word
         objWord.Selection.TypeText "  " & rst!FormName & vbTab & Left(rst!FormDesc, 79) & vbCr
         rst.MoveNext
      Loop
   End If

   rst.Close
   Set rst = Nothing
   Set dbs = Nothing

'--------end modified code---------

End Sub

Remember - this is untested