'type mismatch' and DAO Version Access 97 SR-1 1

[JDRoss]

I'm having trouble with 'type mismatch' and openrecordset with a select sql.

I see from the forums threads that this can often be caused by DAO version. I am using Access 97 SR-1 but I can't seem to see where I can see what version of DAO is being used?

I'd appreciate some help!

Regards

John

 

[KenReay]

Hi

Open any Module, or open the Debug Window

Choose Tools \ REferences, look down the list for one with DAO in it and with tick to denote it is selected, this will telly you version eg 3.5, 3.6 whatever.

But if you give example of your code, that may help someone help you
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[JDRoss]

Thanks Ken

I've found the refernce and I see that I have DAO 3.51 Object Library already ticked.

Do I also need to tick the MS DAO 2.5/3.51 Library ...?


I am trying to trap duplicates at run time on table Monthly Contribution. I need to check three fields on a form to do this. I am passing the values to variables.

    memonth = Me.MonthID
    meyear = Me.YearID
    meparishonersid = Me.ParishonersID

.
.
.

Set DB = CurrentDb()
  Set rs = DB.OpenRecordset("SELECT * FROM [Monthly Contribution]")
  If rs.RecordCount > 0 Then
        rs.FindFirst "Parishonersid = meparishonersid AND monthid = memonth AND yearid = meyear "
    'Record does not already exist

I usually get a type mismatch.

Regards

John

 

[KenReay]

Hi

No you only need reference to one DAO library

You do not say which version of Access you are using, if after A97, it may be that you have ADO also in references, to be sure you can qualify Database, Recordset etc with DAO.Database, DAO.Recordset

but I think your problem may be in your code:

Set DB = CurrentDb()
Set rs = DB.OpenRecordset("SELECT * FROM [Monthly Contribution]&quot
If rs.RecordCount > 0 Then
rs.FindFirst "Parishonersid = " & meparishonersid & " AND monthid = " & memonth & " AND yearid = " & meyear
'Record does not already exist

This is assuming your columns are numeric (i deduced this from the Id Suffix) if they are strings you will need to bound then in ' so

rs.FindFirst "Parishonersid = '" & meparishonersid & "' AND monthid = '" & memonth & "' AND yearid = '" & meyear & "'"
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[JDRoss]

Ken

If rs.RecordCount > 0 Then
        rs.FindFirst "Parishonersid = " & meparishonersid & " AND monthid = " & memonth & " AND yearid = " & meyear

This did the trick. The crucial bit here is that the end of the line does not need closing quotes, who would have thought? Does this appear in a FAQ somewhere, as I think it would be of use and save a fair few headaches.

Thanks once again for your expert help!