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
|
|||
|
|||
Need selection in one list box to pull query info into another
Hi Ron,
Haven't been ignoring you. Just got home. Are you saying that List59 is on one form and List12 is on another? Brian "Ron Weaver" wrote in message ... Brian Ok, I have changed the field from Expr1 to CostomerID. I got by the WHERE statement problem (had a semi colon in there). This where I am now. I believe everything is in there correctly, But when I select a customer in Listbox1 I get the following: Compile Error, Method or Data Member Not Found. The problem is in the after update statement in listbox1 (List59).In the Me.List12.Requery the .List12 is highlighted. List12 is my Listbox2. As you requested earlier I have included the rowsource for both listboxes and there form names. ListBox1(59) Form "OrderDateForm" SELECT [Order Date Query].CustomerID, [Order Date Query].StartDate, [Order Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID FROM [Order Date Query]; ListBox2 (List12) Form "CustomerOrders" SELECT Customer.CustomerID, Customer.FirstName, Customer.Phone, Orders.OrderID, Orders.StartDate FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID; I hope this helps. Thanks "Brian Bastl" wrote: Hi Ron, if you in fact pasted the rowsource below, then I see a trailing space after ListBox0. Beyond that, I can't tell without more info. If that doesn't solve it, then perhaps you can post the SQL for ListBox0. I do see one potential problem: If [Customer Query].Expr1 aliases the customer id, then you still won't get all of the customers with the same name, since an id is supposed to be unique to each customer. Brian "Ron Weaver" wrote in message ... Brian Here is the SQL statement entered into the rowsource of ListBox2. You will notice I am using Expr1 instead of CustomerID, this is because I want to pull all customers with the same name. When I try to save the following statement I get the message: "Characters found after the end of SQL statement". Do you see a problem with it? SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer Query].CustPhone, [Customer Query].OrderID FROM [Customer Query]; WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 )) "Brian Bastl" wrote: Hi Ron, Basically, if you have the customerid as the bound column in the rowsource for the first listbox, your rowsource for the second listbox would be something like: SELECT OrderID, OrderDate, Whatever else FROM [Orders Table] WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1)) Then in the After Update event procedure of Listbox1, you'd requery Listbox2: Private Sub Listbox1_AfterUpdate() Me.Listbox2.Requery End Sub There will be no need for macros or command buttons. If this example doesn't help, can you copy and paste the rowsources for both listboxes as well as the form name? HTH, Brian "Ron Weaver" wrote in message ... Brian, I tried to fiqure out this filter thing. Can you help me with some code and where to put it. Now the second part: If I put a command button on the first list and tie it to a requery macro, and put that macro into the AfterUpdate event of the first list box, does that sound right? "Brian Bastl" wrote: Hi Ron, I'd use a second listbox filtered on the customers id. Then you'd just need to issue a requery on the second listbox in the AfterUpdate event of the first listbox. Brian "Ron Weaver" wrote in message ... Hi, this is my first project and everyone has been very helpful to me. Thank you. I have one more hurdle, if someone can help. I have a list box which pulls customer orders as the result of a date search. When I select a customer in this list, I would like to have the option to push a command button and have this list box, or a second list box display all customer orders with that name. I have created a query for this process, with the fields in it I need, but I don't know where to go from here. |
#12
|
|||
|
|||
Need selection in one list box to pull query info into another
Hi
That's correct. List59 is #1 and List12 is #2. I'm leaving now for a little while myself. Thanks "Brian Bastl" wrote: Hi Ron, Haven't been ignoring you. Just got home. Are you saying that List59 is on one form and List12 is on another? Brian "Ron Weaver" wrote in message ... Brian Ok, I have changed the field from Expr1 to CostomerID. I got by the WHERE statement problem (had a semi colon in there). This where I am now. I believe everything is in there correctly, But when I select a customer in Listbox1 I get the following: Compile Error, Method or Data Member Not Found. The problem is in the after update statement in listbox1 (List59).In the Me.List12.Requery the .List12 is highlighted. List12 is my Listbox2. As you requested earlier I have included the rowsource for both listboxes and there form names. ListBox1(59) Form "OrderDateForm" SELECT [Order Date Query].CustomerID, [Order Date Query].StartDate, [Order Date Query].Expr1, [Order Date Query].CustPhone, [Order Date Query].OrderID FROM [Order Date Query]; ListBox2 (List12) Form "CustomerOrders" SELECT Customer.CustomerID, Customer.FirstName, Customer.Phone, Orders.OrderID, Orders.StartDate FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID; I hope this helps. Thanks "Brian Bastl" wrote: Hi Ron, if you in fact pasted the rowsource below, then I see a trailing space after ListBox0. Beyond that, I can't tell without more info. If that doesn't solve it, then perhaps you can post the SQL for ListBox0. I do see one potential problem: If [Customer Query].Expr1 aliases the customer id, then you still won't get all of the customers with the same name, since an id is supposed to be unique to each customer. Brian "Ron Weaver" wrote in message ... Brian Here is the SQL statement entered into the rowsource of ListBox2. You will notice I am using Expr1 instead of CustomerID, this is because I want to pull all customers with the same name. When I try to save the following statement I get the message: "Characters found after the end of SQL statement". Do you see a problem with it? SELECT [Customer Query].Expr1, [Customer Query].StartDate, [Customer Query].CustPhone, [Customer Query].OrderID FROM [Customer Query]; WHERE ((([Customer Query].[Expr1])=Forms!OrderDateForm!ListBox0 )) "Brian Bastl" wrote: Hi Ron, Basically, if you have the customerid as the bound column in the rowsource for the first listbox, your rowsource for the second listbox would be something like: SELECT OrderID, OrderDate, Whatever else FROM [Orders Table] WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1)) Then in the After Update event procedure of Listbox1, you'd requery Listbox2: Private Sub Listbox1_AfterUpdate() Me.Listbox2.Requery End Sub There will be no need for macros or command buttons. If this example doesn't help, can you copy and paste the rowsources for both listboxes as well as the form name? HTH, Brian "Ron Weaver" wrote in message ... Brian, I tried to fiqure out this filter thing. Can you help me with some code and where to put it. Now the second part: If I put a command button on the first list and tie it to a requery macro, and put that macro into the AfterUpdate event of the first list box, does that sound right? "Brian Bastl" wrote: Hi Ron, I'd use a second listbox filtered on the customers id. Then you'd just need to issue a requery on the second listbox in the AfterUpdate event of the first listbox. Brian "Ron Weaver" wrote in message ... Hi, this is my first project and everyone has been very helpful to me. Thank you. I have one more hurdle, if someone can help. I have a list box which pulls customer orders as the result of a date search. When I select a customer in this list, I would like to have the option to push a command button and have this list box, or a second list box display all customer orders with that name. I have created a query for this process, with the fields in it I need, but I don't know where to go from here. |
#13
|
|||
|
|||
Need selection in one list box to pull query info into another
Brian
I just took a look at that SQL statement I sent you for List12 (Listbox#2) That is not right. Here is the rowsource fo that: SELECT [Customer Query].CustomerID, [Customer Query].Expr1, [Customer Query].Phone, [Customer Query].OrderID, [Customer Query].StartDate FROM [Customer Query] WHERE ((([Customer Query].CustomerID)=[Forms]![OrderDateForm]![Listbox59])); I don't know where that came from. SORRY "Brian Bastl" wrote: Hi Ron, Basically, if you have the customerid as the bound column in the rowsource for the first listbox, your rowsource for the second listbox would be something like: SELECT OrderID, OrderDate, Whatever else FROM [Orders Table] WHERE ((([Orders Table].[CustomerID]) = Forms!MyForm!Listbox1)) Then in the After Update event procedure of Listbox1, you'd requery Listbox2: Private Sub Listbox1_AfterUpdate() Me.Listbox2.Requery End Sub There will be no need for macros or command buttons. If this example doesn't help, can you copy and paste the rowsources for both listboxes as well as the form name? HTH, Brian "Ron Weaver" wrote in message ... Brian, I tried to fiqure out this filter thing. Can you help me with some code and where to put it. Now the second part: If I put a command button on the first list and tie it to a requery macro, and put that macro into the AfterUpdate event of the first list box, does that sound right? "Brian Bastl" wrote: Hi Ron, I'd use a second listbox filtered on the customers id. Then you'd just need to issue a requery on the second listbox in the AfterUpdate event of the first listbox. Brian "Ron Weaver" wrote in message ... Hi, this is my first project and everyone has been very helpful to me. Thank you. I have one more hurdle, if someone can help. I have a list box which pulls customer orders as the result of a date search. When I select a customer in this list, I would like to have the option to push a command button and have this list box, or a second list box display all customer orders with that name. I have created a query for this process, with the fields in it I need, but I don't know where to go from here. |
#14
|
|||
|
|||
Need selection in one list box to pull query info into another
Just wanted to say Thanks for your response. I am going to try the combo.
"Klatuu" wrote: First, is your list box a multiselect list box? If not, I suggest you change it to a combo. It is easier to use as a parameter for the query you will need to pull the data you want. If it is a multiselect listbox, then the way to do that is to build a Where condition for your query based on the selections in the list box. This, however, is pretty code intensive. First you have to determine which rows in the list box are selected, and build a string that can be used as an SQL WHERE clause. Here is a function I use for that. It will seem a little strange, because in the form I am using it, there are six list boxes and none or all of them may have selections made: 'Set up the WhereCondition Argument for the reports Dim varItem As Variant Dim strWhere As String Dim ctl As Control Set ctl = Me.Controls(strControl) Select Case ctl.ItemsSelected.Count Case 0 'Include All strWhere = "" Case 1 'Only One Selected strWhere = "= '" & _ ctl.ItemData(ctl.ItemsSelected(0)) & "'" Case Else 'Multiple Selection strWhere = " IN (" With ctl For Each varItem In .ItemsSelected strWhere = strWhere & "'" & .ItemData(varItem) & "', " Next varItem End With strWhere = Left(strWhere, Len(strWhere) - 2) & ")" End Select BuildWhereCondition = strWhere End Function Now, I have to add it to the query to do the filtering. Notice in this case, I am using HAVING because it is a Totals query. What I do here is use a stored query, read in its SQL string, and modify it with the Replace function, and store it to another query so I don't muck up the original, and the report uses this query as its recordsource: Set dbf = CurrentDb Set qdfs = dbf.QueryDefs Set qdfXl = CurrentDb.QueryDefs(strXlQuery) strSQL = qdfXl.SQL 'Delete the old query in case an error left it hanging For Each qdf In qdfs If qdf.Name = "_BPOTemp" Then qdfs.Delete qdf.Name Exit For End If Next qdf If Len(strWhere) 0 Then 'This keeps the HAVING clause that is common to all versions of the report. strWhere = "HAVING " & strWhere & " AND " strSQL = Replace(strSQL, "HAVING ", strWhere) Set qdf = dbf.CreateQueryDef("_BPOTemp", strSQL) "Ron Weaver" wrote: Hi, this is my first project and everyone has been very helpful to me. Thank you. I have one more hurdle, if someone can help. I have a list box which pulls customer orders as the result of a date search. When I select a customer in this list, I would like to have the option to push a command button and have this list box, or a second list box display all customer orders with that name. I have created a query for this process, with the fields in it I need, but I don't know where to go from here. |
#15
|
|||
|
|||
Need selection in one list box to pull query info into another
Just wanted to say Thanks for your response. I am going to try the combo.
"Klatuu" wrote: First, is your list box a multiselect list box? If not, I suggest you change it to a combo. It is easier to use as a parameter for the query you will need to pull the data you want. If it is a multiselect listbox, then the way to do that is to build a Where condition for your query based on the selections in the list box. This, however, is pretty code intensive. First you have to determine which rows in the list box are selected, and build a string that can be used as an SQL WHERE clause. Here is a function I use for that. It will seem a little strange, because in the form I am using it, there are six list boxes and none or all of them may have selections made: 'Set up the WhereCondition Argument for the reports Dim varItem As Variant Dim strWhere As String Dim ctl As Control Set ctl = Me.Controls(strControl) Select Case ctl.ItemsSelected.Count Case 0 'Include All strWhere = "" Case 1 'Only One Selected strWhere = "= '" & _ ctl.ItemData(ctl.ItemsSelected(0)) & "'" Case Else 'Multiple Selection strWhere = " IN (" With ctl For Each varItem In .ItemsSelected strWhere = strWhere & "'" & .ItemData(varItem) & "', " Next varItem End With strWhere = Left(strWhere, Len(strWhere) - 2) & ")" End Select BuildWhereCondition = strWhere End Function Now, I have to add it to the query to do the filtering. Notice in this case, I am using HAVING because it is a Totals query. What I do here is use a stored query, read in its SQL string, and modify it with the Replace function, and store it to another query so I don't muck up the original, and the report uses this query as its recordsource: Set dbf = CurrentDb Set qdfs = dbf.QueryDefs Set qdfXl = CurrentDb.QueryDefs(strXlQuery) strSQL = qdfXl.SQL 'Delete the old query in case an error left it hanging For Each qdf In qdfs If qdf.Name = "_BPOTemp" Then qdfs.Delete qdf.Name Exit For End If Next qdf If Len(strWhere) 0 Then 'This keeps the HAVING clause that is common to all versions of the report. strWhere = "HAVING " & strWhere & " AND " strSQL = Replace(strSQL, "HAVING ", strWhere) Set qdf = dbf.CreateQueryDef("_BPOTemp", strSQL) "Ron Weaver" wrote: Hi, this is my first project and everyone has been very helpful to me. Thank you. I have one more hurdle, if someone can help. I have a list box which pulls customer orders as the result of a date search. When I select a customer in this list, I would like to have the option to push a command button and have this list box, or a second list box display all customer orders with that name. I have created a query for this process, with the fields in it I need, but I don't know where to go from here. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query data based on List Box multiple selection | titlepusher | Running & Setting Up Queries | 3 | June 9th, 2009 06:43 PM |
Multiple filter query of form | truepantera | Using Forms | 6 | August 4th, 2005 08:26 AM |
List box pull up selection in combo box | [email protected] | Using Forms | 1 | March 14th, 2005 10:35 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |