Open form with parameter query

[TomBP]

Hi,

I have a combobox in frm_Supplier. Users can scroll and click suppliers which will change the values to match the supplier selected.

If people start to type in the combobox though, sh*it starts to hit the fan. Apparently when entering after typing they change the values in tbl_Supplier.

To eliminate the combobox changing the table I was thinking about making use of a parameter query. Is it possible to show a parameter query when opening frm_Supplier in which the name from the supplier can be typed?

Greets,


Tom


A sample database has been attached.

 

[stopher]

Just remove make your combo unbound i.e. remove the control source reference from the combo.

hth
Chris

 

[missinglinq]

If people start to type in the combobox though, sh*it starts to hit the fan.

The only thing that happens when I start to type into the Combobox is that the AutoExpand feature kicks in, as it should, moving the Combobox to the selection matching the letters entered. If, however, you were to type in something that has no match, then leave the record, the supplier name for that record would be overwritten, perhaps the sh*it you refer to.

Comboboxes used to retrieve records, which is what you're doing here, have to be Unbound, as stopher said!

In Form Design View, select the Combobox, go to Properties - Data and delete Name1 from the Control Source Property box.

Linq ;0)>

 

[TomBP]

Thank you stopher for the solution and thanks missinglinq for elaborating.

I'm still struggling with the combobox to react as a parameter criteria though. The combobox will only autocomplete a result if the first characters which are typed match. Is it possible to autocomplete when starting to type in the middle?

 

[Galaxiom]

Is it possible to autocomplete when starting to type in the middle?

No. For that you need to set up a Search box that changes the recordsource query or applies a filter to the form.

The subject is covered regularly on this forum. Try Google with something like this:

searchbox site:access=programmers.co.uk

 

[Galaxiom]

I will elaborate a bit. As I said earlier, a listbox or combo cannot autocomplete from mid string.

To use a "find anwhere" search you need to be typing into a textbox. The OnChange Event Procedure of the textbox is used to apply a filter to a list in another object.

The simplest object to use is a Datasheet subform and simply apply a filter like this:

With Me.subformcontrolname.Form
   .Filter = "fieldname Like '*" & Me.Searchbox & "*'"
   .FilterOn = True
End With

The matching records will be shown in the subform. Then you can select a record and use code to put the value into the field or control on the main form.

However when using searchboxes I generally use Me.Searchbox & * so that the search as you type matches the beginning of the value. I also provide a Search button that searches anywhere. This provides the most versatility and speed.

Match the beginning is far less intensive for Access to run so it is fast. Moreover the user generally does know the beginning of what they want. When this turns up nothing they simply hit the Search button for the deeper search that is a bigger job for Access.

The subform can be made to look exactly like a listbox with headers by removing the extraneous components of the form. If the header is not desirable then the Datasheet simulation using Continuous Forms can be used instead.

Listboxes and combos can also be used as the object to show the matches. These have a major advantage in that they can be displayed on a continuous form. Unfortunately they cannot be directly filtered like a subform.

However the RowSource of the Listbox or Combo can be overridden by setting their Recordset Property to a recordset, which can be filtered. This is a very powerful way to use them.

 

[missinglinq]

...Is it possible to show a parameter query when opening frm_Supplier in which the name from the supplier can be typed?

In direct response to your original question, and in the database file you attached:

• Go to Queries
• Click on Create Query in Design View
• Close the Show Table Box
• Go to View - SQL View
• "Select" should be highlighted
• Paste in

SELECT tbl_Supplier.[Name 1], tbl_Supplier.Count_TotalPositions, tbl_Supplier.Count_Auto, tbl_Supplier.Count_Man, tbl_Supplier.Count_VR, tbl_Supplier.Count_NVR
FROM tbl_Supplier
WHERE (((tbl_Supplier.[Name 1]) Like "*" & [Enter Supplier Characters to Search by: ] & "*"));

• Save and Close the Query
• Name it tbl_SupplierQuery just like that, no spaces!

Now, in code module of frm_Supplier

Private Sub Form_Load()
 DoCmd.OpenQuery "tbl_SupplierQuery"
End Sub

When thew Form opens, the Parameter Box will pop up, you can enter one or more characters, and any Suppliers with those characters in their names, in that order, will populate the Query View that will pop up.

Depending on how your Form is set up, the Query View may open behind the Form. To work around this, what I'd actually do is to open the Query from a Command Button:

In your form's Header Section, create a Command Button, name it SearchButton, and use this code in its OnClick event

Private Sub SearchButton_Click()
 DoCmd.OpenQuery "tbl_SupplierQuery"
End Sub

Linq ;0)>