How do I link a field value to a form when it's not a key?

[psulion01]

Another question...should be easy for most non-beginners...

I have a Clients table/form that contains ClientID (the primary key) and an account number (which is unique, one per ClientID).

I have a Plans table/form with a one->many relationship to the ClientID. For instance, I can have one client with many plans. I want the account number assoicated with the ClientID to populate on my Plans form, as this is the value that I'll be referencing most often, not the autonumbered ClientID.

How do I link the ClientAccount fields on both the Clients and Plans forms?

On a related note, if the fields aren't linked on the Form level, would a report or query that's generated be able to associate the two?

Thanks in advance.
Mike

 

[Norie]

Create a query based on both tables.

The SQL would look something like this

SELECT Clients.AccountNo, Plans.PlanID
FROM Clients INNER JOIN Plans ON Clients.ClientID = Plans.ClientID;

(obviously include any other fields required)

This can then be used as the recordsource for the Plans form.

 

[psulion01]

Re: How do I link a field value to a form when it's not a ke

hmm...got some errors that the syntax of subquery is incorrect. Not sure if this will do what I want though. Basically I have account numbers on one form and want them pulled and populated on another form (although the account number is not the primary key and no relationship is established).

I'll keep poking at it...any further thoughts?