Can't Figure out Type Data Mismatch error

[Caplan1269]

Hello all,



Thank you in advance for your help. I am a very low level access user that mostly dabbles, so I apologize for anything that might be frowned upon. I mostly pick up code from these forums and try to make them work for me. I have the following code that is giving me a data type mismatch error:

Private Sub SubClassNumber_AfterUpdate()

Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = " SELECT ClassDescription " & _
"FROM tblClassAll " & _
"WHERE SubClassNumber = " & Me.SubClassNumber.Value & "" _
And ClassNumber = " & Me.ClassNumber.Value & " _
And DepartmentNumber = " & Me.DepartmentNumber.Value & "
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Me.ClassDescription.Value = rst!ClassDescription
rst.Close
Set rst = Nothing

End Sub

Long and short... I am trying to autopopulate the ClassDescriptionField (text), based upon the selections made under DepartmentNumber (number), ClassNumber (number), and SubClassNumber (number). All of these fields reside on the same table tblClassAll. tblClassAll is simply a record source table listing all of the different Departments, Classes, Sub Classes and Class Descriptions. The form does not update tblClassAll, it updates a table called tblOrderInfo, which has all of the fields referenced under tblClassAll plus some other fields.

To probably further complicate things, the DepartmentNumber, ClassNumber and SubClassNumber fields in the form are cascading combo boxes, so as to limit the selections under ClassNumber depending upon the DepartmentNumber selected, and to limit the SubClassNumber selections depending upon the ClassNumber selected.

At the end of the day I want the person to select the Department, and only be given Class numbers under that Department. Then select the Class number and only be given the Sub Class Numbers under that Department and Class. Then select the Sub Class Number and have the Class Description autopopulate based on all three selections. I already have the cascading feature working, just need that last part.

If I have not provided adequate information please let me know what you need, and I can supply. Thanks!

[pbaldy]

Use this technique to make sure the SQL is coming out like you expect:

http://www.baldyweb.com/ImmediateWindow.htm

You've got some stray quotes there, and haven't restarted the string correctly.

[Caplan1269]

Thank you Paul. I happened upon that website on my search and added in the "Debug.Print strSQL", but I still get the same error and nothing else. I did figure out one thing, but it is not ideal. If I write the WHERE portion of the clause all on one line, it works as intended, but the minute I add _ (with a space) and press enter to make multiple lines, it stops working. Any thoughts?

Here is what it looks like all on one line: " WHERE SubClassNumber = " & Me.SubClassNumber.Value & " And ClassNumber = " & Me.ClassNumber.Value & " And DepartmentNumber = " & Me.DepartmentNumber.Value & ""

I've tried to put the _ before the And and after the And, neither way seems to work.

[NTC]

for trouble shooting purposes - set up the query using the standard query design approach.....it is easier to debug a field type mismatch this way by adding field by field until the mismatch appears.....it should then give you insight on how to resolve....

hope this helps.....

[pbaldy]

Like I said, you aren't restarting the string. After a form reference, add the continuation characters and then hit enter.

[Caplan1269]

Thank you for the help. It is working now. I was having a lot of difficulty figuring out where to put the quotes, and also where not to put them. I'm including the finished code for anyone's reference who views the thread. If there are any issues with the way it is written, please comment.

Private Sub SubClassNumber_AfterUpdate()

Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = " SELECT ClassDescription " & _
" FROM tblClassAll " & _
" WHERE SubClassNumber = " & Me.SubClassNumber.Value & _
" And ClassNumber = " & Me.ClassNumber.Value & _
" And DepartmentNumber = " & Me.DepartmentNumber.Value & ""

Debug.Print strSQL

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Me.ClassDescription.Value = rst!ClassDescription
rst.Close
Set rst = Nothing

End Sub

[pbaldy]

No problem. One day the quotes thing will just click, and it will seem easy.