How to detect when query returns no values?

[dbmanalser84]

I have a combo box that is fueled by a query, and I want to detect when that query (that is a Row Source for the Combo box) returns no values (the Combo box that is fueled by that query is empty)?

I tried using the following codes:

If cmbField1.Value = "" Or cmbField1.Value = Null Then
        cmbField1.Value = "Empty"
End If

If cmbField1.ItemData(0) = "" Or cmbField1.ItemData(0)= Null Then
        cmbField1.Value = "Empty"
End If

and I didn't get the wanted results. Can somebody tell my how to solve my problem?
Thnx.

 

[boblarson]

Use a DCount in the form's On Current event to check before doing anything and enable or disable the combo if there are or aren't records.

cmbField1.Enabled = (DCount("*", "YourQueryName")>0)

 

[dbmanalser84]

Use a DCount in the form's On Current event to check before doing anything and enable or disable the combo if there are or aren't records.

cmbField1.Enabled = (DCount("[YourFieldNameInQuery]", "YourQueryName")>0)

But my query is not saved as a file in database, instead it is generated on the fly with VBA on each After_Update event when another Combobox is selected.
Here's the piece of code that generates the query, and fills the combo box with it:

Private Sub intPrevoznikID_AfterUpdate() // intPrevoznikID Combo box
    Dim prevoznik As Integer // Variable necessary for the SQL syntax
    Dim relacija As Integer // Variable necessary for the SQL syntax
    Dim VremePolaska As ComboBox // Actual combo box that query fills 
    
                        
    prevoznik = Forms![frmUnosKarte]![intPrevoznikID].Value // The value comes from another Combo box on form and it's Integer
    relacija = Forms![frmUnosKarte]![intRelacijaID].Value // The value comes from another Combo box on form and it's Integer
    Set VremePolaska = Forms![frmUnosKarte]![txtVremePolaska] // The Combobox that recieves the query results
    
//The SQL syntax that is row source for Combo box
    VremePolaska.RowSource = "SELECT DISTINCT tblKarta.txtVremePolaska FROM tblRelacija INNER JOIN (tblPrevoznik INNER JOIN tblKarta ON tblPrevoznik.intPrevoznikID = tblKarta.intPrevoznikID) ON tblRelacija.intRelacijaID = tblKarta.intRelacijaID WHERE (((tblPrevoznik.intPrevoznikID)=" & prevoznik & " AND tblRelacija.intRelacijaID = " & relacija & "));"

// I left out the If part from this code

End Sub

Can I apply the your advice here in my code and how?

 

[boblarson]

Here you go, you can do this:

    Dim prevoznik As Integer 
    Dim relacija As Integer 
    Dim VremePolaska As ComboBox 
    Dim strSQL As String
    Dim qryDef As QueryDef
                        
    prevoznik = Forms![frmUnosKarte]![intPrevoznikID].Value 
    relacija = Forms![frmUnosKarte]![intRelacijaID].Value 
    Set VremePolaska = Forms![frmUnosKarte]![txtVremePolaska] 

    strSQL = "SELECT DISTINCT tblKarta.txtVremePolaska FROM tblRelacija INNER JOIN (tblPrevoznik INNER JOIN tblKarta ON tblPrevoznik.intPrevoznikID = tblKarta.intPrevoznikID) ON tblRelacija.intRelacijaID = tblKarta.intRelacijaID WHERE (((tblPrevoznik.intPrevoznikID)=" & prevoznik & " AND tblRelacija.intRelacijaID = " & relacija & "));"

    Set qryDef = CurrentDb.CreateQueryDef("CheckCount", strSQL)
    If DCount("*", qryDef.Name)> 0 Then
        VremePolaska.RowSource = strSQL
    Else
        VremePolaska.Enabled = False
    End If  
 
    CurrentDb.QueryDefs.Delete (qryDef.Name)

 

[dbmanalser84]

Thanx for the code Bob. It works but not 100% as I wanted, so in order to be able to understand it better can You please explain to me (in simple English - translate) the following lines of code:

[B]Dim qryDef As queryDef[/B] // I tried searching help for this Variable type but with no luck, can You explain what is queryDef?
.
.
[B]Set qryDef = CurrentDb.CreateQueryDef("CheckCount",strSQL)[/B] / explain CreateQueryDef method
[B]If DCount("*",qryDef.Name)>0 Then[/B] // Can you explain why the Name property of qryDef and what are the possible values it coukd return in the If statement?
.
[B]VremePolaska.Enabled = True[/B] // I don't want to disable Combo Box if query returns no values, instead I want it to write message "Empty - Enter new value"
.
[B]CurrentDb.QueryDefs.Delete(QueryDef.Name)[/B] // explain this please

When I say it doesn't work 100% I mean, it displays message "Empty - Enter new Value" even if the query (combo box VremePolska recieves) returns some values.
Thnx

 

[DCrake]

What bob is doing is correct

    If DCount("*", qryDef.Name)> 0 Then
        VremePolaska.RowSource = strSQL
    Else
        VremePolaska.Enabled = False
    End If

However as you don't want it to be disabled then replace

      VremePolaska.Enabled = False

with

      VremePolaska.Rowsource = "Empty"

With regard to the code lines

Set qryDef = CurrentDb.CreateQueryDef("CheckCount",strSQL)

This creates a new query that can be manipulated called CheckCount

CurrentDb.QueryDefs.[B]Delete[/B](QueryDef.Name)

As you are only creating this dynamically you don't need it to remain in the database container so it is deleted. If you kept it then next time you run the code Access would detect its presence and throw up an error.

David