How to Create a Basic Select 2

[MC77]

Need an example of code that would run a simple SELECT from a DB and how to assign the returned rows to variables, so that the can be used elsewhere in the script.

(ie. SELECT id, lastname, state FROM Address WHERE id = '12345' then assign lastname and state to variables that can be used locally in the script.

Thanks.

 

[LonnieJohnson]

You should look into using a record set object. See help. The basic structure is...

Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTableName&quot

Do until rs.EOF 'Iterate until the end of the file
IF 'something' Then
'Do something else'
End If prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[twoody54]

I'm not sure exactly what you're talking about but what about something like this...

select id, lastname as FoundLastName, state as FoundState where id='12345'

????

 

[jgpasko]

MC77:

When you mean that the 'variables can be used locally in the script' what script are you refering to? If you mean the returned variables can be used on the form or in another QBE or SQL Statement then LonnieJohnson pointed you in the right direction. Here is how it would work:

Private Function SomeName()
'On Error Insert Error Handling
Dim db as Database
Dim rs as Recordset
Dim strLastName as String
Dim strState as String

Set db = Codedb
Set rs = db.OpenRecordset("SELECT tbl.id, tbl.lastname, tbl.state FROM tbl WHERE tbl.id = '12345'&quot

strLastName = rs.lastname
strState = rs.state

rs.close
db.close

End Function

Now you have you variables and you can use them how you want.

Is this what you needed? John Pasko
john@rts-sd.com
"No matter where you go, there you are."