469,306 Members | 1,903 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Go to a Single Record. Dlookup or SQL Recordset?

When I use Dlookup. I am only able to return a single value and
therefore cannot seem to assign a single records (3-field values) to
(3-Variables). I noticed that I can get the 3-field values, but it is
returned as a single string, probably requiring parsing. Not Desirable!
Maybe I need Dlookup to get a unique key value, and then look again
for the record somehow? But, I understand that Dlookup has issues (ie
performance and reliability).

I am looking for a fast / reliable way of looking up a single record in
code, without affect my currently displayed record, and then fill my
variables. I read that a SQL /RecordSet marriage
of some sort is the way to go, but am lost on concept and syntax. Any
suggestions???

Greg

Jan 26 '07 #1
6 9877
dim rs as recordset
set rs = currentdb.opensrecordset("SELECT * FROM yourtablename WHERE
yourcriteria here")
something1 = rs!oneofyourfields
something2 = rs!anotheroneofyourfields
' do stuff
' close the recordset
rs.close
set rs = nothing

Cheers,
Jason Lepack

On Jan 26, 1:02 pm, "ApexD...@gmail.com" <ApexD...@gmail.comwrote:
When I use Dlookup. I am only able to return a single value and
therefore cannot seem to assign a single records (3-field values) to
(3-Variables). I noticed that I can get the 3-field values, but it is
returned as a single string, probably requiring parsing. Not Desirable!
Maybe I need Dlookup to get a unique key value, and then look again
for the record somehow? But, I understand that Dlookup has issues (ie
performance and reliability).

I am looking for a fast / reliable way of looking up a single record in
code, without affect my currently displayed record, and then fill my
variables. I read that a SQL /RecordSet marriage
of some sort is the way to go, but am lost on concept and syntax. Any
suggestions???

Greg
Jan 26 '07 #2
Thanks Jason

I tried the following code, but am getting a error "Missing operator in
query expression" on the Set rs line of my code, but can't figure it
out. Possible delimiter issue???

Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = " & txtZIP
Set rs = CurrentDb.OpenRecordset(strSQL)
MsgBox rs!CITY
MsgBox rs!LNAME
' do stuff
' close the recordset
rs.Close
Set rs = Nothing

Any ideas???

Jan 26 '07 #3
i think you might have to put some more " " " around your txtzip and a me

strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = " & me.txtZIP & ""
OR
strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = """ & me.txtZIP & """"

i'm still half asleep, but hope this helps you

Ap******@gmail.com wrote:
>Thanks Jason

I tried the following code, but am getting a error "Missing operator in
query expression" on the Set rs line of my code, but can't figure it
out. Possible delimiter issue???

Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = " & txtZIP
Set rs = CurrentDb.OpenRecordset(strSQL)
MsgBox rs!CITY
MsgBox rs!LNAME
' do stuff
' close the recordset
rs.Close
Set rs = Nothing

Any ideas???
--
Regan,
Paeroa
World famous in New Zealand

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

Jan 26 '07 #4
I suspect that the field ZIP is a text field. Therefore you will need
to add "'" (which is " ' " without the spaces) before and after the
txtZip.

The new line will be:
strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = "'" & txtZIP & "'"

Cheers,
Jason Lepack

On Jan 26, 3:02 pm, "ApexD...@gmail.com" <ApexD...@gmail.comwrote:
Thanks Jason

I tried the following code, but am getting a error "Missing operator in
query expression" on the Set rs line of my code, but can't figure it
out. Possible delimiter issue???

Dim rs As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = " & txtZIP
Set rs = CurrentDb.OpenRecordset(strSQL)
MsgBox rs!CITY
MsgBox rs!LNAME
' do stuff
' close the recordset
rs.Close
Set rs = Nothing

Any ideas???
Jan 26 '07 #5

Thanks Regan

strSQL = "SELECT * FROM [TL-ZIPCODE] WHERE ZIP = """ & Left(txtZIP, 5)
& """"

This solved it. I need to study Quote Placement more!
I also needed to refine txtZIP to the five initial chars.

ThankYouALL
Greg

Jan 26 '07 #6
Thanks Jason

Greg

Jan 27 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Peter Morris [Droopy Eyes Software] | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.