Looking up value in code from a SQL statement

[Niranjan]

Hi everyone
Normally, if I need to look up a value in a Code, from a query, I
write the VBA code stated below. I want to find out if there is an
alternate way to lookup because this is a lot of code to find one
value.

I have also used dLookup function which does lookup a vaule from a
query, however, I am not sure if you can lookup a value from a sql
statement and NOT from a saved query.

Do we have any alternatives to dLookup?
************************************************** ************************************************** **
dim sSQL as string
dim rst as recordset

sSQL = "Select * from tblCustomers where numCustomerID = " &
frm.numCustomerID
Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

if not rst.EOF then
CustomerName = rst!txtCustomerName
End If
rst.Close
Set rst = Nothing
************************************************** ************************************************** **

[Jim Devenish]

On Oct 21, 7:00*pm, Niranjan <[e-mail]:

Hi everyone
Normally, if I need to look up a value in a Code, from a query, I
write the VBA code stated below. I want to find out if there is an
alternate way to lookup because this is a lot of code to find one
value.

I have also used dLookup function which does lookup a vaule from a
query, however, I am not sure if you can lookup a value from a sql
statement and NOT from a saved query.

Do we have any alternatives to dLookup?
************************************************** ************************* ***************************
dim sSQL as string
dim rst as recordset

sSQL = "Select * from tblCustomers where numCustomerID = " &
frm.numCustomerID
Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

if not rst.EOF then
* * CustomerName = rst!txtCustomerName
End If
rst.Close
Set rst = Nothing
************************************************** ************************* ***************************

