DLookup for each Field in Table (1 Viewer)

[Futures_Bright]

Hi all,

I originally used a 'multi-valued combobox' to store applicability but ran into a headache or two (!) trying to get it to work.

As a result I'm now using a table with a 1-1 relationship to record yes/no values for each type of company/particular time each record in the parent table will apply. The relationship is between [Applicability].[RelClauseID] and [QMS Clause].[Clause ID] where the latter is a primary key.

Hopefully that gives enough of a background to the new setup, now what I'm looking to do is make the results far more easy to read; the way I intend on doing this is writing all values for that record which are 'True' in an unbound Text box. Cycling through the fields in a table is where I am coming a bit stuck - I believe I want some code along the lines of below:

Dim strFieldName as String, strAppResult as String

For each [COLOR="Red"]field[/COLOR] in [Applicability]
    If dlookup("" & strFieldName & "", "[Applicability]", "[Applicability].[Related Clause] = Me.[Clause ID]") = True then
        [COLOR="red"]'append to strAppResult[/COLOR]: ", " & strFieldName & ""
    Else
    End If
next [COLOR="red"]field[/COLOR]

Any suggestions how best to do this? There are two fields in the table which aren't Yes/No (Number and Autonumber) - will these be read as True, False or Null? I plan on putting a counter for False values in - if none (or 2 if the other two fields are regarded as 'False') then the textbox will just read "All".

Thanks in advance for your help.


Kind regards,

Michael

 

[Futures_Bright]

Can Noone help?

Current code is below. As expected, I've not managed to call up the fields correctly.

Function AppResult(intRelClaID As Integer) As String

Dim strAppResult As String, strFieldName As String
Dim intResultCount As Integer, intFalseCount As Integer

strAppResult = ""
strFieldName = ""
intResultCount = 0
intFalseCount = 0

For Each [Applicability].Field In [Applicability]
    strFieldName = Field
    If DLookup("" & strFieldName & "", "[Applicability]", "[Applicability].[Related Clause] = " & intRelClaID & "") = True Then
        If intResultCount = 0 Then
            strAppResult = strFieldName
        Else
            strAppResult = strAppResult + ", " & strFieldName & ""
        End If
        intResultCount = intResultCount + 1
    ElseIf DLookup("" & strFieldName & "", "[Applicability]", "[Applicability].[Related Clause] = " & intRelClaID & "") = False Then
        intFalseCount = intFalseCount + 1
    Else
        'if value is null?
    End If
Next Field

If intFalseCount = 0 Then
    strAppResult = "All"
Else
End If

End Function

I could call up the fields manually (which gets rid of the worry of the two exceptions too) however this will make the code really long and not very easy should someone wish to add another category to this table!

 

[jdraw]

I suggest you step back and tell us in plain English what you are trying to do.
Forget the Access and vba code for the moment.

I have..., I need to get....

 

[Pat Hartman]

The table that holds the "lookup" values should be 1-many with the main table and should contain a ROW for each possibility rather than a COLUMN. Once you define this table correctly - your method is incorrect, you will be able to eliminate all the VBA. Replace the listbox that wasused by the multi-value version with a subform. You can hide the border and format the subform so it blends in with the form and looks like the listbox it replaced.

 

[Futures_Bright]

Hi Pat,

Thanks for your suggestion, however I would rather try and get this method working if I can because I believe it will make the Form easier to read for the User. The majority of the records are 'apply to all' and so (if I understand your suggestion correctly) there would be around 14 sub-records for each parent. I also expect this would make querying slightly more difficult?

I believe I can make the code work by listing the controls and then calling up a function each time - but I presumed there would be a more efficient 'for each field in this table' like command? The only problem is this makes it less future-proof (if records in the table are added or especially if removed!)

@jdraw: Put in it's simplest form "I have data as described above, I need to present it more cleanly for the user". My planned method is for the parent form to have a textbox listing the name of all fields where the tickbox is true - similar to the multi-value combobox I used previously (attached - see bottom line). For data entry I have put a button to open a new form in a pop-up window with the related record and all necessary tick boxes.

 

[Futures_Bright]

I might be onto a solution now thanks to this thread.

I've used the below code in a blank form to find the correct table ('z') and can cycle through y values 2 onwards (skipping the fields which aren't Yes/No).
one

    While Err.Number = 0
        On Error Resume Next
        Debug.Print DBEngine(0)(0)(z).Name
        While Err.Number = 0
            On Error Resume Next
            Debug.Print z & ". " & y & " - " & DBEngine(0)(0)(z)(y).Name
            y = y + 1
        Wend
        If y > 1 Then
            Err.Number = 0
            y = 0
            z = z + 1
        End If
    Wend

It looks like it numbers the tables in alphabetical order though - so if another table were to be added/removed before this then simply setting "z = 8" will point to the wrong table. I tried putting this code inside a "Do Until DBEngine(0)(0)(z).Name = "Applicability... Loop" but this resulted in a crash (needing me to press ctrl+Break) and I'm not sure why

Help?

 

[Pat Hartman]

There is a one-way street in my neighborhood that would be easier for me if it went both ways. The traffic laws prevent me from going the wrong way down it. Unfortuantely, there is no law to prevent you from making a bad decision on your table design.

