The Link properties can be changed with code but it has been my experience that Access doesn't like us to do it. Your experience my vary.
I would prefer to write code that is more flexible like:
Dim strSQL as String
Dim strWhere as String
strWhere = " 1 = 1 "
strWhere = strWhere & " And subFolderID = " & Me.cmbChooseSubFolders & " "
' you can add more conditions to strWhere here
strSQL = "SELECT DocumentID, DocumentName, DocumentLocation, DocumentFileType, DocumentFileKBSize, " & _
"DocumentMemo, CreatedDate, CreatedTime, FolderID, SubFolderID " & _
"FROM tblDocuments WHERE " & strWhere
Me.sfrmDocumentListing .Form.RecordSource = strSQL
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, February 9, 2018 11:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Sent: Friday, February 9, 2018 11:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Duane,
Thank you that worked perfectly. IS there a way to change the master\child links in VBA. As I work on this I am thinking i will have a couple of different linkings going on.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, February 9, 2018 8:49 AM
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
If the subfolder is required and there is a subfolder field in the subform, all you need to do is set the link master/child properties:
Link Master: cmbChooseSubFolders
Link Child: SubFolderID
There are more difficult solutions involving setting the Record Source of the form using VBA or adding a criteria to the subform's record source.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, February 9, 2018 8:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Sent: Friday, February 9, 2018 8:35 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Yes the subfolder is required. A folder can have multiple subfolders and the user after selecting a folder must select a subfolder. The subform should contain the records that are in the selected subfolder.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 8, 2018 5:08 PM
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Art,
I still don't think you answered my question fully.
If a user doesn't select a subfolder, what do you expect to see in the query/subform? Is the subfolder combo box value required?
Regards,
Duane Hookom
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 8, 2018 3:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Sent: Thursday, February 8, 2018 3:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
When it comes down to it, the cmbChooseCabinets sets up the cmbChooseFolders and cmbChooseSubFolders. The actual filtering is done by cmbChooseFolders and cmbChooseSubFolders.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 8, 2018 3:19 PM
Subject: Re: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Art,
It's not enough to say only "based on the selection made in the combo boxes". Do you want to filter by a single combo box if the user only selects a value in the cabinet combo box? I assume the subfolderID is unique and can only be found in a single folder and cabinet. So if a user selects a subfolder, you don't have to worry about filtering by folder or cabinet.
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups..com>
Sent: Thursday, February 8, 2018 2:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Filtering a continous form with multiple combo boxes
Sent: Thursday, February 8, 2018 2:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Filtering a continous form with multiple combo boxes
I have a from called frmDocumentDashboard on it I have 3 comboboxes:
cmbChooseCabinet - Select cabinetID, cabinetname from tblCabinets
cmbChooseFolder - Me.cmbChooseFolder.......RowSource = "SELECT tblFldrs.FolderID, tblFldrs.FolderName FROM tblFldrs " & _
" WHERE CabinetID = " & Nz(Me.cmbChooseCabinet) & _
" ORDER BY FolderName"
cmbChooseSubfolders - Me.cmbChooseSubfolder.RowSource = "SELECT tblSubfolders.SubfolderID, tblSubfolders.SubfolderName FROM tblSubfolders " & _
" WHERE CabinetID = " & Nz(Me.cmbChooseCabinet) & _
" ORDER BY SubfolderName"
Below these I have subform called sfrmDocumentListing which is a continuous form. Recordsource is
SELECT tblDocuments.DocumentID, tblDocuments.DocumentName, tblDocuments.DocumentLocation, tblDocuments.DocumentFileType, tblDocuments.DocumentFileKBSize, tblDocuments.DocumentMemo, tblDocuments.CreatedDate, tblDocuments.CreatedTime, tblDocuments.FolderID, tblDocuments.SubFolderID
FROM tblDocuments;
I would like to be able filter sfrmDocumentList based on the selection made in the combo boxes. Please advise.
Thank you,
Art Lorenzini
SD
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (12) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar