"Data type mismatch" error

  • Thread starter Thread starter eschloss
  • Start date Start date
E

eschloss

Access 2003 / WinXP

I'll preface this with the fact that I had this working (thanks to many
answered posts here) until, I believe, I started trying to get a subform's
combo box to automatically requery. That is a separate issue though.

I have a main form (frmSchedules) which has combo boxes used to create a
date range, and labels used to (when clicked) populate the subform objects
(Schedules_Subform) source object value.

I have another form (frmSchedules_Add/Edit) which is used as one of the
subforms. The code behind this form is giving me the error. There is a
combo box on this form which has querydefs attached to it.

As soon as I open the main form, I receive the Data type mismatch in
criteria expression which points to the following line of code behind the
frmSchedules_Add/Edit form:
Set rst = qdf.OpenRecordset()
When I escape out of the error popup, the subform will function as expected,
as coded so far. Any ideas why this is no longer working, or how it
initially was working?

Here is the code for most everything:

SQL behind combo box(QC_Names_Combo) on form(frmSchedules_Add/Edit):

PARAMETERS [forms]![frmSchedules]![Month_Combo1] Short,
[forms]![frmSchedules]![Day_Combo1] Short,
[forms]![frmSchedules]![Year_Combo1] Short,
[forms]![frmSchedules]![Month_Combo2] Short,
[forms]![frmSchedules]![Day_Combo2] Short,
[forms]![frmSchedules]![Year_Combo2] Short,
[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo] Text ( 255 );
SELECT DISTINCT L_tblDate.L_Date AS [Date], Format([L_Date],"dddd") AS
[Day], L_tblArea_Tech.L_Tech AS QC, tblSchedules.Start_Work,
tblSchedules.End_Work, tblSchedules.Schedules_Reasons AS Reason,
L_tblSchedules_Reasons.L_Schedules_Reasons_Time_Entry AS Time_Entry
FROM ((tblSchedules LEFT JOIN L_tblDate ON tblSchedules.Date =
L_tblDate.L_Date_ID) LEFT JOIN L_tblArea_Tech ON tblSchedules.Name =
L_tblArea_Tech.L_Area_Tech_ID) LEFT JOIN L_tblSchedules_Reasons ON
tblSchedules.Schedules_Reasons = L_tblSchedules_Reasons.L_Schedules_Reasons_ID
WHERE (((L_tblDate.L_Date)>=[forms]![frmSchedules]![Month_Combo1] & "/" &
[forms]![frmSchedules]![Day_Combo1] & "/" &
[forms]![frmSchedules]![Year_Combo1] And
(L_tblDate.L_Date)<=[forms]![frmSchedules]![Month_Combo2] & "/" &
[forms]![frmSchedules]![Day_Combo2] & "/" &
[forms]![frmSchedules]![Year_Combo2]) AND
((L_tblArea_Tech.L_Tech)=[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]))
ORDER BY L_tblDate.L_Date;


VB behind form(frmSchedules): No code pertaining to subforms in Form_Load()

Private Sub Main_Label_Click()
Me!Schedules_Subform.SourceObject = "frmSchedules_Main"
End Sub
Private Sub Add_Edit_Label_Click()
Me!Schedules_Subform.SourceObject = "frmSchedules_Add/Edit"
End Sub
Private Sub Admin_Label_Click()
Me!Schedules_Subform.SourceObject = "frmSchedules_Admin"
End Sub

VB behind form(frmSchedules_Add/Edit):

Private Sub Form_Load()
Me!QC_Names_Combo.Value = Me!QC_Names_Combo.ItemData(0)
Call QC_Names_Combo_AfterUpdate
End Sub

Private Sub QC_Names_Combo_AfterUpdate()

Me.QC_Names_Combo.Requery

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim monday_count As Integer
Dim tuesday_count As Integer
Dim wednesday_count As Integer
Dim thursday_count As Integer
Dim friday_count As Integer

monday_count = 0
tuesday_count = 0
wednesday_count = 0
thursday_count = 0
friday_count = 0