Normalizing the data will make it easier to query rather than harder. Also, think for a moment what adding a 15th choice would mean to your queries, etc. With a normalized table, it would not impact a single thing.

 

[Futures_Bright]

I'm sorry Pat, I wasn't intending to be rude - I just couldn't work out how that setup would benefit this database design (and I do respect that you will have designed a hell of a lot more databases than I have!). I really appreciate that you have taken the time to try and help me.

Probably upwards of 75% of all records that will go into the table will 'apply to all' - meaning (as I understand the method you suggest) every time a record goes into the parent table, there will need to be another 14 or so records added to the applicability table where the user selects each company/situation said record applies to - rather than what I currently have which is a single related record for each record in the parent table which defaults to all 'True' (a toggle between select/deselect all is on my to-do list). I thought this method would keep the data entry time down to a minimum, if the method you suggest can be done just as quickly then of course I am open to suggestions.

I also thought that limiting this table to a 1-1 relationship would limit the number of human errors possible.

As for your point on querying - you are correct and I didn't think that situation through thoroughly; I expect the addition of fields to this table to be infrequent however every now and again a company manages to make another slightly different category (in fact another document I reviewed yesterday has just added another which is only subtly different to one that already exists! I'm still deciding if it warrants a separate category)

As it stands, I am of the opinion that the way I have set up the tables satisfies the criteria for this database. This thread was to try and find a cleaner method of representing which values are True on some of the more regularly used forms/reports. If what I want to achieve can be done better by another method I am of course open to suggestions and willing to give it a go.

 

[Pat Hartman]

I didn't take your response as being rude. It does seem excessive to add 14 rows each time but believe me, you will be thankful in the future. And it sounds like with the "new" report, you even understand what I ment. Each record needs a uniqueID (autonumber is fine), a FK to the "parent" record, and a field that identifies which of the 14 options this is. Presence/absence of a row will indicate whether it is being used or not. You may decide for convenience to add a check box but you really don't need it.

Create a small subform that is large enough to hold the 14/15 options. It just needs a combo that lets them choose the type code.

 

[Futures_Bright]

For now I am going to stick with the table design I've currently got - purely because (for this particular field) I expect that the time saved in data entry will far outweigh the time spent editing a table and adding/removing the field to/from one form. However I will keep it in mind should that prove to be wrong! (Really depends how many queries will need this field)

As for the code, it looks like I have it working (provided a related record exists - ensuring this is next for me) so I will post it below for anyone else who wishes to use it (or of course if anyone wants to tell me ways of improving the efficiency will be welcome! Perhaps a Select Case instead of the nested ifs for skipping a field would be beneficial)

Global Variables:

Public strTableName As String
Public intTableNumber As Integer
Public strFieldName As String        ' This may become two local variables instead!
Public intFieldCount As Integer

In a self contained module:

Option Compare Database
Option Explicit

Public Sub FindAppTable()

Dim z As Integer


While Err.Number = 0
    On Error Resume Next
    strTableName = DBEngine(0)(0)(z).Name
    If strTableName = "Applicability" Then
        GoTo FoundTable
    Else
    End If
        z = z + 1
        
Wend

FoundTable:
intTableNumber = z
End Sub

Public Sub FieldCount()

Dim y As Integer

y = 0
strFieldName = ""

While Err.Number = 0
    On Error Resume Next
    If strFieldName = DBEngine(0)(0)(intTableNumber)(y).Name Then
        GoTo EndReached
    Else
        strFieldName = DBEngine(0)(0)(intTableNumber)(y).Name
        y = y + 1
    End If
Wend

EndReached:
intFieldCount = y - 1
strFieldName = ""

End Sub

In Form_Load:

Call FindAppTable

Function within Form (probably will be moved to the same module above):

Function AppResult(intRelClaID As Integer) As String

Dim y As Long
Dim intResultCount As Integer, intFalseCount As Integer
Dim booAppTrue As Boolean


AppResult = ""
y = 0

intResultCount = 0
intFalseCount = 0

Call FieldCount



On Error Resume Next
'Check field is not ID or Related Clause
For y = 0 To intFieldCount
    On Error Resume Next
    strFieldName = DBEngine(0)(0)(intTableNumber)(y).Name
'    Debug.Print z & ": " & y; " - "; strFieldName
    
    If strFieldName = "Type ID" Then
        'skip field
    Else
        If strFieldName = "AppRelClause" Then
           'skip field
        Else
                booAppTrue = DLookup("[" & strFieldName & "]", "Applicability", "[AppRelClause] = " & intRelClaID & "")
                If booAppTrue = True Then
                    If intResultCount = 0 Then
                        AppResult = strFieldName
                    Else
                        AppResult = AppResult + ", " & strFieldName & ""
                    End If
                    Debug.Print AppResult        'So I can see what was listed in case of "All"
                    intResultCount = intResultCount + 1
                Else
                    intFalseCount = intFalseCount + 1
                End If

                
        End If
    End If
Next y


On Error GoTo errAppResult
If intFalseCount = 0 Then
    AppResult = "All"
Else
End If



exitSub:
Exit Function

errAppResult:
'        Error handling here!
        Resume Next

End Function

Finally, Form_Current:

Me.AppDisplay = AppResult(Me.Clause_ID)

This should (in my head at least) work with Pat's suggested setup too with a change to the DLookup. Thanks all for the help.