How to say if a cbo is empty or null? (Or what?) (1 Viewer)

[ToucHDowN]

Hi,

I have a form, form1. Inside there is a combo box, cbo1, that gets its values from table1.

On form load, I want an expression that checks if table1 has any values to "send" to cbo1. If yes, meaning, if after form loads cbo1 is loaded with table1's values, I want cbo1 colored RED, if not, colored WHITE.

Any ideas?

Thanks.

 

[bob fitz]

Perhaps something like:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "table1") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

You may need the code in the forms On Current event as well if the number of records in table1 can change while the current form is open.

 

[sumox]

ya that's good and
also simple tricks work:
right-click it --> Conditional Formatting --> type here the Expression to identify
and create a situation and select formatting

this is also another way

 

[bob fitz]

ya that's good and
also simple tricks work:
right-click it --> Conditional Formatting --> type here the Expression to identify
and create a situation and select formatting

this is also another way

I agree that Conditional Formatting can be very useful but I can't see how it could be used simply to fulfill the OP's requirement.

 

[ToucHDowN]

Perhaps something like:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "table1") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

You may need the code in the forms On Current event as well if the number of records in table1 can change while the current form is open.

It's my fault that I oversimplified my problem. Real-case scenario is this (still simplified for clarity reasons, but not as much as before):

I have form1. When it loads, there is already a filter to load a previously clicked item through a list, say Car1. In a previous form, there is a list with Car1, Car2, Car3, etc. User selects Car1, so form1 loads all Car1 details, which are stored in a table called Cars. Ok.

Now, in this form, form1, there is also a cbo that gets its values from another table, say, CarManuals, if and only if for the selected Car1 there is a relevant manual published. Two tables are related through CarID.

The RowSource for the cbo is the following:

SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=qryAllBase.CarID 
ORDER BY tblCarManuals.ManualNo;

Query "qryAllBase" is the Record Source for my form1. So, when form1 loads with information for Car1 (i.e. qryAllBase.CarID=Car1), my cbo runs the code above and displays only the car manuals related to Car1, by comparing the two CarIDs.

Now, what I want to do is to have the cbo colored, say RED, when data found OR leave it with no color if the cbo is empty, meaning that there is no car manual available for the selected car.

Since there will be a lot of different cbos in form, my ultimate goal is to visually guide the user to the cbo which has values in it.
Thank you for your time, I wish it's clearer now.

 

[ToucHDowN]

I was trying to use something like (lucky guesses):

If cbo IsNull then cbo.BackColor = xxxx but it doesn't work. Cbos aren't null? The same goes with IsEmpty.

 

[bob fitz]

I was trying to use something like (lucky guesses):

If cbo IsNull then cbo.BackColor = xxxx but it doesn't work. Cbos aren't null? The same goes with IsEmpty.

The combo will only have a value when the form it is on is opened if it is bound to a field in the bound record source.

So, perhaps you could use the code I posted but replace "Table1" in the DCount function with the SQL statement you posted.

 

[ToucHDowN]

The combo will only have a value when the form it is on is opened if it is bound to a field in the bound record source.

So, perhaps you could use the code I posted but replace "Table1" in the DCount function with the SQL statement you posted.

Run-time error 3078: The Microsoft Office Access database cannot find the input table or query "SELECT......"...

I just want to check on form load if cbo is loaded with values. If yes, RED, if no, WHITE.

 

[ToucHDowN]

I even tried to load the SQL statement to a hidden ListBox and then check if this ListBox IsNull in order to paint cbo RED, but it seems ListBoxes are not "null" either...

 

[bob fitz]

I think you may be misunderstanding what is meant by a combo box being Null. A combo is null if no value is selected, even if there are many items in the list it displays. As I understanding of your requirement is to determine if there are any items in the list or not. To do that, you would need to use DCount() to return the number of rows in the domain that is used for that combo.
Perhaps it would be best for you to post a copy of your db in A2003 mdb format for us to examine and play with.

 

[ToucHDowN]

I think you may be misunderstanding what is meant by a combo box being Null. A combo is null if no value is selected, even if there are many items in the list it displays. As I understanding of your requirement is to determine if there are any items in the list or not. To do that, you would need to use DCount() to return the number of rows in the domain that is used for that combo.
Perhaps it would be best for you to post a copy of your db in A2003 mdb format for us to examine and play with.

Yes but just counting rows won't do any good in my example because I want to check if SOME rows are loaded in the cbo, related only to, say, the Car1, item, in the example above.