Set db = CurrentDb()
Set qdf = db.QueryDefs("qrySchedules_Add/Edit")
qdf.Parameters("[Forms]![frmSchedules]![Month_Combo1]") =
[Forms]![frmSchedules]![Month_Combo1]
qdf.Parameters("[Forms]![frmSchedules]![Day_Combo1]") =
[Forms]![frmSchedules]![Day_Combo1]
qdf.Parameters("[Forms]![frmSchedules]![Year_Combo1]") =
[Forms]![frmSchedules]![Year_Combo1]
qdf.Parameters("[Forms]![frmSchedules]![Month_Combo2]") =
[Forms]![frmSchedules]![Month_Combo2]
qdf.Parameters("[Forms]![frmSchedules]![Day_Combo2]") =
[Forms]![frmSchedules]![Day_Combo2]
qdf.Parameters("[Forms]![frmSchedules]![Year_Combo2]") =
[Forms]![frmSchedules]![Year_Combo2
qdf.Parameters("[Forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]") = [Forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]
Set rst = qdf.OpenRecordset()
'<-----ERROR LINE

rst.MoveLast
If rst.RecordCount = 0 Then
Else
rst.MoveFirst
Do While Not rst.EOF

Select Case rst!Day
Case "Monday"
monday_count = monday_count + 1

If IsNull(rst!Reason) Or rst!Time_Entry = True Then
Me!Monday_Text1.Enabled = 1
Me!Monday_Text2.Enabled = 1
Me!Monday_Combo.Enabled = 1
Else
Me!Monday_Combo.Enabled = 1
End If

Case "Tuesday"
tuesday_count = tuesday_count + 1

If IsNull(rst!Reason) Or rst!Time_Entry = True Then
Me!Tuesday_Text1.Enabled = 1
Me!Tuesday_Text2.Enabled = 1
Me!Tuesday_Combo.Enabled = 1
Else
Me!Tuesday_Combo.Enabled = 1
End If

Case "Wednesday"
wednesday_count = wednesday_count + 1

If IsNull(rst!Reason) Or rst!Time_Entry = True Then
Me!Wednesday_Text1.Enabled = 1
Me!Wednesday_Text2.Enabled = 1
Me!Wednesday_Combo.Enabled = 1
Else
Me!Wednesday_Combo.Enabled = 1
End If

Case "Thursday"
thursday_count = thursday_count + 1

If IsNull(rst!Reason) Or rst!Time_Entry = True Then
Me!Thursday_Text1.Enabled = 1
Me!Thursday_Text2.Enabled = 1
Me!Thursday_Combo.Enabled = 1
Else
Me!Thursday_Combo.Enabled = 1
End If

Case "Friday"
friday_count = friday_count + 1

If IsNull(rst!Reason) Or rst!Time_Entry = True Then
Me!Friday_Text1.Enabled = 1
Me!Friday_Text2.Enabled = 1
Me!Friday_Combo.Enabled = 1
Else
Me!Friday_Combo.Enabled = 1
End If

Case Else
MsgBox "Recordset pulling wrong info or weekend days."
End Select

rst.MoveNext
Loop
End If

qdf.Close
rst.Close
db.Close
Set rst = Nothing

End Sub
 
Hi,

If you create a new query, cancel the add table dialog, go to SQL view,
paste your SQL into it, and then run the query while the frmSchedules form is
open and shows the problem record/values, what happens?

Some comments:

1) You do not not need that entire PARAMETERS section--delete it.

2) Where you have "[forms]![frmSchedules]![Month_Combo1] & "/" &
[forms]![frmSchedules]![Day_Combo1] & "/" &
[forms]![frmSchedules]![Year_Combo1]", I would suggest changing it to
"DateSerial([forms]![frmSchedules]![Year_Combo1],
[forms]![frmSchedules]![Month_Combo1], [forms]![frmSchedules]![Day_Combo1])"

3) You may need to change
"[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]" to
"[forms]![frmSchedules]![Schedules_Subform].Form![QC_Names_Combo]".

Hope that helps,

Clifford Bass

eschloss said:
Access 2003 / WinXP

I'll preface this with the fact that I had this working (thanks to many
answered posts here) until, I believe, I started trying to get a subform's
combo box to automatically requery. That is a separate issue though.

I have a main form (frmSchedules) which has combo boxes used to create a
date range, and labels used to (when clicked) populate the subform objects
(Schedules_Subform) source object value.

I have another form (frmSchedules_Add/Edit) which is used as one of the
subforms. The code behind this form is giving me the error. There is a
combo box on this form which has querydefs attached to it.

As soon as I open the main form, I receive the Data type mismatch in
criteria expression which points to the following line of code behind the
frmSchedules_Add/Edit form:
Set rst = qdf.OpenRecordset()
When I escape out of the error popup, the subform will function as expected,
as coded so far. Any ideas why this is no longer working, or how it
initially was working?

Here is the code for most everything:

SQL behind combo box(QC_Names_Combo) on form(frmSchedules_Add/Edit):

