Form Search Issue - Right Place

[Topham]

Hello all,

On a form i have a search button that runs a query which searches for a person, This works great but how do i get the query results to view on the same form where the search button is?

Regards,
Topham

 

[vbaInet]

...This works great but how do i get the query results to view on the same form where the search button is?

I don't understand what you mean by that question. Please explain. Are you using a subform in Datasheet View?

 

[Topham]

Hello,

Ive created a Query which prompts for First Letter of 1st name and surname then it opens the results from a query. Its not opening as a Subform in Dataview

 

[vbaInet]

So, what is your question because I didn't understand the way you put it initially?

 

[Topham]

I have a form for Customer and i have nearly 1000 customer. I would like to perform a search for 1st name and surname and then view the details on the same form.

 

[vbaInet]

What you can do is get the input from 2 textboxes, one for First Name and the other for Surname. The user enters the first name and surname and clicks a "Search" button.

Is the form in Continuous view?

Have you done some vba?

 

[Topham]

The form views all the records which is in the Contacts Table. I have created a search button that Opens a Query which prompts for the 1st Letter of the 1st name and prompts again for the whole surname. Once done the query shows the result fine. But how do i get the form to view the result?

 

[vbaInet]

No need to run the query and prompt for anything.

Create two textboxes as previously mentioned and put them on the HEADER section of your form. Give them meaningful names.

Once you've done that, tell me the names of the textboxes and the name of your form.

 

[Topham]

Would it be wise to create a query for all the contacts are viewed and then apply a filter through VBA?

 

[vbaInet]

Our message crossed. See above.

 

[Topham]

WOuld it be possible to prompt for the filter instead of having text boxes in the header?

 

[vbaInet]

I'm suggesting the best way to achieve this. If you want to follow your way just make the query your form's Record Source

 

[Topham]

what would the code be to apply 2 filters on for Firstname and the other for Surname using the same button?

 

[vbaInet]

Yeap pretty much. You don't use filters, just base the form's Record Source on that query and on the CLICK event of the button, put this Me.Requery

 

[Topham]

Sorry im lost now. If i base my form recordset on that query each time i open the form it will prompt for the filters each time. I only want to to prompt for the filters when im searching for a particular customer.

 

[vbaInet]

Well, that's what you get using your method. And there are other problems too but I will let you figure it out.

There's a way using vba. You build the sql string and change the record source in code.

1. Put the form's Record Source back to its original state.
2. Copy the sql string of the query (go into sql view and copy the text)
3. Put this on the CLICK event of the button

Const rSource = "copied sql string from step 2"
Me.rSource = rSource
Me.Requery

 

[Topham]

Im using this code if it helps:

Private Sub Open_Search_contacts_form_Click()
If Me.[Open Search contacts form].Caption = "Search Name" Then
Me.Filter = "(FName Like [First Letter of First Name]) And (SName is [Surname])"
Me.FilterOn = True
Me.[Open Search contacts form].Caption = "Remove Search"
Else
Me.Filter = ""
Me.FilterOn = False
Me.[Open Search contacts form].Caption = "Search Name"
End If
End Sub

FName is Firstname and SName is Surname.

How do i get a prompt up to enter the filter criteria?

 

[vbaInet]

I think you will find the answer in my last post. As mentioned, you don't use the form's Filter for this.

 

[Topham]

I keep getting Compile Error: Tyoe Mismatch

 

[vbaInet]

When you click DEBUG, what line does it highlight in yellow?