Run-Time Error '2107'

[Ltexeira]

The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[John Spencer]

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Ltexeira]

John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[John Spencer]

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[Ltexeira]

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[John Spencer]

Another problem is the use of DATE as a field name. If you are going to
use this reserved word (Date is a function that returns the current
system date), then you must surround it with square brackets.


strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
" and [Date] = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

Again if Unit Number is a text field you will need the Chr(34) added.
If it is a number field then remove the Chr(34)

IF that is still causing you problems. Add the following to your code
after the strSQL is built.

Debug.Print strSQL: Stop

Copy the SQL string and paste it into a new query. Try to run the
query, if it fails you may get a better error message and you may get
the cursor to point to the location of the error. Tweak the SQL until
it works. Once it does then go back and fix the strSQL statement.

Comment out or delete the Debug.Print line

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

:

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[Ltexeira]

I'll give it all a shot. Thanks for your time, John. It is appreciated.

Lonny

Another problem is the use of DATE as a field name. If you are going to
use this reserved word (Date is a function that returns the current
system date), then you must surround it with square brackets.


strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
" and [Date] = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

Again if Unit Number is a text field you will need the Chr(34) added.
If it is a number field then remove the Chr(34)

IF that is still causing you problems. Add the following to your code
after the strSQL is built.

Debug.Print strSQL: Stop

Copy the SQL string and paste it into a new query. Try to run the
query, if it fails you may get a better error message and you may get
the cursor to point to the location of the error. Tweak the SQL until
it works. Once it does then go back and fix the strSQL statement.

Comment out or delete the Debug.Print line

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

:

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[Ltexeira]

Okay, here is what happened. I changed the field name to I_Date to eliminate
that problem.

I followed your instructions, did the Debug.Print, copied the statement to a
query, which RAN FINE. Ack! Query follows :

SELECT *
FROM inspections
WHERE [Unit Number]=453 And I_Date=#4/22/2008#;


It pulls up the proper record just fine. It's got to be something about one
of the fields on the form. Can you give me some direction about what I
should be looking for? This is getting ridiculous..

Thanks again for helping out and taking the time to go over this with me.

Lonny

Another problem is the use of DATE as a field name. If you are going to
use this reserved word (Date is a function that returns the current
system date), then you must surround it with square brackets.


strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
" and [Date] = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

Again if Unit Number is a text field you will need the Chr(34) added.
If it is a number field then remove the Chr(34)

IF that is still causing you problems. Add the following to your code
after the strSQL is built.

Debug.Print strSQL: Stop

Copy the SQL string and paste it into a new query. Try to run the
query, if it fails you may get a better error message and you may get
the cursor to point to the location of the error. Tweak the SQL until
it works. Once it does then go back and fix the strSQL statement.

Comment out or delete the Debug.Print line

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

:

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[Ltexeira]

Well, it gets stranger .. for me. I have used your guidance to eliminate the
obvious (to you) problems. Here is the current, revised situation.

When there IS a record on the Inspections table to read, the line :

Me.Recordsource = strSql

fires off a 'Form_BeforeUpdate' event. This, in turn writes a new record
out to the table that contains only the Unit_Number and the I_Date.
Apparently, prior to allowing the record to be assigned to the form, it wants
to write the current form out to the table, and that current form contains
only the Unit_Number and the I_Date. So in the Form_BeforeUpdate event I
have placed 'Cancel=True' line. I am using flags to determine if the time is
during the record read and load so that the cancel isn't all the time. It
blows up during that cancel.

If I comment out the cancel, it works great, except that I get a bunch of
skeletal records as previously noted.

So the problem remains, but now my understanding of it, and therefore it's
description has changed. What am I doing wrong, and how do I fix it?

Again, thanks for the help!

Another problem is the use of DATE as a field name. If you are going to
use this reserved word (Date is a function that returns the current
system date), then you must surround it with square brackets.


strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
" and [Date] = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

Again if Unit Number is a text field you will need the Chr(34) added.
If it is a number field then remove the Chr(34)

IF that is still causing you problems. Add the following to your code
after the strSQL is built.

Debug.Print strSQL: Stop

Copy the SQL string and paste it into a new query. Try to run the
query, if it fails you may get a better error message and you may get
the cursor to point to the location of the error. Tweak the SQL until
it works. Once it does then go back and fix the strSQL statement.

Comment out or delete the Debug.Print line

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

:

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[John Spencer]

Sorry, at this point I am stuck. I can't see how the SQL is adding a record.

HMMM, a thought. Are txt_Unit_Number and txt_Date controls bound to fields?
That is do they have a Control Source. They should NOT be if you are using
them to input filter/query parameters. If they are bound to fields then you
are creating (or modifying) the record that is being displayed on the form and
the creation of the record has nothing to do with changing the source - other
than changing the source of the form triggers the form's before update event.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Well, it gets stranger .. for me. I have used your guidance to eliminate the
obvious (to you) problems. Here is the current, revised situation.

When there IS a record on the Inspections table to read, the line :

Me.Recordsource = strSql

fires off a 'Form_BeforeUpdate' event. This, in turn writes a new record
out to the table that contains only the Unit_Number and the I_Date.
Apparently, prior to allowing the record to be assigned to the form, it wants
to write the current form out to the table, and that current form contains
only the Unit_Number and the I_Date. So in the Form_BeforeUpdate event I
have placed 'Cancel=True' line. I am using flags to determine if the time is
during the record read and load so that the cancel isn't all the time. It
blows up during that cancel.

