CODEWITHSUNDEEP
ms-access
Rob
Rob

Reputation: 2472

MS Access - how to create a label on a form that populates data from another table

I have a fairly simple database that I inherited. For the purposes of this question, there are two tables: Mastertable and Providertable.

Mastertable references Providertable thruogh provid, which is a FK to Providertable PK (provid).

So it looks like this:

Mastertable:

acct (PK) (other fields) provid (FK)

Providertable

provid (PK) provname provspecialty

Simple right? However, the Mastertable!provid field is actually a lookup table which displays Providertable!provname but stores provid. There is a form the users use to populate the Mastertable, and it has this lookup field shown.

The users now want to show the provider specialty based on what they select as the provid. I can't figure this out to save my life. I'm pretty well versed in SQL, having written many stored procedures and created a few db apps using .NET, but this is quite challenging. I tried creating a lookup field called provspeciality, but that's not what they want. I tried changing the "OnUpdate" event for the lookup field to point the Provider Specialty label to the right thing.

Right now, I can't even get a simple select going that joins the two tables since they are using this lookup field as the FK and Access I guess can't understand it. Any help appreciated.

Upvotes: 1

Views: 9519

Answers (2)

Rob
Rob

Reputation: 2472

I created an "On Change" event for the form field "provid" which is a lookup field that displays the provider name while putting the provider id into the master table as a FK. However apparently Access is not able to do lookups based on this field (or I am doing something wrong) using queries - as shown above in comments. What I did is use this as the event code. Important, you must enable macros for this to work!

Private Sub provid_Change()
    Me.txtProviderSpecialty = DLookup("Provspecialty", "Providertable", "provid = " & Me.provid.Value)
End Sub

Upvotes: 1

HansUp
HansUp

Reputation: 97100

Since the Mastertable provid field is a lookup type, the displayed value is the lookup value rather than the value which is actually stored in the field. This query will show you the stored provid values.

SELECT acct, provid
FROM Mastertable;

And I think you should be able to retrieve the matching provider specialties with a query similar to this:

SELECT m.provid, p.provname, p.provspecialty
FROM
    Mastertable AS m
    INNER JOIN Providertable AS p
    ON p.provid = m.provid;

You may even be able to use that query as the Row Source for a combo or list box on your form. Make provid the bound column. You may wish to set the provid column width to 0" so it is not actually displayed in the control, but still stored in Mastertable.

I think you should modify the table to make provid a normal (numeric?) field instead of a lookup. Fortunately you indicated this is "fairly simple database", so that will hopefully limit the amount of additional changes you need to be compatible with the redesigned table. Good luck.

Upvotes: 1

Related Questions