PARAMETERS [forms]![frmSchedules]![Month_Combo1] Short,
[forms]![frmSchedules]![Day_Combo1] Short,
[forms]![frmSchedules]![Year_Combo1] Short,
[forms]![frmSchedules]![Month_Combo2] Short,
[forms]![frmSchedules]![Day_Combo2] Short,
[forms]![frmSchedules]![Year_Combo2] Short,
[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo] Text ( 255 );
SELECT DISTINCT L_tblDate.L_Date AS [Date], Format([L_Date],"dddd") AS
[Day], L_tblArea_Tech.L_Tech AS QC, tblSchedules.Start_Work,
tblSchedules.End_Work, tblSchedules.Schedules_Reasons AS Reason,
L_tblSchedules_Reasons.L_Schedules_Reasons_Time_Entry AS Time_Entry
FROM ((tblSchedules LEFT JOIN L_tblDate ON tblSchedules.Date =
L_tblDate.L_Date_ID) LEFT JOIN L_tblArea_Tech ON tblSchedules.Name =
L_tblArea_Tech.L_Area_Tech_ID) LEFT JOIN L_tblSchedules_Reasons ON
tblSchedules.Schedules_Reasons = L_tblSchedules_Reasons.L_Schedules_Reasons_ID
WHERE (((L_tblDate.L_Date)>=[forms]![frmSchedules]![Month_Combo1] & "/" &
[forms]![frmSchedules]![Day_Combo1] & "/" &
[forms]![frmSchedules]![Year_Combo1] And
(L_tblDate.L_Date)<=[forms]![frmSchedules]![Month_Combo2] & "/" &
[forms]![frmSchedules]![Day_Combo2] & "/" &
[forms]![frmSchedules]![Year_Combo2]) AND
((L_tblArea_Tech.L_Tech)=[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]))
ORDER BY L_tblDate.L_Date;
Click to expand...

[snip]
 
Clifford, I do not receive the error anymore. I'm still concerned though.

For the Source Object value of my main form(frmSchedules), I set it to
frmSchedules_Main instead of frmSchedules_Add/Edit. The form and all
subforms now work as coded without errors. frmSchedules_Main was going to be
my default subform anyways.

However, do you have any idea why I would get the Data type mismatch error
having a different Source Object value? I suspect I will run into this again
before I finish this form. Please let me know if you have any more
suggestions.

Clifford Bass said:
Hi,

If you create a new query, cancel the add table dialog, go to SQL view,
paste your SQL into it, and then run the query while the frmSchedules form is
open and shows the problem record/values, what happens?

Some comments:

1) You do not not need that entire PARAMETERS section--delete it.

2) Where you have "[forms]![frmSchedules]![Month_Combo1] & "/" &
[forms]![frmSchedules]![Day_Combo1] & "/" &
[forms]![frmSchedules]![Year_Combo1]", I would suggest changing it to
"DateSerial([forms]![frmSchedules]![Year_Combo1],
[forms]![frmSchedules]![Month_Combo1], [forms]![frmSchedules]![Day_Combo1])"

3) You may need to change
"[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]" to
"[forms]![frmSchedules]![Schedules_Subform].Form![QC_Names_Combo]".

Hope that helps,

Clifford Bass

eschloss said:
Access 2003 / WinXP

I'll preface this with the fact that I had this working (thanks to many
answered posts here) until, I believe, I started trying to get a subform's
combo box to automatically requery. That is a separate issue though.

I have a main form (frmSchedules) which has combo boxes used to create a
date range, and labels used to (when clicked) populate the subform objects
(Schedules_Subform) source object value.

I have another form (frmSchedules_Add/Edit) which is used as one of the
subforms. The code behind this form is giving me the error. There is a
combo box on this form which has querydefs attached to it.

As soon as I open the main form, I receive the Data type mismatch in
criteria expression which points to the following line of code behind the
frmSchedules_Add/Edit form:
Set rst = qdf.OpenRecordset()
When I escape out of the error popup, the subform will function as expected,
as coded so far. Any ideas why this is no longer working, or how it
initially was working?

Here is the code for most everything:

SQL behind combo box(QC_Names_Combo) on form(frmSchedules_Add/Edit):

