Ready To Pick UP my Computer and throw it out the WINDOW!!!! 1

[Cleis]

I've reached my wits end with this!! I'm trying VERY HARD to see if a record exists! With the query below, I want to know if the vendor First exists in a table called tblActiveVendor AND has a Contract for a given Year. The query is to be filtered by a Form called frmExtAgrmntSetUp using two fields called txtVendor which is a text field and txtYear which is a LongInteger. I then set this query as the recordsouce to a list box called lstLookUP. When I select a record that I know exists, why does this code say theat the list box is null when I can see the record?!?!?!?!

PLEASE HELP! I've wasted WAY TOO MUCH TIME on this!!

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

If IsNull(lstLookUp) Then

MsgBox "help!!"

End If

 

[PaulBricker]

You are getting the null returned because what you are actually saying (testing) with the Null() function is
If Nothing is Selected in my Listbox Then
Msgbox
Else
End If

What you actually want to say is

If there are no records in my listbox Then
Msgbox
Else
End If

To do that you need to test for .EOF or .BOF with your string like this

Me.lstLookUp.RowSource = strSQL
Dim rst = DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
If rst.EOF Then
MsgBox "help!!"
Else
End If

Set rst = Nothing

Paul

 

[Cleis]

Great Paul but how about with ADO?

 

[PaulBricker]

Add this into your code and it should do what you want.

Paul


Dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn
If rst.EOF Then
MsgBox "Help"
End If

rst.Close
cnn.Close

 

[Cleis]

Okay Paul why am I gettin another error. I've seen this a couple of times."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

Thanks for your time! I Appriciate it!!


Rich

 

[PaulBricker]

Rich, unfortunately all I can tell you is it's related to the Where clause in your strSQL statement. I don't work with ADO very often and am not that familiar with debugging it. Is there some reason you can't use DAO. The library is available to use, and it will work in DAO as I described earlier. I'll look at it some more, but unless someone can pipe in with the right ADO code, it may be a while before I get it.

Paul

 

[Cleis]

Paul: Thank you SO MUCH for your help to this point. I'm going to re-post and see if an ADO Expert can help me.


Again thanks for your time and effort, it's been very much appriciated!!



Have a nice holiday!!

Rich

 

[PaulBricker]

I saw the other post. Hopefully someone will have an answer. I'll be interested myself.
Paul

 

[PaulBricker]

Rich, I went back and looked this over some more. The only change I made was to the Dim cnn line. I made it a New ADODB.Connection. Also, when I first ran this looking at two textboxes on form [Work Order] I got the parameters error you did. When I went back, I found a small typo. When I corrected that, it ran smooth as silk. Take a look at your strSQL for any typo's and change the declaration for cnn.

Paul

Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
strSQL = &quot;Select * From [WorkOrders] Where [WorkOrders].[PersonRequesting] = '&quot; & Forms![WorkOrder]![PersonRequesting] & &quot;' And [WorkOrders].[DateIssued] = #&quot; & Forms![WorkOrder]![DateIssued] & &quot;#&quot;

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn

rst.Close
cnn.Close

 

[Cleis]

Hey Paul You and FancyPrairie where right!!

Same post I left with F.P:

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 . . . .

[red]Again Paul, thanks for sticking with me on this, I'll pass the favor on too!![red]

Happy Holidays and THANKS AGAIN!!!!


Rich