Auto Fill help

[Shannon]

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.

 

[BruceM]

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]

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]

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]

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.

 

[BruceM]

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]

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]

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]

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

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

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.

 

[Shannon]

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.

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

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]

Post the code you used, and describe the errors. Did you use the text box
code? If so, is it in an unbound text box? A text box or combo box for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is this the
form/subform I described? If so, in which is the text box or combo box
located, the form or the subform?

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.

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

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.

:

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.



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.

 

[Shannon]

I used an unbound combo box on the form for the patient number. The form
contains the patient information. The sub-form contains the appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it will
pull their information. if the person is not in the database, then it adds in
someone elses information. It does not leave the rest of the fields blank
when the person is not in the database already.

Post the code you used, and describe the errors. Did you use the text box
code? If so, is it in an unbound text box? A text box or combo box for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is this the
form/subform I described? If so, in which is the text box or combo box
located, the form or the subform?

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.

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

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.

:

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.



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.

 

[BruceM]

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think it is
needed since you are testing NoMatch, but to be honest I am still a bit
unclear about how to use EOF (end of file) and BOF (beginning of file)
except when attempting to navigate to the next of previous record when that
is not possible. For instance, if you are on the last record and attempt to
go to the next record, EOF will be true. In the context of the code created
by the wizard I am not quite sure what it does. I might have guessed it was
to allow for no record being found, but if so you have seen it does not seem
to work as you would like in that context. I have tested using my method,
which seems to work, but I expect the EOF version of the code will work too.

If you are at a record and try to move to a non-existant record, it will not
work, and will leave you at the current record, as I recall. It is also
possible to ask if the user wants to create a new record, or to go to the
first record, or whatever, but we can take that up as needed. First be sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are legal
implications to allowing a person's SSN to be obtained by unauthorized
persons. Access, especially with no security applied, is not a secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number. The form
contains the patient information. The sub-form contains the appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it will
pull their information. if the person is not in the database, then it adds
in
someone elses information. It does not leave the rest of the fields blank
when the person is not in the database already.

Post the code you used, and describe the errors. Did you use the text
box
code? If so, is it in an unbound text box? A text box or combo box for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is this the
form/subform I described? If so, in which is the text box or combo box
located, the form or the subform?

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

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

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.

:

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.



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.

 

[Shannon]

Yes i know the legality with the SSN. We are actually Military and that is
how we track our medical patients. They are aware of this. we have a secure
network and only authorized users have access to the system via a password.

I did use the code and when i put in a patient that did not exist it told me
that the ssn had no match and would not allow me to enter the new patient. It
wants me to enter in a number that is already in the system. If need be, i
should be able to enter in a new patient that is not in the system.

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think it is
needed since you are testing NoMatch, but to be honest I am still a bit
unclear about how to use EOF (end of file) and BOF (beginning of file)
except when attempting to navigate to the next of previous record when that
is not possible. For instance, if you are on the last record and attempt to
go to the next record, EOF will be true. In the context of the code created
by the wizard I am not quite sure what it does. I might have guessed it was
to allow for no record being found, but if so you have seen it does not seem
to work as you would like in that context. I have tested using my method,
which seems to work, but I expect the EOF version of the code will work too.

If you are at a record and try to move to a non-existant record, it will not
work, and will leave you at the current record, as I recall. It is also
possible to ask if the user wants to create a new record, or to go to the
first record, or whatever, but we can take that up as needed. First be sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are legal
implications to allowing a person's SSN to be obtained by unauthorized
persons. Access, especially with no security applied, is not a secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number. The form
contains the patient information. The sub-form contains the appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it will
pull their information. if the person is not in the database, then it adds
in
someone elses information. It does not leave the rest of the fields blank
when the person is not in the database already.

Post the code you used, and describe the errors. Did you use the text
box
code? If so, is it in an unbound text box? A text box or combo box for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is this the
form/subform I described? If so, in which is the text box or combo box
located, the form or the subform?

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

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

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.

