Sheils
asked on
asked on Error 2107 The value you entered doesn't meet the validation rule defined for the field or control
I am using the following code in a search form to create an sql to use as the recordsource for the form. It works fine except for when a Date To is selected.
It seems that the problem is with the codes between line 37 and 42. But the codes in these lines are almost identical to the codes in line 30-35 which works fine.
Public Sub SearchForm()
'Main body of the query
strSQL = "SELECT Distinct tblDocs.* FROM tblDocs INNER JOIN tblDocumentContents ON tblDocs.fldDocID=tblDocumentContents.fldDocID"
'Create Where conditions based on the selected search controls
strWhere = " WHERE tblDocs.fldDocID In (Select fldDocID From tblDocs "
strWhere = strWhere & "WHERE fldDocNameID Like " & Nz(Me.cboSearchDocuments, " '*' ") & ") "
If Not IsNull(Me.cboSearchPeople) Then
strWhere = strWhere & "AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldEntityID Like " & Me.cboSearchPeople & ")"
End If
If Not IsNull(Me.cboSearchPlaces) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldPlaceID Like " & Me.cboSearchPlaces & ")"
End If
If Not IsNull(Me.cboSearchTopics) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldTopicID Like " & Me.cboSearchTopics & ")"
End If
If Not IsNull(Me.txtDateFrom) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocs WHERE fldDate >= #" & Me.txtDateFrom & "#)"
End If
If Not IsNull(Me.txtDateTo) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocs WHERE fldDate <= #" & Me.txtDateTo & "#)"
End If
'Complete the SQL
strSQL = strSQL & " " & strWhere
'Assign strSQL to the form recordsource.
Me.RecordSource = strSQL
'Refresh form if there is no records
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "This search produce 0 records"
Call cmdRefresh_Click
End If
End Sub
It seems that the problem is with the codes between line 37 and 42. But the codes in these lines are almost identical to the codes in line 30-35 which works fine.
Microsoft AccessVB Script
Last Comment
Sheils
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
Sheils
OK I think that have seen the problem. I was using the before update event instead of after update by mistake.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SELECT Distinct tblDocs.* FROM
tblDocs INNER JOIN tblDocumentContents ON tblDocs.fldDocID=tblDocume
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDocNameID Like '*' ) AND
tblDocs.fldDocID In (Select fldDocID From tblDocs WHERE fldDate <= #02/03/2014#)