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??
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??