[AccessD] Access 2007 - Type Mismatch

I believe you need to change the statement to
 Set rst = db.OpenRecordset(strSQL)
HTH
Jim Hale

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gale Perez
Sent: Friday, June 22, 2007 3:49 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.