Search form

[Buineugenio]

Dears Experts
I have a small database with one table; the fields are Id, Obra, Material, SNumber, Cliente and several others.
I want a form to search and filter for Cliente = Client; and show all records in a list for this client. Of course my database has several clients.
Any detailed help will be useful for me.
I am beginning to learn Access but I have certain knowledge in programming, in particular, Clipper, Visual Basic.

Buineugenio
Mozambique, Africa

[kcdoell]

Dears Experts
I have a small database with one table; the fields are Id, Obra, Material, SNumber, Cliente and several others.
I want a form to search and filter for Cliente = Client; and show all records in a list for this client. Of course my database has several clients.
Any detailed help will be useful for me.
I am beginning to learn Access but I have certain knowledge in programming, in particular, Clipper, Visual Basic.

Buineugenio
Mozambique, Africa

Hello:

-First create a query by using the "Simple Query Wizard". Choose the table and the fields you want in your form and click finish

-Then in the design mode of that query, look for the field that has the word "Client" and in the criteria section of that column key in:
="client"

-Then use the "Form Wizard" and choose the query as the record source."choose the table or query where the object......" and follow instructions or if you already created the form, go into the properties of the form and point the record source to the query.....

Hope that sheds light on the subject...

Good Luck!

Keith.

[Buineugenio]

Hello:

-First create a query by using the "Simple Query Wizard". Choose the table and the fields you want in your form and click finish

-Then in the design mode of that query, look for the field that has the word "Client" and in the criteria section of that column key in:
="client"

-Then use the "Form Wizard" and choose the query as the record source."choose the table or query where the object......" and follow instructions or if you already created the form, go into the properties of the form and point the record source to the query.....

Hope that sheds light on the subject...

Good Luck!

Keith.

Keith
I follow the steps in your answer and every thing work fine!
Now, I have a next step in this same question.
How to add a text box and a command button in the form to find any Client's name entered in the text box?
I think that is possible to do that.
Thanks again, your help was very useful, I get the answer that I wanted, show all records for a Client in a datagrid!

Buineugenio

Maputo, Mozambique, Africa

[kcdoell]

Keith
How to add a text box and a command button in the form to find any Client's name entered in the text box?
I think that is possible to do that.........
Buineugenio

Maputo, Mozambique, Africa

Of course it is. Where I told you:....the design mode of that query, look for the field that has the word "Client" and in the criteria section of that column key in.....

Replace:

="client"

with:

[forms].[NameofYourForm].[NameofYourTexbox]

Your will have to switch the verbiage to match your form name and textbox name...

Then in the properties of the comand button go to the OnClick event. Choose code builder and input the following:

Private Sub YourCommandButtonName_Click()
'Requery your form
    DoCmd.Requery
End Sub 

All the you will have to do is key in the "DoCmd.Requery" the rest will be there. That should do it. Keep in mind that It will only find exact matches that you input into your text box.

[Buineugenio]

Of course it is. Where I told you:....the design mode of that query, look for the field that has the word "Client" and in the criteria section of that column key in.....

Replace:

="client"

with:

[forms].[NameofYourForm].[NameofYourTexbox]

Your will have to switch the verbiage to match your form name and textbox name...

Then in the properties of the comand button go to the OnClick event. Choose code builder and input the following:

Private Sub YourCommandButtonName_Click()
'Requery your form
    DoCmd.Requery
End Sub 

All the you will have to do is key in the "DoCmd.Requery" the rest will be there. That should do it. Keep in mind that It will only find exact matches that you input into your text box.

Keith
I follow your instruction, step by step but when I open the form, only appaer the datagrid with the name of the fields, empty.
Maybe in your instruction something is missing?

Buineugenio
Mozambique, Africa

[kcdoell]

What happens when you key something into your text box and then hit your command button??

Remember, that your query is now looking for an input in that dialogue box to run the records that it will display so naturally it will be blank when you first open it.

[Buineugenio]

What happens when you key something into your text box and then hit your command button??

Remember, that your query is now looking for an input in that dialogue box to run the records that it will display so naturally it will be blank when you first open it.

Keith
The Query opens immediately, I have no chance to write in the text box!
But when I open the Query directly, not using the form, appear a small windows asking for the parameter to search, in this case, the Client's name, when I enter the name, the query works fine. Show all the records in the datagrid with that Client.
They work separately, seems that a link is missing between the form and the query.
Thanks for your help again!
Buineugenio

[kcdoell]

Keith
The Query opens immediately,................Thanks for your help again!
Buineugenio

Buineugenio:

I need more help on what you are doing.

In your query, give me the name of the field, that you are using for your criteria and the actual criteria expression.

Give me the Form name (Found in the properties section "name")
The Name of the text box on that form (Found in the properties section "name")

It is probably something simple, but without having it in front of me it can be challenging to know what is going on...

Since it did work for the "="Client"" criteria, the problem is most likely rooted in the new criteria expression..

Thanks,

Keith.

[Buineugenio]

Buineugenio:

I need more help on what you are doing.

In your query, give me the name of the field, that you are using for your criteria and the actual criteria expression.

Give me the Form name (Found in the properties section "name")
The Name of the text box on that form (Found in the properties section "name")

It is probably something simple, but without having it in front of me it can be challenging to know what is going on...

Since it did work for the "="Client"" criteria, the problem is most likely rooted in the new criteria expression..

Thanks,

Keith.

Keith

Here is the criteria:
[forms].[Cliente].[Text10]

Text10 is the name of the Text box
Cliente is the form's name

Thanks again
Buineugenio

[kcdoell]

Open the form, leave it open and then input something that you know it should find in Text10. Then go to your query and run it. What happens with your query? Did it find any records? Do you still get a pop up box when the query runs? We are trying to determine first if your query can find Text10 or not...


Also, sometimes swapping the . with ! works also but first see what is going on exactly with the way it works now.

[forms]![Cliente]![Text10]

[Buineugenio]

Open the form, leave it open and then input something that you know it should find in Text10. Then go to your query and run it. What happens with your query? Did it find any records? Do you still get a pop up box when the query runs? We are trying to determine first if your query can find Text10 or not...


Also, sometimes swapping the . with ! works also but first see what is going on exactly with the way it works now.

[forms]![Cliente]![Text10]

Keith
I delete [forms] from the criteria and works fine. First appear a small popup window asking for the parameter to search, I entered 2 non equal Cliente name and show a datagrid with all of them in the Query. This is more or less what I wanted.
By the moment I am glad with the results. Thanks for your help
Buineugenio
Maputo, Mozambique, Africa

[Buineugenio]

Keith
I delete [forms] from the criteria and works fine. First appear a small popup window asking for the parameter to search, I entered 2 non equal Cliente name and show a datagrid with all of them in the Query. This is more or less what I wanted.
By the moment I am glad with the results. Thanks for your help
Buineugenio
Maputo, Mozambique, Africa

Keith
Now I am trying to find and fix, why the form do not open? I how in the actual situation can I use again, without closing, a new search, I want to reset the search and write a new client's name without closing the form, that actually do not open.
Thanks
Buineugenio
Maputo, Mozambique, Africa

[Buineugenio]

Keith
I add the next code:

Dim strStudentRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![Text10]) Or (Me![Text10]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![Text10].SetFocus
Exit Sub
End If
and now the message box appear asking for the parameter. Is working but I am trying to fix this because if I leave empty the Text10 box, the query opens and do not appear a message box asking for the value.
I will try a loop to eliminate this bug.

Regards
Buineugenio
Maputo, Mozambique , Africa