Passing rowsource to combo box without nulls.

  • Thread starter Thread starter Asib
  • Start date Start date
A

Asib

Hello all, I am in need of some assistance. I have 2 combo boxes on my form.
Both are bound to fields. I am useing the following code to limit the second
combo to the records associated with the selection in the first.

This is in the after update event of the First combobox,
On Error Resume Next
cboSub.RowSource = " SELECT DISTINCT ItemMaster.Sub " & _
" FROM ItemMaster " & _
" WHERE ItemMaster.ItemNumber = '" & cboItemNumber.Value & "'" & _
" ORDER BY ItemMaster.Sub;"

Everything works as it should except that i need to get rid of null values
in the second combobox. I need to see that there is data in the [sub] field
to choose from when entering data. I also have this code in the on focus
event of the second combobox.

Me.cboSub = Me.cboSub.ItemData(0)

If i change it to (1) it gets me what i need except when there are not nulls
in the [sub] field.

Any and all assistance is greatly appreciated.
Thank you, Asib
 
Asib said:
Hello all, I am in need of some assistance. I have 2 combo boxes on my form.
Both are bound to fields. I am useing the following code to limit the second
combo to the records associated with the selection in the first.

This is in the after update event of the First combobox,
On Error Resume Next
cboSub.RowSource = " SELECT DISTINCT ItemMaster.Sub " & _
" FROM ItemMaster " & _
" WHERE ItemMaster.ItemNumber = '" & cboItemNumber.Value & "'" & _
" ORDER BY ItemMaster.Sub;"

Everything works as it should except that i need to get rid of null values
in the second combobox. I need to see that there is data in the [sub] field
to choose from when entering data. I also have this code in the on focus
event of the second combobox.

Me.cboSub = Me.cboSub.ItemData(0)

If i change it to (1) it gets me what i need except when there are not nulls
in the [sub] field.
Click to expand...


If you want to exclude Null values in the Sub field, do you
get what you want by changing the row source:

cboSub.RowSource = " SELECT DISTINCT Sub " & _
" FROM ItemMaster " & _
" WHERE ItemNumber = '" & cboItemNumber.Value & "'" & _
" AND Sub Is Not Null " & _
" ORDER BY Sub"

I am noit sure I understand what you are saying about about
ItemData(0). It should work(?) as long as the ColumnHeads
property is set to No
 
That gets exactly what i wanted. Thanks a million. The ItemData(0) does work,
but i am pulling this data from another source and it is not very clean. I
will have to go back in and clean it up. Again, thank you. You saved me a
headache or 2.

Marshall Barton said:
Asib said:
Hello all, I am in need of some assistance. I have 2 combo boxes on my form.
Both are bound to fields. I am useing the following code to limit the second
combo to the records associated with the selection in the first.

This is in the after update event of the First combobox,
On Error Resume Next
cboSub.RowSource = " SELECT DISTINCT ItemMaster.Sub " & _
" FROM ItemMaster " & _
" WHERE ItemMaster.ItemNumber = '" & cboItemNumber.Value & "'" & _
" ORDER BY ItemMaster.Sub;"

Everything works as it should except that i need to get rid of null values
in the second combobox. I need to see that there is data in the [sub] field
to choose from when entering data. I also have this code in the on focus
event of the second combobox.

Me.cboSub = Me.cboSub.ItemData(0)

If i change it to (1) it gets me what i need except when there are not nulls
in the [sub] field.
Click to expand...


If you want to exclude Null values in the Sub field, do you
get what you want by changing the row source:

cboSub.RowSource = " SELECT DISTINCT Sub " & _
" FROM ItemMaster " & _
" WHERE ItemNumber = '" & cboItemNumber.Value & "'" & _
" AND Sub Is Not Null " & _
" ORDER BY Sub"

I am noit sure I understand what you are saying about about
ItemData(0). It should work(?) as long as the ColumnHeads
property is set to No
Click to expand...
 
Back
Top