A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

returning two linked values from a table



 
 
Thread Tools Display Modes
  #11  
Old March 14th, 2006, 04:18 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.

I want to create two boxes on my op log form so that when I click on
the
combo box named "procedure done" I see my list of operations and
when I
choose the one I want it is returned to that field on the form AND
the
CPT
code next to it in the table fills the CPT code box on the form and
in
the
main table.

I know that is not specifically what you asked, but the data base is
at
work
and I am answering this from home

Thanks for your help

"Duane Hookom" wrote:

Can you provide these properties
Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in message
...
I have a feeling if I knew what that meant I would also know how
to
do
what
I
am trying to do - I am smiling while I write that. But honestly
I
have
no
idea what that means. When/where do I write the line below? If
I
have
tow
separate data fields, [Diagnosis] and [ICD-9] Will it then return
both
the
text and the number to [diagnosis]on my form? the objective is to
choose
the
diagnosis from the combo box and when I click on "appendicitis"
the
ICD-9
box
automatically fills with the nuber next to it on the source
table.

"Duane Hookom" wrote:

You can display additional columns from your combo box with
syntax
like:

=cboDiagnosis.Column(x)

"x" is the zero-based column number from the Row Source property
of
the
combo box. A value of 2 will display the 3rd column.

This will not work with storing the other column in your form's
record
source. This would normally not be good practice anyway.

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a very limited database but in it I want to put in a
combo
box
that
will look up a piece of information from another table and
return
not
only
that bit of data but other parts of the record on that other
table.
Specifically, if I have a list of text (diagnoses) and
associated
with
each
diagnosis is a single numerical code (ICD-9), two fields in
the
table
"diagnoses". On my form I can get it to return the diagnosis
name
to
the
field on my form. But I want it to automatically fill in
another
box
on
the
form with the ICD-9 code number associated with that
diagnosis.















  #12  
Old March 14th, 2006, 05:44 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.

I want to create two boxes on my op log form so that when I click
on
the
combo box named "procedure done" I see my list of operations and
when I
choose the one I want it is returned to that field on the form
AND
the
CPT
code next to it in the table fills the CPT code box on the form
and
in
the
main table.

I know that is not specifically what you asked, but the data base
is
at
work
and I am answering this from home

Thanks for your help

"Duane Hookom" wrote:

Can you provide these properties
Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a feeling if I knew what that meant I would also know
how
to
do
what
I
am trying to do - I am smiling while I write that. But
honestly
I
have
no
idea what that means. When/where do I write the line below?
If
I
have
tow
separate data fields, [Diagnosis] and [ICD-9] Will it then
return
both
the
text and the number to [diagnosis]on my form? the objective is
to
choose
the
diagnosis from the combo box and when I click on
"appendicitis"
the
ICD-9
box
automatically fills with the nuber next to it on the source
table.

"Duane Hookom" wrote:

You can display additional columns from your combo box with
syntax
like:

=cboDiagnosis.Column(x)

"x" is the zero-based column number from the Row Source
property
of
the
combo box. A value of 2 will display the 3rd column.

This will not work with storing the other column in your
form's
record
source. This would normally not be good practice anyway.

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a very limited database but in it I want to put in a
combo
box
that
will look up a piece of information from another table and
return
not
only
that bit of data but other parts of the record on that
other
table.
Specifically, if I have a list of text (diagnoses) and
associated
with
each
diagnosis is a single numerical code (ICD-9), two fields in
the
table
"diagnoses". On my form I can get it to return the
diagnosis
name
to
the
field on my form. But I want it to automatically fill in
another
box
on
the
form with the ICD-9 code number associated with that
diagnosis.

















  #13  
Old March 14th, 2006, 11:20 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.

I want to create two boxes on my op log form so that when I click
on
the
combo box named "procedure done" I see my list of operations and
when I
choose the one I want it is returned to that field on the form
AND
the
CPT
code next to it in the table fills the CPT code box on the form
and
in
the
main table.

I know that is not specifically what you asked, but the data base
is
at
work
and I am answering this from home

Thanks for your help

"Duane Hookom" wrote:

Can you provide these properties
Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a feeling if I knew what that meant I would also know
how
to
do
what
I
am trying to do - I am smiling while I write that. But
honestly
I
have
no
idea what that means. When/where do I write the line below?
If
I
have
tow
separate data fields, [Diagnosis] and [ICD-9] Will it then
return
both
the
text and the number to [diagnosis]on my form? the objective is
to
choose
the
diagnosis from the combo box and when I click on
"appendicitis"
the
ICD-9
box
automatically fills with the nuber next to it on the source
table.

"Duane Hookom" wrote:

You can display additional columns from your combo box with
syntax
like:

=cboDiagnosis.Column(x)

