Whats Bad about Lookup Fields???

music_al

Registered User.
Local time
Today, 13:12
Joined
Nov 23, 2007
Messages
200
and what is the preferred way to do it ??

Most access books I have read seem to guide people toward Lookup fields. So what is all the fuss about ?


Al
 
Ahhhh - I get it

Im converted...

Im gonna spend all weekend now putting right all the dreadful Lookup Field errors Ive made...

I wondered why my query criterias never worked properly
 
Do take note that lookup tables (often called Domain Tables) are not evil! ...

The subtlety that is easy to overlook for folks learning about databases is that defining your lookup definition at that Table level is not the way to go, BUT ... use of lookup tables (domain tables) is definately a good thing!

I will often set the Default Control to a 'Combo Box' on fields I INTEND to use a lookup with ... then on the form where I use the field, I define all the combo settings there, on the combo control placed on the form.

EDITS ADDED >>>

Sorry ... some dialog while composing and prior to submit ... so ... sorry for telling you to get it when you already got it! ... :)
 
brent, is there any reason, then, to remove the default control setting in the table or can it stay there?
 
what i dislike is this

if i look at a table, it is only to see EXACTLY what data is in there - so if a field is designed as a long, and actually represents a foreign key to another table, I ACTUALLY want to see the stored number, not the looked up text value. I can make the link easy enough in a query - and I dont necessarily ALWAYS want to see the same text column from the lookup table anyway.

Now lets say the lookup table entry got deleted by accident - the lookup field now shows a blank, even though its actually storing something

lets just see the stored data - and we can manage it ourselves.
 
Ahhhh - I get it

Im converted...

Im gonna spend all weekend now putting right all the dreadful Lookup Field errors Ive made...

I wondered why my query criterias never worked properly

This may help:

Code:
Public Function TurnOffLookup()

    Dim db As DAO.Database

    Dim tdf As DAO.TableDef

    Dim fld As DAO.Field

    Dim prp As DAO.Property

    

    Set db = DBEngine(0)(0)

    For Each tdf In db.TableDefs

        If (tdf.Attributes And dbSystemObject) = 0 Then

            If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not attached, or temp.

                For Each fld In tdf.Fields

                        If HasProperty(fld, "DisplayControl") Then

                            fld.Properties("DisplayControl").Value = 109

                            If HasProperty(fld, "RowSourceType") Then

                                fld.Properties.Delete "RowSourceType"

                            End If

                            If HasProperty(fld, "RowSource") Then

                                fld.Properties.Delete "RowSource"

                            End If

                            If HasProperty(fld, "BoundColumn") Then

                                fld.Properties.Delete "BoundColumn"

                            End If

                            If HasProperty(fld, "ColumnCount") Then

                                fld.Properties.Delete "ColumnCount"

                            End If

                            If HasProperty(fld, "ColumnHeads") Then

                                fld.Properties.Delete "ColumnHeads"

                            End If

                            If HasProperty(fld, "ColumnWidths") Then

                                fld.Properties.Delete "ColumnWidths"

                            End If

                            If HasProperty(fld, "ListRows") Then

                                fld.Properties.Delete "ListRows"

                            End If

                            If HasProperty(fld, "ListWidth") Then

                                fld.Properties.Delete "ListWidth"

                            End If

                            If HasProperty(fld, "LimitToList") Then

                                fld.Properties.Delete "LimitToList"

                            End If

                        End If

                Next

            End If

        End If

    Next

    

    Set prp = Nothing

    Set fld = Nothing

    Set tdf = Nothing

    Set db = Nothing

End Function



Public Function HasProperty(obj As Object, strPropName As String) As Boolean

    'Purpose:   Return true if the object has the property.

    Dim varDummy As Variant

    

    On Error Resume Next

    varDummy = obj.Properties(strPropName)

    HasProperty = (Err.Number = 0)

End Function
 
Thanks everyone.

Just one other question. If I have a Lookup table...

tbl_Colours
Colour_ID (column 1)
Colour_Name (column 2)

When I represent that in a ComboBox on a form, which should be the bound column ?

If column 1 is bound to the database, if I have to import data from a spreadsheet that lists data in the "looked up format", i.e. Red, Blue, Green etc, I would first have to convert the colour string to its corresponding number to get it into the database...

OR

should column 2 be the bound column, therefore actually storing the colour string in the database?

Sorry if these are stupid questions, but its all a bit confusing.

Al
 
Assuming the table you are working with has a foreign key referencing the Colour_ID column of the Colors table, you would bind the first column. This is what almost every profession Access developer would expect the behavior to be.

Yes, when you get the data from the external source, you will have to convert it to the key value.
 

Users who are viewing this thread

Back
Top Bottom