"Like" Query Function in Form

[Reese]

I have a form that I am trying to integrate a search function into.

Here is my goal: (1) Be able to enter in a part of an organization's name (2) have a "like" query pull up all of the organizations that match the given parameters into a combo box (3) select which organization name is the desired entry from said combo box and (4) have the rest of the form's fields display the appropriate data associated with that organization.

Here's what I've done so far:
-Create a like query that does the search in a table form
-Create a form that runs the query and displays these results in a combo box

Here are my problems:
-When opening the form, Access immediately requires a parameter to be entered for the query. I want this to only happen when clicking on the combo box associated with the query.
-The other fields don't display the appropriate data after I select the proper organization name in the combo box.

I realize that this whole thing would be easier if I scrapped the query and just use a combo box for the organization name. The reason I want the like query is because at times the users may not know the exact phrasing of the organization name, which is required for this technique to work (e.g. if a past entry is phrased as "The Willow Point" but the user types "Willow Point" it won't work).

How should I go about this? Thanks.

 

[CJ_London]

Re: "Like" Query Funtion in Form

What control are you using to enter say 'willow'?

I'm not saying this is the best way to solve your problem but from the information provided I would do the following.

1. remove the rowsource from the combo box
2. in the on click event of the combobox has something like the following:
combobox.rowsource="Select fields FROM YourTble where Orgname like '" & inputbox("Enter Approx Name") & "'"

 

[boblarson]

Need to use the WILDCARD character with LIKE. So CJ's suggestion needs asterisks

combobox.rowsource="Select fields FROM YourTble where Orgname like '*" & inputbox("Enter Approx Name") & "*'"

If you don't include the Wildcard character it simply becomes a literal. So, if you don't include the Wildcard then using Willow will only return if the name is EXACTLY Willow.

 

[CJ_London]

oops!

My mistake, well spotted Bob

 

[Reese]

Okay, so I kind of understand what you guys are saying; in fact the SQL of my query is pretty much exactly what you guys suggested, just without the "combobox.rowsource" of course.

I'm not sure this is the right solution, however. I've tried playing around with this and it doesn't seem to be working. It either has the same results as I mentioned in my initial post, comes up with nothing, or says that I need to save it as a Macro.

The form that I had created that seemed to work best was a "test" form that uses the query as the source. My end goal is to place it in a larger, more complex form that has a table as the source. I know I'm not describing things very well but I hope someone can cobble together a solution.

 

[Reese]

I'm still at a loss as to how to go about this. Does anyone have any other suggestions? Or maybe walk me through in more detail the suggestion already provided (in case I did it wrong)? Any help would be greatly appreciated. Thanks.

 

[boblarson]

I'm still at a loss as to how to go about this. Does anyone have any other suggestions? Or maybe walk me through in more detail the suggestion already provided (in case I did it wrong)? Any help would be greatly appreciated. Thanks.

To fit in your original question about having a text box to type in, you can use this in your query's criteria:

Like "*" & [Forms]![FormNameHere]![TextBoxNameHere] & "*"

 

[CJ_London]

in case I did it wrong

Without knowing what you have done, that is a bit difficult! Can you detail what you have done exactly i.e. copy and paste your vba code, table and field names (so often the issue is a simple typo, missing comma etc) or post a db with your form and some relevant data

 

[Reese]

Without knowing what you have done, that is a bit difficult!

Yeah, sorry about that. Fortunately boblarson's suggestion worked:

To fit in your original question about having a text box to type in, you can use this in your query's criteria:

Like "*" & [Forms]![FormNameHere]![TextBoxNameHere] & "*"

I was able to then create a button in the form that would open up the query's form and that worked perfectly. Unfortunately I still have two problems to overcome.

Problem 1:

In the query there's the Organization and Client_ID fields. In table form all of the info appears appropriately. In the query's form I have the Organization field in a combo box so that I can select which of the query results I want. The Client_ID textbox, however, just displays the entry from the first organization that appeared in the query results.

How can I get the Client_ID field to change appropriately when I select an entry in the combo box?

Problem 2:

I need to have the resulting Client_ID from the query (after selecting the appropriate Organization) then set the Client_ID in the form that houses the textbox with the query's parameter, preferably by having the query form as a subform within the first form. I sure this should involve relationships but I'm not sure how to structure said relationship.

Thanks for the help so far!

 

[Reese]

Okay, so looking at the above post I realized it might be confusing to read. Here's the end chain of events that I'm looking for:

Textbox A in Form A --> Open Form B (which is based on the query) --> Query uses content of Textbox A in Form A as it's parameter --> Combo box in Form B displays Organization results from query --> User selects desired result in combo box --> **Client_ID textbox changes to reflect the selected Organization from the combo box --> Using a command or relationship, Client_ID in Form A is changed to match Client_ID in Form B.

The ** is where I'm at right now.

 

[CJ_London]

On the assumption that the combobox in form b has a rowsource of TheQuery then TheQuery should be something like

Select clientID, organisation FROM sometbl where organisation Like "*" & [Forms]![FormNameHere]![TextBoxNameHere] & "*"

The combobox in form b should have the no of columns set to 2, the bound column to 1 and the column widths to 0;2

With regards the clientID textboxes if you want to show the id then the controlsource is something!combobox and and if the name it is something!combobox.column(1) where something refers to the form b.

 

[Reese]

So close, CJ London!

Your suggestion wasn't quite working when I used the following control source for the client text box: "[Client Organization Search Form]!SearchCombo.column(1), which is what your suggestion translated to with my names plugged. It was just coming up as #Name? in the textbox.

However, when I changed the textbox to be unbound, then created an event procedure in the SearchCombo for AfterUpdate, it worked! At least partially. Now, after I select the appropriate organization, the Client_ID is what is visible in the combo box and the Organization is what is visible in the client text box.

Any suggestions?

 

[CJ_London]

Glad you got it working

Any suggestions?

Err, about what?!

If you don't want to see client id in the combo box, set the column width to 0?

 

[HiTechCoach]

Pardon me for jumping in here ...

I have an alternative method for your consideration that I find very user friendly. See: Search criteria

 

[Reese]

Oi... I haven't been able to return to this for the past week. Ironically mostly because I've been busy doing exactly what this database is supposed to do.

Anyway:

Err, about what?!

If you don't want to see client id in the combo box, set the column width to 0?

Yes, I did try that. Perhaps I did it wrong a week ago, but now it's working. The problem is that the Organization name (e.g. The Binghamton Zoo) from the 2nd column in the combo box is what's visible in the ClientID textbox. I need the Client_ID to be visible in the text box but it's not working, despite the fact that in the VBA I set it to column(1) (the id) and not column(2) (the organization name).

HiTechCoach, thanks for the suggestion. I'm checking it out now and seeing if it will help in this situation. Even if it doesn't I think it could definitely be useful later!

 

[Reese]

By the way, here's the query & VBA codes, etc. that I'm using in the query form method:

Query:

SELECT [Client Information].Client_ID, [Client Information].Organization, [Client Information].First_Name, [Client Information].Last_Name
FROM [Client Information]
WHERE ((([Client Information].Organization) Like "*" & [Forms]![ZooMobile Booking Form]![OrgSearchTxt] & "*"));

Combo box row source:

SELECT [Client Organization Search Query].[Client_ID], [Client Organization Search Query].[Organization], [Client Organization Search Query].[First_Name], [Client Organization Search Query].[Last_Name] FROM [Client Organization Search Query] ORDER BY [Organization], [First_Name], [Last_Name];

After Update event procedure for the combo box:

Private Sub SearchCombo_AfterUpdate()
Me.ClientIDTxt.Value = SearchCombo.Column(1)
End Sub

 

[CJ_London]

In the combo properties, if you set the bound column to 1, it refers to the first column, however in VBA the first column is referenced as .column(0).

So just change

Me.ClientIDTxt.Value = SearchCombo.Column(1)

to

Me.ClientIDTxt.Value = SearchCombo.Column(0)

 

[Reese]

Success! Thank you!

 

[rdulanski]

Need to use the WILDCARD character with LIKE. So CJ's suggestion needs asterisks

combobox.rowsource="Select fields FROM YourTble where Orgname like '*" & inputbox("Enter Approx Name") & "*'"

If you don't include the Wildcard character it simply becomes a literal. So, if you don't include the Wildcard then using Willow will only return if the name is EXACTLY Willow.

======================================
I'm trying to create a wild-card drop-down selector. Mine keeps coming out blank.
Could you explain, please?:
Is the "combobox.rowsource" line above supposed to be entered into the VBA Private Sub click event in the Class Object?
Is the line supposed to include the "combobox.rowsource =" part?
Is "combobox" supposed to be the Name of the combobox or literally "combobox"?
Do I need to clear the Column Count and/or Column Widths?