Auto Fill help

I have a data base with the following fields

Customer Number
Last name
First name
address
phone number

When I enter a customer number into my form that has already been entered in
once i would like for the remaining information to auto fill so that i am not
entering the information over and over. I know that there is a way to do
this, however i am new to using access and have self taught so far. Thus i
will need detailed info on how to fix this.

You don't say into what form (and table) you are entering the customer
information, but if it is already in the Customer table you should not be
storing it again unless you need to preserve the information as it was at
the time. For instance, if the address changes you may want an old invoice
to show the old address. In most cases you would link to the information
rather than storing it again. One choice would be a subform to display the
Customer information. On another point, perhaps it would be simplest to
select the customer name from a list rather than typing the number. You
would still store the customer number, but you would see the name. Or you
could display the number if you want. More information needed.


"Shannon" wrote in message
...
I have a data base with the following fields

Customer Number
Last name
First name
address
phone number

When I enter a customer number into my form that has already been entered
in
once i would like for the remaining information to auto fill so that i am
not
entering the information over and over. I know that there is a way to do
this, however i am new to using access and have self taught so far. Thus i
will need detailed info on how to fix this.

I have a table for the Customer demographics and a table for the patient
appointments. the two tables are linked by the customer number. I have
created the forms so that the information can be entered into the system in
one location and the formating not get messed with by someone that does not
know what they are doing. The customer demographics are at the top of the
form and the customer appointments are in a subform. If the at the end of the
day i enter in a customer that did not show up for an appointment and i start
with their customer number, i would like for the demographics information to
automatically fill in. Or rather the previous entry be displayed so that i
can enter in the new missed appointment. i am trying to eliminate the "find a
record" step. If the customer has not been entered before then i will know
when the rest of the demographics are not filled in.

"BruceM" wrote:

You don't say into what form (and table) you are entering the customer
information, but if it is already in the Customer table you should not be
storing it again unless you need to preserve the information as it was at
the time. For instance, if the address changes you may want an old invoice
to show the old address. In most cases you would link to the information
rather than storing it again. One choice would be a subform to display the
Customer information. On another point, perhaps it would be simplest to
select the customer name from a list rather than typing the number. You
would still store the customer number, but you would see the name. Or you
could display the number if you want. More information needed.


"Shannon" wrote in message
...
I have a data base with the following fields

Customer Number
Last name
First name
address
phone number

When I enter a customer number into my form that has already been entered
in
once i would like for the remaining information to auto fill so that i am
not
entering the information over and over. I know that there is a way to do
this, however i am new to using access and have self taught so far. Thus i
will need detailed info on how to fix this.

If I understand correctly the Customer information is in one table, and the
Customer Appointments (I assume these are the same as Patient Appointments)
in a related table. There is a main form based on the Customer table, and a
subform based on the Appointments table. Is this correct?

I'm still having trouble following this. How is it that you can create an
appointment for a nonexistant customer? If the two tables are related, a
record in the parent table (Customer) is needed before a related record in
the child table (Appointments) can be created.


"Shannon" wrote in message
...
I have a table for the Customer demographics and a table for the patient
appointments. the two tables are linked by the customer number. I have
created the forms so that the information can be entered into the system
in
one location and the formating not get messed with by someone that does
not
know what they are doing. The customer demographics are at the top of the
form and the customer appointments are in a subform. If the at the end of
the
day i enter in a customer that did not show up for an appointment and i
start
with their customer number, i would like for the demographics information
to
automatically fill in. Or rather the previous entry be displayed so that i
can enter in the new missed appointment. i am trying to eliminate the
"find a
record" step. If the customer has not been entered before then i will know
when the rest of the demographics are not filled in.

"BruceM" wrote:

You don't say into what form (and table) you are entering the customer
information, but if it is already in the Customer table you should not be
storing it again unless you need to preserve the information as it was at
the time. For instance, if the address changes you may want an old
invoice
to show the old address. In most cases you would link to the information
rather than storing it again. One choice would be a subform to display
the
Customer information. On another point, perhaps it would be simplest to
select the customer name from a list rather than typing the number. You
would still store the customer number, but you would see the name. Or
you
could display the number if you want. More information needed.


