By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

Looking up value in code from a SQL statement

P: n/a
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
************************************************** ************************************************** **
Oct 21 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On Oct 21, 7:00*pm, Niranjan <nhi...@gmail.comwrote:
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)
Oct 21 '08 #2

P: n/a
On Oct 21, 11:06*am, Jim Devenish <internet.shopp...@foobox.com>
wrote:
On Oct 21, 7:00*pm, Niranjan <nhi...@gmail.comwrote:


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?
Oct 21 '08 #3

P: n/a
On Oct 21, 7:06*pm, Jim Devenish <internet.shopp...@foobox.comwrote:
On Oct 21, 7:00*pm, Niranjan <nhi...@gmail.comwrote:
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.
Oct 21 '08 #4

P: n/a
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 <nhi...@gmail.comwrote:
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
************************************************** ************************* ***************************
Oct 21 '08 #5

P: n/a
On Oct 21, 11:31*am, lyle fairfield <lyle.fairfi...@gmail.comwrote:
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 <nhi...@gmail.comwrote:
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?
Oct 21 '08 #6

P: n/a
Niranjan <nh****@gmail.comwrote in
news:ca**********************************@w39g2000 prb.googlegroups.com:
On Oct 21, 11:31*am, lyle fairfield <lyle.fairfi...@gmail.comwrote:
>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
Oct 22 '08 #7

P: n/a
"Niranjan" <nh****@gmail.comwrote in message
news:a9**********************************@a18g2000 pra.googlegroups.com...
On Oct 21, 11:06 am, Jim Devenish <internet.shopp...@foobox.com>
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.

Oct 22 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.