Code Not Working

D

DS

I have a Form called "AddItems" with a Search Button on it. When you
click it a Search Form opens up called "AddItemsSearch" which has
unbound fields on it. I enter the info into it, then click a done
button which closes "AddItemsSearch" and opens "AddItems". If I use
only one of he criteria listed below, Price or ID and REM out the others
in the code it works fine. However when I start adding other criteria
by taking off the rem the code doesn't work. Any help is appreciated.
This has been going on for a couple of Days now. Also will this handle
Null or am I missing this also.

Thanks
DS

Private Sub Command73_Click()
On Error GoTo Err_cmdOpenResult_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "AddItems"

stLinkCriteria = "[ItemID]=" & Me![SearchID] & _
"[ItemID] = " & Me![SearchID] & _
"[ItemName]= " & Me![SearchName] & _
"[ItemPrice]= " & Me![SearchPrice] & _
"[ItemType] = " & Me![SearchType] & _
"[ItemMenu] = " & Me![SearchMenu] & _
"[ItemSalesCat] = " & Me![SearchSales] & _
"[ItemMajorCat] = " & Me![SearchMajor] & _
"[ItemMinorCat] = " & Me![SearchMinor] & _
"[ItemPrepCat] = " & Me![SearchPrep] & _
"[ItemActive] = " & Me![SearchActive]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenResult_Click:
Exit Sub

Err_cmdOpenResult_Click:
MsgBox Err.Description
Resume Exit_cmdOpenResult_Click

End Sub

LIST OF FIELD TYPES

[ItemID] Number
[ItemName] Text
[ItemPrice] Currency
[ItemType] Combo with Value "Main"; "Modifier"
[ItemMenu] Combo with 2 rows, 1st Bound (Number), (Text)
[ItenSalesCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemMajorCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemMinorCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemPrepCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemActive] Yes/No

All of the Search Fields are on the "AddItemsSearch" Form
 
A

Alex White MCDBA MCSE

Hi,

I personally never use this method but is there not some comma's missing
from your code

stLinkCriteria = "[ItemID]=" & Me![SearchID] & ", [ItemID] = " &
Me![SearchID]

rather than

stLinkCriteria = "[ItemID]=" & Me![SearchID] & "[ItemID] = " & Me![SearchID]

I build a complete SQL statement and pass that as the recordsource

e.g.

me.recordsource = "Select * from TblTest Where ....."
me.requery
 
D

DS

Alex said:
Hi,

I personally never use this method but is there not some comma's missing
from your code

stLinkCriteria = "[ItemID]=" & Me![SearchID] & ", [ItemID] = " &
Me![SearchID]

rather than

stLinkCriteria = "[ItemID]=" & Me![SearchID] & "[ItemID] = " & Me![SearchID]

I build a complete SQL statement and pass that as the recordsource

e.g.

me.recordsource = "Select * from TblTest Where ....."
me.requery
Click to expand...
Thanks, You said that you never use this method, how would you do it.
I'm new at this so I'm all ears and quite willing to learn!
Sincerely
DS
 
A

Alex White MCDBA MCSE

Hi,

couple of examples for you, the process here is open the bound form that you
want to show, then build your select statement, pass it as the recordsource,
then requery the form, and hey presto one form with the correct data on it,
the controls on the form have to be bound and to setup the form the starting
recordset would be the recordset/query with all the fields that you are
displaying. In the bottom example when I first opened the form its
recordsource was "CANDIDATE", does that make sense? See the trick here is
even though you have opened the form the procedure that called the form will
complete before you see the form unless the form is Modal.

docmd.openform "Form1"
form_Form1.recordsource = "Select * from TblTest Where Field1=1 and
Field2='" & "test" & "'"
form_Form1.requery



Dim sqlString As String
sqlString = "SELECT CANDIDATE.*, CANDIDATE_ADMIN.* "
sqlString = sqlString & "FROM CANDIDATE LEFT JOIN CANDIDATE_ADMIN ON
CANDIDATE.CANDIDATE_ID = CANDIDATE_ADMIN.CANDIDATE_ID "
sqlString = sqlString & "WHERE CANDIDATE.Candidate_id=" &
Me.cboCandidate.Column(0)
DoCmd.OpenForm "CANDIDATE"
With Form_CANDIDATE
.RecordSource = sqlString
.Requery
End With

If you need any more help post back here

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

