StrWhere with multi search results forms

J

Jon

Hi,
I have a form used to perform search in 3 tables and open 3 result forms
accordingly.
I am using the following code which adapted from (Allen Browne website) but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
‘====================================== First group of txtboxs for form
“DeletedSTDispalyEntryâ€
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo & "*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"") AND "
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes & "*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf & "*"") AND "
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND "
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
‘====================================== First group of txtboxs for form
“DeletedSPDispalyEntry†If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3 &
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"") AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3 &
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 & "*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"") AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
‘==================================== First group of txtboxs for form
“DeletedCSPVDispalyEntry†If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2 & "*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2 &
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 & "*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2 &
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 & "*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"") AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 & "*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
 
B

BruceM

What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see whether
the string is being built as expected. Have you tried stepping through the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Jon said:
Hi,
I have a form used to perform search in 3 tables and open 3 result forms
accordingly.
I am using the following code which adapted from (Allen Browne website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
'====================================== First group of txtboxs for form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo & "*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"") AND "
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes & "*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf & "*"") AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND "
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3 &
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"") AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3 &
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 & "*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"") AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2 &
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2 &
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 & "*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2 &
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 & "*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"") AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 & "*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
 
J

Jon

Hi BruceM,

what happend is access is asking "Enter Parameter value" for the case 2,3 if
i selected case one, like [PlantID2],[KID2],[Part#].....

BruceM said:
What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see whether
the string is being built as expected. Have you tried stepping through the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Jon said:
Hi,
I have a form used to perform search in 3 tables and open 3 result forms
accordingly.
I am using the following code which adapted from (Allen Browne website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
'====================================== First group of txtboxs for form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo & "*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"") AND "
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes & "*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf & "*"") AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND "
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3 &
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"") AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3 &
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 & "*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"") AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2 &
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2 &
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 & "*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2 &
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 & "*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"") AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 & "*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
 
B

BruceM

I would guess the form is based on a query that does not contain the
Parameter Value fields. You say you are searching three tables, but if
those tables are not part of the form's record source you will get errors.

Jon said:
Hi BruceM,

what happend is access is asking "Enter Parameter value" for the case 2,3
if
i selected case one, like [PlantID2],[KID2],[Part#].....

BruceM said:
What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see whether
the string is being built as expected. Have you tried stepping through
the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Jon said:
Hi,
I have a form used to perform search in 3 tables and open 3 result
forms
accordingly.
I am using the following code which adapted from (Allen Browne website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
'====================================== First group of txtboxs for
form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo &
"*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"") AND
"
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes &
"*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf & "*"")
AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND
"
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3 &
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"")
AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3 &
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 &
"*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"")
AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2 &
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2 &
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2 &
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"")
AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 &
"*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
Click to expand...
 
J

Jon

It is not bounded form, it is unbounded and it used the 3 forms to pass the
StrWhere value to them, you may read the code again

BruceM said:
I would guess the form is based on a query that does not contain the
Parameter Value fields. You say you are searching three tables, but if
those tables are not part of the form's record source you will get errors.

Jon said:
Hi BruceM,

what happend is access is asking "Enter Parameter value" for the case 2,3
if
i selected case one, like [PlantID2],[KID2],[Part#].....

BruceM said:
What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see whether
the string is being built as expected. Have you tried stepping through
the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Hi,
I have a form used to perform search in 3 tables and open 3 result
forms
accordingly.
I am using the following code which adapted from (Allen Browne website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
'====================================== First group of txtboxs for
form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo &
"*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"") AND
"
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes &
"*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf & "*"")
AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND
"
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3 &
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"")
AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3 &
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 &
"*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"")
AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2 &
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2 &
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2 &
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"")
AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 &
"*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

I am asking questions because I did try to read the code. I suggested using
Debug.Print to see what is happening with strWhere, and I suggested stepping
through the code. Please try one or the other (or both).

Access is asking for things it cannot find. I do not know why it cannot
find them because I cannot see your database. You need to do some
diagnosis.

Another thing to try is to build strWhere to open one form. Comment out any
code that does not relate to that form. If that works, try commenting out
all code but what is used by the second form. In other words, break it down
into manageable sections.

Jon said:
It is not bounded form, it is unbounded and it used the 3 forms to pass
the
StrWhere value to them, you may read the code again

BruceM said:
I would guess the form is based on a query that does not contain the
Parameter Value fields. You say you are searching three tables, but if
those tables are not part of the form's record source you will get
errors.

Jon said:
Hi BruceM,

what happend is access is asking "Enter Parameter value" for the case
2,3
if
i selected case one, like [PlantID2],[KID2],[Part#].....

:

What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see
whether
the string is being built as expected. Have you tried stepping
through
the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Hi,
I have a form used to perform search in 3 tables and open 3 result
forms
accordingly.
I am using the following code which adapted from (Allen Browne
website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
'====================================== First group of txtboxs for
form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo &
"*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"")
AND
"
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes &
"*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf &
"*"")
AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"")
AND
"
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for
form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3
&
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"")
AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3
&
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 &
"*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"")
AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for
form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2
&
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2
&
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2
&
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"")
AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 &
"*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
Click to expand...
 
