Code Help: Checking to see if value exists in current database

[thr33xx]

I am attempting to create a text field after update validation to check to see if a study id already exists. If it does, a warning message will appear informing the user that such a study already exist. Below is the code I have, however, upon execution, I receive the error,

Run-time error '3061':
Too few parameters. Expected 2.

Private Sub Add_Study_ID_AfterUpdate()
Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

       'Check that all the relevent controls meet the criteria
       If Nz(Me.Add_Study_ID, 0) > 0 And Len(Nz(Me.Add_Study_ID, "")) > 0 Then
           sSQL = "SELECT Study_ID FROM dbo_Setup WHERE ScheduleNum = Me.Add_Study_ID "

           'Check the database
           Set db = CurrentDb
           [COLOR="red"]Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)[/COLOR]
           If rs.AbsolutePosition > 0 Then
               MsgBox "The Study Short Title enterted already exists."
           End If
       End If

       rs.Close
       Set rs = Nothing
       Set db = Nothing

 

[pbaldy]

Try this, presuming a numeric data type:

sSQL = "SELECT Study_ID FROM dbo_Setup WHERE ScheduleNum = " & Me.Add_Study_ID

If that only clears one of them, check the spellings on everything.

 

[thr33xx]

The text field being checked is actually alphanumeric. I tried the code anyway, and the result was the same error. =(

 

[pbaldy]

For an alphanumeric SchedNumber:

sSQL = "SELECT Study_ID FROM dbo_Setup WHERE ScheduleNum = '" & Me.Add_Study_ID & "'"

If you still get the error, something is likely spelled wrong or something. It could also be that the underscores are actually spaces or something like that.

 

[thr33xx]

Hmm... would working in Access 2010 change any of the following,

Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String

????

I am using Access 2010 connected to a SQL 2005 server... so would it be?

Dim cn As ADODB.Connection
Set cn = CurrentProject.AccessConnection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

 

[pbaldy]

Assuming the table is linked, DAO should work fine. If you want to switch, that's fine too, but I don't think DAO is the problem here.

 

[thr33xx]

Alright, thanks. I'll keep at it!

 

[pbaldy]

I should have clarified, the error is telling you that DAO is trying to work, but Access isn't understanding something in the SQL string. The most common error is not concatenating the form reference in, but it can also be a misspelled table/field name or something like that.

 

[thr33xx]

Hah!

You were right, forgot to include "Study_ID" after the WHERE condition.

sSQL = "SELECT Study_ID FROM dbo_Setup WHERE Study_ID = '" & Me.Add_Study_ID & "'"

Well that was silly.

Thanks!

 

[pbaldy]

It's always the little things that trip us up. Glad you got it sorted out.