Help with ADODB Recordsets

[Nick34]

I am trying to create a search on a recordset that is based on a SQL string. If the search comes back with nothing, then I want to exit the sub routine. What am I doing wrong???

strSQL = "SELECT NGSCommunityCommitmentTons.ID, NGSCommunityCommitmentTons.Unit, NGSCommunityCommitmentTons.Month_Year, NGSCommunityCommitmentTons.SO2Tons, NGSCommunityCommitmentTons.NOxTons, NGSCommunityCommitmentTons.PMTons, NGSCommunityCommitmentTons.COTons, NGSCommunityCommitmentTons.VOCTons " & _
"FROM NGSCommunityCommitmentTons " & _
"WHERE (((NGSCommunityCommitmentTons.Unit)= " & sUnit & &quot AND ((NGSCommunityCommitmentTons.Month_Year)= 10/1/2003 ));"

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Source = strSQL
rst.Open Options:=adCmdText


If rst.EOF Then
Exit Sub
Else: msgbox "There is already data entered for this month. Use the " & _
"NGS Community Commitment Update Form.", vbOKOnly
End If

 

[cmmrfrds]

Dates is Access need to be surrounded by literal identifier the #.

"((NGSCommunityCommitmentTons.Month_Year)= #" & 10/1/2003 & "#));"

rst.Open strSQL

 

[Nick34]

I'm still getting the error message "No value given for one or more required parameters."

Any ideas?

 

[cmmrfrds]

After this.
strSQL = "SELECT NGSCommunityCommitmentTons.ID, NGSCommunityCommitmentTons.Unit, NGSCommunityCommitmentTons.Month_Year, NGSCommunityCommitmentTons.SO2Tons, NGSCommunityCommitmentTons.NOxTons, NGSCommunityCommitmentTons.PMTons, NGSCommunityCommitmentTons.COTons, NGSCommunityCommitmentTons.VOCTons " & _
"FROM NGSCommunityCommitmentTons " & _
"WHERE (((NGSCommunityCommitmentTons.Unit)= " & sUnit & &quot AND ((NGSCommunityCommitmentTons.Month_Year)= 10/1/2003 ));"

DO This.
Debug.Print strSQL
and paste in your resulting SQL Statement.

 

[Nick34]

SELECT NGSCommunityCommitmentTons.ID, NGSCommunityCommitmentTons.Unit, NGSCommunityCommitmentTons.Month_Year, NGSCommunityCommitmentTons.SO2Tons, NGSCommunityCommitmentTons.NOxTons, NGSCommunityCommitmentTons.PMTons, NGSCommunityCommitmentTons.COTons, NGSCommunityCommitmentTons.VOCTons FROM NGSCommunityCommitmentTons WHERE (((NGSCommunityCommitmentTons.Unit)= NS1) AND ((NGSCommunityCommitmentTons.Month_Year)= #10/1/2003# ));

This is what I got after I debug.print the SQL statement.

I'm still getting the same error as before.

 

[CajunCenturion]

I think your problem lies here

NGSCommunityCommitmentTons.Unit)= NS1

as NS1 is a string and as such, should be surrouded by single quotes. You might try the following change to the SQL statement

WHERE (((NGSCommunityCommitmentTons.Unit)=

'

" & sUnit & "

'

) AND

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[cmmrfrds]

I thought tons was numeric, but I can see it is not. You will need to work it out with quotes.

WHERE (((NGSCommunityCommitmentTons.Unit)= "NS1&quot AND