Search as you type - Combo box

[lateral]

Hi Guys,

This is my first post to this forum....I'm somewhat of a newbie to Access 2007.

Here is my question.

I am trying to implement a "search as you type" on a Combo Box that is on a form.

I have the following query attached to a Combox Box called "PartID":

SELECT DISTINCTROW Parts.*, Parts.Web_Category, Parts.PartDescription, Parts.UnitPrice, Parts.KitID, Parts.DefaultQty, Parts.Notes, Parts.PartName


FROM Parts
WHERE (((Parts.PartName) Like '*cam*') AND ((Parts.Inactive)=0))
ORDER BY Parts.PartName;

Note that I have added the "Like '*cam*'" condition so that only parts that contain the text "cam" anywhere in them will be displayed in the Combo box list. This works perfectly except that I want the user to be able to type the "cam" or any other string into the Combo box.

I have a question, can I somehow "parameterize" the "Like '*cam*'" condition part of the query so that it "takes" the text that I want to use to filter the records from the combo box that it is connected to???

Thanks again for your help and I'm sorry that I'm struggling with this...

Regards
Greg

[June7]

Why are you using Parts.* in the query and then explicitly selecting fields?

Is this what you want: http://www.datapigtechnologies.com/f...combobox2.html

[lateral]

Hi June7,

I inherited this database and have been modifying it in order to learn Access 2007.

I have no idea why it is using Parts.* and explicitly selecting fields.....I am assuming that you are asking me why as the query should not be doing both?

The link you sent me is not what I am after.

I can get the Combo box to display all records who's partname starts with a user entered value. ie: is the user presses "a", the combo box list will jump to the first occurance of partnames starting with "a".

I want the user to be able to type in a value in the combo box such as "cam" and the list will show all partnames that have the text "cam" anywhere in the partname.....

Basically, I want the functionality of the "like" commands.

After finishing this post, I found the following post that looked as though it did what I wanted but I can't get it to work...


https://www.accessforums.net/access/...tml#post261493


The following lines of code achieves what you desire -
Add a combo box to your form, set its rowsource and set the AutoExpand property (on data tab of combox property sheet) to No.
I have used a demo table having all the country names. You need to alter the code for use in your case.
Code:
Private Sub Combo4_Change()
Dim strSQL As String
If Len(Me.Combo4.Text) > 0 Then
strSQL = "SELECT AllCountries.CountryName FROM AllCountries WHERE (((AllCountries.CountryName) Like '*" & Me.Combo4.Text & "*')) ORDER BY AllCountries.CountryName;"
Else
strSQL = "SELECT AllCountries.CountryName FROM AllCountries ORDER BY AllCountries.CountryName;" 'This is the default row source of combo box
End If
Me.Combo4.RowSource = strSQL
Me.Combo4.Dropdown
End Sub


This is what I changed the code to:

I am trying to implement this bit of code on my existing Combo Box but it does seem to work as it should....

Here is my Row Source:

SELECT DISTINCTROW Parts.*, Parts.Web_Category, Parts.PartDescription, Parts.UnitPrice, Parts.KitID, Parts.DefaultQty, Parts.Notes, Parts.PartName FROM Parts WHERE (((Parts.Inactive)=0)) ORDER BY Parts.PartName;

And the Control Source is "PartID".

Here is the Event (Change) stuff:

Private Sub PartID_Change()

Dim strSQL As String

If Len(Me.PartID.Text) > 0 Then
strSQL = "SELECT Parts.PartsID, Parts.PartName FROM Parts WHERE (((Parts.PartName) Like '*" & Me.PartID.Text & "*')) ORDER BY Parts.PartName;"
Else
strSQL = "SELECT Parts.PartsID, Parts.PartName FROM Parts ORDER BY Parts.PartName;" 'This is the default row source of combo box
End If
Me.PartID.RowSource = strSQL
Me.PartID.Dropdown

End Sub



I really appreciate your help as this thing is driving me nuts..

Regards
Greg

[orange]

Greg,

If you are trying to learn, I recommend you work through this tutorial. You will learn some principles and concepts of database.
There is a sample problem, and a solution.

Good luck.

[June7]

I think you are on the right track. After setting the combobox RowSource property, must requery.

Me.PartID.Requery

Maybe this will help http://allenbrowne.com/AppFindAsUType.html

[lateral]

Thanks guys

I'm struggling with this but will definitely look through the tutorials.

June7, when you say that I am on the right track and I must Me.PartID.Requery, are you saying that I need to add the requery like the following or something else?:

If Len(Me.PartID.Text) > 0 Then
strSQL = "SELECT Parts.PartsID, Parts.PartName FROM Parts WHERE (((Parts.PartName) Like '*" & Me.PartID.Text & "*')) ORDER BY Parts.PartName;"
Else
strSQL = "SELECT Parts.PartsID, Parts.PartName FROM Parts ORDER BY Parts.PartName;" 'This is the default row source of combo box
End If
Me.PartID.RowSource = strSQL
Me.PartID.Dropdown
Me.PartID.Requery


Because if I do the above, I get a Runtime error 2118 telling me "you must save the current field before you run the requery action".

Thanks

Regards
Greg

[June7]

Combobox used to input search criteria must be UNBOUND, otherwise you change the record.

[lateral]

How do I make a combox box that is already "bound" "unbound"?

[June7]

Make the ControlSource property blank, delete whatever is in there.

Is purpose of this combobox just to input search parameter? Or is it intended for data entry to record?

[lateral]

Hi June7

It is used to enable the user to select which record they desire that will then be "written" to another table.

Would you like me to put together a sample database?

[June7]

Then sounds like the answer is it is for input of search criteria, not data entry. Why are you copying record to another table?

You may if you want.

[lateral]

Hi June7

I originally downloaded a template from the Microsoft website that was called" Workorder" or something similar.

I have been modifying/updating it for a friend of mine who runs a small one man automotive parts business as a hobby.

Basically, it enables the user to attach parts to "Workorders" and a Workorder is attached to a Customer.

A Workorder can have many parts attached to it.

The Combo Box enables the user to select which part is to be attached to the Workorder.

I have attached a screen shot...

[June7]

Conventional approach would be form/subform arrangement. Main form bound to Order table and subform bound to OrderDetails table. Combobox in subform to select part and this will save directly into record. The form/subform arrangement will synchronize related records. No code required.

Odd thing about your form is that there are records which are blank except for an ID number. I don't understand this structure.

[lateral]

If you are referring to the "Select Part (Web Category)" column being blank then this can happen as that field in the parts record may be validly blank.

I have attached another screen shot of the form in design mode...

I'm having a problem trimming down the Backend/Front end so that it fits under the 2mb limit.

I

[June7]

Well, couldn't see the other columns because the dropdown is open and assumed they were also blank.

I still don't understand why you need code to 'write' record to another table.

Can attach more than one zip file, although one would be nice.