Retrieving Value using VB 2

[DTSjunkie]

To begin with there are three tables here...

Person
personID - PK
...

Subject
subjectID - PK
personID - FK
...

Contact
subjectID - FK
...

I have a form which points to the Person table. I am attempting to create an action button which opens the contacts for each person record. The problem is the middle subject table. I am wondering if there was a way to retrieve the subjectID using VB so I can pass that value into the open form command.

When they click the button all I have is the personID of the record they selected. I need to retrieve the subjectID value somehow from this personID value. I was thinking of using a sql query but from my research I wasn't able to find one that supports a select statement.

Any help is greatly appreciated.

Thanks!

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DTSjunkie]

PHV,

Thank you for your reply.

I think the way I worded the question originally caused some confusion...

I do recognize there are sql commands to retrieve the data I need, i.e. a select statement. The problem I am having is with calling the statement from a MS Access Form.

I am putting an action button on a form which references the person table. This button is going to be for all the contacts with the individual. When the user clicks the contacts button I need to pull up all of the previous contacts based on the subjectID. Subject ID is not something in the person table. Therefore, I was wondering if there was a Visual Basic function I could use to retrieve the subjectID to pass into the contacts form. Originally I was trying:

Dim tempSID As Integer
tempSID=DoCmd.RunSQL("Select subjectID from Subject where personID=Me![personID]")

..... 'Open Contact form and pass in tempSID

As I have now found out, a select statement is not valid for the RunSQL method. It only takes, alter, insert and delete statements.

Hopefully this does not confuse things further. Thank you for your speedy reply.

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

[blue]I think the way I worded the question originally caused some confusion...[/blue]

No . . . nothing wrong nor any confusion. I see the same thing as [blue]PHV[/blue] does. A definite relationships problem that you need to consider. If not corrected your on the road to more trouble.

I don't know the [blue]realworld[/blue] logic your using to relate [blue]contacts to subjects[/blue] but [purple]contacts should be related to person[/purple].

Person
personID - PK
...

Subject
subjectID - PK
personID - FK
...

Contact
[purple]personID - FK[/purple]
...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[DTSjunkie]

The FK on the contact table is actually the subject ID. I mistyped it the first time I was posting.

The DLookup function worked perfectly and I am now able to open the correct records.

Thank you for all of your help