"x" is the zero-based column number from the Row Source
property
of
the
combo box. A value of 2 will display the 3rd column.

This will not work with storing the other column in your
form's
record
source. This would normally not be good practice anyway.

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a very limited database but in it I want to put in a
combo
box
that
will look up a piece of information from another table and
return
not
only
that bit of data but other parts of the record on that
other
table.

  #14  
Old March 14th, 2006, 02:11 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.

I want to create two boxes on my op log form so that when I
click
on
the
combo box named "procedure done" I see my list of operations
and
when I
choose the one I want it is returned to that field on the
form
AND
the
CPT
code next to it in the table fills the CPT code box on the
form
and
in
the
main table.

I know that is not specifically what you asked, but the data
base
is
at
work
and I am answering this from home

Thanks for your help

"Duane Hookom" wrote:

Can you provide these properties
Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a feeling if I knew what that meant I would also know
how
to
do
what
I
am trying to do - I am smiling while I write that. But
honestly
I
have
no
idea what that means. When/where do I write the line
below?
If
I
have
tow
separate data fields, [Diagnosis] and [ICD-9] Will it then
return
both
the
text and the number to [diagnosis]on my form? the objective
is
to
choose
the
diagnosis from the combo box and when I click on
"appendicitis"
the
ICD-9
box
automatically fills with the nuber next to it on the source
table.

"Duane Hookom" wrote:

You can display additional columns from your combo box
with
syntax
like:

=cboDiagnosis.Column(x)

"x" is the zero-based column number from the Row Source
property
of
the
combo box. A value of 2 will display the 3rd column.

This will not work with storing the other column in your
form's
record
source. This would normally not be good practice anyway.

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a very limited database but in it I want to put in
a
combo
box
that
will look up a piece of information from another table
and
return
not
only
that bit of data but other parts of the record on that
other
table.



  #15  
Old March 15th, 2006, 09:50 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.

I want to create two boxes on my op log form so that when I
click
on
the
combo box named "procedure done" I see my list of operations
and
when I
choose the one I want it is returned to that field on the
form
AND
the
CPT
code next to it in the table fills the CPT code box on the
form
and
in
the
main table.

I know that is not specifically what you asked, but the data
base
is
at
work
and I am answering this from home

Thanks for your help

"Duane Hookom" wrote:

Can you provide these properties
Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a feeling if I knew what that meant I would also know
how
to
do
what
I
am trying to do - I am smiling while I write that. But
honestly
I
have
no
idea what that means. When/where do I write the line
below?
If
I
have
tow
separate data fields, [Diagnosis] and [ICD-9] Will it then
return
both
the
text and the number to [diagnosis]on my form? the objective
is
to
choose
the

  #16  
Old March 15th, 2006, 11:01 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

If you really still need help, please come back with this information

First Table Name:
Field Names:

Second Table Name:
Field Names:


Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?


--
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.

I want to create two boxes on my op log form so that when I
click
on
the
combo box named "procedure done" I see my list of
operations
and
when I
choose the one I want it is returned to that field on the
form
AND
the
CPT
code next to it in the table fills the CPT code box on the
form
and
in
the
main table.

I know that is not specifically what you asked, but the
data
base
is
at
work
and I am answering this from home

Thanks for your help

"Duane Hookom" wrote:

Can you provide these properties
Combo box:
Name:
Control Source:
Bound Column:
Column Count:
Row Source:
Column Widths:

Other text box that you might want to display something:
Name:
Control Source:
what do you want to display here?

--
Duane Hookom
MS Access MVP
--

"DrTominRI" wrote in
message
...
I have a feeling if I knew what that meant I would also
know
how
to
do
what
I
am trying to do - I am smiling while I write that. But
honestly
I
have
no
idea what that means. When/where do I write the line
below?
If
I
have
tow
separate data fields, [Diagnosis] and [ICD-9] Will it
then
return
both
the
text and the number to [diagnosis]on my form? the
objective
is
to
choose
the



  #17  
Old March 16th, 2006, 11:39 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.

  #18  
Old March 16th, 2006, 01:35 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

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.



  #19  
Old March 17th, 2006, 01:56 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default returning two linked values from a table

Duanne,

100% unmitigated success!

Thank you!

Tom


"Duane Hookom" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David General Discussion 1 January 6th, 2006 04:56 AM
Add New Field to DB Karen Database Design 7 October 19th, 2005 08:03 PM
Help again from Ken Snell (Query) Randy Running & Setting Up Queries 22 August 29th, 2005 08:15 PM
Formula to Sum values in word table Craig Mailmerge 1 August 29th, 2005 06:19 PM
Complicated Databse w/many relationships Søren Database Design 7 July 13th, 2004 05:41 AM


All times are GMT +1. The time now is 03:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.