"Shannon" wrote in message
...
I have a data base with the following fields

Customer Number
Last name
First name
address
phone number

When I enter a customer number into my form that has already been
entered
in
once i would like for the remaining information to auto fill so that i
am
not
entering the information over and over. I know that there is a way to
do
this, however i am new to using access and have self taught so far.
Thus i
will need detailed info on how to fix this.

I created this data base for a department in a medical building. They are
trying to track their patients/customers that either do not show up for their
appointments or that the department has to cancel. Along with that, the
department works with different companies and is trying to find out if there
is a trend in the canceled appointments. (Are the majority of canceled or no
show appointments comming from one company? Are there specific
patients/customers that constantly cancel or don't show up for their
appointment?) This department is getting repremanded for patient/customer
complaints when the patient/customer is the one that is creating the
problems. All of the patients/customers need to be seen by a certain date and
when there are a ton of patients/customers that cancel or don't show up that
is one appointment that the medical department could have used to see a
patient/customer that actually wanted to get seen.

With that... I had to create a blank database for the patient/customer
demographics to be entered in on one table and the appointments for the
patients on another table. The reason for this is for the different reports
that will have to be ran at a later date. But as an example: A report might
be created from a question of "Was there a specific company that seemed to
have a lot more cancelations or no shows."

The forms that I created were for the staff of the medical department to
enter in the patients/customers that canceled or didn't show for their
appointments at the end of the day. Kind of like a tracking device. So, lets
say that a patient/customer called and had to cancel their appointment for
that day, but they rescheduled for a later date. At the end of that day the
information for that canceled appointment would go into this database. But
lets say that that same patient/customer didn't show up for their second
appointment. At the end of the day that information would also go into this
database. But because that patient/customer was already entered into the
database their information has already been entered into the database. The
only change would be to add an aditional appointment to that same
patient/customer. I know that i will get a prompt if the patient/customer
number has already been entered into the database, but that prompt doesn't
occur until all of the information has already been entered. Thus the
information is getting entered in over and over.

I would like to be able to enter the patient/customer number and hit tab.
At that point if the patient/customer has already been entered into the
database the remainder of their information, including the sub form
(appointments) that go along with that patient/customer, will automatically
fill in. The information for the new appointment can be entered in below the
previous appointment information in doing this. If that patient/customer has
not been entered in before then the rest of the form will remain blank so
that it can be entered.

I do hope that i have provided enough information without confusing you.

The data base is blank until the patients/customers don't show up or cancel
their appointments. Only then does information get entered in. I don't want
people to waste their time typing in information that is already in the
system and then be told after the fact that it is entered that it already
exists.

What do you mean when you say you created a blank database? Do I understand
that the patient record exists only when a patient has cancelled, and that
there is no connection with any existing patient listing? That is, is this
database a listing only of patients who have cancelled appointments? If so,
that would mean you need to copy patient information from one table to
another, which is going to be a maintenance headache at the least if a
patient moves, gets another phone number, changes their name, etc.

It would be much better if you could link to an existing Patient table. If
you can't, the question seems to be how you can determine whether a patient
has cancelled before (i.e. has a record in the database). I have suggested
that you use a combo box to select the patient name (actually, I said
"select from a list"). I will outline the procedure. If you prefer to type
the number into a text box rather than selecting the name or number from a
list, you can adapt the procedure.

First, you need a main form based on the Customer table, and a subform based
on the related MissedAppointments table. The subform could have the Default
View set to Continuous if you want to see several appointment records at
once. Enter the patient information into the main form; then you can create
appointment records for the patient.

Use the combo box wizard to create a combo box that will go to a record on
your form. To use the wizard, open the toolbox. Be sure the magic wand
icon is highlighted. Click the combo box icon, and click onto the form.
Follow the prompts to create a combo box that will find a record on the
form. If you see that the patient number/name is not there you can create a
new patient record on the main form.

You can still type the number into the text box, or you can select it from
the combo box list. If you want to use a text box so that there is no
option of selecting from a list you can adapt the combo box code. Post back
if you need help with that.



