[RESOLVED] need help with my query function

It's been awhile since I did any vb programing and I am having some trouble getting my function to work correctly. I need to select from my Centers table a field called "nositeplan" the value is "no". If the field has a "no" in it I want to hide my command button. But I get a error"variable not defined"

I am sure I have forgotten something very simple but I don't remember what it is. This is probably and easy one.:rolleyes:

Code:

---

'added no site plan rule
Public Sub nositeplan()

  On Error GoTo Err_handler

  Dim sSQL As String

  Screen.MousePointer = vbHourglass
 
  Call CreateConnection
 
  Set objRs = New ADODB.Recordset
      objRs.ActiveConnection = objConn
      objRs.CursorLocation = adUseClient
      objRs.CursorType = adOpenForwardOnly
      objRs.LockType = adLockReadOnly
      objRs.Source = "select * from centers where nositeplan = 'no'"
      'MsgBox objRs.Source
      objRs.Open
     
 'CHECK FOR nositeplan and set command button visible to false
    If no > 1 Then
      HoverCommand12.Visible = False
    Else
        HoverCommand12.Visible = True
    End If
   

      objRs.Close
      Set objRs = Nothing
      Call CloseConnection
     
     
  End If
 
  Screen.MousePointer = vbDefault

End Sub

---

First of all, do objRs.Open not objRs.Source

Second, if all you want is one field, why are you doing a SELECT *?

Third, the way your query is written you will probably have more than just one record. You will have ALL records in the entire table where nositeplan = 'No' - is that what you want?

Thanks for getting back to me so soon. Well let me explain what I am trying to do in a nutshell. First I have this map of the US, if I click on a state, it pulls up all the centers (ie, store front properties) for that state. If I click on one of the centers, it pulls up specific info on that property. On this searchResults form is a command button that launches a siteplan pdf. If the property has a site plan I want the button to be visible, if not then hide the button.

So now that I think about it, maybe I need to go back to the point where I click on the property list and before displaying the property info check for the nositeplan.

Let me look at that and see what I can come up with. The code I have won't work, I see that now.:)

Yeah, it would seem to me that you would want to be pulling back one record at a time and checking the nositeplan field. If the field = 'No' for the one, specific, record, then you could take whatever actions are appropriate.

So, you need to construct a suitable WHERE clause in order to drill down to the specific record that you need.

Thanks again. I think I found were the programmer loaded the property information or details it was in the module. I added my code to check for the nositeplan field and I think everything is working now.:)

I think somewhere I can mark this thread resolved can't I?