DS said:
Alex said:
Hi,

I personally never use this method but is there not some comma's missing
from your code

stLinkCriteria = "[ItemID]=" & Me![SearchID] & ", [ItemID] = " &
Me![SearchID]

rather than

stLinkCriteria = "[ItemID]=" & Me![SearchID] & "[ItemID] = " &
Me![SearchID]

I build a complete SQL statement and pass that as the recordsource

e.g.

me.recordsource = "Select * from TblTest Where ....."
me.requery
Click to expand...
Thanks, You said that you never use this method, how would you do it. I'm
new at this so I'm all ears and quite willing to learn!
Sincerely
DS
Click to expand...
 
D

DS

Alex said:
Hi,

couple of examples for you, the process here is open the bound form that you
want to show, then build your select statement, pass it as the recordsource,
then requery the form, and hey presto one form with the correct data on it,
the controls on the form have to be bound and to setup the form the starting
recordset would be the recordset/query with all the fields that you are
displaying. In the bottom example when I first opened the form its
recordsource was "CANDIDATE", does that make sense? See the trick here is
even though you have opened the form the procedure that called the form will
complete before you see the form unless the form is Modal.

docmd.openform "Form1"
form_Form1.recordsource = "Select * from TblTest Where Field1=1 and
Field2='" & "test" & "'"
form_Form1.requery



Dim sqlString As String
sqlString = "SELECT CANDIDATE.*, CANDIDATE_ADMIN.* "
sqlString = sqlString & "FROM CANDIDATE LEFT JOIN CANDIDATE_ADMIN ON
CANDIDATE.CANDIDATE_ID = CANDIDATE_ADMIN.CANDIDATE_ID "
sqlString = sqlString & "WHERE CANDIDATE.Candidate_id=" &
Me.cboCandidate.Column(0)
DoCmd.OpenForm "CANDIDATE"
With Form_CANDIDATE
.RecordSource = sqlString
.Requery
End With

If you need any more help post back here
Click to expand...
Thanks...I'm going to play with it and I'll check back with later.
Sincerely
DS
 
D

DS

Alex said:
Hi,

couple of examples for you, the process here is open the bound form that you
want to show, then build your select statement, pass it as the recordsource,
then requery the form, and hey presto one form with the correct data on it,
the controls on the form have to be bound and to setup the form the starting
recordset would be the recordset/query with all the fields that you are
displaying. In the bottom example when I first opened the form its
recordsource was "CANDIDATE", does that make sense? See the trick here is
even though you have opened the form the procedure that called the form will
complete before you see the form unless the form is Modal.

docmd.openform "Form1"
form_Form1.recordsource = "Select * from TblTest Where Field1=1 and
Field2='" & "test" & "'"
form_Form1.requery



Dim sqlString As String
sqlString = "SELECT CANDIDATE.*, CANDIDATE_ADMIN.* "
sqlString = sqlString & "FROM CANDIDATE LEFT JOIN CANDIDATE_ADMIN ON
CANDIDATE.CANDIDATE_ID = CANDIDATE_ADMIN.CANDIDATE_ID "
sqlString = sqlString & "WHERE CANDIDATE.Candidate_id=" &
Me.cboCandidate.Column(0)
DoCmd.OpenForm "CANDIDATE"
With Form_CANDIDATE
.RecordSource = sqlString
.Requery
End With

If you need any more help post back here
Click to expand...
Heres what I came up with...it stops at (AddItems "& sCriteria") I
indicated below....to tell you the truth though I'm still confused!
Thanks
DS



Private Sub Command75_Click()
On Error Resume Next

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE "


'tblSubject qrySearchCriteriaSub
If Me![SearchID] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemID =
""" & SearchID & """"
End If

If Me![SearchName] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemID = """ &
SearchName & "*"""
End If

If Me![SearchPrice] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemPrice
= """ & SearchPrice & """"
End If

If Me![SearchType] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemType =
""" & SearchType & """"
End If

If Me![SearchMenu] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemMenu = """
& SearchMenu & """"""
End If

If Me![SearchSales] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemSalesCat =
""" & SearchSales & "*"""
End If

If Me![SearchMajor] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemMajorCat =
""" & SearchMajor & "*"""
End If

