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 said:
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
--
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
:
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
--
I will try again
where do I find the Northwind example please?
:
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
--
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
(e-mail address removed)?
Thanks for hanging with me on this Duane
Tom
:
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
--
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
:
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
--
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