Changing field based on drop down / lookup

[cballew]

So I have a table named providers and on it there are two columns ProviderName and NPI. I'm making a form for another table called tblCases. The first field the user would select is provider name which is a straightforward drop down query from the tblproviders.providername . What I would then like is the NPI text field on my form for tblcases to automatically change to the associated NPI on the tblproviders based on what the user selected as the provider's name.

Is that possible? Or any other ideas if it isn't?



Thanks!

[Krushert]

I was justing to ask this question because I have the same issue. Thanks for asking.


The question I was going to ask is "Would this be IF-Then Statement? Or a look-up? Or something else?"

Also, I am a newbie to Access. So please point me to an example as a go by to learn from.

[Beetle]

As an example, let's say that on your form you have a combo box named cboProviders and a text box named txtNPI. In the row source query for cboProviders you need to also return the NPI field from the Providers table (along with the ProviderName field). You do not need to display this field in the combo box, but it needs to exist in the row source query. You can hide it from the users by setting the Column Width property to zero. So properties of your combo box might look like;

Row Source: Select ProviderName, NPI From tblProviders
Column Count: 2
Column Widths: 1"; 0"

Then, in the After Update event of the combo box you would set the value of the text box equal to the second column of the combo box. Column numbering is zero based, so the first column is Column(0), the second is Column(1), etc. The code might look like;

Private Sub cboProviders After_Update()

    Me.txtNPI = Me.cboProviders.Column(1)

End Sub

By the way, I don't recommend that you store NPI in tblCases, only that you display it in an unbound control on your form. If NPI is uniquely related to each provider then there is no reason to redundantly store it in tblCases, just retrieve it as needed based in ProviderName (or ProviderID, whatever is the primary key of the Providers table). If this form is a continuous or datasheet style form then there can be some slight problems with this method, but those can be easily overcome.