Table Column Linking?

[Guest]

As always, much thanks in advance!!!

I have a table that lists payments from clients. One column of that table
lists visits numbers and diagnosis that are connected to that clients. For
example, one client (#12345) might have two visits (1 and 2) and have two
different diagnosis for each visit (Hypertension and Diabetes). Remember,
this is a payments table, so most of the data comes from other tables
(clientID table, diagnosis lookup table, visits table). Here is the
question: the Diagnosis table has three columns (pkDiagNum, Diagnosis,
Diagnosis Code). I need to allow my client (my wife, so this is important
) to be able to choose a Diagnosis and have the appropriate Diagnosis Code
which is connected to that Diagnosis appear in another column in the table.
I suppose I could do this in the Visits table, but I still cannot figure out
how to do it. I have Diagnosis as a drop down (lookup) column, and this
works well. The next column needs to connect that Diagnosis to the connected
Diagnosis Code.

Would a query be appropriate here? I was thinking so, but I cannot figure
out what to do with that query. Do I have it query BOTH Diagnosis and
Diagnosis Code? Then have both Payment Table Columns have this query as
their lookup source? Does the query "fire" for each row of a table
separately-in that, if one selects "Hypertension" in the first field, would
the second field "know" that the first field was "Hypertension"?

Any help would be deeply appreciated.

Dave

 

[Guest]

A classic use for a Combo box. Create a 2 column combo the first column will
be the Diagnosis and the second column the Diagnosis Code. Now, here is
where multi column combos get confusing. You will want to set the bound
column of the combo to 1, which is the first column; but, later we will
discuss Column Number referencing so Column 1 is referenced by Column(0) and
column 2 is referenced as Column(1). So what is the diagnoisis code for Head
Ache?

Also, set the column width of the first column wide enough to show the
diagonisis and the column width of the second column to 0. That way, only
the diagnosis will show in the drop down.

You will want to make your rowsource for your combo a query that will return
all the diagnoisis and codes in whatever table you have them. Get rid of the
Look up fields. Most professionals never use them.

Now, you want to put them both in text boxes on your form? Use the After
Update event of the combo box:

Me.txtDiagnosis = Me.cboDiag.Column(0)
Me.txtDiagCode = Me.cboDiag.Column(1)

The names are made up. You will need to substitute your own.

 

[John Vinson]

Here is the
question: the Diagnosis table has three columns (pkDiagNum, Diagnosis,
Diagnosis Code). I need to allow my client (my wife, so this is important
) to be able to choose a Diagnosis and have the appropriate Diagnosis Code
which is connected to that Diagnosis appear in another column in the table.

If the Diagnosis Code is unique, stable, and reasonably short, I'd
really strongly suggest using IT as the Primary Key; despite Access'
persuasion, it is not necessary to create an Autonumber surrogate key
if you have a perfectly good natural key like this!

It should certainly *NOT* be necessary to store it redundantly in the
second table. Even if you keep the two fields, just store the
pkDiagNum and use a Query joining the two tables to pick up the
DiagnosisCode.

John W. Vinson[MVP]

 

[Guest]

Klatuu,

You nailed it, square on the head. Now, another question, based on the
last. Can I use the Me.txtDiagnosis = Me.cboDiag.Column(1) to update another
field on a subform. Here is what I have so far, One main form with client
info, with two subforms, subVisits and subAccount. On subVisits, I only want
to see Diagnosis, and I want to pass the Diagnosis to the SubAccount subform
with both Diagnosis AND Diagnosis Code and have both be visible on that
subAccount subform. I guess I need to figure out the syntax for something
like this:

from subVisits.cboDiagnosis AfterUpdate

Me.txtDiagnosis = Me.cboDiag.Column(0)
Me.txtDiagCode = Me.cboDiag.Column(1)
'***Next line to pass txtbox content to the other subform
(subAccounts.txtDiagnosis)
Forms!subAccount!txtDiagnosis = Me.txtDiagnosis
'***Next line passes the DiagCode
Forms!subAccount!txtDiagCode = Me.txtDiagCode

Close?

Again, deepest thanks.

Dave

 

[Guest]

An Update: I found this:
http://www.mvps.org/access/forms/frm0031.htm

with this info

Forms!Mainform!Subform1.Form!ControlName

Which would make my line (from the afterupdate of the combobox of the first
subform--Me.txtDiagnosis is on the Visits subform)

Forms!frmClients.Form!subAccount.Form.txtDiagnosis = Me.txtDiagnosis

does that mean that the txtDiagnosis on the subAccount subform will not have
a source in its properties?

Dave

???