SQL for List box search form (1 Viewer)

mba_110

Registered User.
Local time
Today, 07:37
Joined
Jan 20, 2015
Messages
280
Hi

I want your help in below code, which include 2 tables in list box query.


I have named frmSearch on this form their is a list box placed with rowsource
Code:
SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID;

and following code in btnSearch on click event is incomplete and i am totally blank how to do it with 2 tables with in query to search by LIKE parameter on any of above table's field content including/similar to my search text box value.

Code:
Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID;"
& " from tblemployees" _
& where [EmpID]"like '*" & me.txtSearch &"*' " _
& or [Fullname]"Like '*" & me.txtSearch &"*' " _
& or [Surname]"Like '*" & me.txtSearch &"*' " _
& or [Gender]"Like '*" & me.txtSearch &"*'" _
& or [Nationality] " Like '*" & me.txtSearch &"*'" _


End Sub


Your kind help is required.
 

Minty

AWF VIP
Local time
Today, 14:37
Joined
Jul 26, 2013
Messages
10,354
If the resident ID and empID are joined and equal (which they are in your rowsource query) - I'm not sure what your problem is ?

Finding the employee ID will, by definition, find the resident ID?
 

mba_110

Registered User.
Local time
Today, 07:37
Joined
Jan 20, 2015
Messages
280
I have to include like condition in all fields, which i am missing the code, my above code is incomplete and giving me the errors.
 

Minty

AWF VIP
Local time
Today, 14:37
Joined
Jul 26, 2013
Messages
10,354
Okay what errors are you getting?

I personally wouldn't search in that way - using the * wildcard at the beginning of the search will stop access using any indexes on those fields and make this extremely slow. Also there is no point (assuming your EmpID is number) in using Like at all with your EmpID criteria, just use = without the delimiters.

I would recommend having a look at this http://allenbrowne.com/ser-62.html which is the most recognised method of building a useable, well performing search form.
 

mba_110

Registered User.
Local time
Today, 07:37
Joined
Jan 20, 2015
Messages
280
its giving syntex error because code is not complete, can you provide complete code...please.
 

JHB

Have been here a while
Local time
Today, 15:37
Joined
Jun 17, 2012
Messages
7,732
You have the " in the wrong place and then you've a _ to much and you're missing a ".
Code:
& where [EmpID][B][COLOR=red]"[/COLOR][/B]like '*" & me.txtSearch &"*' " _ 
& or [Fullname][B][COLOR=red]"[/COLOR][/B]Like '*" & me.txtSearch &"*' " _ 
& or [Surname][B][COLOR=red]"[/COLOR][/B]Like '*" & me.txtSearch &"*' " _ 
& or [Gender][B][COLOR=red]"[/COLOR][/B]Like '*" & me.txtSearch &"*'" _ 
& or [Nationality] [B][COLOR=red]"[/COLOR][/B] Like '*" & me.txtSearch &"*'" [B][COLOR=Red]_ [/COLOR][/B]
Correct should be:
Code:
& [B][COLOR=red]"[/COLOR][/B] where [EmpID] Like '*" & me.txtSearch & "*' " _ 
& [B][COLOR=red]"[/COLOR][/B]or [Fullname] Like '*" & me.txtSearch & "*' " _ 
& [B][COLOR=red]"[/COLOR][/B]or [Surname] Like '*" & me.txtSearch & "*' " _ 
& [B][COLOR=red]"[/COLOR][/B]or [Gender] Like '*" & me.txtSearch & "*'" _ 
& [B][COLOR=red]"[/COLOR][/B]or [Nationality] Like '*" & me.txtSearch & "*'"
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:37
Joined
Sep 21, 2011
Messages
14,044
What does
Code:
 debug.print SQL
show?
 

mba_110

Registered User.
Local time
Today, 07:37
Joined
Jan 20, 2015
Messages
280
My full code is below, but still getting syntex error.


Code:
Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID;"
& *"* where [EmpID] Like '*" & me.txtSearch &"*' " _
& *"*or [Fullname] Like '*" & me.txtSearch & "*' " _
& *"*or [Surname] Like '*" & me.txtSearch & "*' " _
& *"*or [Gender] Like '*" & me.txtSearch & "*'" _
& *"*or [Nationality] Like '*" & me.txtSearch & "*'" _
& *"*or [ResidentID] Like '*" & me.txtSearch & "*'" _

Me.LstEmployees.RowSource = SQL
Me.LstEmployees.Requery


End Sub
 

Minty

AWF VIP
Local time
Today, 14:37
Joined
Jul 26, 2013
Messages
10,354
You haven't applied JHB's corrections properly.

You have a semi colon at the end of the first statement. Remove it.
You have added *"* to the beginning of every line ?

Add a debug.print SQL before setting the rowsource as per Gasmans's suggestion.