Is it not sufficient to write?:
CustomerName = dlookup("CustomerName","tblCustomers","numCustomer ID =
" & frm.numCustomerID)

[Niranjan]

On Oct 21, 11:06*am, Jim Devenish <[e-mail]>
wrote:

On Oct 21, 7:00*pm, Niranjan <[e-mail]:

Hi everyone
Normally, if I need to look up a value in a Code, from a query, I
write the VBA code stated below. I want to find out if there is an
alternate way to lookup because this is a lot of code to find one
value.

I have also used dLookup function which does lookup a vaule from a
query, however, I am not sure if you can lookup a value from a sql
statement and NOT from a saved query.

Do we have any alternatives to dLookup?
************************************************** ************************* ***************************
dim sSQL as string
dim rst as recordset

sSQL = "Select * from tblCustomers where numCustomerID = " &
frm.numCustomerID
Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

if not rst.EOF then
* * CustomerName = rst!txtCustomerName
End If
rst.Close
Set rst = Nothing
************************************************** ************************* ***************************

Is it not sufficient to write?:
CustomerName = dlookup("CustomerName","tblCustomers","numCustomer ID =
" & frm.numCustomerID)- Hide quoted text -

- Show quoted text -

In this case, it is because the query is based upon only one table.
However, if the query is based on multiply tables, I dont think I can
use the sql statement in place of "tblCustomers" in the dlookup
function. I have never tried it, may be it will work. Does anyone know?

[Jim Devenish]

On Oct 21, 7:06*pm, Jim Devenish <[e-mail]:

On Oct 21, 7:00*pm, Niranjan <[e-mail]:

Hi everyone
Normally, if I need to look up a value in a Code, from a query, I
write the VBA code stated below. I want to find out if there is an
alternate way to lookup because this is a lot of code to find one
value.

I have also used dLookup function which does lookup a vaule from a
query, however, I am not sure if you can lookup a value from a sql
statement and NOT from a saved query.

Do we have any alternatives to dLookup?
************************************************** ************************* ***************************
dim sSQL as string
dim rst as recordset

sSQL = "Select * from tblCustomers where numCustomerID = " &
frm.numCustomerID
Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

if not rst.EOF then
* * CustomerName = rst!txtCustomerName
End If
rst.Close
Set rst = Nothing
************************************************** ************************* ***************************

Is it not sufficient to write?:
CustomerName = dlookup("CustomerName","tblCustomers","numCustomer ID =
" & frm.numCustomerID)

The help system says that you can use a table name or a query name.
So not an SQL statement.

[lyle fairfield]

Dim CustomerName$
On Error Resume Next
CustomerName = DBEngine(0)(0).OpenRecordset( _
"Select * from tblCustomers where numCustomerID = " _
& frm.numCustomerID)(0)
On Error GoTo 0

On Oct 21, 2:00*pm, Niranjan <[e-mail]:

Hi everyone
Normally, if I need to look up a value in a Code, from a query, I
write the VBA code stated below. I want to find out if there is an
alternate way to lookup because this is a lot of code to find one
value.

I have also used dLookup function which does lookup a vaule from a
query, however, I am not sure if you can lookup a value from a sql
statement and NOT from a saved query.

Do we have any alternatives to dLookup?
************************************************** ************************* ***************************
dim sSQL as string
dim rst as recordset

sSQL = "Select * from tblCustomers where numCustomerID = " &
frm.numCustomerID
Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

if not rst.EOF then
* * CustomerName = rst!txtCustomerName
End If
rst.Close
Set rst = Nothing
************************************************** ************************* ***************************

[Niranjan]

On Oct 21, 11:31*am, lyle fairfield <[e-mail]:

Dim CustomerName$
On Error Resume Next
CustomerName = DBEngine(0)(0).OpenRecordset( _
"Select * from tblCustomers where numCustomerID = " _
& frm.numCustomerID)(0)
On Error GoTo 0

On Oct 21, 2:00*pm, Niranjan <[e-mail]:

Hi everyone
Normally, if I need to look up a value in aCode, from aquery, I
write the VBAcodestated below. I want to find out if there is an
alternate way to lookup because this is a lot ofcodeto find one
value.

I have also used dLookup function which does lookup a vaule from a
query, however, I am not sure if you can lookup a value from a sql
statement and NOT from a savedquery.

Do we have any alternatives to dLookup?
************************************************** ************************* ***************************
dim sSQL as string
dim rst as recordset

sSQL = "Select * from tblCustomers where numCustomerID = " &
frm.numCustomerID
Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

if not rst.EOF then
* * CustomerName = rst!txtCustomerName
End If
rst.Close
Set rst = Nothing
************************************************** ************************* ***************************- Hide quoted text -

- Show quoted text -

I am getting a Run-Time Error 13 "Type Mismatch" with the code above.
Any ideas?

[lyle fairfield]

Niranjan <nh****@gmail.comwrote in
news:ca**********************************@w39g2000 prb.googlegroups.com:

On Oct 21, 11:31*am, lyle fairfield <[e-mail]:

>Dim CustomerName$
On Error Resume Next
CustomerName = DBEngine(0)(0).OpenRecordset( _
"Select * from tblCustomers where numCustomerID = " _
& frm.numCustomerID)(0)
On Error GoTo 0

I am getting a Run-Time Error 13 "Type Mismatch" with the code above.
Any ideas?

1. That you should click on debug and find the line where the error
occurs?

2. That I didn't copy the code exactly to my post?

3. That you didn't copy the code exactly?

4. It's very hard to get a runtime error after On Error Resume Next.

If I had to bet I'd bet on 1, 3 and 4. But it's not as sure as Curlin.

This works in Northwind 2007. You could modify it for your own needs.

Function CustomerName$()
On Error Resume Next
CustomerName = DBEngine(0)(0).OpenRecordset( _
"Select * from Customers where ID = 3")(0)
On Error GoTo 0
End Function

Sub temp()
Debug.Print CustomerName
End Sub

Perhaps you think real programmers don't use "On Error Resume Next". You
and a few million others are wrong. People write their own error handling
because it reenforces their self-image as sophisticated programmers. The
fact that 99.44% of error handling routines are redundant doesn't bother
them at all if writing them makes them king of the hill (even if it's a
tiny hill.)

BTW, the "On Error Resume Next" line here simply effects an empty string
being returned if the SQL call fails, or there are no records.

--
lyle fairfield

[Keith Wilby]

"Niranjan" <nh****@gmail.comwrote in message

news:a9**********************************@a18g2000 pra.googlegroups.com...
On Oct 21, 11:06 am, Jim Devenish <[e-mail]>
wrote:

In this case, it is because the query is based upon only one table.
However, if the query is based on multiply tables, I dont think I can
use the sql statement in place of "tblCustomers" in the dlookup
function. I have never tried it, may be it will work. Does anyone know?

So why not use DLookup on a stored query on all required tables?

Keith.