[AccessD] Jet version sql error Try 2

Hi Doug,
To me this code does not make sense to me, but I understand that is is not
the complete code, so my comments below may be meaningless.

1. Presumably GetUserLocaleInfo is a function that returns a string (and not
a null)
2. You haven't tested to see if if sCountrySettings has a value before using
it.
3. What does tFM.sCountry mean.  If it is a field in a table then it really
should use the pling (!).  If it is a property setting in a Class then where
was the class defined?  The "." is really for a property. (I do understand
that some people use them for both, but the pling makes it clear what the
interpretation is to be.
4. You have told the recordset to move to the first record, but you haven't
tested to see if the recordset has any content.  If it is empty, then that
would generate an error.  Ie, use "If not rs.eof then...."
5. Are you sure that the returned value is actually what you type in. If
there is an embedded single quote in the returned value then  you can get
this error.
6. Are your Reference correctly installed and/or have you recently installed
an update on your system.  Does the function work for other values other
than the one you quoted?  You could try removing references and then putting
them back in again - do any show as "missing".

Hope some of this may help.
Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Tuesday, September 11, 2007 5:13 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Jet version sql error Try 2

 
OK I'll try again with a simpler (?) question.

Any one see a reason why the following sql string generates an error when
used to open a recordset?

"Select * From tblUserFormat Where fldCountry = 'United States';"

Generates an error 3075 when run in the following line:

"Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)"

The error message that gives the error number 3075 indicates that there is
some problem with fldCountry = 'United States'

I have modified the code slightly from my first post to use a dynaset type
recordset and added the semicolon at the end of the query string.  Same
error is generated with the updated code.  The complete procedure up to this
point is as follows.

Public Sub GetCountrySettings()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL As String
Dim sCountrySettings As String
Dim fld As Field
Dim LCID As Long

  LCID = 0  'GetSystemDefaultLCID()

   On Error GoTo GetCountrySettings_Error

      sCountrySettings = GetUserLocaleInfo(LCID, LOCALE_SCOUNTRY)
      tFM.sCountry = sCountrySettings
      
      If Not IsNull(sSQL) Then
            sSQL = "Select * From tblUserFormat Where fldCountry = '" &
sCountrySettings & "';"
            Set db = CurrentDb
            Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
            rs.MoveFirst

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com