"Shannon" wrote in message
...
I created this data base for a department in a medical building. They are
trying to track their patients/customers that either do not show up for
their
appointments or that the department has to cancel. Along with that, the
department works with different companies and is trying to find out if
there
is a trend in the canceled appointments. (Are the majority of canceled or
no
show appointments comming from one company? Are there specific
patients/customers that constantly cancel or don't show up for their
appointment?) This department is getting repremanded for patient/customer
complaints when the patient/customer is the one that is creating the
problems. All of the patients/customers need to be seen by a certain date
and
when there are a ton of patients/customers that cancel or don't show up
that
is one appointment that the medical department could have used to see a
patient/customer that actually wanted to get seen.

With that... I had to create a blank database for the patient/customer
demographics to be entered in on one table and the appointments for the
patients on another table. The reason for this is for the different
reports
that will have to be ran at a later date. But as an example: A report
might
be created from a question of "Was there a specific company that seemed to
have a lot more cancelations or no shows."

The forms that I created were for the staff of the medical department to
enter in the patients/customers that canceled or didn't show for their
appointments at the end of the day. Kind of like a tracking device. So,
lets
say that a patient/customer called and had to cancel their appointment for
that day, but they rescheduled for a later date. At the end of that day
the
information for that canceled appointment would go into this database. But
lets say that that same patient/customer didn't show up for their second
appointment. At the end of the day that information would also go into
this
database. But because that patient/customer was already entered into the
database their information has already been entered into the database. The
only change would be to add an aditional appointment to that same
patient/customer. I know that i will get a prompt if the patient/customer
number has already been entered into the database, but that prompt doesn't
occur until all of the information has already been entered. Thus the
information is getting entered in over and over.

I would like to be able to enter the patient/customer number and hit tab.
At that point if the patient/customer has already been entered into the
database the remainder of their information, including the sub form
(appointments) that go along with that patient/customer, will
automatically
fill in. The information for the new appointment can be entered in below
the
previous appointment information in doing this. If that patient/customer
has
not been entered in before then the rest of the form will remain blank so
that it can be entered.

I do hope that i have provided enough information without confusing you.

The data base is blank until the patients/customers don't show up or
cancel
their appointments. Only then does information get entered in. I don't
want
people to waste their time typing in information that is already in the
system and then be told after the fact that it is entered that it already
exists.

Yes, the patient record only exists when they have cancelled. Otherwise, all
of the information is maintained in a regular medical record, of witch we do
not maintain. The problem is that the patients hardly ever come through for
appointments on a regular basis. They are only seen twice within a 1-2 week
period once a year. Worst case senario, they get seen two or three times over
two or three years. We don't have to worry about name changes, address
changes, and so on. The patient number is the only constant. It is as unique
as one's Social Security Number. It will never be used on another person.
With that, having a combo box would be too dificult. We are talking about
thousands of people here. Thousands of people that we don't have to maintain
and don't see all that often. It is rare to see the same person two or threee
years in a row. And having to scroll through thousands of people is very time
consuming. I could see using a combo box if the patients were with us on a
constant basis, however, they are not.
if the person has had to be entered in for a cancelled appointment once, and
we go to put them in again we don't want to type everything out only to find
they are in already. After typing in the Patient number we want the rest of
the form to auto update/fill. I know this might not make sence, but it is
working really well. and would work even better if we werent typing in the
same information over and over for someone that is already in the database.

"BruceM" wrote:

What do you mean when you say you created a blank database? Do I understand
that the patient record exists only when a patient has cancelled, and that
there is no connection with any existing patient listing? That is, is this
database a listing only of patients who have cancelled appointments? If so,
that would mean you need to copy patient information from one table to
another, which is going to be a maintenance headache at the least if a
patient moves, gets another phone number, changes their name, etc.

It would be much better if you could link to an existing Patient table. If
you can't, the question seems to be how you can determine whether a patient
has cancelled before (i.e. has a record in the database). I have suggested
that you use a combo box to select the patient name (actually, I said
"select from a list"). I will outline the procedure. If you prefer to type
the number into a text box rather than selecting the name or number from a
list, you can adapt the procedure.

