Using combo boxes to link data in tables

[Rob]

Hi

I am setting up a database to track my medical expenses. This database also
indicates which services have been paid by my medical insurance and which
service I need to pay for myself.

My first problem is defining the service providers (GP's, Cardiologists,
hospitals etc,). I want to be able to categorize the service providers by
practice type. To do this I have set up a table for service providers and a
table for practice types.

I have created a form with the details of the service providers and on this
form I want to place a combo box on the form so that a.) I can select one of
the existing practice types from the practice types table, or b.) add a new
practice type to the practice types table directly from the service provider
form through the combo box.

I have created a table of service providers with all the relevant fields
e.g. "Name", "address" and so on. I have also created a field "Practice type"
(of type number - long integer). I have created a table for practice types
consisting of two fields ("Practice type ID" - Auto number - is primary key,
and "Practice description" - Text)

I have set up a relationship between these two tables using the "Service
provider : pratice type" and "Practice types: Practice type ID"

For some reason the form will not allow me to enter ANY data, nor display
any data existing in the service providers table. I think this has something
to do with the way I have set up the relationship. So:

a.) Have I set up the relationship correctly?
b.) Is the logic of having a combo box to call up or enter new practice
types correct?

 

[Carl Rapson]

Hi

I am setting up a database to track my medical expenses. This database
also
indicates which services have been paid by my medical insurance and which
service I need to pay for myself.

My first problem is defining the service providers (GP's, Cardiologists,
hospitals etc,). I want to be able to categorize the service providers by
practice type. To do this I have set up a table for service providers and
a
table for practice types.

I have created a form with the details of the service providers and on
this
form I want to place a combo box on the form so that a.) I can select one
of
the existing practice types from the practice types table, or b.) add a
new
practice type to the practice types table directly from the service
provider
form through the combo box.

I have created a table of service providers with all the relevant fields
e.g. "Name", "address" and so on. I have also created a field "Practice
type"
(of type number - long integer). I have created a table for practice types
consisting of two fields ("Practice type ID" - Auto number - is primary
key,
and "Practice description" - Text)

I have set up a relationship between these two tables using the "Service
provider : pratice type" and "Practice types: Practice type ID"

For some reason the form will not allow me to enter ANY data, nor display
any data existing in the service providers table. I think this has
something
to do with the way I have set up the relationship. So:

a.) Have I set up the relationship correctly?
b.) Is the logic of having a combo box to call up or enter new practice
types correct?

a) The relationship looks OK to me. However, the relationship really isn't
required to do what you are wanting. See below for more information.

b) Yes, that logic should be fine. At least, I use it with success. Again,
see below.

What is the RecordSource of your form? It should be just the Service
Providers table (or a query based on that table), not the joined tables. On
your form, add a combo box with a ControlSource of Practice type, a
RowSourceType of Table/Query, and a RowSource consisting of the information
from the Practice types table (I recommend using a query based on that
table, so you can sort the entries). In order to add new service providers
with this combo box, you will need to handle the Not In List event.

Carl Rapson

 

[Rob]

Thanks Carl, will try and let you know how it works out. Are you busy working
on something similar (tracking medical expenses - I mean)?
Thanks
Rob