rs select field

[Martin]

Hello,

I have a drop down list that when clicked on I want to be able to see if the
user has asked for a pop up form to be shown again or not. the code is as
follows:

Private Sub LongDescription_Click()
Dim SQL As String, dbs As Database, rs As Recordset
Set dbs = CurrentDb

SQL = "SELECT [Form Name], ShowAgain.Status FROM ShowAgain WHERE
(((ShowAgain.[Form Name])='MessageTransactions'))"

Set rs = dbs.OpenRecordset(SQL, dbOpenDynaset)

Select Case rs.[Status]

Case Is = No

Case Is = Yes
If [Form_Reports Menu].LongDescription <> "(All)" Then
DoCmd.OpenForm "MessageTransactions", acNormal
End If

End Select

End Sub

The problem is that in the select case statement I can't seem to refer to
the field "Status" in the SQL. However when I copy the SQL to a query the
SQL works fine and the field "Status" is there.

Can anyone help with making this work properly?

Thank you in advance.

Martin

 

[Paolo]

Hi Martin,
if status is a Yes/No field you can do in this way

Select Case rs.[Status]
Case Is = 0
do something
Case Is = -1
do something else

or also in this way

Select Case rs.[Status]
Case Is = "No"
do something
Case Is = "Yes"
do something else

HTH Paolo

 

[Klatuu]

One problem may be that sQL is an Access reserved word. Using reserved words
as names can cause strange problems. Try SQL to something like strSQL.

Also, with only two options, I would not use a Select Case. A simple If
Then Else would be preferrable. This has nothing to do with the problem, but
is just good parctice.