Select Statement

  • Thread starter Thread starter Ice Man
  • Start date Start date
I

Ice Man

Hi

let's say that I got an access database products.mdb
in this database there is a table products(code, name)

I want to perform a query that returns the product name once the product
code is given by a user using a form that belongs to the same database:
products.mdb

I want to to this using code builder and not a macro

what is the best way to do it


Thanks
 
DLookUp( "[ProductName]", "tblProduct", "[ProductCode] = '" &
UserInputProductCode & "'")

assuming ProductCode is of Text type.

Check Access VB Help on the DLookUp() function.
 
You like to write 5 lines of code rather than 1?

Beware of the reference problem with DAO Recordset vs ADO Recordset also.
 
Then this


Dim rst As Recordset, sSQL As String

sSQL = "SELECT [stockproductmaster].[MdlCode],
[stockproductmaster].[MdlName] "
sSQL = sSQL & " FROM [stockproductmaster] WHERE
[stockproductmaster].[MdlCode]='" & Replace([varPCode], "'", "''") & "'"
Set rst = CurrentDb.OpenRecordset(sSQL)
If Not rst.EOF Then GetModel = rst.Fields(1)
rst.Close: Set rst = Nothing
 
Back
Top