Hi Art
You're welcome!
Just a note of explanation about LinkMaster/ChildFields – LinkMasterFields should be a control (or list of controls) on the parent form – the control(s) do not need to be bound. Conversely, LinkChildFields should be a field (or list of fields) in the subform's Recordset. The value(s) in the LinkMasterFields control(s) act as a filter value for the corresponding field(s) in the subform. When that value changes, either by navigating records in the case of a bound control, or manually in the case of an unbound one, the subform is requeried. There is no need to explicitly requery after the unbound value changes.
On the question of the checkboxes – more information please! What do you want to achieve with these checkboxes?
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 15 May 2017 06:59
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
That was great. THank you. What about adding checkboxes to my subform?
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: Sunday, May 14, 2017 1:38 PM
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
Art-
No! Just cboCabinet.
And you can't include attachments on this forum.
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 14, 2017, at 7:16 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I tried to set the link properties but it does not recognize cbocabinet.cabinetID. See snapshot below.
With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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: Sunday, May 14, 2017 11:58 AM
Subject: Re: [MS_AccessPros] Populating a combo from other combo.
Art-
It doesn't matter that the control is unbound. Just do what Graham said:
"set the subform's LinkMasterFields property to "cboCabinets", and its LinkChildFields property to the name of the related (foreign key) field."
If the subform doesn't show the data when you set cboCabinets, you may need a .Requery of the subform in AfterUpdate of the combo box.
John Viescas, AuthorEffective SQLSQL Queries for Mere Mortals Microsoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications http://www.viescas.com/ (Paris, France)
On May 14, 2017, at 6:50 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
With cboCabinet, it is an unbound control. So how do I make the link...
With Warm Regards, Arthur D. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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: Sunday, May 14, 2017 1:35 AM
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, authorEffective SQLSQL Queries for Mere MortalsMicrosoft Office Access 2010 Inside OutMicrosoft Office Access 2007 Inside OutBuilding Access Applicationshttp://www.viescas.com/
On May 14, 2017, at 03:33, 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi ArtPardon 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 WithOn 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. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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, AuthorEffective SQLSQL Queries for Mere Mortals Microsoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding 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. LorenziniIT System ManagerCheyenne River Housing AuthorityWk.(605)964-4265 Ext. 130Fax (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, AuthorEffective SQLSQL Queries for Mere Mortals Microsoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding 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 LorenziniSioux Falls, SD
#yiv4380309262 #yiv4380309262 -- #yiv4380309262ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4380309262 #yiv4380309262ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4380309262 #yiv4380309262ygrp-mkp #yiv4380309262hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4380309262 #yiv4380309262ygrp-mkp #yiv4380309262ads {margin-bottom:10px;}#yiv4380309262 #yiv4380309262ygrp-mkp .yiv4380309262ad {padding:0 0;}#yiv4380309262 #yiv4380309262ygrp-mkp .yiv4380309262ad p {margin:0;}#yiv4380309262 #yiv4380309262ygrp-mkp .yiv4380309262ad a {color:#0000ff;text-decoration:none;}#yiv4380309262 #yiv4380309262ygrp-sponsor #yiv4380309262ygrp-lc {font-family:Arial;}#yiv4380309262 #yiv4380309262ygrp-sponsor #yiv4380309262ygrp-lc #yiv4380309262hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4380309262 #yiv4380309262ygrp-sponsor #yiv4380309262ygrp-lc .yiv4380309262ad {margin-bottom:10px;padding:0 0;}#yiv4380309262 #yiv4380309262actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4380309262 #yiv4380309262activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4380309262 #yiv4380309262activity span {font-weight:700;}#yiv4380309262 #yiv4380309262activity span:first-child {text-transform:uppercase;}#yiv4380309262 #yiv4380309262activity span a {color:#5085b6;text-decoration:none;}#yiv4380309262 #yiv4380309262activity span span {color:#ff7900;}#yiv4380309262 #yiv4380309262activity span .yiv4380309262underline {text-decoration:underline;}#yiv4380309262 .yiv4380309262attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4380309262 .yiv4380309262attach div a {text-decoration:none;}#yiv4380309262 .yiv4380309262attach img {border:none;padding-right:5px;}#yiv4380309262 .yiv4380309262attach label {display:block;margin-bottom:5px;}#yiv4380309262 .yiv4380309262attach label a {text-decoration:none;}#yiv4380309262 blockquote {margin:0 0 0 4px;}#yiv4380309262 .yiv4380309262bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4380309262 .yiv4380309262bold a {text-decoration:none;}#yiv4380309262 dd.yiv4380309262last p a {font-family:Verdana;font-weight:700;}#yiv4380309262 dd.yiv4380309262last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4380309262 dd.yiv4380309262last p span.yiv4380309262yshortcuts {margin-right:0;}#yiv4380309262 div.yiv4380309262attach-table div div a {text-decoration:none;}#yiv4380309262 div.yiv4380309262attach-table {width:400px;}#yiv4380309262 div.yiv4380309262file-title a, #yiv4380309262 div.yiv4380309262file-title a:active, #yiv4380309262 div.yiv4380309262file-title a:hover, #yiv4380309262 div.yiv4380309262file-title a:visited {text-decoration:none;}#yiv4380309262 div.yiv4380309262photo-title a, #yiv4380309262 div.yiv4380309262photo-title a:active, #yiv4380309262 div.yiv4380309262photo-title a:hover, #yiv4380309262 div.yiv4380309262photo-title a:visited {text-decoration:none;}#yiv4380309262 div#yiv4380309262ygrp-mlmsg #yiv4380309262ygrp-msg p a span.yiv4380309262yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4380309262 .yiv4380309262green {color:#628c2a;}#yiv4380309262 .yiv4380309262MsoNormal {margin:0 0 0 0;}#yiv4380309262 o {font-size:0;}#yiv4380309262 #yiv4380309262photos div {float:left;width:72px;}#yiv4380309262 #yiv4380309262photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4380309262 #yiv4380309262photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4380309262 #yiv4380309262reco-category {font-size:77%;}#yiv4380309262 #yiv4380309262reco-desc {font-size:77%;}#yiv4380309262 .yiv4380309262replbq {margin:4px;}#yiv4380309262 #yiv4380309262ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4380309262 #yiv4380309262ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4380309262 #yiv4380309262ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4380309262 #yiv4380309262ygrp-mlmsg select, #yiv4380309262 input, #yiv4380309262 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4380309262 #yiv4380309262ygrp-mlmsg pre, #yiv4380309262 code {font:115% monospace;}#yiv4380309262 #yiv4380309262ygrp-mlmsg * {line-height:1.22em;}#yiv4380309262 #yiv4380309262ygrp-mlmsg #yiv4380309262logo {padding-bottom:10px;}#yiv4380309262 #yiv4380309262ygrp-msg p a {font-family:Verdana;}#yiv4380309262 #yiv4380309262ygrp-msg p#yiv4380309262attach-count span {color:#1E66AE;font-weight:700;}#yiv4380309262 #yiv4380309262ygrp-reco #yiv4380309262reco-head {color:#ff7900;font-weight:700;}#yiv4380309262 #yiv4380309262ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4380309262 #yiv4380309262ygrp-sponsor #yiv4380309262ov li a {font-size:130%;text-decoration:none;}#yiv4380309262 #yiv4380309262ygrp-sponsor #yiv4380309262ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4380309262 #yiv4380309262ygrp-sponsor #yiv4380309262ov ul {margin:0;padding:0 0 0 8px;}#yiv4380309262 #yiv4380309262ygrp-text {font-family:Georgia;}#yiv4380309262 #yiv4380309262ygrp-text p {margin:0 0 1em 0;}#yiv4380309262 #yiv4380309262ygrp-text tt {font-size:120%;}#yiv4380309262 #yiv4380309262ygrp-vital ul li:last-child {border-right:none !important;}#yiv4380309262
[Non-text portions of this message have been removed]
------------------------------------
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
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 (18) |
Tidak ada komentar:
Posting Komentar