If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Search Database to Reference Subform
Number 1 is like what I want to do. Examples: 1) I would like to be able to
type in "ABC" in the company name field and have it return all records that have ABC in their name. 2) Or type ABC and then select employee from CboEmployee and have it return records that have ABC in the name but are only assigned to that employee. 3) Or only select an employee in CboEmployee and have it return all the records that have been assigned to that customer. The main form is a list of CLEC's (customers) names with CLEC ID. There are two subforms. The subform I'm trying to filter is based on tblEmployeeAssignments which has Customer ID, employee ID, Title, employeeAssignmentID. If I can make examples 1-3 work, I would like to eventually extend the search capability to include the other subform, which includes regions and it's embedded subform which includes states. So that the final solution would allow me to e.g. 4) search a customer by employee assignment, region, state etc. I thought the subquery would have worked. I went back to Mr.Browne's site it said to extend this use subqueries but not much more information. But it just seems to either make visible or not visible instead of filter. But of course any way to accomplish would indebt me (more than I already am)! Hope this was clear. "KenSheridan via AccessMonster.com" wrote: I'm far from clear what you are trying to do: 1. Do you want to filter the parent form on the basis of a value in its underlying recordset AND on the value of any related row in the subform's underlying recordset? This would mean that only those records in the parent form's underlying recordset would be returned if the value in the relevant column of its recordset matched the selected value, and at least one related row in the subform's underlying recordset included the other selected value. Also with this scenario, do you want the criteria to be optional, i.e. if you select a value to filter from the parent form's underlying recordset, but not one from the subform's underlying recordset the parent form would be filtered on the selected value regardless of the values in the subform's underlying recordset, and vice versa? 2. Do you want to filter the parent form on the basis of a value in its underlying recordset OR on the value of any related row in the subform's underlying recordset? This would mean that those records in the parent form's underlying recordset would be returned if the value in the relevant column of its recordset matched the selected value, along with those records whose value did not match this value but at least one row in the subform's underlying recordset included the other selected value. 3. I suspect its 1 you want, but in either case, you'll have to set the Filter and FilterOn properties of the parent form, using a subquery as I described, but depending on whether you want 1 or 2 the code would differ slightly. 4. If you filter the parent form on the value of any related row in the subform's underlying recordset, but as well as filtering the parent form you also want the subform to be filtered, i.e. to show only the rows which match the selected criterion on its recordset, then you'll need to set the Filter and FilterOn properties of both the parent form and the subform, but the Filter properties for each will be different string expressions. If you can explain fully in plain English *what* you are attempting to do in terms of the underlying real world entities reflected in the form and subform, rather than *how* you are attempting to do it, then we might have a clearer view of how best this can be achieved, but my gut feeling at present is that this might more easily be done by means of parameters in the parent form's (and subform's if you want that filtered too) query which reference the controls on the form rather than by filtering the form. All you'd need to do then in code would be to requery the form (and possibly subform). And BTW when you add a control to a form the first thing to do is change its name from something like Combo52 to cboEmployee or whatever is appropriate. Any code or parameters which reference the control will then be far more easily understood. This should be done before entering any code in a control's event procedure, however, as of you change the name afterwards the link with the code will be broken and you'll need to recreate the event procedure. Ken Sheridan Stafford, England Confused wrote: I tried that and it does not filter any records. The subform goes blank. But when I scroll through the records, the records that have that particular employee appear and disappear wheren the record does not have that employee. Also I got it to do almost the same thing with this ( The difference is that only the employee selected becomes visible: If Not IsNull(Me.Combo52) Then strwhere = strwhere & " ([EmployeeID] = " & Me.Combo52 & ") AND " End If Also I had to change this part of the code. Me.[qryemployeeAssignments subform].Form.Filter = strwhere Me.[qryemployeeAssignments subform].Form.FilterOn = True But after doing so, can no longer search what is on the main form that I used to with this: If Not IsNull(Me.Text58) Then strwhere = strwhere & "([CLEC Name] Like ""*" & Me.Text58 & "*"") And " Here is the code if you know of antoher way or have a resolution to be able to search main form and subform based on criteria. I'd be real happy if I could just get it to filter the subform. Unfortunately, it worked perfectly until I normalized the data adn placed part of it in the subform. Please see Code below: Private Sub CmdFilter_Click() Dim strwhere As String Dim lngLen As Long Const conJetDate = "\#mm\/dd\/yyyy\#" If Not IsNull(Me.Combo52) Then strwhere = strwhere & " ([EmployeeID] = " & Me.Combo52 & ") AND " End If If Not IsNull(Me.Text58) Then strwhere = strwhere & "([CLEC Name] Like ""*" & Me.Text58 & "*"") And " End If 'See if the string has more than 5 characters (a trailng " AND ") to remove. lngLen = Len(strwhere) - 5 If lngLen = 0 Then 'Nah: there was nothing in the string. MsgBox "No criteria", vbInformation, "Nothing to do." Else 'Yep: there is something there, so remove the " AND " at the end. strwhere = Left$(strwhere, lngLen) 'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G). 'Debug.Print strWhere 'Finally, apply the string as the form's Filter. Me.[qryemployeeAssignments subform].Form.Filter = strwhere Me.[qryemployeeAssignments subform].Form.FilterOn = True End If My mistake; in my second example the value of the combo box should have been concatenated into the string expression (as I did in the first), not [quoted text clipped - 41 lines] FullName. Data is stored in tables, and only in tables; your query will need to reference the *TABLE* containing the data, not the form. -- Message posted via http://www.accessmonster.com |
#12
|
|||
|
|||
Search Database to Reference Subform
Firstly you've referred to both CLEC ID and Customer ID columns. I'm
assuming below that the primary key of Customers and the corresponding foreign keys in both tblEmployeeAssignments and my putative CustomerRegions table are all called CLEC ID. If not you'll need to make the necessary amendments to the SQL. I think I'd be inclined to use a query to filter the main parent form (and subform if you also wanted that filtered). You'd do this by basing the main from on a query with parameters which reference the unbound controls on the main form. With the company and employee controls, which I'll call txtCompany and cboEmployee for this example, the referenced in the query in the usual way, testing for OR IS NULL to make it optional; the second would be referenced by a subquery, but again testing in the outer query for OR IS NULL to make it optional e.g. SELECT * FROM Customers WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*" OR Forms!CLECS2MainForm!txtCustomer IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM tblemployeeAssignments WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee) OR Forms!CLECS2MainForm!cboEmployee IS NULL) ORDER BY [CLEC Name]; To include the region and/or state you'd add further subqueries on, in the first instance the table underlying the second subform, which I'll assume is called CustomerRegions and contains columns CLEC ID and RegionID, and in the second instance on a query which joins the table underlying the second subform to the table underlkying its subform, which I'll assume is called States and contains columns RegionID and StateID: SELECT * FROM Customers WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*" OR Forms!CLECS2MainForm!txtCustomer IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM tblemployeeAssignments WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee) OR Forms!CLECS2MainForm!cboEmployee IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM CustomerRegions WHERE [RegionID] = Forms!CLECS2MainForm!cboRegion) OR Forms!CLECS2MainForm!cboRegion IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM CustomerRegions INNER JOIN States ON CustomerRegions.RegionID = States.RegionID WHERE [StateID] = Forms!CLECS2MainForm!cboState) OR Forms!CLECS2MainForm!cboState IS NULL) ORDER BY [CLEC Name]; To restrict the form on the basis of the selections you just need to requery it with: Me.Requery which you can do in the AfterUpdate event procedures of each of the txtCustomer, cboEmployee, cboRegion and cboState controls. This will cause the form to be progressively restricted on the basis of the selections as each is made in the unbound controls. If you also want the subform's restricted so that as well as the main form being restricted on the basis of the criteria in combination each subform is also filtered on the basis of each *individual* criterion then you then you'd use a query which refernces the rlevant contolas a parameter as the each subform's RecordSource property, e.g. for the employee assignments subform: SELECT * FROM tblEmployeeAssignments WHERE ([EmployeeID] = Forms!CLECS2MainForm!cboEmployee OR Forms!CLECS2MainForm!cboEmployee IS NULL); and then also requery the subform in the AfterUpdate procedure of the relevant unbound control, cboEmployee in this case: Me.[qryemployeeAssignments subform].Requery I'd suggest getting the restriction of the parent form by means of its underlying query working first, then tackling the restriction of the subforms if you want that also. Ken Sheridan Stafford, England Confused wrote: Number 1 is like what I want to do. Examples: 1) I would like to be able to type in "ABC" in the company name field and have it return all records that have ABC in their name. 2) Or type ABC and then select employee from CboEmployee and have it return records that have ABC in the name but are only assigned to that employee. 3) Or only select an employee in CboEmployee and have it return all the records that have been assigned to that customer. The main form is a list of CLEC's (customers) names with CLEC ID. There are two subforms. The subform I'm trying to filter is based on tblEmployeeAssignments which has Customer ID, employee ID, Title, employeeAssignmentID. If I can make examples 1-3 work, I would like to eventually extend the search capability to include the other subform, which includes regions and it's embedded subform which includes states. So that the final solution would allow me to e.g. 4) search a customer by employee assignment, region, state etc. I thought the subquery would have worked. I went back to Mr.Browne's site it said to extend this use subqueries but not much more information. But it just seems to either make visible or not visible instead of filter. But of course any way to accomplish would indebt me (more than I already am)! Hope this was clear. I'm far from clear what you are trying to do: [quoted text clipped - 118 lines] FullName. Data is stored in tables, and only in tables; your query will need to reference the *TABLE* containing the data, not the form. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200907/1 |
#13
|
|||
|
|||
Search Database to Reference Subform
So change the recorsource of the form to the query listed? I did this by
clicking on view SQL and inputted the first block of code. is this right? I tried that and when I open the form it pops up the different paremeter questions i.e,. [forms]! [CLECS2MainForm]![cboemployee], [CLEC ID] etc. When I enter the employeeID in this box it opens and doesn't filter any records. If I don't selecet anything at least all of the records return. I must be way off. Maybe subforms are not supposed to be filtered? "KenSheridan via AccessMonster.com" wrote: Firstly you've referred to both CLEC ID and Customer ID columns. I'm assuming below that the primary key of Customers and the corresponding foreign keys in both tblEmployeeAssignments and my putative CustomerRegions table are all called CLEC ID. If not you'll need to make the necessary amendments to the SQL. I think I'd be inclined to use a query to filter the main parent form (and subform if you also wanted that filtered). You'd do this by basing the main from on a query with parameters which reference the unbound controls on the main form. With the company and employee controls, which I'll call txtCompany and cboEmployee for this example, the referenced in the query in the usual way, testing for OR IS NULL to make it optional; the second would be referenced by a subquery, but again testing in the outer query for OR IS NULL to make it optional e.g. SELECT * FROM Customers WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*" OR Forms!CLECS2MainForm!txtCustomer IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM tblemployeeAssignments WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee) OR Forms!CLECS2MainForm!cboEmployee IS NULL) ORDER BY [CLEC Name]; To include the region and/or state you'd add further subqueries on, in the first instance the table underlying the second subform, which I'll assume is called CustomerRegions and contains columns CLEC ID and RegionID, and in the second instance on a query which joins the table underlying the second subform to the table underlkying its subform, which I'll assume is called States and contains columns RegionID and StateID: SELECT * FROM Customers WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*" OR Forms!CLECS2MainForm!txtCustomer IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM tblemployeeAssignments WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee) OR Forms!CLECS2MainForm!cboEmployee IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM CustomerRegions WHERE [RegionID] = Forms!CLECS2MainForm!cboRegion) OR Forms!CLECS2MainForm!cboRegion IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM CustomerRegions INNER JOIN States ON CustomerRegions.RegionID = States.RegionID WHERE [StateID] = Forms!CLECS2MainForm!cboState) OR Forms!CLECS2MainForm!cboState IS NULL) ORDER BY [CLEC Name]; To restrict the form on the basis of the selections you just need to requery it with: Me.Requery which you can do in the AfterUpdate event procedures of each of the txtCustomer, cboEmployee, cboRegion and cboState controls. This will cause the form to be progressively restricted on the basis of the selections as each is made in the unbound controls. If you also want the subform's restricted so that as well as the main form being restricted on the basis of the criteria in combination each subform is also filtered on the basis of each *individual* criterion then you then you'd use a query which refernces the rlevant contolas a parameter as the each subform's RecordSource property, e.g. for the employee assignments subform: SELECT * FROM tblEmployeeAssignments WHERE ([EmployeeID] = Forms!CLECS2MainForm!cboEmployee OR Forms!CLECS2MainForm!cboEmployee IS NULL); and then also requery the subform in the AfterUpdate procedure of the relevant unbound control, cboEmployee in this case: Me.[qryemployeeAssignments subform].Requery I'd suggest getting the restriction of the parent form by means of its underlying query working first, then tackling the restriction of the subforms if you want that also. Ken Sheridan Stafford, England Confused wrote: Number 1 is like what I want to do. Examples: 1) I would like to be able to type in "ABC" in the company name field and have it return all records that have ABC in their name. 2) Or type ABC and then select employee from CboEmployee and have it return records that have ABC in the name but are only assigned to that employee. 3) Or only select an employee in CboEmployee and have it return all the records that have been assigned to that customer. The main form is a list of CLEC's (customers) names with CLEC ID. There are two subforms. The subform I'm trying to filter is based on tblEmployeeAssignments which has Customer ID, employee ID, Title, employeeAssignmentID. If I can make examples 1-3 work, I would like to eventually extend the search capability to include the other subform, which includes regions and it's embedded subform which includes states. So that the final solution would allow me to e.g. 4) search a customer by employee assignment, region, state etc. I thought the subquery would have worked. I went back to Mr.Browne's site it said to extend this use subqueries but not much more information. But it just seems to either make visible or not visible instead of filter. But of course any way to accomplish would indebt me (more than I already am)! Hope this was clear. I'm far from clear what you are trying to do: [quoted text clipped - 118 lines] FullName. Data is stored in tables, and only in tables; your query will need to reference the *TABLE* containing the data, not the form. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200907/1 |
#14
|
|||
|
|||
Search Database to Reference Subform
Ken,
You're a genius! I went back after much effort to make the different way work along with frustration, agony, and prayer and decided to build the forms over. I found that in the tblemployeeAssignments, the CLEC ID had a space between it and the CLECS table did not have a space. So I rebuilt the form/subform after changing it. I then placed your statement along with the original code (which I knew had to work based on well...it came from you and the amateur late night readings on subqueries): If Not IsNull(Me.Combo52) Then strWhere = strWhere & _ " [CLECID] IN(SELECT [cLECID]" & _ " FROM tblemployeeAssignments" & _ " WHERE [EmployeeID] = " & Me.Combo52 & ") AND " End If Now the whole thing works beautifully. Thank you! Thank you! That was a couple days spent, but I'm learning.... Sorry for the big curve ball. I"Confused" wrote: So change the recorsource of the form to the query listed? I did this by clicking on view SQL and inputted the first block of code. is this right? I tried that and when I open the form it pops up the different paremeter questions i.e,. [forms]! [CLECS2MainForm]![cboemployee], [CLEC ID] etc. When I enter the employeeID in this box it opens and doesn't filter any records. If I don't selecet anything at least all of the records return. I must be way off. Maybe subforms are not supposed to be filtered? "KenSheridan via AccessMonster.com" wrote: Firstly you've referred to both CLEC ID and Customer ID columns. I'm assuming below that the primary key of Customers and the corresponding foreign keys in both tblEmployeeAssignments and my putative CustomerRegions table are all called CLEC ID. If not you'll need to make the necessary amendments to the SQL. I think I'd be inclined to use a query to filter the main parent form (and subform if you also wanted that filtered). You'd do this by basing the main from on a query with parameters which reference the unbound controls on the main form. With the company and employee controls, which I'll call txtCompany and cboEmployee for this example, the referenced in the query in the usual way, testing for OR IS NULL to make it optional; the second would be referenced by a subquery, but again testing in the outer query for OR IS NULL to make it optional e.g. SELECT * FROM Customers WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*" OR Forms!CLECS2MainForm!txtCustomer IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM tblemployeeAssignments WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee) OR Forms!CLECS2MainForm!cboEmployee IS NULL) ORDER BY [CLEC Name]; To include the region and/or state you'd add further subqueries on, in the first instance the table underlying the second subform, which I'll assume is called CustomerRegions and contains columns CLEC ID and RegionID, and in the second instance on a query which joins the table underlying the second subform to the table underlkying its subform, which I'll assume is called States and contains columns RegionID and StateID: SELECT * FROM Customers WHERE ([CLEC Name] LIKE "*" & Forms!CLECS2MainForm!txtCustomer & "*" OR Forms!CLECS2MainForm!txtCustomer IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM tblemployeeAssignments WHERE [EmployeeID] = Forms!CLECS2MainForm!cboEmployee) OR Forms!CLECS2MainForm!cboEmployee IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM CustomerRegions WHERE [RegionID] = Forms!CLECS2MainForm!cboRegion) OR Forms!CLECS2MainForm!cboRegion IS NULL) AND ([CLEC ID] IN (SELECT [CLEC ID] FROM CustomerRegions INNER JOIN States ON CustomerRegions.RegionID = States.RegionID WHERE [StateID] = Forms!CLECS2MainForm!cboState) OR Forms!CLECS2MainForm!cboState IS NULL) ORDER BY [CLEC Name]; To restrict the form on the basis of the selections you just need to requery it with: Me.Requery which you can do in the AfterUpdate event procedures of each of the txtCustomer, cboEmployee, cboRegion and cboState controls. This will cause the form to be progressively restricted on the basis of the selections as each is made in the unbound controls. If you also want the subform's restricted so that as well as the main form being restricted on the basis of the criteria in combination each subform is also filtered on the basis of each *individual* criterion then you then you'd use a query which refernces the rlevant contolas a parameter as the each subform's RecordSource property, e.g. for the employee assignments subform: SELECT * FROM tblEmployeeAssignments WHERE ([EmployeeID] = Forms!CLECS2MainForm!cboEmployee OR Forms!CLECS2MainForm!cboEmployee IS NULL); and then also requery the subform in the AfterUpdate procedure of the relevant unbound control, cboEmployee in this case: Me.[qryemployeeAssignments subform].Requery I'd suggest getting the restriction of the parent form by means of its underlying query working first, then tackling the restriction of the subforms if you want that also. Ken Sheridan Stafford, England Confused wrote: Number 1 is like what I want to do. Examples: 1) I would like to be able to type in "ABC" in the company name field and have it return all records that have ABC in their name. 2) Or type ABC and then select employee from CboEmployee and have it return records that have ABC in the name but are only assigned to that employee. 3) Or only select an employee in CboEmployee and have it return all the records that have been assigned to that customer. The main form is a list of CLEC's (customers) names with CLEC ID. There are two subforms. The subform I'm trying to filter is based on tblEmployeeAssignments which has Customer ID, employee ID, Title, employeeAssignmentID. If I can make examples 1-3 work, I would like to eventually extend the search capability to include the other subform, which includes regions and it's embedded subform which includes states. So that the final solution would allow me to e.g. 4) search a customer by employee assignment, region, state etc. I thought the subquery would have worked. I went back to Mr.Browne's site it said to extend this use subqueries but not much more information. But it just seems to either make visible or not visible instead of filter. But of course any way to accomplish would indebt me (more than I already am)! Hope this was clear. I'm far from clear what you are trying to do: [quoted text clipped - 118 lines] FullName. Data is stored in tables, and only in tables; your query will need to reference the *TABLE* containing the data, not the form. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200907/1 |
|
Thread Tools | |
Display Modes | |
|
|