Go to a Single Record. Dlookup or SQL Recordset?

[ApexData]

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

[Jason Lepack]

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, "[e-mail]" <[e-mail]:

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

[ApexData]

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 via AccessMonster.com]

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

[Jason Lepack]

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, "[e-mail]" <[e-mail]:

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???

[ApexData]

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

[ApexData]

Thanks Jason

Greg