Using SQL Query to create a RecordSet 1

[Deb2004]

I have used the following code in other apps successfully. It is not working in the present app. I don't know why. Are there any suggestions?

Private Sub Form_Current()
  Dim db As Database 'declare a variable that points to the database
  Dim rs As DAO.Recordset 'declare a variable that will hold your recordset
  Dim sql As String 'declare a variable that will hold your sql string

  'set the db variable equal to the currentDB
   Set db = CurrentDb

  'Check to make sure PRClaimNo is not = 0 if it is then it means that
  'the person is trying to enter a new record that has no Client
     If (Me.PRClaimNo = 0) Then
     MsgBox "Please select a client"
  Else
    'set the sqlString to contain all of the other values you want on
    'your form.  In this case...first and last
     sql = "select PatientFName, PatientMName, PatientLName From tblInitialEval"
     sql = sql & " Where tblInitialEval.IEClaimNo = " & Me.PRClaimNo

     'set the rs to the sql query ...basically
     [COLOR=red]Set rs = db.OpenRecordset(sql, dbOpenSnapshot)[/color]

     'now you can set the values of the text boxes with their appropriate
     'values in the query

     Me.PatientName = rs.Fields("PatientFName")
    
    End If
End Sub

The debugger stops at the line:

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Help!

 

[HitechUser]

Deb2004,

"Looks" okay.

Is the table already opened? In debug what is the value of SQL. I believe there is a syntax problem.

Info from Microsoft --->>
Note If source refers to an SQL statement composed of a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to open the Recordset. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and SQL only accepts U.S. decimal characters. <<-- End of MS


Let me know.



Good Luck...

 

[Deb2004]

When I checked the variable values in debug mode, the sql value is correct, but there is no value for db - nothing (nada) pops up. Consequently, the value for rs is "Nothing". Do you know why?

BTW, thank you for your quick response.

 

[Zion7]

Not sure, but I usually use...

Set db = CurrentDb()

Hope this helps, good luck either way!

 

[Zion7]

...apparently not. ( just tried it).

Actually, it may be ...

Dim db As DAO.Database

Are your references checked, DAO library?

 

[Deb2004]

Hi Zion7,

Thank you for responding. I tried adding the (), then changed the Dim statement to DAO.Database. The value of db is still nothing. I have added Microsoft DAO Object 3.6 Library to the references. I don't know what else to do...

I appreciate any and all suggestions!

 

[Zion7]

Could it be the type of recordset you're opening?
I know dynasets allow SQL statements, I think tabletype don't, not sure about snapshot?

Maybe worth a try?

Good luck either way!

 

[RoyVidar]

I can't seem to find what errormessage you're getting.

Two things:
1 - you don't close nor release the recordset object variable
2 - you don't release the database object variable

One is often encouraged to do so at the end of the sub.

[tt]rs.close
set rs=nothing
set db=nothing[/tt]

But do try also:
[tt]Set rs = currentdb.OpenRecordset(sql, dbOpenSnapshot)[/tt]

and/or try without the dbOpenSnapshot arguement.

What datatype is the IEClaimNo field? If it is text, you'll need to enclose it in single quotes:

[tt]...Where tblInitialEval.IEClaimNo = '" & Me.PRClaimNo & "'"[/tt]

Roy-Vidar

 

[Deb2004]

Thank you Thank you Thank you!

I appreciate all suggestions. It was a data type mismatch.

Debra