Cannot get values for a Combobox..

[krishnak]

Hi All,

I am trying to retrieve the unique names from a table into the combobox on a form. I have the attached code for the command button on the form.

When I run the code, I am getting the error message:
'Invalid use of Null'.

In the same code, if I use another table for a string value for the same recordset, the code works and the combobox gets populated with the unique names of the field selected. I tried to retrieve the values for different fields from the table tblPlants, but I get the same error.
It appears that I cannot populate the combobox with any of the field values from the tblPlants. But the values are not Null - by using the code line for the MsgBox (msg), I am able to see the values in the MsgBox.
What can go wrong with the tblPlants? Or is it some other criteria I am missing?

Public Sub btnShMetrics_Click()

Dim pName As String, divNr As String
Dim strSqlPl As String, strSqlSe As String
Dim msg As String

Dim db As DAO.Database
Dim rstPlName As DAO.Recordset
Dim rstDiv As DAO.Recordset

' Dim msg As String
Set db = CurrentDb
' strSqlPl = "SELECT DISTINCT [PlantName]FROM M_Plants_Input_Data;"
strSqlSe = "SELECT DISTINCT[Plant_Sh_Name]FROM tblPlants;"
' strSqlMnth = "SELECT DISTINCT [RefMonth] FROM M_Plants_Input_Data"
' strSqlYr = "SELECT DISTINCT [RefYear] FROM M_Plants_Input_Data"

' Open the recordset for populating the plant names.
Set rstPlName = db.OpenRecordset(strSqlSe, dbOpenDynaset)
If (rstPlName.RecordCount <> 0) Then
rstPlName.MoveFirst
Do
Me.cmbPName.AddItem (rstPlName![Plant_Sh_Name])
' Me.cmbPName.AddItem (rstPlName.Fields(0).Value)
msg = msg & vbCrLf & rstPlName![Plant_Sh_Name]
rstPlName.MoveNext
Loop While (rstPlName.EOF = False)
End If
MsgBox (msg)
rstPlName.Close
db.Close

End Sub

[OBP]

If you create and run a normal Select Query do you get a recordset?
If you change the query to Distinct do you get a Recordset?
Have you tried
strSqlSe = "SELECT DISTINCT tblPlants.[Plant_Sh_Name]FROM tblPlants;"
Have you also tried splitting up the String
strSqlSe = "SELECT DISTINCT tblPlants.[Plant_Sh_Name] " & _
"FROM tblPlants;"

[Gollem]

Hi,

you don't have a space between your field and from:



"SELECT DISTINCT[Plant_Sh_Name]FROM tblPlants;"




"SELECT DISTINCT[Plant_Sh_Name] FROM tblPlants;"


I don't know if this is necessary. Otherwise I agree with OBP.

[krishnak]

Hi All,

It appears that there is a single null value appearing in the recordset and that is causing the problem.

When I included the code...


Do
If Not (IsNull(rstPlName![Plant_Sh_Name])) Then
....rest of the code here ....
End If
rstPlName.MoveNext
Loop While (rst.EOF = False)


.. this works OK. The combobox gets filled up.