SQL comes up Empty

[wtrimble]

Hey guys,

I have a form that searches for Power plants found in a table. I have a textbox where the user can enter in part of the power plant and a command button that uses an SQL to list the possible records onto a listbox. However if the SQL doesn't come up with any records than I need "No Record Found" listed in the Listbox. I have it set up as:

Dim srchplant As String
srchplant = Me.Text18
Me.List20.RowSourceType = "Table/Query"
Me.List20.RowSource = "SELECT tblgdmreport.Facility_name, tblgdmreport.unitid_epa FROM tblgdmreport WHERE Facility_name like  '*" & srchplant & "*' ORDER BY tblgdmreport.Facility_name, tblgdmreport.unitid_epa"

And then after that I have code to see if anything is present in the listbox:

If IsNull(Me.List20.Column(0)) Then
    Me.List20.RowSourceType = "Value List"
    Me.List20.RowSource = "- No Record Found -"
End If

But for some reason I believe it is looking at the "if" statement first and sees that the listbox is empty because now when I click the search button "No Record Found" appears when I know records should appear

Is there a way to say " If isnull( Select * ...From ....)" or " If Select * from .... = 0" to see if anything will appear first before it is entered into my listbox??

Thanks for any help

 

[c_smithwick]

Try this:
Dim srchplant As String
dim rst as DAO.Recordset

srchplant = Me.Text18
Set rst = CurrentDb.OpenRecordset("SELECT tblgdmreport.Facility_name, tblgdmreport.unitid_epa FROM tblgdmreport WHERE Facility_name like '*" & srchplant & "*' ORDER BY tblgdmreport.Facility_name, tblgdmreport.unitid_epa")
if rst.EOF then
Me.List20.RowSourceType = "Value List"
Me.List20.RowSource = "- No Record Found -"
Else
Me.List20.RowSourceType = "Table/Query"
Me.List20.RowSource = "SELECT tblgdmreport.Facility_name, tblgdmreport.unitid_epa FROM tblgdmreport WHERE Facility_name like '*" & srchplant & "*' ORDER BY tblgdmreport.Facility_name, tblgdmreport.unitid_epa"
End If
rst.close
Set rst = Nothing

 

[wazz]

you might also try:
Dim srchplant As String
srchplant = Me.Text18.Text
if this textbox is unbound.

 

[wtrimble]

Will try, thanks C smith

 

[wtrimble]

Works great! Thanks alot C_Smith

 

[wtrimble]

Wick .

 

[LPurvis]

Just to mention that you can save some processing and database work by assigning the recordset once you have it open.
So the code you were given can be slightly altered:

...
Else 
    Me.List20.RowSourceType = "Table/Query"
    Set Me.List20.Recordset = rst
End If
...

(Assuming you're using Access 2002 or newer).

Cheers.