[AccessD] Access 2007 - Type Mismatch

Hi Gustav and Andy,

Thank you very much for your suggestions.  I tried it with the single/double
quotes, but it's stubbornly insisting "type mismatch."  I changed the "dim"
statement, but am now getting the "method or member not found" error on the
line following it.  I don't have much experience with writing this kind of
code, so maybe I am missing something!  Here's what I've got now:

Dim db As Database
Set db = CurrentDb

Dim rst As DAO.Recordset
Set rst = OpenRecordset(strSQL) 'getting an error here

If Not (rst.EOF And rst.BOF) Then
    Cancel = True
    msgbox "This number has already been used.  Please enter another
number.", vbOKOnly, conAppTitle
    Response = acDataErrContinue
End If

On 6/22/07, Andy Lacey <andy at minstersystems.co.uk> wrote:

> Gale
> My guess is that it's that the recordset's not DAO. Try changing the Dim
> of
> the recordset to
>
> Dim rst as DAO.Recordset
>
> and see if it's that.
>
> -- Andy Lacey
> http://www.minstersystems.co.uk
>
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gale Perez
> > Sent: 21 June 2007 21:39
> > To: Access Developers discussion and problem solving
> > Subject: [AccessD] Access 2007 - Type Mismatch
> >
> >
> > Hi!
> >
> > After converting a 2003 db to 2007, I'm getting a "Type
> > Mismatch" error in a field's BeforeUpdate code (the code
> > checks to ensure the value hasn't already been used).  The
> > field is a text field, so the problem shouldn't be that its
> > value is in quotes.  Has anyone else experienced this or have
> > any ideas?  It worked fine before.
> >
> > Thank you very much (code below),
> > Gale
> >
> >
> > Dim strSQL As String
> > strSQL = "SELECT * FROM MyTable WHERE MyField = " & Chr$(34)
> > & Me!MyField &
> > Chr$(34)
> >
> > Dim db As Database
> > Set db = CurrentDb
> >
> > Dim rst As Recordset
> > Set rst = db.OpenRecordset(strSQL)
> >
> > If Not (rst.EOF And rst.BOF) Then
> >     Cancel = True
> >     msgbox "This number has already been used.", etc.
> > End If
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>