"pauldennis" <u19890@uwe> wrote in message news:5d8bba7b11502@uwe
> 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
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Similar Threads
