krishnak
05-29-2010, 11:22 AM
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
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