Sabtu, 13 Mei 2017

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

 

 John,

Figured out the select statement had errors in it. It almost works.

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:37 PM
Subject: 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 
(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 (7)
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.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)