Q: finding a field's lookup string

  • Thread starter Thread starter A C
  • Start date Start date
A

A C

Hi

I have some fields in a table that have lookups on them.

How do I
1. detect which fields these are when looking at the table in VB
2. get the lookup string, and/or
3. get the 2nd field/value in the lookup (I know it will always be the 2nd
value (FK) I am interested in).

I was assuming its a field property but cannot seem to find it...

Thanks
A
 
A said:
I have some fields in a table that have lookups on them.

How do I
1. detect which fields these are when looking at the table in VB
2. get the lookup string, and/or
3. get the 2nd field/value in the lookup (I know it will always be the 2nd
value (FK) I am interested in).

I was assuming its a field property but cannot seem to find it...
Click to expand...


Since it's an Access custom defined property, it's not
available directly through DAO. Here's a sequence that will
tell you what type of display the field uses:

Set db = CurrentDb
With .TableDefs!tablename.Fields
If !fieldname.Properties!DisplayControl = 111 Then
' field uses a combo box display
End If
End With
Set db = Nothing

For a lookup field, you can also get its related properties:
RowSourceType
RowSource
BoundColumn
ColumnCount
ColumnHeads
ColumnWidths
ListRows
ListWidth

If the RowSourceType is "Value List", the RowSource will be
the list of values, which you can parse to find the desired
value.

Note: You should use error handling to guard against the
situation where some or all of these properties do not
exist.
 
Hi Marshall

Thanks for the reply, thats exactly what I was after. To assist others I
have some observations

1. Your code had a small bug, its if fieldname.Properties!DisplayControl =
111 , without the "!" <not>
2. Properties!DisplayControl crashes on some field types, so far I have
found dates and memo fields dont work.
The rough fix I have is simply to put On Error Resume Next on the
previous line, but there is probably a list somewhere on the internet of
what field types (fld.Type) wont work and those could be excluded from
executing the line
3. The list of DisplayConstants I found was:

109 = text box
110 = list box
111 = combo box


Thanks again Marshall

Regards
A
 
1. You're right, there was something wrong. But, it was
missing the db on the With statement, the ! before fieldname
is needed:

With db.TableDefs!tablename.Fields
If !fieldname.Properties!DisplayControl = 111 Then

2. I warned you about using error handling for fields that
don't use a combo box display.

3. I think that's all there are. Those numbers are the
same as the control type VBA constants acTextBox, acListBox
and acComboBox.
--
Marsh
MVP [MS Access]



A said:
Thanks for the reply, thats exactly what I was after. To assist others I
have some observations

1. Your code had a small bug, its if fieldname.Properties!DisplayControl =
111 , without the "!" <not>
2. Properties!DisplayControl crashes on some field types, so far I have
found dates and memo fields dont work.
The rough fix I have is simply to put On Error Resume Next on the
previous line, but there is probably a list somewhere on the internet of
what field types (fld.Type) wont work and those could be excluded from
executing the line
3. The list of DisplayConstants I found was:

109 = text box
110 = list box
111 = combo box


Thanks again Marshall

Regards
A
Click to expand...
 
Back
Top