Access 2007 Runtime Error

I'm not sure if this is the right site to post this question, but here it goes...

In Access 2007 I get the error "Runtime Error '3061': Too few parameters. Expected 1" on this piece of VBA code:

Private Sub btnCheck_Click()

    Dim rs As Recordset
    Dim db As Database
    Dim id As String
    Dim query As String
    MsgBox ("one")
    Set db = CurrentDb()
    id = Me.UniqueID.Value
    query = "SELECT [Unique_ID] from tblPatients WHERE [Unique_ID] =" & id
    MsgBox (id)
    Set rs = db.OpenRec开发者_如何转开发ordset(query) <<<<<HIGHLIGHTED LINE

    If IsNull(rs) Then
        Me.lblCheck.Caption = "NEW"
    Else
        Me.lblCheck.Caption = "EXISTS"
    End If

End Sub

The data source is a table, not a query. Any help would be much appreciated!

---

There is no field named Unique_ID in your table tblPatients. If you posted all of your code then that is the only possible explanation.

EDIT: Your comment confirmed my suspicions:

I just triple checked :P Table name: tblPatients Column name: Unique ID

You added an underscore in your code that did not exist in your field name. You are correct in using square brackets, but the correct code should be:

query = "SELECT [Unique ID] from tblPatients WHERE [Unique ID] =" & id

Please note the removed underscores. Alternatively (and I'd say preferably if you are in the early stages of design), you can rename the field in the table to either Unique_ID or UniqueID and save yourself a good deal of hassle.

---

A Few things can cause this error. A common error is misspelled table names and field names. I would check tblPatients is spelled correctly or that there is no prior suffix like dbo.tblPatients required if the table is linked to a Server Connection. As well we are assuming the id is a number and isn't a text field which would cause an error if you do not have the correct quotes. ie. it would instead read query = "SELECT [Unique_ID] from tblPatients WHERE [Unique_ID] = '" & id & "';"

Lastly, try to place ";" like I did in the line above.

---

I suggest you add a Debug.Print statement to your code like this:

query = "SELECT [Unique_ID] from tblPatients WHERE [Unique_ID] =" & id
Debug.Print "query: " & query

The reason for that suggestion is Debug.Print will print your SQL statement to the Immediate Window. (You can use the Ctrl+g keyboard shortcut to get to the Immediate Window.) Then you can view the completed string you're asking OpenRecordset to use. Often just seeing that string (rather than trying to imagine what it should look like) will let you spot the problem. If not, you can copy the string from the Immediate Window and paste it into SQL View of a new query ... the query designer can help you pinpoint syntax errors ... or in this case, I think it may alert you to which item in your query the database engine doesn't recognize and suspects must therefore be a parameter. And if that step still doesn't resolve the problem, you can paste the string into your question on Stack Overflow.

Finally, I think you may have a logic error with IsNull(rs) ... because rs has been declared a recordset, it will never be Null. In the following example, the SELECT statement returns no records. And the Debug.Print statement says IsNull(rs): False both before and after OpenRecordset.

Public Sub RecordsetIsNeverNull()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String

    strSql = "SELECT * FROM tblFoo WHERE 1 = 2;"
    Set db = CurrentDb

    Debug.Print "IsNull(rs): " & IsNull(rs)
    Set rs = db.OpenRecordset(strSql)
    Debug.Print "IsNull(rs): " & IsNull(rs)

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Edit: According to Problem names and reserved words in Access, query is an Access reserved word. I don't actually think that is the cause of your problem, but suggest you change it anyway ... perhaps strQuery.