Hi Art
You need to add the "Select…" line to your RowSource using a UNION query. For example:
qrySelectDrawer:
SELECT tblDrawer.intDrawerID, tblDrawer.txtDrawerName, tblDrawer.intCabinetID, 0 AS IsHeader FROM tblDrawer
UNION SELECT 0, 'Select...', 0, -1 AS IsHeader FROM tblDrawer
ORDER BY IsHeader, tblDrawer.txtDrawerName;
If you set the Value (or DefaultValue) of the ComboBox to 0 then it will display "Select…". Of course, you need not to have either an intDrawerID, nor an intCabinetID with the value 0. I suggest you build these as saved queries, because it can get pretty messy building such complex SQL strings in VBA on the fly.
You need to decide how to populate rowsources that cascade from this combo if its value is 0 – for example, either you show ALL the PriFolders, or you display NONE.
Best wishes,
Graham (in New Zealand)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 1 August 2017 01:26
To: 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Subject: Re: RE: [MS_AccessPros] Populate list box via a combo box
That was perfect, I guess there was not much difference between the listbox and combo box. Is there a way when the form loads to set each of combo boxes to display 'Select..." and have the listbox empty? Thank you for that code...
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Sun Jul 30 2017 17:19:32 GMT-0500 (Central Daylight Time), 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Art
There is little functional difference between a combobox and a listbox as far as the RowSource is concerned, so you do it just the same way as you do for a combo-to-combo cascading relationship:
Private Sub cboPriFolders_AfterUpdate()
Dim sRowSource As String
sRowSource = "SELECT tblSubFolder.intSubFolderID, tblSubFolder.txtSubFolderName, " _
& "tblSubFolder.intPriFolderID FROM tblSubFolder"
If Not IsNull(cboPriFolders) Then
sRowSource = sRowSource & " WHERE tblSubFolder.intPriFolderID=" & cboPriFolders
End If
lstSubFolders.RowSource = sRowSource
End Sub
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 31 July 2017 09:30
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Populate list box via a combo box
I was looking at the samples on the site but could not find what I am looking for. I have a series of combo boxes and a list box set up as follows which are related, they populate the combo below them.:
cboCabinets:
RowSource:
SELECT tblCabinet.intCabinetID, tblCabinet.txtCabinetName, tblCabinet.txtCabinetLocation FROM tblCabinet;
After_Update:
Dim sDrawerLoadSource As String
sDrawerLoadSource = "SELECT [tblDrawer].[intDrawerID], [tblDrawer].[intCabinetID], [tblDrawer].[txtDrawerName] " & _
"FROM tblDrawer " & _
"WHERE [intCabinetID] = " & Me.cboCabinet.Value
Me.cboDrawer.RowSource = sDrawerLoadSource
Me.cboDrawer.Requery
cboDrawer:
RowSource:SELECT tblDrawer.intDrawerID, tblDrawer.intCabinetID, tblDrawer.txtDrawerName, tblDrawer.txtDrawerLocation FROM tblDrawer;
After_Update:
Dim sPriFoldersLoadSource As String
sPriFoldersLoadSource = "SELECT [tblPrimaryFolder].[intPriFolderID], [tblPrimaryFolder].[intDrawerID], [tblPrimaryFolder].[txtPriFolderName] " & _
"FROM tblPrimaryFolder " & _
"WHERE [intDrawerID] = " & Me.cboDrawer.Value
Me.cboPriFolders.RowSource = sPriFoldersLoadSource
Me.cboPriFolders.Requery
cboPriFolders
RowSource:SELECT tblPrimaryFolder.intPriFolderID, tblPrimaryFolder.intDrawerID, tblPrimaryFolder.txtPriFolderName, tblPrimaryFolder.txtPriFolderLocation FROM tblPrimaryFolder;
After_Update
This were I get lost. I am not sure how to write the connection code for the list box:
lstSubfolders
RpwSource: SELECT tblSubFolder.intSubFolderID, tblSubFolder.txtSubFolderName, tblSubFolder.intPriFolderID FROM tblSubFolder;
Now a Primary folder can have several subfolders which can be loaded into the list box. Hope youcan help.
Thank you
Art Lorenzini,
Sioux Falls, SD
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 (4) |
Tidak ada komentar:
Posting Komentar