Query Improvement

[CLSkcab]

Background-
Front end: MS Access 2010/VBA
Back end: MS SQL Server 2008
Me: Scale 1 - 10 (10 being high) Access: 5 SQL: 4

I have a form/sub form combo that solicits the visitor for their company name (see attached).
After they press the "Look Up" button the query will list the company along with the company's
location. The Link Master Fields and Link Child Fields causes the query to work.

The client has asked if partial name could be entered instead the full company's name and list all companies. For example
the visitor instead of entering National Fabrication Company could enter National. They would
get something like this:

Active Y National Bearings Columbus
Active Y National Fabrication Company Cincinnati
Active Y National Fabrication Company St. Louis

Thanks in advanced for you help.

[Seth Schrock]

Probably the easiest method would be to have the list of possible companies be listed in a combobox that sorts them by name. The combobox has built in the lookup as you type.

A more complicated, but possibly more like what you are looking for, would be to have your lookup form have a textbox in the header that uses its On_Change event to constantly update the form's filter. The form would be viewed in Continuous form view. You could then double-click the company that you want and write the code so that the record you select gets entered as the company that the visitor is from.

[jforbes]

I use this:

Private Sub cmdApplyFilter_Click()
    Dim sSearch As String
    sSearch = Nz(Me.txtSearch.Value, "")
    If Len(sSearch) > 0 Then
        Me.CompanySub.Form.Filter = "Company LIKE '*" & sSearch & "*'"
        Me.CompanySub.Form.FilterOn = True
    Else
        Me!CompanySub.Form.FilterOn = False
    End If
End Sub

When doing this, there is no link between the Main and SubForm

It's tied to a button just because it makes people more comfortable, but you could easily attach it to the OnChange like Seth is talking about.

[CLSkcab]

What is me.companysub.form referring to?

[jforbes]

The Me.CompanySub.Form is getting a reference to the actual Form Object being displayed by the SubForm Control. So in this case it is the Form that is to be Filtered. You would need to replace CompanySub with the name of the Control for your SubForm. This might help or confuse you http://access.mvps.org/access/forms/frm0031.htm, it explains how to refer to MainForms and SubForms.

I just copied and pasted something I had and my SubForm Control was named CompanySub.

[CLSkcab]

Here is my code. I put in the "Look Up" button.

Private Sub cmdNext_Click()
Dim sSearch As String
    sSearch = Nz(Me.fCompany.Value, "")
    If Len(sSearch) > 0 Then
        Me.[signin subform].Form.Filter = "Company LIKE '*" & sSearch & "*'"
        Me.[signin subform].Form.FilterOn = True
    Else
       Me.[signin subform].Form.FilterOn = False
    End If
End Sub
 

It does not find anything.

[jforbes]

Hmmm... Here is a couple things to check/try:

Make sure your MainForm and SubForm do not have any linking as you will be taking over that functionality with the filtering. When you open the MainForm the SubForm should be showing you all it's records. (You can change that later if you would rather it not show any records to start with)

Hard code something for your sSearch variable that you know will return a recordset with records and see what it accomplishes.

[CLSkcab]

Got it to work. Had to remove the Link Master Fields and Link Child Fields values.