Creating a search on a form with a results table.

[soorags]

On the "View Directory" form of my database I would like to allow the user to be able to fill in AT LEAST one piece of information about a company so that when he or she clicks on the "Submit Information for Search" command button, the table below shows the results that have been searched for with all company information.

For example if only the company name "HMC" was typed in, clicking on the "Submit information for search button" would display all of the searched results in the table below, showing companies with the name "HMC"

However if HMC was typed in with the company's address as well, and then searched for, only the specific result would be shown.

How is it possible to make this happen on my database?

I would like to find out very soon, thank you.

Gurdip Soora.

 

[kjv1611]

Are you talking about just having one input box for the user, or multiple boxes?

If you are talking just one input box, and you have multiple fields, then it could be rather complicated and processor intensive - thus time consuming to have it search through the database, assuming you have very many records.

If you are just wanting the table/query to show up based on the results, you can do that with a subform, or possibly even just using a continuous form.

I am actually looking at doing something along these lines, as far as returning the results, myself, so I may do some additional digging on the subject here in the next couple weeks, possibly, if it doesn't get resolved soon.

--

"If to err is human, then I must be some kind of human!" -Me

 

[soorags]

There are multiple combo boxes on the form to enter different of data.

Example:

If you enter the company's name, address and telephone number, the search displays all of the details of the company with that data.

Thanks for your help so far.

 

[scottian]

why not try the Nz function.

something like this in the criteria of the query that feeds the form:-

Like Nz([Forms]![frmRecords]![txtSearchField]+"*",[tblRecords].[CompanyAddress1])

Using that will allow the query to filter if there is info in the matching field on the form, if the field is empty the query wont include it. Put one in each of the fields of the query that you want the user to be able to use.

"My God! It's full of stars...

 

[soorags]

I do not understand where to type in the "Like Nz..."
I don't know much coding. Where do I write this on the form?

 

[scottian]

Im assuming your form is feeding off a query. If not create one and add the table that contains the company info. In the criteria field of the query copy and paste the above but change the

[frmRecords] bit to the name of your form.
[txtSearchField] bit to the name of your text search field on the form
[tblRecords] bit to the name of the table you are querying
[CompanyAddress1] bit to the field in the table

"My God! It's full of stars...

 

[kjv1611]

scottian, that's a most excellent idea! Hopefully, I'll get a chance to try it out in the next couple weeks, myself. But first, I may just have to create a way to create more time.

Any ideas!

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

scottian's suggestion will not select the rows with NULL CompanyAddress1.
I'd suggest this instead, in the criteria cell of CompanyAddress1:
=[Forms]![frmRecords]![txtSearchField] Or [Forms]![frmRecords]![txtSearchField] Is Null

Or:
Like [Forms]![frmRecords]![txtSearchField] & '*' Or [Forms]![frmRecords]![txtSearchField] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[soorags]

I have tried all of the three query criteria techniques given
but none work. Is there anything else?

Thanks for the help though.

 

[PHV]

soorag, you say you have tried but you post no code, no info about what is not working.
Please, read carefully this: FAQ181-2886

 

[soorags]

I have tried to place the coding that I have been given into the criteria for each field in my query. The codes that I have received, one from here and the others from other forums do not work. I cannot attach a file here on this forum also. Is there a good Access forum anybody knows where I can attach files?

Thanks.

 

[scottian]

PHV, im baffled by what youve said, that my suggestion wont work, you gave me that solution on the 1st march. and it works perfectly for me. I have a form with 3 unbound text boxes. The user types in 1,2 or 3 of the boxes and then hits a search button which returns the records which match.



"My God! It's full of stars...

 

[soorags]

I have 8 unbound textboxes in my form.
I have created a query and for each field I have used the Nz function in the criteria. After saving the query, entering data on the form and then coming back to the query I get no result. Why is this?

Thanks.

 

[kjv1611]

soorags, in order to show what you have for looking at what may be wrong, you can go to the SQL view of your query, and then post that here. No need to attach a file.

--

"If to err is human, then I must be some kind of human!" -Me

 

[soorags]

I have typed the following:

Like Nz([Forms]![frmNz]![txtBusinessName]+"*",[UK Table].[Business Name])

Into the criteria for the Business Name field. The coding is fine. When I used this for the first time it worked. However after using the same method for the criteria of the other fields it did not work. The query result was blank.

Gurdip.

 

[kjv1611]

soorags,

The best way to get the fullest answer and therefore the best help is for you to past the entire SQL string for that query, thus allowing the "experts" here to see the whole picture. It may be that something in that statement is causing the problem, but it could also be that it is causing the problem BECAUSE of something else in your query. If you want to get it solved quickly, I'd advise copying and pasting the whole SQL string.

If you're not sure on how to do that, or what I'm talking about, just ask, and one of us will tell you how.

--

"If to err is human, then I must be some kind of human!" -Me

 

[soorags]

The code that I've posted is the full code. It isn't typed in VBA. It is typed in each of the criteria for the query, under each field. The coding is altered to suit the fields, e.g. for the address field it is:

Like Nz([Forms]![frmNz]![txtAddress]+"*",[UK Table].[Address])

This is how I have done it, not in VBA.

 

[PHV]

Have you tried this ?
Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[soorags]

I have also tried exactly that. The same thing happens. I will try that one again later. I'll go to library to see if I can find anything tomorrow also.

Gurdip.

 

[PHV]

The same thing happens
Which things ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886