Tom,
I believe you have an issue that you might be storing the ID from the
Procedure table in the Main Table. Your combo box is bound to the first
column of the Row Source which is the ID (numeric) while the Procedure field
in the Main Table is text.
Try add a text box to your form and set the control source to [Procedure].
Do you see text or a number?
The way you seem to want your application to behave suggests you want to
store the Procedure field from the Procedure table in the Procedure field of
the Main Table. I don't think you even want to use an ID field in your
Procedure table.
If you need to store the Procedure and the CPT Code in the first table, make
these changes to your controls:
Combo box:in form [case log form]
Name: cboProcedure
Row Source:
SELECT Procedure, [CPT code]
FROM [Procedure]
ORDER BY Procedure;
Column Count:2
Bound Column:1
Column Widths: 1";0.5625"
Other text box that you might want to display something:
Name: txtCPTCode
Control Source:[CPT Code]
While in design view of your form, select the combo box (cboProcedure) and
find the After Update event property.
1) Click the builder button [...]
2) select "Code Builder" and click "OK"
3) change the code to read
Private Sub cboProcedure_AfterUpdate()
Me.txtCPTCode = Me.cboProcedure.Column(1)
End Sub
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in message
news
Duane,
here is the info you requested, copied verbaitim
First Table Name: Main Table
Field Names:
ID, auto number
Last Name, text
First Name,text
Date of Birth,date
Med Rec #, integer
Date of Surgery, Date
Procedure, text
CPT Code, integer
Diagnosis, text
ICD-9, decimal
Location of Procedure, text
Attending Surgeon, text
Fellow performed, yes/no
Date Billed, date
Comments, text
Second Table Name: Procedure
Field Names:
ID, autonumber
Procedure, text
CPT Code, integer
Combo box:in form [case log form]
Name: procedure
Control Source: procedure
Bound Column:1
Column Count:3
Row Source: {i originally built this using the wizard but I may have
changed it around}
SELECT Procedure.ID, Procedure.Procedure, Procedure.[CPT code]
FROM [Procedure]
ORDER BY Procedure.Procedure;
Column Widths: 0";1";0.5625"
Other text box that you might want to display something:
Name: CPT Code
Control Source:right now, blank
what do you want to display here?
I want it to show the unique CPT code from column 3 of the table
"procedure" that is associated with a procedure in column 2 of that
table.
The user chooses the procedure from the pull down list and the CPT fills
in -
just like in that Northwind example.
Thanks,
Tom
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in message
...
OK Duane. I found the example and that is EXACTLY what I want it to do.
you have warn me down and I wont try to store the secondary value, but
I
might come back to you when I am building my queries.
BUT I am still not getting it to work. I think I don't know enough
about
the "Me" thing. Is the syntax wrong. In the Northwind example it is
Me!
not
Me.
does that matter? I tried to search help for Me but I got nothing.
Please from scratch, as Danzel Washington said in the movie
Philadelphia "
like I am a three year old, take me through this one thing and I will
leave
you alone
Thanks for hanging with me
Tom
"Duane Hookom" wrote:
It normally gets installed in a samples directory depending on your
version
of Office. On my PC it is:
C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in message
...
I will try again
where do I find the Northwind example please?
"Duane Hookom" wrote:
I would rather you try:
You can use a combo box with a properties like:
Name: cboProcedure
Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes]
ORDER BY [Procedure];
Then add a text box to your form with the
Name: txtCPTCode
Control Source: [CPT Code]
If you really have a need to store the CPT Code in your operative
log
table,
you will need code in the After Update event of cboProcedure like
Me.txtCPTCode = Me.cboProcedure.Column(1)
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in message
...
Duane, where/how do I find that example?
and I appreciate that it is nothing really short of stupid to
store
both
"red" and "apple" when red=apple, but trust me, if I need to
search
for
the
procedure and I don't know the code, or the code and I don't know
the
procedure I need them both. I am trying to make this so people
who
are
less
acess literate than can use it.
May I mail you a copy of my test DB to
?
Thanks for hanging with me on this Duane
Tom
"Duane Hookom" wrote:
You might want to check the example in the Northwind database.
Check
the
Orders Subform and the ProductID after update. I don't like the
use
of
DLookup() but it should work.
I still think you would be better of NOT storing the duplicate
values.
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in
message
...
well duanne it is getting closer, but wierder
I hadn't seen the Vbasic box before so that explains why I
didn't
recognize
some of what you were saying. I wrote in the subroutine
exactly
as
you
wrote
me in a little test database and I got tow things now. I get
an
"enter
paramter value" dialogue box with the name of my
procedure_table.ID.
and
then when I get to the form and check the procedure I want it
enters
that
name in the other box, not the code number. nothing shows up
in
the
box
that
I chose the procedure from.
Tom
"Duane Hookom" wrote:
You can filter a query with the two tables rather than the
one
table
by
itself.
If you still want to store the value then create a combo box
to
selet
the
Procedure as I noted previously. Add a text box to your form
that
has
a
control source of your CPT code field. Name this text box
txtCPTCode.
Then
find the after update event property of your combo box
"cboProcedure"
and
set the property to
[Event Procedure]
Then click on the builder button on the right [...] and
change
your
code
to
look like:
Private Sub cboProcedure_AfterUpdate()
Me.txtCPTCode = Me.cboProcedure.Column(1)
End Sub
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in
message
...
I really think i want to store the data returned from
procedure
and
CPT
in
the main table so that when I ultimately search my main
table
I
can
filter
records by either CPT or the procedure name.
My problem is that I don't speak enough "access", or at
least
not
fluently
I do have a fair idea about what your describing but I am
not
sure
what
and
where to type it. What part of what your writing should be
copied
verbatum
and what is meant to represent something else?
Is there away to create this relationship through a wizard
or
the
event
builder?
Is there a way I can send you pieced of the DB or a
screenshot
or
something?
"Duane Hookom" wrote:
Is there a good reason to store both the Procedure and the
CPT
Code
in
the
Operative Log table? You can use a combo box with a
properties
like:
Name: cboProcedure
Row Source: SELECT [Procedure], [CPT Code] FROM
[tblProcCodes]
ORDER
BY
[Procedure];
Then add a text box to your form with
Control Source: =cboProcedure.Column(1)
This will display the [CPT Code] value.
If you really have a need to store the CPT Code in your
operative
log
table,
you will need code in the After Update event of
cboProcedure
like
Me.txtCPTCode = Me.cboProcedure.Column(1)
--
Duane Hookom
MS Access MVP
--
"DrTominRI" wrote in
message
...
OK, here we go, I hope this is what you mean
the main table is called "operative log" It is meant to
be
a
record
of
all
the operations done by the myself and the three surgeons
I
work
with.
the fields in this table include
patient name surgeon name medical record #
Procedure
done
CPT
Code
The table is associated with a form named op log form
on the form there are text boxes to fill in typed
information
the physicians name is linked to a table named
physicians
and
that
works
perfectly fine, you click on it and the four names pop
down
and
you
pick
one
and it successfully returns it to the table. Now here
is
the
question.
I have another table with two fields named procedure and
CPT
code
in there I have about 30 of the most common operations
and
a
code
number
associated with each operation name.