First, you need a main form based on the Customer table, and a subform based
on the related MissedAppointments table. The subform could have the Default
View set to Continuous if you want to see several appointment records at
once. Enter the patient information into the main form; then you can create
appointment records for the patient.

Use the combo box wizard to create a combo box that will go to a record on
your form. To use the wizard, open the toolbox. Be sure the magic wand
icon is highlighted. Click the combo box icon, and click onto the form.
Follow the prompts to create a combo box that will find a record on the
form. If you see that the patient number/name is not there you can create a
new patient record on the main form.

You can still type the number into the text box, or you can select it from
the combo box list. If you want to use a text box so that there is no
option of selecting from a list you can adapt the combo box code. Post back
if you need help with that.



"Shannon" wrote in message
...
I created this data base for a department in a medical building. They are
trying to track their patients/customers that either do not show up for
their
appointments or that the department has to cancel. Along with that, the
department works with different companies and is trying to find out if
there
is a trend in the canceled appointments. (Are the majority of canceled or
no
show appointments comming from one company? Are there specific
patients/customers that constantly cancel or don't show up for their
appointment?) This department is getting repremanded for patient/customer
complaints when the patient/customer is the one that is creating the
problems. All of the patients/customers need to be seen by a certain date
and
when there are a ton of patients/customers that cancel or don't show up
that
is one appointment that the medical department could have used to see a
patient/customer that actually wanted to get seen.

With that... I had to create a blank database for the patient/customer
demographics to be entered in on one table and the appointments for the
patients on another table. The reason for this is for the different
reports
that will have to be ran at a later date. But as an example: A report
might
be created from a question of "Was there a specific company that seemed to
have a lot more cancelations or no shows."

The forms that I created were for the staff of the medical department to
enter in the patients/customers that canceled or didn't show for their
appointments at the end of the day. Kind of like a tracking device. So,
lets
say that a patient/customer called and had to cancel their appointment for
that day, but they rescheduled for a later date. At the end of that day
the
information for that canceled appointment would go into this database. But
lets say that that same patient/customer didn't show up for their second
appointment. At the end of the day that information would also go into
this
database. But because that patient/customer was already entered into the
database their information has already been entered into the database. The
only change would be to add an aditional appointment to that same
patient/customer. I know that i will get a prompt if the patient/customer
number has already been entered into the database, but that prompt doesn't
occur until all of the information has already been entered. Thus the
information is getting entered in over and over.

I would like to be able to enter the patient/customer number and hit tab.
At that point if the patient/customer has already been entered into the
database the remainder of their information, including the sub form
(appointments) that go along with that patient/customer, will
automatically
fill in. The information for the new appointment can be entered in below
the
previous appointment information in doing this. If that patient/customer
has
not been entered in before then the rest of the form will remain blank so
that it can be entered.

I do hope that i have provided enough information without confusing you.

The data base is blank until the patients/customers don't show up or
cancel
their appointments. Only then does information get entered in. I don't
want
people to waste their time typing in information that is already in the
system and then be told after the fact that it is entered that it already
exists.

Again, you can type the number into the combo box just as you would a text
box. You do not have to scroll through thousands of records. Also, a combo
box gives you the option of adding the patient name (again, you can type it
in).
Do you have a main form and a subform as I suggested? If not, what do you
have? If so, have you tried adding a combo box using the wizard as I
suggested? If so, did it work?
If you want to use a text box, and if you have a main form and a subform as
I suggested, you can create an unbound text box on the form (in the header,
maybe) with something like the following as its After Update event:

Private Sub txtPatientID_AfterUpdate()

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[PatientID] = " & Me.txtPatientID

If rs.NoMatch Then
Me.txtPatientID = ""
MsgBox "No Patient with this ID"
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