J

Jon

Hi BruceM,

Could you please tell me where should I put "Debug.Print"?

BruceM said:
I am asking questions because I did try to read the code. I suggested using
Debug.Print to see what is happening with strWhere, and I suggested stepping
through the code. Please try one or the other (or both).

Access is asking for things it cannot find. I do not know why it cannot
find them because I cannot see your database. You need to do some
diagnosis.

Another thing to try is to build strWhere to open one form. Comment out any
code that does not relate to that form. If that works, try commenting out
all code but what is used by the second form. In other words, break it down
into manageable sections.

Jon said:
It is not bounded form, it is unbounded and it used the 3 forms to pass
the
StrWhere value to them, you may read the code again

BruceM said:
I would guess the form is based on a query that does not contain the
Parameter Value fields. You say you are searching three tables, but if
those tables are not part of the form's record source you will get
errors.

Hi BruceM,

what happend is access is asking "Enter Parameter value" for the case
2,3
if
i selected case one, like [PlantID2],[KID2],[Part#].....

:

What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see
whether
the string is being built as expected. Have you tried stepping
through
the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Hi,
I have a form used to perform search in 3 tables and open 3 result
forms
accordingly.
I am using the following code which adapted from (Allen Browne
website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
'====================================== First group of txtboxs for
form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo &
"*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"")
AND
"
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes &
"*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf &
"*"")
AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"")
AND
"
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for
form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3
&
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"")
AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3
&
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 &
"*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"")
AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for
form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2
&
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2
&
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2
&
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"")
AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 &
"*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

You could put it after each If statement. For instance:

If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & _
"([PlantID] Like ""*" & Me.txtPlantNo & "*"") AND "
End If

Debug.Print strWhere

After the code runs, press Ctrl + G to bring up the VBA editor Immediate
window. There will be a printed line for each strWhere (until you run into
an error in the code). You could also do something like this to make it
easier to sort out:

Debug.Print "PlantID: " & strWhere

For the next If it could be:

Debug.Print "KID: " & strWhere

The idea is to associate each printed line in the immediate window with a
specific part of the code.

To step through the code, click the vertical bar (just to the left of the
code window in the VBA editor) next to a line of code. The first line of
the first If would be a good place:
If Not IsNull(Me.txtPlantNo) Then

This is called "setting a break point." When you run the code it will break
at that line. Press F8 to move through the code one line at a time. Press
F5 to skip to the end or to the next break point. As you step through the
code, note the strWhere printout (from the Debug.Print lines of code) in the
immediate window. When the code runs into a problem you can see exactly
which line of code is responsible.
..
Jon said:
Hi BruceM,

Could you please tell me where should I put "Debug.Print"?

BruceM said:
I am asking questions because I did try to read the code. I suggested
using
Debug.Print to see what is happening with strWhere, and I suggested
stepping
through the code. Please try one or the other (or both).

Access is asking for things it cannot find. I do not know why it cannot
find them because I cannot see your database. You need to do some
diagnosis.

Another thing to try is to build strWhere to open one form. Comment out
any
code that does not relate to that form. If that works, try commenting
out
all code but what is used by the second form. In other words, break it
down
into manageable sections.

Jon said:
It is not bounded form, it is unbounded and it used the 3 forms to pass
the
StrWhere value to them, you may read the code again

:

I would guess the form is based on a query that does not contain the
Parameter Value fields. You say you are searching three tables, but
if
those tables are not part of the form's record source you will get
errors.

Hi BruceM,