PARAMETERS [forms]![frmSchedules]![Month_Combo1] Short,
[forms]![frmSchedules]![Day_Combo1] Short,
[forms]![frmSchedules]![Year_Combo1] Short,
[forms]![frmSchedules]![Month_Combo2] Short,
[forms]![frmSchedules]![Day_Combo2] Short,
[forms]![frmSchedules]![Year_Combo2] Short,
[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo] Text ( 255 );
SELECT DISTINCT L_tblDate.L_Date AS [Date], Format([L_Date],"dddd") AS
[Day], L_tblArea_Tech.L_Tech AS QC, tblSchedules.Start_Work,
tblSchedules.End_Work, tblSchedules.Schedules_Reasons AS Reason,
L_tblSchedules_Reasons.L_Schedules_Reasons_Time_Entry AS Time_Entry
FROM ((tblSchedules LEFT JOIN L_tblDate ON tblSchedules.Date =
L_tblDate.L_Date_ID) LEFT JOIN L_tblArea_Tech ON tblSchedules.Name =
L_tblArea_Tech.L_Area_Tech_ID) LEFT JOIN L_tblSchedules_Reasons ON
tblSchedules.Schedules_Reasons = L_tblSchedules_Reasons.L_Schedules_Reasons_ID
WHERE (((L_tblDate.L_Date)>=[forms]![frmSchedules]![Month_Combo1] & "/" &
[forms]![frmSchedules]![Day_Combo1] & "/" &
[forms]![frmSchedules]![Year_Combo1] And
(L_tblDate.L_Date)<=[forms]![frmSchedules]![Month_Combo2] & "/" &
[forms]![frmSchedules]![Day_Combo2] & "/" &
[forms]![frmSchedules]![Year_Combo2]) AND
((L_tblArea_Tech.L_Tech)=[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]))
ORDER BY L_tblDate.L_Date;
Click to expand...

[snip]
Click to expand...
 
Hi,

Answers also below. Will post to your other message to.

Clifford Bass

eschloss said:
Thanks for helping Clifford, I've answered your questions below...



I grabbed the SQL from the query(qrySchedules_Add/Edit) used in my code.
When ran with the frmSchedules open (after I exit out of the Data type
mismatch error), the query doesn't return any records because the criteria
for the "QC" field isn't supplied with a value. No errors are generated.
The "QC" field pulls its value from the subform(frmSchedules_Add/Edit) and
control(QC_Names_Combo). As it is now, I have to manually enter a value in
this control.
When I manually enter in a value, I rerun the query and the correct record
is returned.
Click to expand...

So the SQL is fine. Possibly the problem was caused by this line:

qdf.Parameters("[Forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]") = [Forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]

when QC_Names_Combo is null.


The parameters section in the query or in the QueryDefs section of the code?
When I was constructing this query(qrySchedules_Add/Edit), I added the
parameters because I would get an error like "expecting 7 parameters". That
is not the exact error since I can't duplicate it now, but adding those
parameters made it go away.
Click to expand...

Were you designing the query while the form was showing in form view?
The "[forms]![frmSchedules]![Month_Combo1]" specifically tells it to get the
value from the specified control on an open form of that name, without making
it a parameter. (So no need to specify the parameter values in the code
either.)
2) Where you have "[forms]![frmSchedules]![Month_Combo1] & "/" &
[forms]![frmSchedules]![Day_Combo1] & "/" &
[forms]![frmSchedules]![Year_Combo1]", I would suggest changing it to
"DateSerial([forms]![frmSchedules]![Year_Combo1],
[forms]![frmSchedules]![Month_Combo1], [forms]![frmSchedules]![Day_Combo1])"
Click to expand...

Good idea.
3) You may need to change
"[forms]![frmSchedules]![Schedules_Subform]![QC_Names_Combo]" to
"[forms]![frmSchedules]![Schedules_Subform].Form![QC_Names_Combo]".
Click to expand...

When I do this, I now get error 3265, Item not found in this collection.
Click to expand...

Since you are dynamically switching the subform, you would need a
QC_Names_Combo on all of the subforms.
 
Hi,

This may take some detective work. If it does occur again, I think the
key will be in trying the query from the query design tool when it does not
work. Then you can narrow it down to either something about the code, or
something about the query. If the query, you could add in as separate
display columns each of the [Forms]!... items. Then you could view the
actual data that it is using from the form. So:

SELECT DISTINCT L_tblDate.L_Date ..., [forms]![frmSchedules]![Month_Combo1],
....

For values that should be numeric, make sure they are all numbers. By
the by, I see you do a rst.MoveLast. When viewing the data in the query
designer, did you go to the end? It could be that the problem does not crop
up on the first screen of data, but later on in the dataset.

Or, if you have not done so, and the problem is not in the query, you
could add a break point in the code such as on the Set db = CurrentDb() line.
Then test and when the code breaks use Shift-F8 to step through until you
get to the line that causes the problem. Then by hovering your cursor over
the various variables you can see the actual values being used.

Hope that helps,

Clifford Bass
 
Back
Top