Re: [MS_AccessPros] Populating a combo from other combo.

 

John,

I also debugged the sFolderSource variable and this is what is being returned and I think it looks good:

?sFolderSource
SELECT [tblSubFolders].[SUbFolderID], [tblSubFolder].[FolderID], [tblSubFolder].[SubFolderName] FROM tblSubFolders WHERE [FolderID] = 1229

 

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: "Art Lorenzini dbalorenzini@yahoo.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:22 PM
Subject: Re: [MS_AccessPros] Populating a combo from other combo.

 

John,

Thanks, So I integrated you code but it says its expecting 2 parameters on the OpenRecordset? 

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 

http://www.viescas.com/ 

(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 

http://www.viescas.com/ 

(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: Art Lorenzini <dbalorenzini@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 4

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___