"Shannon" wrote in message
news
Yes, the patient record only exists when they have cancelled. Otherwise,
all
of the information is maintained in a regular medical record, of witch we
do
not maintain. The problem is that the patients hardly ever come through
for
appointments on a regular basis. They are only seen twice within a 1-2
week
period once a year. Worst case senario, they get seen two or three times
over
two or three years. We don't have to worry about name changes, address
changes, and so on. The patient number is the only constant. It is as
unique
as one's Social Security Number. It will never be used on another person.
With that, having a combo box would be too dificult. We are talking about
thousands of people here. Thousands of people that we don't have to
maintain
and don't see all that often. It is rare to see the same person two or
threee
years in a row. And having to scroll through thousands of people is very
time
consuming. I could see using a combo box if the patients were with us on a
constant basis, however, they are not.
if the person has had to be entered in for a cancelled appointment once,
and
we go to put them in again we don't want to type everything out only to
find
they are in already. After typing in the Patient number we want the rest
of
the form to auto update/fill. I know this might not make sence, but it is
working really well. and would work even better if we werent typing in the
same information over and over for someone that is already in the
database.

"BruceM" wrote:

What do you mean when you say you created a blank database? Do I
understand
that the patient record exists only when a patient has cancelled, and
that
there is no connection with any existing patient listing? That is, is
this
database a listing only of patients who have cancelled appointments? If
so,
that would mean you need to copy patient information from one table to
another, which is going to be a maintenance headache at the least if a
patient moves, gets another phone number, changes their name, etc.

It would be much better if you could link to an existing Patient table.
If
you can't, the question seems to be how you can determine whether a
patient
has cancelled before (i.e. has a record in the database). I have
suggested
that you use a combo box to select the patient name (actually, I said
"select from a list"). I will outline the procedure. If you prefer to
type
the number into a text box rather than selecting the name or number from
a
list, you can adapt the procedure.

First, you need a main form based on the Customer table, and a subform
based
on the related MissedAppointments table. The subform could have the
Default
View set to Continuous if you want to see several appointment records at
once. Enter the patient information into the main form; then you can
create
appointment records for the patient.

Use the combo box wizard to create a combo box that will go to a record
on
your form. To use the wizard, open the toolbox. Be sure the magic wand
icon is highlighted. Click the combo box icon, and click onto the form.
Follow the prompts to create a combo box that will find a record on the
form. If you see that the patient number/name is not there you can
create a
new patient record on the main form.

You can still type the number into the text box, or you can select it
from
the combo box list. If you want to use a text box so that there is no
option of selecting from a list you can adapt the combo box code. Post
back
if you need help with that.



"Shannon" wrote in message
...
I created this data base for a department in a medical building. They
are
trying to track their patients/customers that either do not show up for
their
appointments or that the department has to cancel. Along with that, the
department works with different companies and is trying to find out if
there
is a trend in the canceled appointments. (Are the majority of canceled
or
no
show appointments comming from one company? Are there specific
patients/customers that constantly cancel or don't show up for their
appointment?) This department is getting repremanded for
patient/customer
complaints when the patient/customer is the one that is creating the
problems. All of the patients/customers need to be seen by a certain
date
and
when there are a ton of patients/customers that cancel or don't show up
that
is one appointment that the medical department could have used to see a
patient/customer that actually wanted to get seen.

With that... I had to create a blank database for the patient/customer
demographics to be entered in on one table and the appointments for the
patients on another table. The reason for this is for the different
reports
that will have to be ran at a later date. But as an example: A report
might
be created from a question of "Was there a specific company that seemed
to
have a lot more cancelations or no shows."

The forms that I created were for the staff of the medical department
to
enter in the patients/customers that canceled or didn't show for their
appointments at the end of the day. Kind of like a tracking device. So,
lets
say that a patient/customer called and had to cancel their appointment
for
that day, but they rescheduled for a later date. At the end of that day
the
information for that canceled appointment would go into this database.
But
lets say that that same patient/customer didn't show up for their
second
appointment. At the end of the day that information would also go into
this
database. But because that patient/customer was already entered into
the
database their information has already been entered into the database.
The
only change would be to add an aditional appointment to that same
patient/customer. I know that i will get a prompt if the
patient/customer
number has already been entered into the database, but that prompt
doesn't
occur until all of the information has already been entered. Thus the
information is getting entered in over and over.

I would like to be able to enter the patient/customer number and hit
tab.
At that point if the patient/customer has already been entered into the
database the remainder of their information, including the sub form
(appointments) that go along with that patient/customer, will
automatically
fill in. The information for the new appointment can be entered in
below
the
previous appointment information in doing this. If that
patient/customer
has
not been entered in before then the rest of the form will remain blank
so
that it can be entered.

