Search Form with Combobox (1 Viewer)

mba_110

Registered User.
Local time
Today, 05:24
Joined
Jan 20, 2015
Messages
280
Hi everyone,

My code is below and its not working asking for parameter value error EC , AM etc because data in my tblEmploymentContracts is recorded like EC/AM/001 in ContractID field is this any issue for this but anyhow its a text field.

Code:
Private Sub cmbContractSearch_ContractID_AfterUpdate()
Dim myContracts As String
myContracts = " Select * from tblEmploymentContracts WHERE ([ContractID] = " & Me.cmbContractSearch_ContractID & ")"
Me.frmContractsSearch_subform.Form.RecordSource = myContracts
Me.frmContractsSearch_subform.Form.Requery
End Sub

Can anyone help with this please.

thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:24
Joined
Aug 30, 2003
Messages
36,118
Try

myContracts = " Select * from tblEmploymentContracts WHERE ([ContractID] = '" & Me.cmbContractSearch_ContractID & "')"

FYI, I moved your post out of a moderated forum.
 

mba_110

Registered User.
Local time
Today, 05:24
Joined
Jan 20, 2015
Messages
280
Re: Search Form

Hi again

This time i came with text box search.

My RecordSource is tblEmployees

My SQL is

Code:
Private Sub BtnSearch_Click()
Dim Strsearch As String
Dim strText As String
strText = Me.txtSearch.Value
Strsearch = "SELECT * from tblEmployees where (EmpID like  '" & strText & "'*"") _
                                            or(Fullname like ""*" & strText & "*"") _
                                            or(Surname like ""*" & strText & "*"") _
                                            or(Gender like ""*" & strText & "*"") _
                                            or(MaritalStatus like ""*" & strText & "*"") _
                                            or(Nationality like ""*" & strText & "*"") _
                                            or(Religion like ""*" & strText & "*"") _
                                            or(StaffLevel like '" & strText & "'*"")"
Me.RecordSource = Strsearch
End Sub

it is giving compile error syntax error, their is something i am missing here with quotation marks, also i need how to add the [DOB] which is date field in above SQL?

Please help.

Thanks.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 12:24
Joined
Sep 21, 2011
Messages
14,038
Debug.Print StrSearch and that should show you your errors.
If you cannot still spot the error than copy and paste into the QBE gui window.
 

isladogs

MVP / VIP
Local time
Today, 12:24
Joined
Jan 14, 2017
Messages
18,186
Is the EmpID a text field or a number field?

Suggest you use single quotes or double double quotes consistently
The single quotes on the first line are wrongly placed.

As Gasman suggested, do a debug.print to spot your errors.
Once you fix it, performance will be like a geriatric slug.
If possible omit the leading wildcards as they force Access to search every record

The delimiters for date fields are #
However, I wouldn't recommend using a wildcard on a date.
See https://www.access-programmers.co.uk/forums/showthread.php?t=303093
 
Last edited:

mba_110

Registered User.
Local time
Today, 05:24
Joined
Jan 20, 2015
Messages
280
EmpID and StaffLevel is number fields i put '" and "' on end but still showing syntax error in this both fields.

Also i need to add DOB which is date fields below criteria is correct for date?

