Auto Fill help

[BruceM]

Sounds like you are using a bound combo box. In an earlier posting I
specified that a combo box for searching needs to be unbound.

To which "second code " do you refer?

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?

message
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

 

[Shannon]

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

The above code was the second code that you gave me that i was referring to.
I fixed the combo box... i don't know what had happened, but it was bound
again.

So, it recognizes the SSN if it is already in the data base, and pulls up
another persons info when it does not recognize it. That is where that second
code came in that is posted above.

Sounds like you are using a bound combo box. In an earlier posting I
specified that a combo box for searching needs to be unbound.

To which "second code " do you refer?

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

 

[BruceM]

The second code was actually the first version I posted, as I recall. The
only difference is that it does not ask if you want to add a new record. If
there is nothing to handle the situation of no matching SSN I think it will
take you to the first record (in whatever order you are using for the
records).
You could try the shorter version of the code, although I don't see what is
causing the problem with the version you are now using. I see you have
Combo34 and Combo36 in the two versions, so be sure you are using the
correct one in the code. It needs to be the same within the code as in the
Private Sub line.
Are you receiving the message informing you that the SSN was not found?
Try adding a message box to be sure the Combo34 (or Combo36) value is what
you expect. Under Dim rs as Object add:
MsgBox Me.Combo34

There are other ways of doing this sort of test, but this one is probably
the simplest for now.

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

The above code was the second code that you gave me that i was referring
to.
I fixed the combo box... i don't know what had happened, but it was bound
again.

So, it recognizes the SSN if it is already in the data base, and pulls up
another persons info when it does not recognize it. That is where that
second
code came in that is posted above.

Sounds like you are using a bound combo box. In an earlier posting I
specified that a combo box for searching needs to be unbound.

To which "second code " do you refer?

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

 

[alex]

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.