The source for the cbo is a table containing hundreds of records, for all available Cars, in the same example. When Car1 form is loaded, cbo only loads records relevant/related to Car1. And if so, meaning, if cbo's list is not empty, I want to know and .backcolor accordingly.

Uploading my db won't be of any help I guess, due to language restrictions... (Greek).

Thanks for your time. I'll post back If I find the solution.

p.s. replacing table1 with my SQL statement, as suggested, didn't work.

 

[bob fitz]

p.s. replacing table1 with my SQL statement, as suggested, didn't work.

Can you be more specific. Error number ? Error Message ?

 

[ToucHDowN]

Can you be more specific. Error number ? Error Message ?

Already posted it, 4 or 5 messages above I thought you'd noticed it.

 

[bob fitz]

Already posted it, 4 or 5 messages above I thought you'd noticed it.

Sorry, I missed that. Can you post the code you used when you got that error.

 

[ToucHDowN]

Sorry, I missed that. Can you post the code you used when you got that error.

This is the code:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=qryAllBase.CarID 
ORDER BY tblCarManuals.ManualNo;") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

Also Bob, I've got this idea: couldn't we just assign the results of the above SQL statement to a variable, then check if this variable is zero length or not and then paint the cbo accordingly?

 

[bob fitz]

In the SELECT statement, you have "....tblCarManuals.CarID=qryAllBase.CarID...."
What is qryAllBase.CarID that you are using.

 

[ToucHDowN]

In the SELECT statement, you have "....tblCarManuals.CarID=qryAllBase.CarID...."
What is qryAllBase.CarID that you are using.

Bob thank you very much for your time. You probably missed this one too. Never mind, I'm pasting again a previous post of mine explaining this point:

It's my fault that I oversimplified my problem. Real-case scenario is this (still simplified for clarity reasons, but not as much as before):

I have form1. When it loads, there is already a filter to load a previously clicked item through a list, say Car1. In a previous form, there is a list with Car1, Car2, Car3, etc. User selects Car1, so form1 loads all Car1 details, which are stored in a table called Cars. Ok.

Now, in this form, form1, there is also a cbo that gets its values from another table, say, CarManuals, if and only if for the selected Car1 there is a relevant manual published. Two tables are related through CarID.

The RowSource for the cbo is the following:

Code:
SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals WHERE
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=qryAllBase.CarID
ORDER BY tblCarManuals.ManualNo;

Query "qryAllBase" is the Record Source for my form1. So, when form1 loads with information for Car1 (i.e. qryAllBase.CarID=Car1), my cbo runs the code above and displays only the car manuals related to Car1, by comparing the two CarIDs.

Now, what I want to do is to have the cbo colored, say RED, when data found OR leave it with no color if the cbo is empty, meaning that there is no car manual available for the selected car.

Since there will be a lot of different cbos in form, my ultimate goal is to visually guide the user to the cbo which has values in it.
Thank you for your time, I wish it's clearer now.

 

[bob fitz]

Thank you. To be honest I thought I'd read something of it already but thought it would be easier to ask you again rather than search through the whole thread. Any way, I assume you have a control on form1 that is called CarID which holds numeric data. If that is correct try the following:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=" & Forms!Form1.CarID & " ORDER BY tblCarManuals.ManualNo;") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

If CarID is not numeric try:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID='" & Forms!Form1.CarID & "' ORDER BY tblCarManuals.ManualNo;") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

 

[ToucHDowN]

Thank you. To be honest I thought I'd read something of it already but thought it would be easier to ask you again rather than search through the whole thread. Any way, I assume you have a control on form1 that is called CarID which holds numeric data. If that is correct try the following:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=" & Forms!Form1.CarID & " ORDER BY tblCarManuals.ManualNo;") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

If CarID is not numeric try:

Private Sub Form_Open(Cancel As Integer)
  If DCount("*", "SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID='" & Forms!Form1.CarID & "' ORDER BY tblCarManuals.ManualNo;") < 1 Then
    Me.cbo1.BackColor = 225
  Else
    Me.cbo1.BackColor = 16777215
  End If
End Sub

Yes, CarID is numeric. I used your numeric code.

Still, I get the same runtime error. Access can't find the input table or query...

 

[bob fitz]

OK, well you were correct when you said in an earlier post that probably do not understand Greek. However, if you can post your db in A2003 mdb format I may still be able to debug the code and correct where we are going wrong. I would at least like to try.