[VBA] Query Return Value

[fahur]

Hi!

I have a query:

SELECT port_number_new FROM NE_Data WHERE number=(SELECT LAST(number) FROM NE_Data WHERE NE_number=NEnumber);

NEnumber is a variable, which comes from another form.

How can I obtain a return value from above query? Is there some kind of function?

I'd like to have a certain value (String) to be able to use it in the form.

Thanks in advance for reply...

--
fahur

 

[DJN]

If you are trying to get the user to enter something, use a parameter query. Or have I misread the situation?

 

[supercharge]

DLookup (in addition of more statements) would be more appropriate for this, don't yah think?

 

[fahur]

OK. I know that DLookup() would do the thing, but how to parameterize the query?

 

[MarkK]

You can put your SQL results into a recordset...

Public Function GetLatestPortNumber() As Long
  Dim rst As DAO.Recordset

  Set rst = CurrentDb.OpenRecordset( _
    "SELECT port_number_new FROM NE_Data WHERE number = " & _
    "(SELECT LAST(number) FROM NE_Data WHERE NE_number=NEnumber);")

  With rst
    If Not .EOF then GetLatestPortNumber = .Fields(0).Value
    .Close
  End With

  Set rst = nothing

End Function