If Me![SearchMinor] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemMinorCat =
""" & SearchMinor & "*"""
End If
If Me![SearchPrep] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemPrepCat =
""" & SearchPrep & "*"""
End If
If Me![SearchActive] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemActive =
""" & SearchActive & "*"""
End If
DoCmd.OpenForm "AddItems"
sSql = "SELECT DISTINCT
[ItemID],[ItemName],[ItemPrice],[ItemType],[ItemMenu],[ItemSalesCat]"
[ItemMajorCat] , [ItemMinorCat], [ItemPrepCat], [ItemActive], FROM
STOPS HERE AddItems "& sCriteria"
Forms![AddItems].RecordSource = sSql
Forms![AddItems].Requery

End Sub
 
A

Alex White MCDBA MCSE

Hi,

I think your almost there another example below

but I think the line

AddItems "& sCriteria"

Should be

" AddItems " & sCriteria

build a dynamic SQL statement with values from the textboxes

Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select Candidate_ID, Title, First_Name as 'First
Name' , Surname, Tel_Work as 'Work Tel', Date_Of_Birth as 'DOB', Email,
Postcode, County, Locations from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Title.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '*' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.First_Name.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Surname.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Email_Address.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_Postcode.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_County.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"


Forms![AddItems].RecordSource = SQL
Forms![AddItems].Requery

We will get this working......

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

DS said:
Alex said:
Hi,

couple of examples for you, the process here is open the bound form that
you want to show, then build your select statement, pass it as the
recordsource, then requery the form, and hey presto one form with the
correct data on it, the controls on the form have to be bound and to
setup the form the starting recordset would be the recordset/query with
all the fields that you are displaying. In the bottom example when I
first opened the form its recordsource was "CANDIDATE", does that make
sense? See the trick here is even though you have opened the form the
procedure that called the form will complete before you see the form
unless the form is Modal.

docmd.openform "Form1"
form_Form1.recordsource = "Select * from TblTest Where Field1=1 and
Field2='" & "test" & "'"
form_Form1.requery



Dim sqlString As String
sqlString = "SELECT CANDIDATE.*, CANDIDATE_ADMIN.* "
sqlString = sqlString & "FROM CANDIDATE LEFT JOIN CANDIDATE_ADMIN ON
CANDIDATE.CANDIDATE_ID = CANDIDATE_ADMIN.CANDIDATE_ID "
sqlString = sqlString & "WHERE CANDIDATE.Candidate_id=" &
Me.cboCandidate.Column(0)
DoCmd.OpenForm "CANDIDATE"
With Form_CANDIDATE
.RecordSource = sqlString
.Requery
End With

If you need any more help post back here
Click to expand...
Heres what I came up with...it stops at (AddItems "& sCriteria") I
indicated below....to tell you the truth though I'm still confused!
Thanks
DS



Private Sub Command75_Click()
On Error Resume Next

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE "


'tblSubject qrySearchCriteriaSub
If Me![SearchID] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemID = """
& SearchID & """"
End If

If Me![SearchName] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemID = """ &
SearchName & "*"""
End If

If Me![SearchPrice] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemPrice =
""" & SearchPrice & """"
End If

If Me![SearchType] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemType =
""" & SearchType & """"
End If

If Me![SearchMenu] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemMenu = """ &
SearchMenu & """"""
End If

If Me![SearchSales] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemSalesCat =
""" & SearchSales & "*"""
End If

If Me![SearchMajor] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemMajorCat =
""" & SearchMajor & "*"""
End If

If Me![SearchMinor] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemMinorCat =
""" & SearchMinor & "*"""
End If
If Me![SearchPrep] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemPrepCat = """
& SearchPrep & "*"""
End If
If Me![SearchActive] <> "" Then
sCriteria = sCriteria & " AND Forms!AddItems!ItemActive = """
& SearchActive & "*"""
End If
DoCmd.OpenForm "AddItems"
sSql = "SELECT DISTINCT
[ItemID],[ItemName],[ItemPrice],[ItemType],[ItemMenu],[ItemSalesCat]"
[ItemMajorCat] , [ItemMinorCat], [ItemPrepCat], [ItemActive], FROM
STOPS HERE AddItems "& sCriteria"
Forms![AddItems].RecordSource = sSql
Forms![AddItems].Requery

End Sub
Click to expand...
 
D

DS

Alex said:
Hi,

I think your almost there another example below

but I think the line

AddItems "& sCriteria"

Should be

" AddItems " & sCriteria

