Run Time Error 13 - Type Mismatch

[ardy]

Folks I am going crazy:banghead:.........Can't figure this out....

    Dim Num As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
   
    
        
    Num = Me.Well_ID ' Reading an String from a field in the form
    MsgBox "Form WellID ----> " & Num ' making sure I am reading the string
    
    
     strSQL = "SELECT [Assets.Well_ID] " & "From Assets " & _
     "WHERE [Assets.Well_ID] =" & Chr$(39) & Num & Chr$(39)
     
     Debug.Print strSQL
             
     
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

MsgBox rs

It runs but when it gets to MsgBox it gives me the error.

 

[Mihail]

Next time please ! use a color to show us the line with error (red is my preference ) because I stop more than 2 minutes in order to understand what can be wrong with your FIRST message box.

The message box do what the name say: display a message, a string.
How can a message box to display an entire record set ? A record set can contain millions of records, isn't it ?

 

[ardy]

Fair enough:
My assumption is that the sql string is returning one record, well one field from a record I guess.

The MsgBox rs is the one causing the error 13 type Mismatch, but it could be the strSQL string also I can't tell.

From your response I guess the issue could be in

[FONT=Arial][FONT=Arial][FONT=Arial][SIZE=3]Set rs = db.OpenRecordset(strSQL[/SIZE][/FONT][/FONT][/FONT]

is it returning more than just the field, if so what is the code for just returning the field not the entire record?

[FONT=Arial][FONT=Arial][SIZE=3]Dim Num As String     
Dim db As DAO.Database    
Dim rs As DAO.Recordset     
Dim strSQL As String         
              
Num = Me.Well_ID ' [COLOR=Magenta]Reading an String from a field in the form lets assum[/COLOR][FONT=Arial][COLOR=Magenta]e the word [B]is Book.  [U]Num now is set to Book[/U][/B][/COLOR]
[/FONT] MsgBox "Form WellID ----> " & Num ' [COLOR=Magenta]making sure I am reading the string This is C[/COLOR][FONT=Arial][COLOR=Magenta]onfirme[/COLOR]d [COLOR=Magenta]By this msg box

My assumption is that this sql string should return Book as it's result or not   if not it is null
if it returns it is BOOK
[/COLOR][/FONT] strSQL = "SELECT [Assets.Well_ID] " & "From Assets " & _      "WHERE [Assets.Well_ID] =" & Chr$(39) & Num & Chr$(39) 

Debug.Print strSQL                     
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL)  [COLOR=Magenta]I guess this is not OK based [FONT=Arial]on what you said in regards to record set[/FONT][/COLOR]......[B][COLOR=Red]

MsgBox rs[/COLOR][/B][/SIZE][/FONT] [/FONT]

 

[pbaldy]

Probably bedtime in Romania. You want:

MsgBox rs!FieldName

And unless your field name has a period in it, this should be

[Assets].[Well_ID]

or simply

Well_ID

the brackets are only required when there are spaces or symbols other than underscores.

 

[ardy]

Hello Paul,
Thanks for the reply. Wonderful it worked, had to change it a bit for some reason MsgBox rs![Assets].[Well_ID] and well_ID didn't work kept giving me Run-time error '3265' - item not found in this collection. So I tried MsgBox rs![Assets.Well_ID] and it worked. For some reason it had to be like what was in the strSQL string. if the same string that I picked up in the form exists in the table(Assets) the message box came back with the string. Great......

I have a question if the string doesn't exist or it is not the same string I get Run-Time error '3021' - No Current Record. I think this is it's way of saying it didn't find it....... How would I set this error to a more meaningful response in a dialog box.

Also can you point me to either a book or resource that explains a bit more abt DAO and recordsets, I need to read a bit more to be able to understand a bit more......

I really do appreciate it......

Ardy

 

[pbaldy]

I'm surprised that bracketing works at all, but in any case to avoid the error test the recordset for EOF first.

 

[Mihail]

Maybe On Error statement will solve the issue.

 

[pbaldy]

Maybe On Error statement will solve the issue.

It could; my preference is code to prevent easily anticipated errors like this.

 

[Mihail]

It could; my preference is code to prevent easily anticipated errors like this.

I used a Select Case statement under the errors handler.

ErrorHandler:
Select Case Err.Number
  Case .... expected error number
  .....
  Case Else
    MsgBox("Unexpected error in Form """ & FormName & " ProcedureName " & Err.Number & " - " & Err.Description
End Select

 Resume .... to label

End Sub/Function

I like to know your opinion about this approach.
Thank you !

 

[pbaldy]

In this instance I'd simply have:

If Not rs.EOF Then
  MsgBox...
End If

 

[ardy]

I have tried Paul's Suggestion

If Not rs.EOF Then
        MsgBox rs![Assets.Well_ID]
            Else
        MsgBox "No Record"
     End If

What do you guys think,,,,,,, it works......

 

[pbaldy]

That's how I would do it.

 

[ardy]

Thanks Paul for your help , I do Appreciate it.....

 

[pbaldy]

Happy to help!