I do hope that i have provided enough information without confusing
you.

The data base is blank until the patients/customers don't show up or
cancel
their appointments. Only then does information get entered in. I don't
want
people to waste their time typing in information that is already in the
system and then be told after the fact that it is entered that it
already
exists.

I get it now.... I think that that will work. I guess i wasn't understanding.
you are awsome!

"BruceM" wrote:

Again, you can type the number into the combo box just as you would a text
box. You do not have to scroll through thousands of records. Also, a combo
box gives you the option of adding the patient name (again, you can type it
in).
Do you have a main form and a subform as I suggested? If not, what do you
have? If so, have you tried adding a combo box using the wizard as I
suggested? If so, did it work?
If you want to use a text box, and if you have a main form and a subform as
I suggested, you can create an unbound text box on the form (in the header,
maybe) with something like the following as its After Update event:

Private Sub txtPatientID_AfterUpdate()

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[PatientID] = " & Me.txtPatientID

If rs.NoMatch Then
Me.txtPatientID = ""
MsgBox "No Patient with this ID"
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

"Shannon" wrote in message
news
Yes, the patient record only exists when they have cancelled. Otherwise,
all
of the information is maintained in a regular medical record, of witch we
do
not maintain. The problem is that the patients hardly ever come through
for
appointments on a regular basis. They are only seen twice within a 1-2
week
period once a year. Worst case senario, they get seen two or three times
over
two or three years. We don't have to worry about name changes, address
changes, and so on. The patient number is the only constant. It is as
unique
as one's Social Security Number. It will never be used on another person.
With that, having a combo box would be too dificult. We are talking about
thousands of people here. Thousands of people that we don't have to
maintain
and don't see all that often. It is rare to see the same person two or
threee
years in a row. And having to scroll through thousands of people is very
time
consuming. I could see using a combo box if the patients were with us on a
constant basis, however, they are not.
if the person has had to be entered in for a cancelled appointment once,
and
we go to put them in again we don't want to type everything out only to
find
they are in already. After typing in the Patient number we want the rest
of
the form to auto update/fill. I know this might not make sence, but it is
working really well. and would work even better if we werent typing in the
same information over and over for someone that is already in the
database.

"BruceM" wrote:

What do you mean when you say you created a blank database? Do I
understand
that the patient record exists only when a patient has cancelled, and
that
there is no connection with any existing patient listing? That is, is
this
database a listing only of patients who have cancelled appointments? If
so,
that would mean you need to copy patient information from one table to
another, which is going to be a maintenance headache at the least if a
patient moves, gets another phone number, changes their name, etc.

It would be much better if you could link to an existing Patient table.
If
you can't, the question seems to be how you can determine whether a
patient
has cancelled before (i.e. has a record in the database). I have
suggested
that you use a combo box to select the patient name (actually, I said
"select from a list"). I will outline the procedure. If you prefer to
type
the number into a text box rather than selecting the name or number from
a
list, you can adapt the procedure.

First, you need a main form based on the Customer table, and a subform
based
on the related MissedAppointments table. The subform could have the
Default
View set to Continuous if you want to see several appointment records at
once. Enter the patient information into the main form; then you can
create
appointment records for the patient.

Use the combo box wizard to create a combo box that will go to a record
on
your form. To use the wizard, open the toolbox. Be sure the magic wand
icon is highlighted. Click the combo box icon, and click onto the form.
Follow the prompts to create a combo box that will find a record on the
form. If you see that the patient number/name is not there you can
create a
new patient record on the main form.

You can still type the number into the text box, or you can select it
from
the combo box list. If you want to use a text box so that there is no
option of selecting from a list you can adapt the combo box code. Post
back
if you need help with that.