build a dynamic SQL statement with values from the textboxes

Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select Candidate_ID, Title, First_Name as 'First
Name' , Surname, Tel_Work as 'Work Tel', Date_Of_Birth as 'DOB', Email,
Postcode, County, Locations from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Title.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '*' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.First_Name.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Surname.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Email_Address.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_Postcode.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_County.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"


Forms![AddItems].RecordSource = SQL
Forms![AddItems].Requery

We will get this working......
Click to expand...
Thanks, I'll try again!
DS
 
D

DS

Alex said:
Hi,

I think your almost there another example below

but I think the line

AddItems "& sCriteria"

Should be

" AddItems " & sCriteria

build a dynamic SQL statement with values from the textboxes

Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select Candidate_ID, Title, First_Name as 'First
Name' , Surname, Tel_Work as 'Work Tel', Date_Of_Birth as 'DOB', Email,
Postcode, County, Locations from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Title.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '*' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.First_Name.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Surname.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Email_Address.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_Postcode.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_County.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"


Forms![AddItems].RecordSource = SQL
Forms![AddItems].Requery

We will get this working......
Click to expand...
I Think I'm in over my head....Again. There has got to be an easier way
of doing this! I see people all over the internet trying to build a
search form in Access, and it's ecoming more confusing by the minute.
There must be 10 ways to do this and none of them seem easy. I thank
you for your help and patience thus far, I will continue trying.
DS
 
A

Alex White MCDBA MCSE

Hi,

email me directly my email address is in this post somewhere and I will zip
up and email you some of my projects so you can see how I bolt this stuff
together.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

DS said:
Alex said:
Hi,

I think your almost there another example below

but I think the line

AddItems "& sCriteria"

Should be

" AddItems " & sCriteria

build a dynamic SQL statement with values from the textboxes