You haven't removed the Like * for the EmpID and changed it to =

I presume you haven't looked at the example search form in the link I provided either?
Please read and try to understand what people have given you as answers. We aren't a code writing service, we are trying to help you learn.
 

JHB

Have been here a while
Local time
Today, 15:37
Joined
Jun 17, 2012
Messages
7,732
My full code is below, but still getting syntex error.
..
In addition to Minty's comments, you still have the _ at the end of last line.
Code:
.. Like '*" & me.txtSearch & "*'" [B][COLOR=red]_[/COLOR][/B]
Why didn't you just copy the code I showed?
 

mba_110

Registered User.
Local time
Today, 07:37
Joined
Jan 20, 2015
Messages
280
I am sorry, but i am not a programmer just started to learn from access because i like to complete my database not extremely interested unless i get direct classes for programming languages.

Anyways, as best of my understanding i try to cover all areas that you have pointed me out, also i went through your sample database, but that is something not precisely related to my project.

Code:
Private Sub btnSearch_Click()
Dim SQL As String

Debug.Print SQL = "SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID"
& " where [EmpID] = me.txtSearch & "*'" _
& " or [Fullname] Like '*" & me.txtSearch & "*'" _
& " or [Surname] Like '*" & me.txtSearch & "*'" _
& " or [Gender] Like '*" & me.txtSearch & "*'" _
& " or [Nationality] Like '*" & me.txtSearch & "*'" _
& " or [ResidentID] Like '*" & me.txtSearch & "*'"

Me.LstEmployees.RowSource = SQL
Me.LstEmployees.Requery


End Sub

i dont know if i need to set the variable for debut.print also and what will be that.

If you dont mind keep me corrected and show me what needed to be rectified until i reach the goal.

thanks &regards,
MA
 
Last edited:

Minty

AWF VIP
Local time
Today, 14:37
Joined
Jul 26, 2013
Messages
10,354
Well I think we understand that, but it's good to understand where you have made mistakes, so that next time you have a better understanding.

Code:
Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID "
& " Where [EmpID] = " & me.txtSearch & " " _
& " or [Fullname] Like '*" & me.txtSearch & "*'" _
& " or [Surname] Like '*" & me.txtSearch & "*'" _
& " or [Gender] Like '*" & me.txtSearch & "*'" _
& " or [Nationality] Like '*" & me.txtSearch & "*'" _
& " or [ResidentID] Like '*" & me.txtSearch & "*';"


Debug.Print SQL

Me.LstEmployees.RowSource = SQL
Me.LstEmployees.Requery

End Sub
Try this.

If it errors out then look in the immediate window (Press Ctrl+G) in the VBA editor.
You will see the SQL string as it is being passed to the listbox. You can cut and paste it into the SQL view of a blank query and see what errors it gives to help you debug the syntax.
 

mba_110

Registered User.
Local time
Today, 07:37
Joined
Jan 20, 2015
Messages
280
I did try but nothing came up in VBA (Ctrl+G button) bottom window appear but blank.

Anyhow, i have attached the strip version of my database, to see what exactly not working.


thanks.
 

Attachments

  • ProjetTest.zip
    78.2 KB · Views: 80

Minty

AWF VIP
Local time
Today, 14:37
Joined
Jul 26, 2013
Messages
10,354
You were missing a continuation character of the first part of the string, so it was never getting to the debug statement.

I am running a check to see if you enter a number to catch errors in adding text to a number comparison. I've commented out the listbox requery as it isn't necessary, changing the rowsource forces a requery.

Code:
Private Sub btnSearch_Click()

    Dim SQL As String
    Dim iNumberID As Double


    If IsNumeric(Me.txtSearch) Then iNumberID = Me.txtSearch

    SQL = "SELECT tblEmployees.EmpID, tblEmployees.FullName, tblEmployees.Surname, tblEmployees.Gender, tblEmployees.Nationality, tblResidentID.Resident_ID FROM tblEmployees INNER JOIN tblResidentID ON tblEmployees.EmpID = tblResidentID.ID " _
        & " WHERE [EmpID] = " & iNumberID & " " _
        & " OR [Fullname] Like '*" & Me.txtSearch & "*'" _
        & " OR [Surname] Like '*" & Me.txtSearch & "*'" _
        & " OR [Gender] Like '*" & Me.txtSearch & "*'" _
        & " OR [Nationality] Like '*" & Me.txtSearch & "*' " _
        & " OR [Resident_ID] = " & iNumberID & " ;"


    Debug.Print SQL

    Me.LstEmployees.RowSource = SQL
    'Me.LstEmployees.Requery

End Sub

As I said before this will become very much slower once you get a large dataset due to the wildcards at the beginning of the search strings.
 

Users who are viewing this thread

Top Bottom