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
|
|||
|
|||
Use wildcard in combo box with multiple strings
Add the field to the query then. Just because a field is in the query
doesn't mean you have to display it on the form. However, if you want to filter by it, it has to be in the query... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Rebecca_EIC" wrote in message ... I have a summary report by BatchId and it is filtering fine. The detail report that does not have BatchId in the output is giving me an error - "Enter Parameter Value tblBatchPickList.BatchID" (tblBatchPickList.BatchID is the field I am trying to filter on.) I am assuming it is asking because it can not find that field. The report's data is based on a embedded query that has tblBatchPickList in the table list, but not in the output of the query. "Douglas J. Steele" wrote: Just because the value doesn't appear on the report doesn't mean you can't use the field to filter what rows are in the RecordSource for the report. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Rebecca_EIC" wrote in message ... New spin on the old question... I have a report that works off this same text box. The report uses these batch ids to LIMIT the records but it does not USE the batch id field in the output so I can't filter by that field, right? The report takes these multiple batch ids and cross references them to a materials list and then groups and subtotals by the raw material item (not the batch id). What do I do now? How do I use multiple strings in a combo box to limit records in a report but not output that field in the report itself? "Rebecca_EIC" wrote: Perfect!!! I knew I must have been missing something with the [Notes] reference. Thank you all! "Douglas J Steele" wrote: You must have the name of the field against which you're comparing: that's what Allen meant by [Notes] in his original example. Replace [Notes] with the appropriate field name. The string you use as a filter must repeat the name of the field for each comparison. That means you need something like: Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR ([Notes] Like '*0515*') OR ([Notes] Like '*0516*')" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rebecca_EIC" wrote in message ... I can not use the multi-select list box - there are just too many user defined possibilities (although I printed the article for a different project - thanks.) I tried the code you provided for multiple keywords and I had two issues 1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " ...what does the [Notes] mean? For it to work I had to revise the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR " (Per the Locals window this is working fine) 2) When I try to run the code and get to the line "Me.Filter = Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You can't assign a value to this object." I can not figure out what is wrong...the Left function give the correct sting (I tested that separately) PS I made sure the form is set to allow filters, just in case that was the problem. "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
#12
|
|||
|
|||
Use wildcard in combo box with multiple strings
That is exactly my problem. I CAN'T add it to the query. The query is a
summary query and the filter will not work if I add the field as a where or expression field and I CAN'T add it as a group or it will mess up the report results. My problem is I can not add the field to the query and I can not filter if the field is not in the query. My question is - How do I query for a report for multiple wildcards strings in a text box when I can not have the text box field in the report's query? "Douglas J. Steele" wrote: Add the field to the query then. Just because a field is in the query doesn't mean you have to display it on the form. However, if you want to filter by it, it has to be in the query... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Rebecca_EIC" wrote in message ... I have a summary report by BatchId and it is filtering fine. The detail report that does not have BatchId in the output is giving me an error - "Enter Parameter Value tblBatchPickList.BatchID" (tblBatchPickList.BatchID is the field I am trying to filter on.) I am assuming it is asking because it can not find that field. The report's data is based on a embedded query that has tblBatchPickList in the table list, but not in the output of the query. "Douglas J. Steele" wrote: Just because the value doesn't appear on the report doesn't mean you can't use the field to filter what rows are in the RecordSource for the report. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Rebecca_EIC" wrote in message ... New spin on the old question... I have a report that works off this same text box. The report uses these batch ids to LIMIT the records but it does not USE the batch id field in the output so I can't filter by that field, right? The report takes these multiple batch ids and cross references them to a materials list and then groups and subtotals by the raw material item (not the batch id). What do I do now? How do I use multiple strings in a combo box to limit records in a report but not output that field in the report itself? "Rebecca_EIC" wrote: Perfect!!! I knew I must have been missing something with the [Notes] reference. Thank you all! "Douglas J Steele" wrote: You must have the name of the field against which you're comparing: that's what Allen meant by [Notes] in his original example. Replace [Notes] with the appropriate field name. The string you use as a filter must repeat the name of the field for each comparison. That means you need something like: Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR ([Notes] Like '*0515*') OR ([Notes] Like '*0516*')" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rebecca_EIC" wrote in message ... I can not use the multi-select list box - there are just too many user defined possibilities (although I printed the article for a different project - thanks.) I tried the code you provided for multiple keywords and I had two issues 1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " ...what does the [Notes] mean? For it to work I had to revise the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR " (Per the Locals window this is working fine) 2) When I try to run the code and get to the line "Me.Filter = Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You can't assign a value to this object." I can not figure out what is wrong...the Left function give the correct sting (I tested that separately) PS I made sure the form is set to allow filters, just in case that was the problem. "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
#13
|
|||
|
|||
Use wildcard in combo box with multiple strings
I don't understand what you're trying to do, then.
If you're wanting to limit what's reported, you have to somehow include the criteria. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rebecca_EIC" wrote in message ... That is exactly my problem. I CAN'T add it to the query. The query is a summary query and the filter will not work if I add the field as a where or expression field and I CAN'T add it as a group or it will mess up the report results. My problem is I can not add the field to the query and I can not filter if the field is not in the query. My question is - How do I query for a report for multiple wildcards strings in a text box when I can not have the text box field in the report's query? "Douglas J. Steele" wrote: Add the field to the query then. Just because a field is in the query doesn't mean you have to display it on the form. However, if you want to filter by it, it has to be in the query... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Rebecca_EIC" wrote in message ... I have a summary report by BatchId and it is filtering fine. The detail report that does not have BatchId in the output is giving me an error - "Enter Parameter Value tblBatchPickList.BatchID" (tblBatchPickList.BatchID is the field I am trying to filter on.) I am assuming it is asking because it can not find that field. The report's data is based on a embedded query that has tblBatchPickList in the table list, but not in the output of the query. "Douglas J. Steele" wrote: Just because the value doesn't appear on the report doesn't mean you can't use the field to filter what rows are in the RecordSource for the report. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Rebecca_EIC" wrote in message ... New spin on the old question... I have a report that works off this same text box. The report uses these batch ids to LIMIT the records but it does not USE the batch id field in the output so I can't filter by that field, right? The report takes these multiple batch ids and cross references them to a materials list and then groups and subtotals by the raw material item (not the batch id). What do I do now? How do I use multiple strings in a combo box to limit records in a report but not output that field in the report itself? "Rebecca_EIC" wrote: Perfect!!! I knew I must have been missing something with the [Notes] reference. Thank you all! "Douglas J Steele" wrote: You must have the name of the field against which you're comparing: that's what Allen meant by [Notes] in his original example. Replace [Notes] with the appropriate field name. The string you use as a filter must repeat the name of the field for each comparison. That means you need something like: Me.Filter = "([Notes] Like '*0506*') OR ([Notes] Like '*0517*') OR ([Notes] Like '*0515*') OR ([Notes] Like '*0516*')" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Rebecca_EIC" wrote in message ... I can not use the multi-select list box - there are just too many user defined possibilities (although I printed the article for a different project - thanks.) I tried the code you provided for multiple keywords and I had two issues 1) the code... strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " ...what does the [Notes] mean? For it to work I had to revise the code to be... strWhere = strWhere & "Like ""*" & strWord & "*"" OR " (Per the Locals window this is working fine) 2) When I try to run the code and get to the line "Me.Filter = Left(strWhere, lngLen)" I get a run time error "run-time error '2448': You can't assign a value to this object." I can not figure out what is wrong...the Left function give the correct sting (I tested that separately) PS I made sure the form is set to allow filters, just in case that was the problem. "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
#14
|
|||
|
|||
Use wildcard in combo box with multiple strings
Ok I have tried to understand whats going on here but getting a little confused
I have a Table with Products (text) then a correspnding form with a subform where you can use a Combo box to select the product - then shows price/cost and so on. example product - Great big caravan I want the combo box to limit the what is in the drop down list when I type car - so like a query automaticly does the following *car* for me, but I have become very confused with this post Many thanks "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
#15
|
|||
|
|||
Use wildcard in combo box with multiple strings
OK, you have a Products table and a corresponding form, but what is the
subform? What is the purpose of the Products form? Is the combo box a search combo box (to find a particular record), or is it to add Product information to another record (such as Invoice)? The combo box can be made to limit the list that drops down, but in what way do you wish to limit it? "CP" wrote in message ... Ok I have tried to understand whats going on here but getting a little confused I have a Table with Products (text) then a correspnding form with a subform where you can use a Combo box to select the product - then shows price/cost and so on. example product - Great big caravan I want the combo box to limit the what is in the drop down list when I type car - so like a query automaticly does the following *car* for me, but I have become very confused with this post Many thanks "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
#16
|
|||
|
|||
Use wildcard in combo box with multiple strings
Ok I have a products table, customerproducts table then a customer table
The customerproducts table looks up data in the product table - ref productID in both tables The customer table and the customerproducts table contains custID as a ref There fore I have productID and CustomerID in there respective tables My form has been limited to customers using a combo/list selection box - then my subform uses customerproducts to show product, and price - using original data retrieved from products table. Now I select my customer which shows address tel etc. I then choose the product description box (on the subform) to add a product related to that customer. I have 700 products and wish to type anypart of the name in the combo box and it show only those records that match so I can then choose it Customer - CustID, Name, Tel and so on CustProduct - CustID, ProductID, CustPrice Product - ProductID, Description, List Price, Cost Price so the productID in CustProduct retrieves the description fields up so I can show it in my subform then I can enter the customers unique price for that item. thanks for the reply "BruceM" wrote: OK, you have a Products table and a corresponding form, but what is the subform? What is the purpose of the Products form? Is the combo box a search combo box (to find a particular record), or is it to add Product information to another record (such as Invoice)? The combo box can be made to limit the list that drops down, but in what way do you wish to limit it? "CP" wrote in message ... Ok I have tried to understand whats going on here but getting a little confused I have a Table with Products (text) then a correspnding form with a subform where you can use a Combo box to select the product - then shows price/cost and so on. example product - Great big caravan I want the combo box to limit the what is in the drop down list when I type car - so like a query automaticly does the following *car* for me, but I have become very confused with this post Many thanks "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
#17
|
|||
|
|||
Use wildcard in combo box with multiple strings
If I understand correctly, CustProduct is a junction table between Customer
and Product. A typical setup is a main form based on the Customer table and a subform based on CustProduct. A combo box on the subform is bound to ProductID; its row source is based on the Product table. Setting the combo box Auto Expand property to Yes may do what you need. If your intention is to filter a very large list according to the first few characters you type into the combo box, see: http://allenbrowne.com/ser-32.html For more information about combo boxes: http://allenbrowne.com/ser-03.html "CP" wrote in message ... Ok I have a products table, customerproducts table then a customer table The customerproducts table looks up data in the product table - ref productID in both tables The customer table and the customerproducts table contains custID as a ref There fore I have productID and CustomerID in there respective tables My form has been limited to customers using a combo/list selection box - then my subform uses customerproducts to show product, and price - using original data retrieved from products table. Now I select my customer which shows address tel etc. I then choose the product description box (on the subform) to add a product related to that customer. I have 700 products and wish to type anypart of the name in the combo box and it show only those records that match so I can then choose it Customer - CustID, Name, Tel and so on CustProduct - CustID, ProductID, CustPrice Product - ProductID, Description, List Price, Cost Price so the productID in CustProduct retrieves the description fields up so I can show it in my subform then I can enter the customers unique price for that item. thanks for the reply "BruceM" wrote: OK, you have a Products table and a corresponding form, but what is the subform? What is the purpose of the Products form? Is the combo box a search combo box (to find a particular record), or is it to add Product information to another record (such as Invoice)? The combo box can be made to limit the list that drops down, but in what way do you wish to limit it? "CP" wrote in message ... Ok I have tried to understand whats going on here but getting a little confused I have a Table with Products (text) then a correspnding form with a subform where you can use a Combo box to select the product - then shows price/cost and so on. example product - Great big caravan I want the combo box to limit the what is in the drop down list when I type car - so like a query automaticly does the following *car* for me, but I have become very confused with this post Many thanks "Allen Browne" wrote: The query will not be able to interpret multiple criteria like that, so you will need to generate the filter string in code. For individual values (not wildcards), you could use an unbound multi-select list box on your form, and build the string as shown in this article: Use a multi-select list box to filter a report at: http://allenbrowne.com/ser-50.html The article explains how to build the WhereCondition for a report, but building the Filter for a form is identical. Another option would be just to type all the values into a text box separated by spaces, and use code like this to build the filter string: Private Sub txtKeywords_AfterUpdate() Dim strWhere As String Dim strWord As String Dim varKeywords As Variant 'Array of keywords. Dim i As Integer Dim lngLen As Long If Me.Dirty Then 'Save first. Me.Dirty = False End If If IsNull(Me.txtKeywords) Then 'Show all if blank. If Me.FilterOn Then Me.FilterOn = False End If Else varKeywords = Split(Me.txtKeywords, " ") If UBound(varKeywords) = 99 Then '99 words max. MsgBox "Too many words." Else 'Build up the Where string from the array. For i = LBound(varKeywords) To UBound(varKeywords) strWord = Trim$(varKeywords(i)) If strWord vbNullString Then strWhere = strWhere & "([Notes] Like ""*" & strWord & "*"") OR " End If Next lngLen = Len(strWhere) - 4 'Without trailing " OR ". If lngLen 0 Then Me.Filter = Left(strWhere, lngLen) Me.FilterOn = True Else Me.FilterOn = False End If End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Rebecca_EIC" wrote in message ... I have a form with a combo box (BatchID) that when changed will populate the detail in the form. I want to be able to use multiple wildcard strings to search the database for the detail. For example, I want to enter *0506* or *0515* or *0516* and get all records where there is a 0506, 0515 OR 0516 in the BatchId field. This works fine in the query directly, but when I put the text in the combo box, it will not bring back the data. I have tried [Forms]![frmBatch Pick List]![BatchID] Like "*"&[Forms]![frmBatch Pick List]![BatchID]&"*" Like [Forms]![frmBatch Pick List]![BatchID] but they do not work if I add multiple wildcard requests to the BatchID combo box. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
You cancelled the previous action error on certain combo box selec | Eamonn | Using Forms | 8 | March 11th, 2006 04:51 PM |
search button to report based on multiple combo boxs ( combo criteria based upon queries ) | [email protected] | Using Forms | 6 | October 5th, 2005 05:02 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Filtering records using multiple combo boxes | Mark Senibaldi | Using Forms | 0 | June 17th, 2004 03:55 PM |
Filtering Records using multiple combo boxes | Mark Senibaldi | Using Forms | 0 | June 17th, 2004 03:51 PM |