Select Statement

[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

 

[Van T. Dinh]

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

assuming ProductCode is of Text type.

Check Access VB Help on the DLookUp() function.

 

[Van T. Dinh]

You like to write 5 lines of code rather than 1?

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

 

[Ice Man]

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