DAO OpenRecordset no errors but not returning any records.

[pauldennis]

I have a query that looks at the T_AVAILABILITY table and then calls this
module. The idea of the module is to find all other records in this table
(T_AVAILABILITY) where ITEM is the same. I will need to do some complex
calculation once I can work out how to loop through the table based on the
ITEM.

So far I have the code below and the syntax seems fine, i.e. no error,
however at no point does the fields returned seem to be populated. Can you
help?

Function Availability(E_ITEM As String, E_SEVERITY As Integer, E_PROBLEM_ID
As String) As String
On Error GoTo Err_Availability

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim strItem As String
Dim strSQL As String
Dim DB As DAO.Database

Set DB = CurrentDb
strSQL = "SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_AVAILABILITY " & _
"ORDER BY ITEM;"
Set rst = CurrentDb.OpenRecordset(strSQL)

strItem = "ITEM = '" & E_ITEM & "'"
rst.FindFirst strItem
Availability = PROBLEM_ID

 

[Dirk Goldgar]

I have a query that looks at the T_AVAILABILITY table and then calls
this module. The idea of the module is to find all other records in
this table (T_AVAILABILITY) where ITEM is the same. I will need to do
some complex calculation once I can work out how to loop through the
table based on the ITEM.

So far I have the code below and the syntax seems fine, i.e. no error,
however at no point does the fields returned seem to be populated.
Can you help?

Function Availability(E_ITEM As String, E_SEVERITY As Integer,
E_PROBLEM_ID As String) As String
On Error GoTo Err_Availability

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim strItem As String
Dim strSQL As String
Dim DB As DAO.Database

Set DB = CurrentDb
strSQL = "SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_AVAILABILITY " & _
"ORDER BY ITEM;"
Set rst = CurrentDb.OpenRecordset(strSQL)

strItem = "ITEM = '" & E_ITEM & "'"
rst.FindFirst strItem
Availability = PROBLEM_ID

At the least, you need to qualify PROBLEM_ID with a reference to the
recordset:

Availability = rst!PROBLEM_ID

Also, you need to check to make sure the recordset is not empty, and
that the FindFirst call finds a matching record. Making minimal
modifications to your current code, that would look like this:

' ...

Set rst = CurrentDb.OpenRecordset(strSQL)

If Not rst.EOF Then
rst.FindFirst strItem
If Not rst.NoMatch Then
Availability = rst!PROBLEM_ID
End If
End If

' ...

If you're only looking up one item in this function, though, it makes
more sense to open the recordset on a query that returns just records
for that item:

strSQL = _
"SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_AVAILABILITY " & _
"WHERE ITEM = '" & E_ITEM & "'"

Set rst = CurrentDb.OpenRecordset(strSQL)

If Not rst.EOF Then
Availability = rst!PROBLEM_ID
End If

 

[Alex Dybenko]

Hi,
instead for using rst.FindFirst much more efficient to filter by item in
SQL:
strSQL = "SELECT ITEM, SEVERITY, PROBLEM_ID " & _
"FROM T_AVAILABILITY " & _
"Where BY ITEM='" & E_ITEM & "'