Hi All: CAn anyboy help me with th 1

[Cleis]

Hi All: CAn anyboy help me with this. I'm gettinga an error and have no idea where to go with this."NO value give for one or more parms"

Dim strSQL As String

strSQL = "SELECT tblActiveVendors.[Vendor#]" & vbCrLf
strSQL = strSQL & " , tblAgreementHed.Year" & vbCrLf
strSQL = strSQL & " FROM tblActiveVendors " & vbCrLf
strSQL = strSQL & " INNER JOIN tblAgreementHed " & vbCrLf
strSQL = strSQL & " ON (tblActiveVendors.[Vendor#] = tblAgreementHed.[Vendor#]) " & vbCrLf
strSQL = strSQL & " AND (tblActiveVendors.Assignee = tblAgreementHed.Assignee)" & vbCrLf
strSQL = strSQL & " WHERE (((tblActiveVendors.[Vendor#])=[Forms]![frmExtAgrmntSetUp]![txtVendor]) " & vbCrLf
strSQL = strSQL & " AND ((tblAgreementHed.Year)=[Forms]![frmExtAgrmntSetUp]![txtYear]));"

Me.lstLookUp.RowSource = strSQL

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn <<<Error occurs here
If rst.EOF Then
MsgBox &quot;Help&quot;
End If

rst.Close
cnn.Close

I've gone though rst.open strSQL and added , adOpenStatic, adLockOptimistic but I still get the dame Damn error. What the heck am I missing!!!!! Going flippen nuts here!! I'm not doing BRaIn SUrgErY

 

[FancyPrairie]

I belive your Where clause is the problem. It should look something like this:

strSQL = strSQL & &quot; WHERE (tblActiveVendors.[Vendor#] = &quot; & [Forms]![frmExtAgrmntSetUp]![txtVendor] & &quot&quot; & vbCrLf
strSQL = strSQL & &quot; AND (tblAgreementHed.Year = &quot; & [Forms]![frmExtAgrmntSetUp]![txtYear] & &quot;&quot;

There still may be problems with it. If tblAgreementHed.Year represents a date then you need the # sign around [Forms]![frmExtAgrmntSetUp]![txtYear]. Like this:

strSQL = strSQL & &quot; AND (tblAgreementHed.Year = #&quot; & [Forms]![frmExtAgrmntSetUp]![txtYear] & &quot;#);&quot;

If tblActiveVendors.[Vendor#] represents a string, then it should look like this:

strSQL = strSQL & &quot; WHERE (tblActiveVendors.[Vendor#] = '&quot; & [Forms]![frmExtAgrmntSetUp]![txtVendor] & &quot;')&quot; & vbCrLf

 

[cmmrfrds]

Check out your sql string. Why are you putting vbCrLf in the string?

Set rst = New ADODB.Recordset
msgbox strSQL
rst.Open strSQL, cnn <<<Error occurs here

 

[Cleis]

Thanks FancyPrairie!!!

This problem had me wound up tighter than a than you can imagine. You were right in that my where clause was all #$%@ up! I must admit, I wish that I was stronger in SQL than I am. You know, they don't give kids calculators for the same reason. . .Learn Math!! Well, I've relied for so long on the query by design grid in Access, I've never given much effort writing SQL out the long way. Unfortunately, in order to learn ADO and use it effectively ya' gotta know SQL.

Guess you know what I'm gonna be doing . . . .

Happy Holidays and THANKS AGAIN!!!!


Rich

 

[FancyPrairie]

Cleis, here's a hint concerning building SQL statements via code. I usually first build my SQL statement using the Query Builder. Once I have it working, I switch to SQL view, highlight the SQL statement, copy it (CTRL+C) and then paste it in my code.

If, after pasting it in my code and formatting it correctly it no longer works, I place a STOP statement after the SQL statement in my code. Then I run my program. When it encounters the STOP statement, I examine the SQL statement via debug's immediate window, highlight the SQL statement, go back to the Query Builder, switch to SQL view and paste the code and test it again. Saves a lot of time and headaches.