Query Form 1

[jcpelejo]

Hello. I would like to create a query form that can query a client by the [Client Name] and/or [city] and/or [Client Number] and/or [Phone Number] and display in a subform the first 10 results. Please advise.

 

[tlbroadbent]

The query would something look like this. Create the query, save it and execute it after the user inputs the criteria.

Select Top 10
[Client Name], [City], [Client Number], [Phone Number]
From TableName
Where [Client Name] Like "*" & NZ([Forms].[FormName].txtClientName,"&quot & "*"
Or [City] Like "*" & NZ([Forms].[FormName].txtCity,"&quot & "*"
Or [Client Number] = [Forms].[FormName].txtClientNo
Or [Phone Number] Like "*" & NZ([Forms].[FormName].txtPhone,"&quot & "*"

---------------------------

FormName and TableName should be changed to the names of your form and table, respectively. txtClientName, txtCity, txtClientNo and txtPhone should be changed to the names of the text boxes on your search form. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jcpelejo]

Hello Terry,

I wrote this command in the AfterUpdate PROC:
Private Sub txtClientName_AfterUpdate()
[Client Selection].Requery
End Sub

For the query I wrote this:
SELECT TOP 10 Client.ClientName, Client.PhoneNumber, Client.City, Client.Province, Client.ClientNumber
FROM Client
WHERE (((Client.ClientName) Like "*" & NZ([Forms]![Client Selection]![txtClientName],"&quot & "*&quot) OR (((Client.City) Like "*" & NZ([Forms]![Client Selection]![txtCity],"&quot & "*&quot) OR (((Client.PhoneNumber) Like "*" & NZ([Forms]![Client Selection]![txtPhoneNumber],"&quot & "*&quot);

I am not sure what I am doing wrong in order to get the proper results. I used the subform wizard to create the subform with the query results but it is shows the first ten records in my [Client] table. My main form name is [Client Selection] and my subform name is [Client Selection subform]. Please advise. Thank you.

 

[tlbroadbent]

I see the error of my query. I apologize for the misdirection. Use AND between the criteria rather than OR.

WHERE (((Client.ClientName) Like "*" & NZ([Forms]![Client Selection]![txtClientName],"&quot & "*&quot)
AND (((Client.City) Like "*" & NZ([Forms]![Client Selection]![txtCity],"&quot & "*&quot)
AND (((Client.PhoneNumber) Like "*" & NZ([Forms]![Client Selection]![txtPhoneNumber],"&quot & "*&quot); Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jcpelejo]

It is working in datasheet view. I was wondering if there is a way of getting the results in continous form view and having the results display a radio button so that when the button is pressed, it stores the value of the result in a table. Please advise.

 

[jcpelejo]

I am also having a problem when I type in a value in and of the text boxes and press tab, it does not update the results in the subform until I change the view to design view and back to form view. Please advise.

 

[jcpelejo]

Thank you tbroadbent for your help.

 

[tlbroadbent]

Have you tried using <SubForm>.ReQuery after updating the main form? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jcpelejo]

That worked. Thank you.