"Shannon" wrote in message
...
I created this data base for a department in a medical building. They
are
trying to track their patients/customers that either do not show up for
their
appointments or that the department has to cancel. Along with that, the
department works with different companies and is trying to find out if
there
is a trend in the canceled appointments. (Are the majority of canceled
or
no
show appointments comming from one company? Are there specific
patients/customers that constantly cancel or don't show up for their
appointment?) This department is getting repremanded for
patient/customer
complaints when the patient/customer is the one that is creating the
problems. All of the patients/customers need to be seen by a certain
date
and
when there are a ton of patients/customers that cancel or don't show up
that
is one appointment that the medical department could have used to see a
patient/customer that actually wanted to get seen.

With that... I had to create a blank database for the patient/customer
demographics to be entered in on one table and the appointments for the
patients on another table. The reason for this is for the different
reports
that will have to be ran at a later date. But as an example: A report
might
be created from a question of "Was there a specific company that seemed
to
have a lot more cancelations or no shows."

The forms that I created were for the staff of the medical department
to
enter in the patients/customers that canceled or didn't show for their
appointments at the end of the day. Kind of like a tracking device. So,
lets
say that a patient/customer called and had to cancel their appointment
for
that day, but they rescheduled for a later date. At the end of that day
the
information for that canceled appointment would go into this database.
But
lets say that that same patient/customer didn't show up for their
second
appointment. At the end of the day that information would also go into
this
database. But because that patient/customer was already entered into
the
database their information has already been entered into the database.
The
only change would be to add an aditional appointment to that same
patient/customer. I know that i will get a prompt if the
patient/customer
number has already been entered into the database, but that prompt
doesn't
occur until all of the information has already been entered. Thus the
information is getting entered in over and over.

I would like to be able to enter the patient/customer number and hit
tab.
At that point if the patient/customer has already been entered into the
database the remainder of their information, including the sub form
(appointments) that go along with that patient/customer, will
automatically
fill in. The information for the new appointment can be entered in
below
the
previous appointment information in doing this. If that
patient/customer
has
not been entered in before then the rest of the form will remain blank
so
that it can be entered.

I do hope that i have provided enough information without confusing
you.

The data base is blank until the patients/customers don't show up or
cancel
their appointments. Only then does information get entered in. I don't
want
people to waste their time typing in information that is already in the
system and then be told after the fact that it is entered that it
already
exists.

It works, and it doesn't. If the patient is already in the system then it
does pull up the persons information, but if the person is not in the system
then it adds someone elses information. I just did a test with all 1's, 2's
and 3's for the patient number and A's, B's, and C's for their information
and when i entered in a patient number of all 4's it pulled the informaiton
entered in for the test person of all 2's. That isn't good. if you enter in a
new person and it pulls another persons information unless you know the
person and their information then you will not know that the information
belongs to someone else.
So, I tried the code that you input in. and then i had errors.
"BruceM" wrote:

Again, you can type the number into the combo box just as you would a text
box. You do not have to scroll through thousands of records. Also, a combo
box gives you the option of adding the patient name (again, you can type it
in).
Do you have a main form and a subform as I suggested? If not, what do you
have? If so, have you tried adding a combo box using the wizard as I
suggested? If so, did it work?
If you want to use a text box, and if you have a main form and a subform as
I suggested, you can create an unbound text box on the form (in the header,
maybe) with something like the following as its After Update event:

Private Sub txtPatientID_AfterUpdate()

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[PatientID] = " & Me.txtPatientID

If rs.NoMatch Then
Me.txtPatientID = ""
MsgBox "No Patient with this ID"
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

"Shannon" wrote in message
news
Yes, the patient record only exists when they have cancelled. Otherwise,
all
of the information is maintained in a regular medical record, of witch we
do
not maintain. The problem is that the patients hardly ever come through
for
appointments on a regular basis. They are only seen twice within a 1-2
week
period once a year. Worst case senario, they get seen two or three times
over
two or three years. We don't have to worry about name changes, address
changes, and so on. The patient number is the only constant. It is as
unique
as one's Social Security Number. It will never be used on another person.
With that, having a combo box would be too dificult. We are talking about
thousands of people here. Thousands of people that we don't have to
maintain
and don't see all that often. It is rare to see the same person two or
threee
years in a row. And having to scroll through thousands of people is very
time
consuming. I could see using a combo box if the patients were with us on a
constant basis, however, they are not.
if the person has had to be entered in for a cancelled appointment once,
and
we go to put them in again we don't want to type everything out only to
find
they are in already. After typing in the Patient number we want the rest
of
the form to auto update/fill. I know this might not make sence, but it is
working really well. and would work even better if we werent typing in the
same information over and over for someone that is already in the
database.

