Hi All,

I am using a Dynamic SQL query as the rowsource for a combo box which is 
being used to find and return patient names form a database. The SQL query 
(associated with the ComboBox OnChange event) returns the rows that match 
the patient surname string entered by the user  and the user can then 
choose one of the rows displayed to open the appropriate record form using 
a DoCmd.OpenForm command associated with the ComboBox AfterUpdate event. 
So far so good
 
but ...
 
I would like to be able to detect the condition where the SQL query 
returns no rows to automatically trigger a prompt to add a new record. 
When this condition occurs the NotInList Event is not triggered until the 
user hits a key e.g the <enter> key but I want to be able to detect the 
condition without the user having to do this. I have tried to do this by 
detecting whether the first column value cbo.column(0) is Null but if the 
query returns no rows this does not work. So I was wondering whether 
anyone had any ideas as to how I might successfully detect the condition 
where the SQL query used to populate the combo box returns no rows?
 
regards Howard Beswick (UK)

Howard,

<< I would like to be able to detect the condition where the SQL query 
returns no rows to automatically trigger a prompt to add a new record. 
When this condition occurs the NotInList Event is not triggered until the 
user hits a key e.g the <enter> key but I want to be able to detect the 
condition without the user having to do this. I have tried to do this by 
detecting whether the first column value cbo.column(0) is Null but if the 
query returns no rows this does not work. So I was wondering whether 
anyone had any ideas as to how I might successfully detect the condition 
where the SQL query used to populate the combo box returns no rows? >>

Yes, the answer is quite easy!  There is a property called .ListCount you 
can use it like this:

   Me.cboPickPerson.Rowsource = "SELECT... blah blah"
   If Me.cboPickPerson.ListCount = 0
      'No data in the list.
      MsgBox "Please add a new record.", vbExclamation, "No Data!"
   End If

Incidentally, depending on the form and kind of data, if there's only one 
item on the list and the user MUST pick SOMETHING, I just assign it to the 
combobox automatically.

   If Me.cboPickPerson.ListCount = 1 Then
      Me.cboPickPerson = Me.cboPickPerson.Column(0)
   End If  

   'Run the combobox AfterUpdate event if it exists.  If not, leave out
   'this line below.
   cboPickPerson_AfterUpdate


Greg

> Howard,

> << I would like to be able to detect the condition where the SQL query 
r> eturns no rows to automatically trigger a prompt to add a new record. 
W> hen this condition occurs the NotInList Event is not triggered until 
the 
u> ser hits a key e.g the <enter> key but I want to be able to detect the 
c> ondition without the user having to do this. I have tried to do this by 
d> etecting whether the first column value cbo.column(0) is Null but if 
the 
q> uery returns no rows this does not work. So I was wondering whether 
a> nyone had any ideas as to how I might successfully detect the condition 
w> here the SQL query used to populate the combo box returns no rows? >>

> Yes, the answer is quite easy!  There is a property called .ListCount 
you 
c> an use it like this:

>    Me.cboPickPerson.Rowsource = "SELECT... blah blah"
 >   If Me.cboPickPerson.ListCount = 0
 >      'No data in the list.
 >      MsgBox "Please add a new record.", vbExclamation, "No Data!"
 >   End If

> Incidentally, depending on the form and kind of data, if there's only 
one 
i> tem on the list and the user MUST pick SOMETHING, I just assign it to 
the 
c> ombobox automatically.

>    If Me.cboPickPerson.ListCount = 1 Then
 >      Me.cboPickPerson = Me.cboPickPerson.Column(0)
 >   End If  

>    'Run the combobox AfterUpdate event if it exists.  If not, leave out
 >   'this line below.
 >   cboPickPerson_AfterUpdate

> 
G> reg

Thanks for the reply Greg I will check it out, regards Howard

<< Thanks for the reply Greg I will check it out, regards Howard >>

No problem.  One boo-boo... the cboPickPerson_AfterUpdate line should be 
inside the IF statement, not after it.

Greg