:

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.



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

 

[BruceM]

You may be surprised how many people leave SSNs floating around for snoops
to find. Just thought I'd mention it.

If you used the wizard to create the combo box I think the Limit to List
property (on the Data tab) is set to Yes. Try setting it to No. If that
works, here is some code modification that should allow the user to start a
new patient record.

Private Sub Combo34_AfterUpdate()

Dim rs As Object
Dim strMsg as String, strTitle as String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

If rs.NoMatch Then
Me.Combo34 = ""
If MsgBox (strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

Yes i know the legality with the SSN. We are actually Military and that is
how we track our medical patients. They are aware of this. we have a
secure
network and only authorized users have access to the system via a
password.

I did use the code and when i put in a patient that did not exist it told
me
that the ssn had no match and would not allow me to enter the new patient.
It
wants me to enter in a number that is already in the system. If need be, i
should be able to enter in a new patient that is not in the system.

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think it is
needed since you are testing NoMatch, but to be honest I am still a bit
unclear about how to use EOF (end of file) and BOF (beginning of file)
except when attempting to navigate to the next of previous record when
that
is not possible. For instance, if you are on the last record and attempt
to
go to the next record, EOF will be true. In the context of the code
created
by the wizard I am not quite sure what it does. I might have guessed it
was
to allow for no record being found, but if so you have seen it does not
seem
to work as you would like in that context. I have tested using my
method,
which seems to work, but I expect the EOF version of the code will work
too.

If you are at a record and try to move to a non-existant record, it will
not
work, and will leave you at the current record, as I recall. It is also
possible to ask if the user wants to create a new record, or to go to the
first record, or whatever, but we can take that up as needed. First be
sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are legal
implications to allowing a person's SSN to be obtained by unauthorized
persons. Access, especially with no security applied, is not a secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number. The form
contains the patient information. The sub-form contains the appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it
will
pull their information. if the person is not in the database, then it
adds
in
someone elses information. It does not leave the rest of the fields
blank
when the person is not in the database already.

:

Post the code you used, and describe the errors. Did you use the text
box
code? If so, is it in an unbound text box? A text box or combo box
for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is this
the
form/subform I described? If so, in which is the text box or combo
box
located, the form or the subform?

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

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

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.

:

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.



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

 

[Shannon]

Ok, the limit to list was set to "NO". I didn't know if i was supposed to
delete the previous code for this combo box so i tried adding this under the
one that we used previously and i tried using this one instead of the last
and i got error messages. I was told that i had an ambiguous name. then it
brought up a message box that allowed me to enter in the ssn. after i did
that, i also entered in patient informtion for the ssn. when i got ready to
save the form it said that i did not have a number in the ssn box and that it
was required, when i did have a number in that box. I hope that makes sense.

You may be surprised how many people leave SSNs floating around for snoops
to find. Just thought I'd mention it.

If you used the wizard to create the combo box I think the Limit to List
property (on the Data tab) is set to Yes. Try setting it to No. If that
works, here is some code modification that should allow the user to start a
new patient record.

Private Sub Combo34_AfterUpdate()

Dim rs As Object
Dim strMsg as String, strTitle as String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

If rs.NoMatch Then
Me.Combo34 = ""
If MsgBox (strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

Yes i know the legality with the SSN. We are actually Military and that is
how we track our medical patients. They are aware of this. we have a
secure
network and only authorized users have access to the system via a
password.

I did use the code and when i put in a patient that did not exist it told
me
that the ssn had no match and would not allow me to enter the new patient.
It
wants me to enter in a number that is already in the system. If need be, i
should be able to enter in a new patient that is not in the system.

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think it is
needed since you are testing NoMatch, but to be honest I am still a bit
unclear about how to use EOF (end of file) and BOF (beginning of file)
except when attempting to navigate to the next of previous record when
that
is not possible. For instance, if you are on the last record and attempt
to
go to the next record, EOF will be true. In the context of the code
created
by the wizard I am not quite sure what it does. I might have guessed it
was
to allow for no record being found, but if so you have seen it does not
seem
to work as you would like in that context. I have tested using my
method,
which seems to work, but I expect the EOF version of the code will work
too.

If you are at a record and try to move to a non-existant record, it will
not
work, and will leave you at the current record, as I recall. It is also
possible to ask if the user wants to create a new record, or to go to the
first record, or whatever, but we can take that up as needed. First be
sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are legal
implications to allowing a person's SSN to be obtained by unauthorized
persons. Access, especially with no security applied, is not a secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number. The form
contains the patient information. The sub-form contains the appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it
will
pull their information. if the person is not in the database, then it
adds
in
someone elses information. It does not leave the rest of the fields
blank
when the person is not in the database already.

:

Post the code you used, and describe the errors. Did you use the text
box
code? If so, is it in an unbound text box? A text box or combo box
for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is this
the
form/subform I described? If so, in which is the text box or combo
box
located, the form or the subform?

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

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

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.

:

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

 

[BruceM]

You need to answer this question: Do you have a form/subform based on
related tables as I described?

The code I posted starts with Private Sub... and ends with End Sub. That
needs to replace all of what was in there before. Please post the code from
the first instance of the line Private Sub Combo34_AfterUpdate() through End
Sub. If there is another End Sub that is not preceded by a Private Sub
line, post everything through that line. Also, go to the Row Source. If
there is code that includes the word SELECT in that line, post it. If it is
a named query, mention that.

Did it tell you what the ambiguous name was?

A message box does not allow you to enter anything. You can only select an
option such as Yes, No, OK, Cancel. If it looked like a message box but
provided a place to enter SSN it could have been a parameter box from a
query. Is the form based on a query? It could have been an input box, too,
but if so I don't know where it came from.

Ok, the limit to list was set to "NO". I didn't know if i was supposed to
delete the previous code for this combo box so i tried adding this under
the
one that we used previously and i tried using this one instead of the last
and i got error messages. I was told that i had an ambiguous name. then
it
brought up a message box that allowed me to enter in the ssn. after i did
that, i also entered in patient informtion for the ssn. when i got ready
to
save the form it said that i did not have a number in the ssn box and that
it
was required, when i did have a number in that box. I hope that makes
sense.

You may be surprised how many people leave SSNs floating around for
snoops
to find. Just thought I'd mention it.

If you used the wizard to create the combo box I think the Limit to List
property (on the Data tab) is set to Yes. Try setting it to No. If that
works, here is some code modification that should allow the user to start
a
new patient record.

Private Sub Combo34_AfterUpdate()

Dim rs As Object
Dim strMsg as String, strTitle as String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

If rs.NoMatch Then
Me.Combo34 = ""
If MsgBox (strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

Yes i know the legality with the SSN. We are actually Military and that
is
how we track our medical patients. They are aware of this. we have a
secure
network and only authorized users have access to the system via a
password.

I did use the code and when i put in a patient that did not exist it
told
me
that the ssn had no match and would not allow me to enter the new
patient.
It
wants me to enter in a number that is already in the system. If need
be, i
should be able to enter in a new patient that is not in the system.

:

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think it
is
needed since you are testing NoMatch, but to be honest I am still a
bit
unclear about how to use EOF (end of file) and BOF (beginning of file)
except when attempting to navigate to the next of previous record when
that
is not possible. For instance, if you are on the last record and
attempt
to
go to the next record, EOF will be true. In the context of the code
created
by the wizard I am not quite sure what it does. I might have guessed
it
was
to allow for no record being found, but if so you have seen it does
not
seem
to work as you would like in that context. I have tested using my
method,
which seems to work, but I expect the EOF version of the code will
work
too.

If you are at a record and try to move to a non-existant record, it
will
not
work, and will leave you at the current record, as I recall. It is
also
possible to ask if the user wants to create a new record, or to go to
the
first record, or whatever, but we can take that up as needed. First
be
sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are
legal
implications to allowing a person's SSN to be obtained by unauthorized
persons. Access, especially with no security applied, is not a secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number. The
form
contains the patient information. The sub-form contains the
appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it
will
pull their information. if the person is not in the database, then
it
adds
in
someone elses information. It does not leave the rest of the fields
blank
when the person is not in the database already.

:

Post the code you used, and describe the errors. Did you use the
text
box
code? If so, is it in an unbound text box? A text box or combo
box
for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is
this
the
form/subform I described? If so, in which is the text box or combo
box
located, the form or the subform?

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

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

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.

:

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

 

[Shannon]

Yes, the form/subform is based on two related tables. the primary key being
the patient number linking the patient informtion on the form to the
appointment information in the sub form.

I do not have any queries.

The message that comes up after i entered in a new patient number, after i
inserted that code that you gave me is as follows:
"The expression After Update you entered as the event property setting
produced the following error: Ambiguous name detected:Combo34_AfterUpdate."
So, in that box i clicked the "OK" button and it brought up a small window
that says "Enter Parameter Value", and is labeled "SSN" with an "OK" and a
"Cancel" button.
This comes up, now, even when i enter in a patient number that is already in
the database.
SELECT is not in any lines for this command.

You need to answer this question: Do you have a form/subform based on
related tables as I described?

The code I posted starts with Private Sub... and ends with End Sub. That
needs to replace all of what was in there before. Please post the code from
the first instance of the line Private Sub Combo34_AfterUpdate() through End
Sub. If there is another End Sub that is not preceded by a Private Sub
line, post everything through that line. Also, go to the Row Source. If
there is code that includes the word SELECT in that line, post it. If it is
a named query, mention that.

Did it tell you what the ambiguous name was?

A message box does not allow you to enter anything. You can only select an
option such as Yes, No, OK, Cancel. If it looked like a message box but
provided a place to enter SSN it could have been a parameter box from a
query. Is the form based on a query? It could have been an input box, too,
but if so I don't know where it came from.

Ok, the limit to list was set to "NO". I didn't know if i was supposed to
delete the previous code for this combo box so i tried adding this under
the
one that we used previously and i tried using this one instead of the last
and i got error messages. I was told that i had an ambiguous name. then
it
brought up a message box that allowed me to enter in the ssn. after i did
that, i also entered in patient informtion for the ssn. when i got ready
to
save the form it said that i did not have a number in the ssn box and that
it
was required, when i did have a number in that box. I hope that makes
sense.

You may be surprised how many people leave SSNs floating around for
snoops
to find. Just thought I'd mention it.

If you used the wizard to create the combo box I think the Limit to List
property (on the Data tab) is set to Yes. Try setting it to No. If that
works, here is some code modification that should allow the user to start
a
new patient record.

Private Sub Combo34_AfterUpdate()

Dim rs As Object
Dim strMsg as String, strTitle as String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

If rs.NoMatch Then
Me.Combo34 = ""
If MsgBox (strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub
Yes i know the legality with the SSN. We are actually Military and that
is
how we track our medical patients. They are aware of this. we have a
secure
network and only authorized users have access to the system via a
password.

I did use the code and when i put in a patient that did not exist it
told
me
that the ssn had no match and would not allow me to enter the new
patient.
It
wants me to enter in a number that is already in the system. If need
be, i
should be able to enter in a new patient that is not in the system.

:

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think it
is
needed since you are testing NoMatch, but to be honest I am still a
bit
unclear about how to use EOF (end of file) and BOF (beginning of file)
except when attempting to navigate to the next of previous record when
that
is not possible. For instance, if you are on the last record and
attempt
to
go to the next record, EOF will be true. In the context of the code
created
by the wizard I am not quite sure what it does. I might have guessed
it
was
to allow for no record being found, but if so you have seen it does
not
seem
to work as you would like in that context. I have tested using my
method,
which seems to work, but I expect the EOF version of the code will
work
too.

If you are at a record and try to move to a non-existant record, it
will
not
work, and will leave you at the current record, as I recall. It is
also
possible to ask if the user wants to create a new record, or to go to
the
first record, or whatever, but we can take that up as needed. First
be
sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are
legal
implications to allowing a person's SSN to be obtained by unauthorized
persons. Access, especially with no security applied, is not a secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number. The
form
contains the patient information. The sub-form contains the
appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database it
will
pull their information. if the person is not in the database, then
it
adds
in
someone elses information. It does not leave the rest of the fields
blank
when the person is not in the database already.

:

Post the code you used, and describe the errors. Did you use the
text
box
code? If so, is it in an unbound text box? A text box or combo
box
for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is
this
the
form/subform I described? If so, in which is the text box or combo
box
located, the form or the subform?

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

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

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

 

[BruceM]

Did you create a command button, then delete it and create another one with
the same name, or something like that? Look through the code carefully to be
sure there are not two separate procedures with the same name. If there is
nothing obvious, refer to this request in my previous posting:

"Please post the code from the first instance of the line Private Sub
Combo34_AfterUpdate() through End Sub. If there is another End Sub that is
not preceded by a Private Sub line, post everything through that line."

This article has more information about the error message:
http://support.microsoft.com/?id=817411

Yes, the form/subform is based on two related tables. the primary key
being
the patient number linking the patient informtion on the form to the
appointment information in the sub form.

I do not have any queries.

The message that comes up after i entered in a new patient number, after i
inserted that code that you gave me is as follows:
"The expression After Update you entered as the event property setting
produced the following error: Ambiguous name
detected:Combo34_AfterUpdate."
So, in that box i clicked the "OK" button and it brought up a small window
that says "Enter Parameter Value", and is labeled "SSN" with an "OK" and a
"Cancel" button.
This comes up, now, even when i enter in a patient number that is already
in
the database.
SELECT is not in any lines for this command.

You need to answer this question: Do you have a form/subform based on
related tables as I described?

The code I posted starts with Private Sub... and ends with End Sub. That
needs to replace all of what was in there before. Please post the code
from
the first instance of the line Private Sub Combo34_AfterUpdate() through
End
Sub. If there is another End Sub that is not preceded by a Private Sub
line, post everything through that line. Also, go to the Row Source. If
there is code that includes the word SELECT in that line, post it. If it
is
a named query, mention that.

Did it tell you what the ambiguous name was?

A message box does not allow you to enter anything. You can only select
an
option such as Yes, No, OK, Cancel. If it looked like a message box but
provided a place to enter SSN it could have been a parameter box from a
query. Is the form based on a query? It could have been an input box,
too,
but if so I don't know where it came from.

Ok, the limit to list was set to "NO". I didn't know if i was supposed
to
delete the previous code for this combo box so i tried adding this
under
the
one that we used previously and i tried using this one instead of the
last
and i got error messages. I was told that i had an ambiguous name.
then
it
brought up a message box that allowed me to enter in the ssn. after i
did
that, i also entered in patient informtion for the ssn. when i got
ready
to
save the form it said that i did not have a number in the ssn box and
that
it
was required, when i did have a number in that box. I hope that makes
sense.

:

You may be surprised how many people leave SSNs floating around for
snoops
to find. Just thought I'd mention it.

If you used the wizard to create the combo box I think the Limit to
List
property (on the Data tab) is set to Yes. Try setting it to No. If
that
works, here is some code modification that should allow the user to
start
a
new patient record.

Private Sub Combo34_AfterUpdate()

Dim rs As Object
Dim strMsg as String, strTitle as String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

If rs.NoMatch Then
Me.Combo34 = ""
If MsgBox (strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub
Yes i know the legality with the SSN. We are actually Military and
that
is
how we track our medical patients. They are aware of this. we have a
secure
network and only authorized users have access to the system via a
password.

I did use the code and when i put in a patient that did not exist it
told
me
that the ssn had no match and would not allow me to enter the new
patient.
It
wants me to enter in a number that is already in the system. If need
be, i
should be able to enter in a new patient that is not in the system.

:

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think
it
is
needed since you are testing NoMatch, but to be honest I am still a
bit
unclear about how to use EOF (end of file) and BOF (beginning of
file)
except when attempting to navigate to the next of previous record
when
that
is not possible. For instance, if you are on the last record and
attempt
to
go to the next record, EOF will be true. In the context of the
code
created
by the wizard I am not quite sure what it does. I might have
guessed
it
was
to allow for no record being found, but if so you have seen it does
not
seem
to work as you would like in that context. I have tested using my
method,
which seems to work, but I expect the EOF version of the code will
work
too.

If you are at a record and try to move to a non-existant record, it
will
not
work, and will leave you at the current record, as I recall. It is
also
possible to ask if the user wants to create a new record, or to go
to
the
first record, or whatever, but we can take that up as needed.
First
be
sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are
legal
implications to allowing a person's SSN to be obtained by
unauthorized
persons. Access, especially with no security applied, is not a
secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number.
The
form
contains the patient information. The sub-form contains the
appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database
it
will
pull their information. if the person is not in the database,
then
it
adds
in
someone elses information. It does not leave the rest of the
fields
blank
when the person is not in the database already.

:

Post the code you used, and describe the errors. Did you use
the
text
box
code? If so, is it in an unbound text box? A text box or combo
box
for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is
this
the
form/subform I described? If so, in which is the text box or
combo
box
located, the form or the subform?

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

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

message
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

 

[Shannon]

Private Sub Combo36_AfterUpdate()

Dim rs As Object
Dim strMsg As String, strTitle As String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo36] & "'"

If rs.NoMatch Then
Me.Combo36 = ""
If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub

I did delete the original combo box and start over. after i created the new
combo box there was not a code for it. I put in the above code for the combo
box and it gave the following prompt when i typed in a patient that already
existed.
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entried and try again."
When i check the properties on the combo box and for "limit to list" it said
no.
I was not sure about the second code that you had given me... Do i add that
other code in with the code above or did i have to enter in the other code
under the code above?

Did you create a command button, then delete it and create another one with
the same name, or something like that? Look through the code carefully to be
sure there are not two separate procedures with the same name. If there is
nothing obvious, refer to this request in my previous posting:

"Please post the code from the first instance of the line Private Sub
Combo34_AfterUpdate() through End Sub. If there is another End Sub that is
not preceded by a Private Sub line, post everything through that line."

This article has more information about the error message:
http://support.microsoft.com/?id=817411

Yes, the form/subform is based on two related tables. the primary key
being
the patient number linking the patient informtion on the form to the
appointment information in the sub form.

I do not have any queries.

The message that comes up after i entered in a new patient number, after i
inserted that code that you gave me is as follows:
"The expression After Update you entered as the event property setting
produced the following error: Ambiguous name
detected:Combo34_AfterUpdate."
So, in that box i clicked the "OK" button and it brought up a small window
that says "Enter Parameter Value", and is labeled "SSN" with an "OK" and a
"Cancel" button.
This comes up, now, even when i enter in a patient number that is already
in
the database.
SELECT is not in any lines for this command.

You need to answer this question: Do you have a form/subform based on
related tables as I described?

The code I posted starts with Private Sub... and ends with End Sub. That
needs to replace all of what was in there before. Please post the code
from
the first instance of the line Private Sub Combo34_AfterUpdate() through
End
Sub. If there is another End Sub that is not preceded by a Private Sub
line, post everything through that line. Also, go to the Row Source. If
there is code that includes the word SELECT in that line, post it. If it
is
a named query, mention that.

Did it tell you what the ambiguous name was?

A message box does not allow you to enter anything. You can only select
an
option such as Yes, No, OK, Cancel. If it looked like a message box but
provided a place to enter SSN it could have been a parameter box from a
query. Is the form based on a query? It could have been an input box,
too,
but if so I don't know where it came from.

Ok, the limit to list was set to "NO". I didn't know if i was supposed
to
delete the previous code for this combo box so i tried adding this
under
the
one that we used previously and i tried using this one instead of the
last
and i got error messages. I was told that i had an ambiguous name.
then
it
brought up a message box that allowed me to enter in the ssn. after i
did
that, i also entered in patient informtion for the ssn. when i got
ready
to
save the form it said that i did not have a number in the ssn box and
that
it
was required, when i did have a number in that box. I hope that makes
sense.

:

You may be surprised how many people leave SSNs floating around for
snoops
to find. Just thought I'd mention it.

If you used the wizard to create the combo box I think the Limit to
List
property (on the Data tab) is set to Yes. Try setting it to No. If
that
works, here is some code modification that should allow the user to
start
a
new patient record.

Private Sub Combo34_AfterUpdate()

Dim rs As Object
Dim strMsg as String, strTitle as String

strMsg = "There is no patient with this ID." & vbCrLf & _
"Would you like to add a patient?"
strTitle = "Number Not Found"

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

If rs.NoMatch Then
Me.Combo34 = ""
If MsgBox (strMsg, vbYesNo, strTitle) = vbYes Then
Me.Recordset.AddNew
End If
Else
Me.Bookmark = rs.Bookmark
End If

End Sub
Yes i know the legality with the SSN. We are actually Military and
that
is
how we track our medical patients. They are aware of this. we have a
secure
network and only authorized users have access to the system via a
password.

I did use the code and when i put in a patient that did not exist it
told
me
that the ssn had no match and would not allow me to enter the new
patient.
It
wants me to enter in a number that is already in the system. If need
be, i
should be able to enter in a new patient that is not in the system.

:

The extra bit about NoMatch should take care of that:

Private Sub Combo34_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"

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

End Sub

You will note that I left out "If Not rs.EOF Then". I don't think
it
is
needed since you are testing NoMatch, but to be honest I am still a
bit
unclear about how to use EOF (end of file) and BOF (beginning of
file)
except when attempting to navigate to the next of previous record
when
that
is not possible. For instance, if you are on the last record and
attempt
to
go to the next record, EOF will be true. In the context of the
code
created
by the wizard I am not quite sure what it does. I might have
guessed
it
was
to allow for no record being found, but if so you have seen it does
not
seem
to work as you would like in that context. I have tested using my
method,
which seems to work, but I expect the EOF version of the code will
work
too.

If you are at a record and try to move to a non-existant record, it
will
not
work, and will leave you at the current record, as I recall. It is
also
possible to ask if the user wants to create a new record, or to go
to
the
first record, or whatever, but we can take that up as needed.
First
be
sure
it works if no record is found.

BTW, if SSN is Social Security Number, be very careful. There are
legal
implications to allowing a person's SSN to be obtained by
unauthorized
persons. Access, especially with no security applied, is not a
secure
environment for sensitive or personal information.

I used an unbound combo box on the form for the patient number.
The
form
contains the patient information. The sub-form contains the
appointment
informtion. Here is the code attached to that combo box.

Private Sub Combo34_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SSN] = '" & Me![Combo34] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I did a test, as i said before. if the person is in the database
it
will
pull their information. if the person is not in the database,
then
it
adds
in
someone elses information. It does not leave the rest of the
fields
blank
when the person is not in the database already.

:

Post the code you used, and describe the errors. Did you use
the
text
box
code? If so, is it in an unbound text box? A text box or combo
box
for
searching needs to be unbound.
It may help if you describe more about the structure. Again, is
this
the
form/subform I described? If so, in which is the text box or
combo
box
located, the form or the subform?

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

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