Hi Art
Philosophically, I'm with Duane – "if it ain't broke, don't fix it". However, in this case you can greatly reduce the amount of code and improve performance (though I suspect that is not an issue).
If I understand correctly, you want the caption of the label set to the value of [IndexKey01Label], which is a field in the same record as the selected row in your combo box, cmbCurrentECabinet.
First, I suggest you add this field as a third (hidden) column in your RowSource, to get rid of the DLookups:
RowSource: SELECT tblCabinets.CabinetID, tblCabinets.CabinetName, tblCabinets.IndexKey01Label FROM tblCabinets ORDER BY tblCabinets.CabinetName;
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0;;0
(Note the two semicolons in ColumnWidths – if you don't specify the width for a column then it gets to use (or share, if there are more than one) all the unallocated width in the combo box. In this case, as the other two columns have zero width, the second column (CabinetName) gets the whole combo box width. This makes it easier to maintain – if, say, you want to change the size of the combo box.)
Now, you can refer to the IndexKey01Labelfor the currently selected cabinet by:
Me.cmbCurrentECabinet.Column(2)
(Note that the Column property for a combo box starts counting at 0, so Column(2) is the THIRD column)
Also, I assume that lblIndexKey01Label is the attached label for cboIndexKey01, in which case it will appear/disappear as you make the combo box visible.invisible.
Now your code can look like this:
Dim strIndexKey01Label As String
strIndexKey01Label = Nz(Me.cmbCurrentECabinet.Column(2), "")
If Len(strIndexKey01Label) =0 Then
Me.cboIndexKey01.Visible = False
Else
Me.lblIndexKey01Label.Caption = strIndexKey01Label
Me.cboIndexKey01.RowSource = "SELECT tblFolders.FolderID, tblFolders.IndexKey01 FROM tblFolders " & _
" WHERE FolderID = " & Nz(Me.cmbCurrentEFolder) & _
" ORDER BY IndexKey01"
Me.cboIndexKey01.Visible = True
End If
What I'm not clear about is the relationship between cmbCurrentECabinet and cmbCurrentEFolder and cboIndexKey01. It seems to me that you should be setting cboIndexKey01.RowSource in the AfterUpdate event of cmbCurrentEFolder, not cmbCurrentECabinet, because it needs to change when (and only when) cmbCurrentECabinet changes.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 29 November 2017 10:32
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Dlookup Compile Error
If it works, stick with it. I don't see:
- where you would be setting cboIndexKey01.visible to false
- where you are actually running the code
I came up with this and it works but is there a cleaner way to do it?
If IsNull(DLookup("[IndexKey01Label]", "tblCabinets", "[CabinetID] = " & Forms!frmMainWindow![cmbCurrentECabinet])) Then
Me.lblIndexKey01Label.Visible = False
Else
Me.lblIndexKey01Label.Caption = DLookup("[IndexKey01Label]", "tblCabinets", "[CabinetID] = " & Forms!frmMainWindow![cmbCurrentECabinet])
Me.lblIndexKey01Label.Visible = True
Me.cboIndexKey01.Visible = True
Me.cboIndexKey01.RowSource = "SELECT tblFolders.FolderID, tblFolders.IndexKey01 FROM tblFolders " & _
" WHERE FolderID = " & Nz(Me.cmbCurrentEFolder) & _
" ORDER BY IndexKey01"
End If
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Art,
Is the control a label? If so, it doesn't have a value. You would need to set the caption property. Assuming the CabinetID is numeric:
Me.lblIndexKey01Label.Caption = DLookup("[IndexKey01Label]", "tblCabinets", "[CabinetID] = " & Me.cmbCurrentECabinet)
Duane Hookom
Yeah me. I figured out the syntax error but now I have another error:
Me.lblIndexKey01Label = DLookup("[IndexKey01Label]", "tblCabinets", "[CabinetID] = " & Me.cmbCurrentECabinet)
Object doesn't support this property or method.
The specific on the combo is:
Name: cmbCurrentECabinet
Record Source: SELECT tblCabinets.CabinetID, tblCabinets.CabinetName FROM tblCabinets;
Bound Colum 1
Column COunt: 2
Column Width: 0;1
