If a queryvfinds no records (2 Viewers)

[Bobp3114]

I have a combo box "cboOpportunity", run by a query :qryActivityOpportunity" which simply looks to find records linked to the value of "cboCompany"
cboOpportunity is set to Limit to List and is a bound combo
I wish to add the phrase "No Opportunity" when the query finds no records

 

[theDBguy]

There's a couple of approaches here.

Forms: Adding "All" to a listbox or combobox

 

[MajP]

I do not interpret that the same way

1. You have cboCompany with company ID
2. You have cboOpportunity based on qryActivityOpportunity which has a foreign key to the company
3. You pick a company in cboCompany and want a message if no company in cboOpportunity

Private Sub cboCompany_AfterUpdate()
  Dim rs As Recordset

  Const qryName = "qryActivityOpportunity"
 
  If Not IsNull(Me.cboCompany) Then
    Set rs = CurrentDb.OpenRecordset(qryName)
    rs.FindFirst "CompanyID = " & Me.cboCompany
    If rs.NoMatch Then
      MsgBox "Company not found", vbInformation
    Else
      'do something if found
    End If
  End If
    
End Sub

or

Private Sub cboCompany_AfterUpdate()
  Dim rs As Recordset
  
  If Not IsNull(Me.cboCompany) Then
    Set rs = me.cboOpportunity.recordset
    rs.FindFirst "CompanyID = " & Me.cboCompany
    If rs.NoMatch Then
      MsgBox "Company not found", vbInformation
    Else
      'do something if found
    End If
  End If
    
End Sub

 

[Ken Sheridan]

I have a combo box "cboOpportunity", run by a query :qryActivityOpportunity" which simply looks to find records linked to the value of "cboCompany"
cboOpportunity is set to Limit to List and is a bound combo
I wish to add the phrase "No Opportunity" when the query finds no records

This implies that there is a relationship between Companies and Opportunities, modelled by a table CompanyOpportunities with columns CompanyID and OpportunityID for example. I'd assume that the query is correlated with a form's current record by referencing a CompanyID control or similar. The following query is for an analogous situation where a relationship between Contacts and Employers is modelled by a ContactEmployers table with columns ContactID and EmployerID:

SELECT
    Employers.EmployerID,
    Employer
FROM
    Employers
    INNER JOIN ContactEmployers ON Employers.EmployerID = ContactEmployers.EmployerID
WHERE ContactID = Forms!frmContacts!ContactID
    AND Employer <> "Unemployed"
UNION
SELECT
    EmployerID,
    Employer
FROM
    Employers
WHERE Employer = "Unemployed"
    AND NOT EXISTS
        (SELECT * FROM ContactEmployers
        WHERE ContactID = Forms!frmContacts!ContactID)
ORDER BY
    Employer;

As you can see from the above the Employers table includes a row with the value 'Unemployed' at the Employer column position. If, in a frmContacts form, the current contact has one or more employers then, by using the above query as the RowSource for a combo box in the form, or more likely a subform, the combo box's list would be of the contact’s employers. If, on the other hand the contact has no employers, then the combo box's list would be of the one value 'Unemployed' only. The combo box should be requeried in the form's Current event procedure