If I comment out the cancel, it works great, except that I get a bunch of
skeletal records as previously noted.

So the problem remains, but now my understanding of it, and therefore it's
description has changed. What am I doing wrong, and how do I fix it?

Again, thanks for the help!

Another problem is the use of DATE as a field name. If you are going to
use this reserved word (Date is a function that returns the current
system date), then you must surround it with square brackets.


strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
" and [Date] = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

Again if Unit Number is a text field you will need the Chr(34) added.
If it is a number field then remove the Chr(34)

IF that is still causing you problems. Add the following to your code
after the strSQL is built.

Debug.Print strSQL: Stop

Copy the SQL string and paste it into a new query. Try to run the
query, if it fails you may get a better error message and you may get
the cursor to point to the location of the error. Tweak the SQL until
it works. Once it does then go back and fix the strSQL statement.

Comment out or delete the Debug.Print line

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

:

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

:

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.

 

[Lendog]

Lterxeira,

Please forgive me if I have misunderstood your problem after going through
this thread, but are you trying to view/load only one record at a time out of
a table? If so, why not try the filter properties associated with the form
itself. You can set the "Filter" using the same Where criteria as you used
in your sql statement, just don'tinclude the word WHERE. You can turn th
filter on and off with the "FilterOn" property.

Lendog

Well, it gets stranger .. for me. I have used your guidance to eliminate the
obvious (to you) problems. Here is the current, revised situation.

When there IS a record on the Inspections table to read, the line :

Me.Recordsource = strSql

fires off a 'Form_BeforeUpdate' event. This, in turn writes a new record
out to the table that contains only the Unit_Number and the I_Date.
Apparently, prior to allowing the record to be assigned to the form, it wants
to write the current form out to the table, and that current form contains
only the Unit_Number and the I_Date. So in the Form_BeforeUpdate event I
have placed 'Cancel=True' line. I am using flags to determine if the time is
during the record read and load so that the cancel isn't all the time. It
blows up during that cancel.

If I comment out the cancel, it works great, except that I get a bunch of
skeletal records as previously noted.

So the problem remains, but now my understanding of it, and therefore it's
description has changed. What am I doing wrong, and how do I fix it?

Again, thanks for the help!

Another problem is the use of DATE as a field name. If you are going to
use this reserved word (Date is a function that returns the current
system date), then you must surround it with square brackets.


strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
" and [Date] = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

Again if Unit Number is a text field you will need the Chr(34) added.
If it is a number field then remove the Chr(34)

IF that is still causing you problems. Add the following to your code
after the strSQL is built.

Debug.Print strSQL: Stop

Copy the SQL string and paste it into a new query. Try to run the
query, if it fails you may get a better error message and you may get
the cursor to point to the location of the error. Tweak the SQL until
it works. Once it does then go back and fix the strSQL statement.

Comment out or delete the Debug.Print line

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Run-time error 3464.
Data type mismatch in criteria expression.

It doesn't like the quotes around the unit number, apparently.

Anything else I should look at?

:

Did you check to see if UNIT Number is a text field or if it is a number
field? It probably should be a text field since you are not going to be doing
arithmetic with a Unit Number? If it is a text field then you need to change
the SQL statement to something like the following. Also, I would format the
date with four characters for the year. Probably not a problem, but it is a
"normal" practice for me to do so, just in case.

strSql = "SELECT * from inspections where " & _
" [Unit Number] = " & chr(34) & txt_Unit_Number.Value & Chr(34)& _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yyyy") & "#"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
John,

Thanks for the reply, and sorry about the typo (DOA) but it was appropriate!

I made that change, and then get error 3464, Data type
mismatch in criteria expression, so I'm guessing it has to be something else.

Where's the next place I should be looking?

Thank you again.

:

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset '<<<<<<<<<<
Dim rstInspection as DAO.Recordset

'Assumes the Unit Number is a Number field, if it is a text
'field then you need to add quote marks into the string to
'properly delimit txt_Unit_Number.Value
'one method of doing so is
'...= " & Chr(34) & txt_Unit_Number.Value & Chr(34) & _
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"

Set rstInspection = CurrentDb.OpenRecordset(strSql)

If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Ltexeira wrote:
The following code :

Private Sub txt_Date_AfterUpdate()
Dim strSql As String
Dim rstInspection as DOA.Recordset
strSql = "select * from inspections where " & _
" [Unit Number] = " & txt_Unit_Number.Value & _
" and Date = #" & Format(txt_Date.Value, "mm/dd/yy") & "#"
Set rstInspection = CurrentDb.OpenRecordset(strSql)
If rstInspection.RecordCount > 0 Then
Me.RecordSource = strSql
End If
End Sub

produces the following error on the 'Me.RecordSource = strSql' line

Run-Time Error '2107'

The value you entered doesn't meet the validation rule defined for the field
or control.

I found no validation rules on the controls. What else would cause this?

txt_Unit_Number is entered prior to txt_Date and is verified against another
table.

I did notice that the input mask for txt_Date is 99/99/00;0 but the data is
stored on the file in the form 99/99/9999. Is this a problem?

Thank you for any guidance.