(DOB Like #" * " & strText & "# * "")

Thanks
 

isladogs

MVP / VIP
Local time
Today, 12:24
Joined
Jan 14, 2017
Messages
18,186
As I've already said, this will have terrible performance if you get it working particularly due to the leading wildcards

Trying to use wildcards based on a text string for number and date fields make no sense. It's also poor practice to do this anyway
As I've already said, your single quotes are wrongly placed but for number fields they aren't needed.
If you want to persevere with this, do look at the screenshot of wildcards used in a query in the thread linked in my last reply.

Then have a look at this example by Allen Browne http://allenbrowne.com/ser-62.html

After that debug your code and view the output.
Then tweak till you get it to work. You'll learn more by solving it yourself.
Then test on a large dataset and you'll see why I've said performance will be bad
 

mba_110

Registered User.
Local time
Today, 05:24
Joined
Jan 20, 2015
Messages
280
Thanks guys.

However, different SQL for different examples i can see the methods also different in my case its limited to single table and no complicated row source or record source.

I want to keep this as example for my use for future SQL studies when its required, also i am not putting date field in to string, that was just for incase if i have date field, but number field can be used for both string and number in text box on search form.

Now in my code its showing the end of statement error i try putting ; and try to remove the brackets as in examples etc but not working.

I hope you don't mind correcting it to work.

Code:
Private Sub BtnSearch_Click()
Dim Strsearch As String
Dim strText As String
strText = Me.txtSearch.Value
Strsearch = "SELECT * from tblEmployees where (EmpID like  " * " & strText & " * ") _
                                            Or (FullName Like "" * " & strText & " * "") _
                                            Or (Surname Like "" * " & strText & " * "") _
                                            Or (Gender Like "" * " & strText & " * "") _
                                            Or (MaritalStatus Like "" * " & strText & " * "") _
                                            Or (Nationality Like "" * " & strText & " * "") _
                                            Or (Religion Like "" * " & strText & " * "") _
                                            Or (StaffLevel Like "*" & strText & " * ")"
Me.RecordSource = Strsearch
End Sub
 

JHB

Have been here a while
Local time
Today, 13:24
Joined
Jun 17, 2012
Messages
7,732
As Gasman already suggested:
Debug.Print StrSearch and that should show you your errors.
If you cannot still spot the error than copy and paste into the QBE GUI window.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:24
Joined
Sep 21, 2011
Messages
14,038
As Gasman already suggested:
Debug.Print StrSearch and that should show you your errors.
If you cannot still spot the error than copy and paste into the QBE GUI window.

Sadly as it is, it does not even compile and comes up as the SELECT statement all in red.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:24
Joined
Sep 21, 2011
Messages
14,038
Try
Code:
Private Sub BtnSearch_Click()
Dim StrSearch As String
Dim strText As String
strText = Me.txtSearch.Value

StrSearch = "SELECT * from tblEmployees where (EmpID like *" & strText & "*)"
StrSearch = StrSearch & " Or (FullName Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Surname Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Gender Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (MaritalStatus Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Nationality Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Religion Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (StaffLevel Like *" & strText & "*)"
Debug.Print StrSearch
Me.RecordSource = StrSearch

End Sub

This way you can identify the errant line, rather than one huge block of code
 

mba_110

Registered User.
Local time
Today, 05:24
Joined
Jan 20, 2015
Messages
280
Thank you sir.

the debug show arrow on
Code:
Me.RecordSource = StrSearch

and in immediate window following lines appear what it means ?


Code:
SELECT * from tblEmployees where (EmpID like *Romeo*) Or (FullName Like '*Romeo*') Or (Surname Like '*Romeo*') Or (Gender Like '*Romeo*') Or (MaritalStatus Like '*Romeo*') Or (Nationality Like '*Romeo*') Or (Religion Like '*Romeo*') Or (StaffLevel Like *Romeo*)
SELECT * from tblEmployees where (EmpID like *Romeo*) Or (FullName Like '*Romeo*') Or (Surname Like '*Romeo*') Or (Gender Like '*Romeo*') Or (MaritalStatus Like '*Romeo*') Or (Nationality Like '*Romeo*') Or (Religion Like '*Romeo*') Or (StaffLevel Like *Romeo*)
SELECT * from tblEmployees where (EmpID like *Romeo*) Or (FullName Like '*Romeo*') Or (Surname Like '*Romeo*') Or (Gender Like '*Romeo*') Or (MaritalStatus Like '*Romeo*') Or (Nationality Like '*Romeo*') Or (Religion Like '*Romeo*') Or (StaffLevel Like *Romeo*)
SELECT * from tblEmployees where (EmpID like *Romeo*) Or (FullName Like '*Romeo*') Or (Surname Like '*Romeo*') Or (Gender Like '*Romeo*') Or (MaritalStatus Like '*Romeo*') Or (Nationality Like '*Romeo*') Or (Religion Like '*Romeo*') Or (StaffLevel Like *Romeo*)


The error is run time error '3075' syntax error for above lines.

I am sorry but i am not programmer profession and not intended to go deeper in VBA just need my project to be completed with my limited knowledge i came half way, and off course you guy's are tremendous source of help and reconciliation time to time for my premature skills in VBA.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:24
Joined
Sep 21, 2011
Messages
14,038
Comment out all but the first line, and uncomment the next line after running each time.

or try

Code:
Private Sub BtnSearch_Click()
Dim StrSearch As String
Dim strText As String
strText = Me.txtSearch.Value

StrSearch = "SELECT * from tblEmployees where (EmpID like '*" & strText & "*')"
StrSearch = StrSearch & " Or (FullName Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Surname Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Gender Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (MaritalStatus Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Nationality Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (Religion Like '*" & strText & "*')"
StrSearch = StrSearch & " Or (StaffLevel Like '*" & strText & "*')"
Debug.Print StrSearch
Me.RecordSource = StrSearch

End Sub
 

mba_110

Registered User.
Local time
Today, 05:24
Joined
Jan 20, 2015
Messages
280
Thanks, i have added the following criteria if its blank also added the same code to txtSearch afterupdate event so when i fill it i dont need to click the button i can just press enter key.

Code:
If IsNull(Me.txtSearch) Then
MsgBox "Please enter the search value"
Me.txtSearch.SetFocus
Exit Sub
End If

Many Thanks for your help sir, i will keep this as a sample for future use.
 

Users who are viewing this thread

Top Bottom