John, you will never be "off your game"!! J
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, 14 May 2017 18:36
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
Thanks, Graham. I KNEW there had to be a property to check, and I even looked up combo box properties in MSDN, but just skipped right past ListCount. I'll leave it to you to continue to follow up with Art. I'm clearly "off my game!"
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On May 14, 2017, at 03:33, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Art
Pardon me for jumping in, but I have a few suggestions (and I expect John is sound asleep in Paris at the moment!)
First, there is no need to .Requery a combo box after setting the RowSource, as this is done automatically. An explicit .Requery will just make it happen twice.
Second, you don't need to open a Recordset to check if there are any subfolders – just set the RowSource of the combo box and then check its .ListCount property to see if it is zero.
cboSubFolders appears to have three columns with the first two being hidden, and the first being the bound column. This is why you can't set its Value to "No Sub Folders". Instead, I suggest you create a dummy SQL string that returns only one record: 0, 0, No Sub Folders, and set its Value to zero. Something like this:
sFolderSource = "SELECT [tblSubFolders].[SubFolderID], [tblSubFolder].[FolderID], [tblSubFolder].[SubFolderName] " & _
"FROM tblSubFolders " & _
"WHERE [FolderID] = " & Me.cboFolders.Value
With Me.cboSubFolders
.RowSource = sFolderSource
If .ListCount = 0 Then
.RowSource = "SELECT DISTINCT 0, 0, 'No Sub Folders' FROM tblSubFolders;"
.Value = 0
.Locked = True
.Enabled = False
Else
.Locked = False
.Enabled = True
End If
End With
On your question about the subform, is tblCabinets related one-to-many to tblIndexes? If so, simply set the subform's LinkMasterFields property to "cboCabinets", and its LinkChildFields property to the name of the related (foreign key) field.
Best,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, 14 May 2017 11:31
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
John -
Just a little more help. Based on the value in cboCabinets, there is a subform called sfrmIndexes which may or may not have records according to the selection in the cboCabinet. No cbo Cabinet is unbound control and sfrmIndexes is bound to tblIndexes. When I load the form I need all the combo boxes blank as will as the subform. Based on the value of cboCabinet I need to populate sfrmIndexes or not as the case maybe. ALso if sfrmIndexes get populated then I need to add check box to each of the records in the subform. Ok maybe more than a little.
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."
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, May 13, 2017 5:12 PM
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
Dim db As DAO.Database, rs As DAO.Recordset
sFolderSource = "SELECT [tblSubFolders].[SUbFolderID], [tblSubFolder].[FolderID], [tblSubFolder].[SubFolderName] " & _
"FROM tblSubFolders " & _
"WHERE [FolderID] = " & Me.cboFolders.Value
Me.cboSubFolders.RowSource = sFolderSource
Set db = CurrentDb
Set rs = db.OpenRecordset(sFolderSource)
If rs.EOF Then
Me.cboSubFolders.Value = "No Sub Folders"
Else
Me.cboSubFolders.Requery
End If
rs.Close
Set rs = Nothing
Set db = Nothing
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 13, 2017, at 11:12 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
How would I do that?
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."
From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, May 13, 2017 4:07 PM
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
Art-
You set sFolderSource to an SQL statement, so it will never equal "". You could open a recordset using sFolderSource and see if it returns any rows. But unless the Bound Column of the combo box is text, you'll never be able to set it equal to "No Sub Folders".
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On May 13, 2017, at 10:57 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have three combos boxes, which should populate the next automatiacally. The first one is cboCabinet:
Private Sub cboCabinet_AfterUpdate()
Dim sFolderLoadSource As String
sFolderLoadSource = "SELECT [tblFolders].[FolderID], [tblFolders].[CabinetID], [tblFolders].[FolderName] " & _
"FROM tblFolders " & _
"WHERE [CabinetID] = " & Me.cboCabinet.Value
Me.cboFolders.RowSource = sFolderLoadSource
Me.cboFolders.Requery
Me.sfrmIndexes.Form.Requery
End Sub
This Works to populate the second combo called cboFolders.
Private Sub cboFolders_AfterUpdate()
Dim sFolderSource As String
sFolderSource = "SELECT [tblSubFolders].[SUbFolderID], [tblSubFolder].[FolderID], [tblSubFolder].[SubFolderName] " & _
"FROM tblSubFolders " & _
"WHERE [FolderID] = " & Me.cboFolders.Value
Me.cboSubFolders.RowSource = sFolderSource
If sFolderSource = "" Then
Me.cboSubFolders.Value = "No Sub Folders"
Else
Me.cboSubFolders.Requery
End If
End Sub
My issues when it tries to populate the 3rd combo called cboSubFolders, there might be cases where there is no records in the subfolder table for a given combination of the first two combos. I am trying to set the value of cboSubFolders to No SubFolders in case of this combination. But it is not working. Any ideas?
Signed,
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 (11) |
Tidak ada komentar:
Posting Komentar