type mismatch error

  • Thread starter Thread starter beeyule
  • Start date Start date
B

beeyule

I'm getting a type mismatch with the following and it's
highlighting the Set rst section, anybody see what's
going on?

Private Sub Write_Credit_AfterUpdate()

Dim db As Database
Dim rst As Recordset
Dim strSQL As String


strSQL = " SELECT dbo_CREDITS_DETAIL.TTNUM,
dbo_CREDITS_DETAIL.Full_Item, dbo_CREDITS_DETAIL.Price,
dbo_CREDITS_DETAIL.QTY" & _
" FROM dbo_CREDITS_DETAIL" & _
" WHERE (dbo_CREDITS_DETAIL.TTNUM = " & Me!TTNUM
& ")"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) TELLING
ME PROBLEMS IS HERE.

Dim strHeader As String, strBody As String

strHeader = "01" & Me!CUST_NUMBER & "," & Me!CUST_NAME
strBody = "02" & "6" & "," & "1" & "," & Me!TTNUM & "," &
Me!TTCode

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("H:\Text_Files\Test.txt", True)
a.WriteLine (strHeader)
a.WriteLine (strBody)
Do Until rst.EOF
a.WriteLine (rst)
rst.MoveNext
Loop
a.Close

End Sub
 
Let me guess (since you don't say). You're using Access 2000 or 2002 and
you've set a reference to DAO, but didn't remove the reference to ADO, or
else you're using Access 2003, which by default has references set to both
ADO and DAO, but the ADO reference is higher in the sequence.

If this wasn't the case, you'd get a failure on the Dim db As Database
statement, as well as the Set db = CurrentDb statement.

When you have references to both ADO and DAO, you need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. Since you're trying to use DAO, you need to change your declaration
from

Dim rst As Recordset

to

Dim rst As DAO.Recordset

(while an unspecified Dim rst As Recordset will result in an ADO recordset,
since the reference is higher in the sequence, if you wanted to guarantee an
ADO recordset, you'd use Dim rst As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset
 
Back
Top