what happend is access is asking "Enter Parameter value" for the
case
2,3
if
i selected case one, like [PlantID2],[KID2],[Part#].....

:

What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see
whether
the string is being built as expected. Have you tried stepping
through
the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Hi,
I have a form used to perform search in 3 tables and open 3
result
forms
accordingly.
I am using the following code which adapted from (Allen Browne
website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected
for
dates
in a JET query string.
'====================================== First group of txtboxs
for
form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" &
Me.txtPlantNo &
"*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo &
"*"")
AND
"
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" &
Me.txtDes &
"*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf &
"*"")
AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox &
"*"")
AND
"
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs
for
form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" &
Me.txtPlantNo3
&
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 &
"*"")
AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" &
Me.txtDes3
&
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3
&
"*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 &
"*"")
AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for
form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" &
Me.cboPlantN2
&
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" &
Me.txtDes2
&
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2
&
"*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" &
Me.txtModel2
&
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" &
Me.txtStockNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 &
"*"")
AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2
&
"*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry",
WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry",
WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
Click to expand...
 
G

gregfoley

BruceM said:
I would guess the form is based on a query that does not contain the
Parameter Value fields. You say you are searching three tables, but if
those tables are not part of the form's record source you will get errors.

Jon said:
Hi BruceM,

what happend is access is asking "Enter Parameter value" for the case 2,3
if
i selected case one, like [PlantID2],[KID2],[Part#].....

BruceM said:
What exactly doesn't work? From what code was this adapted?

You could add Debug.Print strWhere after each If statement to see
whether
the string is being built as expected. Have you tried stepping through
the
code?

This line may not work as intended:
If Me.FilterOn Then strWhere = Me.Filter

Unless I misunderstand, I would do:
Me.Filter = strWhere
Me.FilterOn = True

The Select Case doesn't look right. I think this line:
Case 1, Is = 1 '=> 1
should be simply:
Case 1
Same for the other Cases.


Hi,
I have a form used to perform search in 3 tables and open 3 result
forms
accordingly.
I am using the following code which adapted from (Allen Browne
website)
but
it does not work:
Dim strWhere As String 'The criteria string.
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
'====================================== First group of txtboxs for
form
"DeletedSTDispalyEntry"
If Not IsNull(Me.txtPlantNo) Then
strWhere = strWhere & "([PlantID] Like ""*" & Me.txtPlantNo &
"*"")
AND "
End If

If Not IsNull(Me.txtKNo) Then
strWhere = strWhere & "([KID] Like ""*" & Me.txtKNo & "*"") AND
"
End If

If Not IsNull(Me.txtDes) Then
strWhere = strWhere & "([DESCRIPTION] Like ""*" & Me.txtDes &
"*"")
AND "
End If

If Not IsNull(Me.txtShelf) Then
strWhere = strWhere & "([Shelf] Like ""*" & Me.txtShelf & "*"")
AND
"
End If
If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND
"
End If
If Me.Deletedbox = -1 Then
strWhere = strWhere & "([OptionDelete] = -1) AND "
End If
'====================================== First group of txtboxs for
form
"DeletedSPDispalyEntry" If Not IsNull(Me.txtPlantNo3) Then
strWhere = strWhere & "([PlantID2] Like ""*" & Me.txtPlantNo3 &
"*"") AND "
End If
If Not IsNull(Me.txtKNo3) Then
strWhere = strWhere & "([KID2] Like ""*" & Me.txtKNo3 & "*"")
AND "
End If
If Not IsNull(Me.txtDes3) Then
strWhere = strWhere & "([DESCRIPTION2] Like ""*" & Me.txtDes3 &
"*"") AND "
End If
If Not IsNull(Me.txtShelf3) Then
strWhere = strWhere & "([Shelf2] Like ""*" & Me.txtShelf3 &
"*"")
AND "
End If
If Not IsNull(Me.txtBox3) Then
strWhere = strWhere & "([box2] Like ""*" & Me.txtBox3 & "*"")
AND "
End If
If Me.Deletedbox3 = -1 Then
strWhere = strWhere & "([OptionDelete2] = -1) AND "
End If
'==================================== First group of txtboxs for form
"DeletedCSPVDispalyEntry" If Not IsNull(Me.cboPlantN2) Then
strWhere = strWhere & "([PlantID3] Like ""*" & Me.cboPlantN2 &
"*"")
AND "
End If

If Not IsNull(Me.txtDes2) Then
strWhere = strWhere & "([DESCRIPTION3] Like ""*" & Me.txtDes2 &
"*"") AND "
End If

If Not IsNull(Me.txtPartNo2) Then
strWhere = strWhere & "([Part#] Like ""*" & Me.txtPartNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtModel2) Then
strWhere = strWhere & "([Model&Type] Like ""*" & Me.txtModel2 &
"*"") AND "
End If

If Not IsNull(Me.txtStockNo2) Then
strWhere = strWhere & "([Stock#] Like ""*" & Me.txtStockNo2 &
"*"")
AND "
End If

If Not IsNull(Me.txtMatStatus2) Then
strWhere = strWhere & "([MaterialStatus] Like ""*" &
Me.txtMatStatus2 & "*"") AND "
End If

If Not IsNull(Me.txtbox2) Then
strWhere = strWhere & "([box3] Like ""*" & Me.txtbox2 & "*"")
AND "
End If
If Not IsNull(Me.txtShelf2) Then
strWhere = strWhere & "([Shelf3] Like ""*" & Me.txtShelf2 &
"*"")
AND "
End If
If Me.Deletedbox2 = -1 Then
strWhere = strWhere & "([OptionDelete3] = -1) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
If Me.FilterOn Then strWhere = Me.Filter
Select Case Me.CatOption.Value
Case 1, Is = 1 '=> 1
DoCmd.OpenForm "DeletedSTDispalyEntry", WhereCondition:=strWhere
Case 2, Is = 2 '=> 2
DoCmd.OpenForm "DeletedSPDispalyEntry", WhereCondition:=strWhere
Case 3, Is = 3 '=> 3
DoCmd.OpenForm "DeletedCSPVDispalyEntry", WhereCondition:=strWhere
End Select
End If

Any help please??
Click to expand...
Click to expand...
Click to expand...
 

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

Filters not responding 2
MultiSelect List Box to Filter Form 4
Multiple Criteria Search Form 3
Filtering search results 7
filter by multiple combo boxes 2
Get the sum of Filtered form 0
Datasheet view alignment after filtering on subform 3
Report based on form filter? 1

Top