MLH wrote:
What I would like to know is if DLookUp can be
used to extract the same data. Do you know how?
Not, AFAIK, with joined table. DlookUp deals with one table.
Make it a function, it's easy as pie (air code) - you're using A97, right?
You want to retrieve the OTypDescription value. Just use this very
simple function with the job id as an argument.
Function fRetrievePreciiselyWhatMlhNeeds(lngVehJobId as long) as String
'assumes a long is returned. If there is no result from the
'SQL, then a "" (ZLS) is returned
'
'lngVehJobId is the vehicle job number ID fed from calling proc
dim strSql as string
dim dbs as DAO.Database
dim rst as DAO.recordset
On error goto Err_Proc
'Simplified SQL follows - assumes you have unique field names and
'won't need the overly verbose SQL the excellent Access query
'design grid returns by default. LOOK AT WHAT I've WRITTEN
'CAREFULLY and how lngVehJobId is introduced
strsql = "SELECT TOP 1 OTypDescription " & _
"FROM tblOutboundTypes INNER JOIN tblCorrespondence ON " & _
"tblOutboundTypes.OutType = tblCorrespondence.OutType " & _
"WHERE OutDate Is Not Null AND " & _
"VehicleJobID = " & lngVehJobId & _
"ORDER BY CorrespID DESC"
Set dbs = Access.currentdb
'Open SQL in snapshot mode - this is much faster when
'there are lots of records involved
set rst = dbs.openrecordset(strSql, dbopensnapshot)
with rst
if .eof then
fRetrievePreciiselyWhatMlhNeeds = ""
else
.movefirst 'note that top 1 values can return more than 1 record
fRetrievePreciiselyWhatMlhNeeds = .fields!OTypDescription
end if
End with
Exit_Proc:
On Error Resume Next
rst.close
Set rst = nothing
dbs.close
set dbs = nothing
on error goto 0
exit function
Err_Proc:
Select Case err.number
case else
msgbox "Error " & err.number & " " & _
err.description,vbcritical, _
"Error in fRetrievePreciiselyWhatMlhNeeds", _
Err.HelpFile, Err.HelpContext
Resume exit_proc
End select
end function
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
