13 Type Mismatch error

[Maggie May]

I have the following code and I keep getting a 13 Type
Mismatch error when I get to the line "For Each fld In
rec.Fields" The "M" is a parameter which is defined in
qryXLRatesbyShipline_Crosstab (crosstab query). The
strShip is a string and field that is compared to the "M"
parameter is text. This is code from a different
programmer that I have inherited. It worked before and I
have been adding a form (completely unrelated to this)
and now cannot get it to work. I copied over all of the
queries and code involved with this, but it still does
not work. Please help!! Maggie



Dim db As Database
Dim rec As DAO.Recordset
Dim recf As DAO.Recordset
Dim qd As QueryDef
Dim fld As Field
Dim intcolumn As Integer
Dim intRow As Integer
Dim intTotalcolumn As Integer
Dim i As Integer


Set db = CurrentDb()
Set qd = db.QueryDefs("qryXLRatesbyShipline_Crosstab")
qd.Parameters("M") = strShip
Set rec = qd.OpenRecordset()
Set objWS = MyXL.Application.ActiveSheet
MyXL.Application.Worksheets(strSheet).Cells(1,
3).Value = UCase(rec.Fields(0))
intcolumn = 1
intRow = 2
For Each fld In rec.Fields

 

[Dan Artuso]

Hi,
What happens if you declare fld as a variant?

 

[Marshall Barton]

Are you mixing DAO and ADO stuff in this mdb? If so, then
you should use:

Dim fld As DAO.Field

 

[Douglas J. Steele]

You don't mention what version of Access you're using, but I'm guessing it's
Access 2000 or newer, and that you didn't removed the reference to ADO when
you added the reference to DAO.

Field is an object in both the ADO and DAO models. When you have references
to both models, the higher one in the list will get chosen when you don't
disambiguate. Since ADO is higher in the list, you're getting an ADO field.

Try:

Dim fld As DAO.Field

For those times when you want to guarantee you're getting an ADO object,
you'd use something like

Dim fld As ADODB.Field

 

[Maggie May]

There is no choice for a parameter to be assigned a
variant. I tried value and that did not work. Same
error.. any other ideas?

Maggie

-----Original Message-----
Hi,
What happens if you declare fld as a variant?

--
HTH
Dan Artuso, Access MVP


"Maggie May" <[email protected]> wrote

in message news:[email protected]...

 

[Maggie May]

I changed it to have the following:

Dim fld As dao.Field
Dim intRow As Integer
Dim intcolumn As Integer
Dim db As Database
Dim rec As dao.Recordset
Dim recf As dao.Recordset
Dim qd As dao.QueryDef

First I changed just the field to dao.field and that did
not work. Then I did the querydef as dao.querydef. still
no luck. I am on Access 2003. Any other ideas?

Maggie

-----Original Message-----
Are you mixing DAO and ADO stuff in this mdb? If so, then
you should use:

Dim fld As DAO.Field
--
Marsh
MVP [MS Access]

I have the following code and I keep getting a 13 Type
Mismatch error when I get to the line "For Each fld In
rec.Fields" The "M" is a parameter which is defined in
qryXLRatesbyShipline_Crosstab (crosstab query). The
strShip is a string and field that is compared to the "M"
parameter is text. This is code from a different
programmer that I have inherited. It worked before and I
have been adding a form (completely unrelated to this)
and now cannot get it to work. I copied over all of the
queries and code involved with this, but it still does
not work. Please help!! Maggie



Dim db As Database
Dim rec As DAO.Recordset
Dim recf As DAO.Recordset
Dim qd As QueryDef
Dim fld As Field
Dim intcolumn As Integer
Dim intRow As Integer
Dim intTotalcolumn As Integer
Dim i As Integer


Set db = CurrentDb()
Set qd = db.QueryDefs ("qryXLRatesbyShipline_Crosstab")
qd.Parameters("M") = strShip
Set rec = qd.OpenRecordset()
Set objWS = MyXL.Application.ActiveSheet
MyXL.Application.Worksheets(strSheet).Cells(1,
3).Value = UCase(rec.Fields(0))
intcolumn = 1
intRow = 2
For Each fld In rec.Fields
.
.
<<<the rest of the code>>>

.

 

[Maggie May]

I am on Access 2003. I changed it to have the following:

Dim fld As dao.Field
Dim qd As dao.QueryDef

First I changed just the field to dao.field and that did
not work. Then I did the querydef as dao.querydef. still
did not work. Any other ideas?

Thanks,
Maggie

Maggie

 

[Maggie May]

I was mistaken - the field was declared at two different
places. It worked the first place and not the other.

Thanks for the help!
Maggie

 

[Maggie May]

I was mistaken - the field was declared at two different
places. It worked the first place and not the other.

Thanks for the help!
Maggie

 

[Chris Nebinger]

The first thing that came to mind is that both ADO and DAO
are selected, and Dim fld As Field is declaring it as and
ADO Field.

Try:

Dim fld As DAO.Field


Chris Nebinger

-----Original Message-----
Hi,
What happens if you declare fld as a variant?

--
HTH
Dan Artuso, Access MVP


"Maggie May" <[email protected]> wrote

in message news:[email protected]...

 

[Dan Artuso]

Maggie,
I meant:
Dim fld As Variant