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
Art,
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
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, November 28, 2017 3:06 PM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: Re: [MS_AccessPros] Re: Dlookup Compile Error
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
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
On Tuesday, November 28, 2017 02:23:11 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, November 28, 2017 2:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Dlookup Compile Error
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
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar