Trying to get 1 record to reference another on the same table in a Module

[mehmet]

I'm trying to write a module in Access 97 that takes a record that is predefined, compares it to a table, finds a corresponding record on that table and displays that corresponding record as my result.

My table (named "Records")looks like this:

Table name: Records

Predefined | Corresponding
A B



I thought I could do this using SELECT FROM & WHERE, but I think I've been going about this all wrong. Here's what I've been trying to get work:



Private Sub Test1_Click()

Dim myrecord As String

myrecord = "SELECT tblRecords.Corresponding FROM tblRecords WHERE tblRecords.Predefined = A"


MsgBox ([myrecord])

Exit Sub



I want B as a result but all that I'm getting as the result in my Message Box is "SELECT tblRecords.Corresponding FROM tblRecords WHERE tblRecords.Predefined = A " and not the record I was looking for. :(

What am I doing wrong? Am I going about this the completely wrong way?

[techgnome]

Originally posted by mehmet
I'm trying to write a module in Access 97 that takes a record that is predefined, compares it to a table, finds a corresponding record on that table and displays that corresponding record as my result.

My table (named "Records")looks like this:

Table name: Records

Predefined | Corresponding
A B



I thought I could do this using SELECT FROM & WHERE, but I think I've been going about this all wrong. Here's what I've been trying to get work:



Private Sub Test1_Click()

Dim myrecord As String

myrecord = "SELECT tblRecords.Corresponding FROM tblRecords WHERE tblRecords.Predefined = A"


MsgBox ([myrecord])

Exit Sub



I want B as a result but all that I'm getting as the result in my Message Box is "SELECT tblRecords.Corresponding FROM tblRecords WHERE tblRecords.Predefined = A " and not the record I was looking for. :(

What am I doing wrong? Am I going about this the completely wrong way?

That's because that's all you are doing with it. You haven't actualy executed the SELECT.

I'm going to go out on a limb and say "Yer new to DB programming aren't you?"

Try reading these articles (http://www.developerkb.com/modules/wfsection/index.php?category=23) they walk you through some of the basics of ADO. Ignore the part about SQL Server in the intro, they actualy use Access in the examples.

TG

[mehmet]

Originally posted by techgnome

I'm going to go out on a limb and say "Yer new to DB programming aren't you?"


I don't think that's really going out on a limb. :)

Thanks for the link.

edit:
I edited my original code to be like this:

Private Sub test1_click()
dim rst as DAO.recordset
dim db as DAO.Database

dim strSQL as string
strsql="SELECT tblRecords.Corresponding FROM tblRecords WHERE tblRecords.Predefined = 'A'"
set db=currentdb
set rst=db.openrecordset(strSQL)
msgbox rst.fields("Corresponding").value
end sub


Now I'm getting "Item not found in this Collection" as my error. Any idea why? I have values for both "Predefined" and "Corresponding"

edit: nevermind, I got it. I was messing up my syntax. Thanks!