Dim SQL As String
Dim bolAnd As Boolean
Dim bolWhere As Boolean
bolWhere = False
Me.Recalc
bolAnd = False
SQL = "Select Candidate_ID, Title, First_Name as 'First
Name' , Surname, Tel_Work as 'Work Tel', Date_Of_Birth as 'DOB', Email,
Postcode, County, Locations from TblCandidate "
If Len(Me.Title.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Title.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
Else
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Title Like '*' "
bolAnd = True
End If
If Len(Me.First_Name.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.First_Name Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.First_Name.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Surname.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Surname Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Surname.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Email_Address.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Email Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Email_Address.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_Postcode.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.Postcode Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_Postcode.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
If Len(Me.Ad_County.Value) > 0 Then
If Not bolWhere Then
bolWhere = True
SQL = SQL & " Where "
End If
If bolAnd Then
SQL = SQL & " and "
End If
SQL = SQL & " TblCandidate.County Like '"
If Me.chkLeft.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & Me.Ad_County.Value
If Me.chkRight.Value = True Then
SQL = SQL & "*"
End If
SQL = SQL & "'"
bolAnd = True
End If
SQL = SQL & " Order By Surname"


Forms![AddItems].RecordSource = SQL
Forms![AddItems].Requery

We will get this working......
Click to expand...
I Think I'm in over my head....Again. There has got to be an easier way
of doing this! I see people all over the internet trying to build a
search form in Access, and it's ecoming more confusing by the minute.
There must be 10 ways to do this and none of them seem easy. I thank you
for your help and patience thus far, I will continue trying.
DS
Click to expand...
 
V

Van T. Dinh

Filter string "stLinkCriteria" is simple the WHERE clause without the word
WHERE. You need the Boolean operator "AND" rather than comma, e.g.

stLinkCriteria = "[ItemID]=" & Me![SearchID] & " AND [...] = " & Me![...]
 
G

Guest

DS said:
I have a Form called "AddItems" with a Search Button on it. When you
click it a Search Form opens up called "AddItemsSearch" which has
unbound fields on it. I enter the info into it, then click a done
button which closes "AddItemsSearch" and opens "AddItems". If I use
only one of he criteria listed below, Price or ID and REM out the others
in the code it works fine. However when I start adding other criteria
by taking off the rem the code doesn't work. Any help is appreciated.
This has been going on for a couple of Days now. Also will this handle
Null or am I missing this also.

Thanks
DS

Private Sub Command73_Click()
On Error GoTo Err_cmdOpenResult_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "AddItems"

stLinkCriteria = "[ItemID]=" & Me![SearchID] & _
"[ItemID] = " & Me![SearchID] & _
"[ItemName]= " & Me![SearchName] & _
"[ItemPrice]= " & Me![SearchPrice] & _
"[ItemType] = " & Me![SearchType] & _
"[ItemMenu] = " & Me![SearchMenu] & _
"[ItemSalesCat] = " & Me![SearchSales] & _
"[ItemMajorCat] = " & Me![SearchMajor] & _
"[ItemMinorCat] = " & Me![SearchMinor] & _
"[ItemPrepCat] = " & Me![SearchPrep] & _
"[ItemActive] = " & Me![SearchActive]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenResult_Click:
Exit Sub

Err_cmdOpenResult_Click:
MsgBox Err.Description
Resume Exit_cmdOpenResult_Click

End Sub

LIST OF FIELD TYPES

[ItemID] Number
[ItemName] Text
[ItemPrice] Currency
[ItemType] Combo with Value "Main"; "Modifier"
[ItemMenu] Combo with 2 rows, 1st Bound (Number), (Text)
[ItenSalesCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemMajorCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemMinorCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemPrepCat] Combo with 2 rows, 1st Bound (Number), (Text)
[ItemActive] Yes/No

All of the Search Fields are on the "AddItemsSearch" Form
Click to expand...

DS,

You almost had it right. Just missing a few qotes and some "Ifs" and "Ands".

The controls that are type TEXT need to be enclosed in quotes. I used "Like"
and a wildcard so you could enter partial words and find all occurances.

I modified your code: added the quotes where necessary and added checks for
empty controls. If the control is empty (or null), that control is skipped
from the criteria string. If the empty control was included, no records would
be returned.

This is Air Code/ untested. Watch for line wrap!!

'***** Begin code *******
Private Sub Command73_Click()
On Error GoTo Err_cmdOpenResult_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "AddItems"

' initalize
stLinkCriteria =""

'number
if len(Me![SearchID])>0 then
stLinkCriteria = "[ItemID]=" & Me![SearchID] & " And "
end if

' text
if len(trim(Me![SearchName] & ""))>0 then
stLinkCriteria = stLinkCriteria & "[ItemName] = Like *'" &
Me![SearchName] & "'* And "
end if

' number
if len(Me![SearchPrice])>0 then
stLinkCriteria = stLinkCriteria & "[ItemPrice]= " &
Me![SearchPrice] & " And "
end if

' text
if len(trim(Me![SearchType] & ""))>0 then
stLinkCriteria = stLinkCriteria & "[ItemType] = Like *'" &
Me![SearchType] & "'* And "
end if

' number
if len(Me![SearchMenu])>0 then
stLinkCriteria = stLinkCriteria & "[ItemMenu]= " & Me![SearchMenu]
& " And "
end if

' number
if len(Me![SearchSales])>0 then
stLinkCriteria = stLinkCriteria & "[ItemSalesCat]= " &
Me![SearchSales] & " And "
end if

' number
if len(Me![SearchMajor])>0 then
stLinkCriteria = stLinkCriteria & "[ItemMajorCat]= " &
Me![SearchMajor] & " And "
end if

' number 8
if len(Me![SearchMinor])>0 then
stLinkCriteria = stLinkCriteria & "[ItemMinorCat]= " &
Me![SearchMinor] & " And "
end if

' number 9
if len(Me![SearchPrep])>0 then
stLinkCriteria = stLinkCriteria & "[ItemPrepCat]= " &
Me![SearchPrep] & " And "
end if

' number 10
if len(Me![SearchActive])>0 then
stLinkCriteria = stLinkCriteria & "[ItemActive]= " &
Me![SearchActive] & " And "
end if

' remove the last 4 chars (" And ")
if len(stLinkCriteria)>0 then
stLinkCriteria = left(stLinkCriteria, len(stLinkCriteria)-5)
end if

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenResult_Click:
Exit Sub

Err_cmdOpenResult_Click:
MsgBox Err.Description
Resume Exit_cmdOpenResult_Click

End Sub

'***** end code **********


HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I NEED A CODE FOR A SEARCH / FIND FROM ONE FORM TO ANOTHER. 3
VBA not working 1
Fiscal YTD Calculation Not Working Correctly 2
How to fix code with Collection error 4
Attn Sprinks - clarification on VB code 6
Attn Sprinks- Not duplicate insert records 1

Top