"BruceM" wrote:

What do you mean when you say you created a blank database? Do I
understand
that the patient record exists only when a patient has cancelled, and
that
there is no connection with any existing patient listing? That is, is
this
database a listing only of patients who have cancelled appointments? If
so,
that would mean you need to copy patient information from one table to
another, which is going to be a maintenance headache at the least if a
patient moves, gets another phone number, changes their name, etc.

It would be much better if you could link to an existing Patient table.
If
you can't, the question seems to be how you can determine whether a
patient
has cancelled before (i.e. has a record in the database). I have
suggested
that you use a combo box to select the patient name (actually, I said
"select from a list"). I will outline the procedure. If you prefer to
type
the number into a text box rather than selecting the name or number from
a
list, you can adapt the procedure.

First, you need a main form based on the Customer table, and a subform
based
on the related MissedAppointments table. The subform could have the
Default
View set to Continuous if you want to see several appointment records at
once. Enter the patient information into the main form; then you can
create
appointment records for the patient.

Use the combo box wizard to create a combo box that will go to a record
on
your form. To use the wizard, open the toolbox. Be sure the magic wand
icon is highlighted. Click the combo box icon, and click onto the form.
Follow the prompts to create a combo box that will find a record on the
form. If you see that the patient number/name is not there you can
create a
new patient record on the main form.

You can still type the number into the text box, or you can select it
from
the combo box list. If you want to use a text box so that there is no
option of selecting from a list you can adapt the combo box code. Post
back
if you need help with that.



"Shannon" wrote in message
...
I created this data base for a department in a medical building. They
are
trying to track their patients/customers that either do not show up for
their
appointments or that the department has to cancel. Along with that, the
department works with different companies and is trying to find out if
there
is a trend in the canceled appointments. (Are the majority of canceled
or
no
show appointments comming from one company? Are there specific
patients/customers that constantly cancel or don't show up for their
appointment?) This department is getting repremanded for
patient/customer
complaints when the patient/customer is the one that is creating the
problems. All of the patients/customers need to be seen by a certain
date
and
when there are a ton of patients/customers that cancel or don't show up
that
is one appointment that the medical department could have used to see a
patient/customer that actually wanted to get seen.

With that... I had to create a blank database for the patient/customer
demographics to be entered in on one table and the appointments for the
patients on another table. The reason for this is for the different
reports
that will have to be ran at a later date. But as an example: A report
might
be created from a question of "Was there a specific company that seemed
to
have a lot more cancelations or no shows."

The forms that I created were for the staff of the medical department
to
enter in the patients/customers that canceled or didn't show for their
appointments at the end of the day. Kind of like a tracking device. So,
lets
say that a patient/customer called and had to cancel their appointment
for
that day, but they rescheduled for a later date. At the end of that day
the
information for that canceled appointment would go into this database.
But
lets say that that same patient/customer didn't show up for their
second
appointment. At the end of the day that information would also go into
this
database. But because that patient/customer was already entered into
the
database their information has already been entered into the database.
The
only change would be to add an aditional appointment to that same
patient/customer. I know that i will get a prompt if the
patient/customer
number has already been entered into the database, but that prompt
doesn't
occur until all of the information has already been entered. Thus the
information is getting entered in over and over.

I would like to be able to enter the patient/customer number and hit
tab.
At that point if the patient/customer has already been entered into the
database the remainder of their information, including the sub form
(appointments) that go along with that patient/customer, will
automatically
fill in. The information for the new appointment can be entered in
below
the
previous appointment information in doing this. If that
patient/customer
has
not been entered in before then the rest of the form will remain blank
so
that it can be entered.

I do hope that i have provided enough information without confusing
you.

The data base is blank until the patients/customers don't show up or
cancel
their appointments. Only then does information get entered in. I don't
want
people to waste their time typing in information that is already in the
system and then be told after the fact that it is entered that it
already
exists.