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