a bit of vba, a bit of sql

G

Guest

in my a2k form, once the user has selected the appropriate values for the
patient's lastname, firstname, and middleinitial, from combobox controls, the
only the idea's crossed my mind that a2k may be able to automatically enter
that mr_number value and thus 'save' the user from the 'burden' of having to
actually use the arrow on the mr's combobox and select its value from the
list (consisting of always just one mr_number). i think i once hear someone
talk about a property of a combobox that allows the programmer to designate
which of a litany of choices is entered or possibly defaulted into a field
but the trail's gone a little cold. if the mr_number field were to be input
via vba code or whathaveyou then it's also occuring to me that the control
would not need to be a combobox but a textcontrol (and that possibly it might
be disabled/locked/skipped over since the user would not be directly inputing
the datum). i'm attaching below for reference purposes, the a) vba code on
the only event property i'm coding for mr_number and b) the sql version of
the "Row source' of the mr_number in hope it'll make the question more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
 
G

Guest

Hi, Ted.

If the record already exists in the table, then a common approach to
selecting the name is to use a query such as the following:

SELECT MR_Number, ([First Name] & " " & MI & " " & [Last Name]) AS FullName
FROM [ID Table]
ORDER BY [Last Name], [First Name], MI;

.. . . and then use this query as the combo box's Row Source Property, the
first column as the Bound Column Property, and the primary key (this appears
to be MR_Number in your case) as the Control Source Property. The Column
Count Property would be set to 2, and the Column Widths Property would be set
to 0";1.2"

That way, the user selects the entire name from the combo box, and the
MR_Number field would be populated with this choice -- with no coding on your
part and no "burden" on the user's part.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Ted said:
in my a2k form, once the user has selected the appropriate values for the
patient's lastname, firstname, and middleinitial, from combobox controls, the
only the idea's crossed my mind that a2k may be able to automatically enter
that mr_number value and thus 'save' the user from the 'burden' of having to
actually use the arrow on the mr's combobox and select its value from the
list (consisting of always just one mr_number). i think i once hear someone
talk about a property of a combobox that allows the programmer to designate
which of a litany of choices is entered or possibly defaulted into a field
but the trail's gone a little cold. if the mr_number field were to be input
via vba code or whathaveyou then it's also occuring to me that the control
would not need to be a combobox but a textcontrol (and that possibly it might
be disabled/locked/skipped over since the user would not be directly inputing
the datum). i'm attaching below for reference purposes, the a) vba code on
the only event property i'm coding for mr_number and b) the sql version of
the "Row source' of the mr_number in hope it'll make the question more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
Click to expand...
 
G

Guest

hi gunny,

it's probably me, but i think we're going 'round in a little circle....

as i understood your solution, the query required the user to 'pick' the mr
number from the combobox 'indirectly' by selecting the patient's name. the
thing i tried to communicate in my posting is that the patient's last, first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
my 'idea' stemmed from the fact that much like social security numbers, mr
numbers are unique to a person, so therefore, even though there might be
multiple does, and multiple johns and multiple middle initials, there is only
one person in the database who has any one mr number and therefore, once the
appropriate person is coded into the three said fields, there is no doubt
over the mr number, making the selection (per se) of an mr number in a
combobox represent a (small) 'extra step'. i think your idea somehow is based
Hi, Ted.

If the record already exists in the table, then a common approach to
selecting the name is to use a query such as the following:

SELECT MR_Number, ([First Name] & " " & MI & " " & [Last Name]) AS FullName
FROM [ID Table]
ORDER BY [Last Name], [First Name], MI;

. . . and then use this query as the combo box's Row Source Property, the
first column as the Bound Column Property, and the primary key (this appears
to be MR_Number in your case) as the Control Source Property. The Column
Count Property would be set to 2, and the Column Widths Property would be set
to 0";1.2"

That way, the user selects the entire name from the combo box, and the
MR_Number field would be populated with this choice -- with no coding on your
part and no "burden" on the user's part.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Ted said:
in my a2k form, once the user has selected the appropriate values for the
patient's lastname, firstname, and middleinitial, from combobox controls, the
only the idea's crossed my mind that a2k may be able to automatically enter
that mr_number value and thus 'save' the user from the 'burden' of having to
actually use the arrow on the mr's combobox and select its value from the
list (consisting of always just one mr_number). i think i once hear someone
talk about a property of a combobox that allows the programmer to designate
which of a litany of choices is entered or possibly defaulted into a field
but the trail's gone a little cold. if the mr_number field were to be input
via vba code or whathaveyou then it's also occuring to me that the control
would not need to be a combobox but a textcontrol (and that possibly it might
be disabled/locked/skipped over since the user would not be directly inputing
the datum). i'm attaching below for reference purposes, the a) vba code on
the only event property i'm coding for mr_number and b) the sql version of
the "Row source' of the mr_number in hope it'll make the question more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
Click to expand...
Click to expand...
 
G

Guest

Hi, Ted.
the
thing i tried to communicate in my posting is that the patient's last, first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
Click to expand...

I'm suggesting that you replace the current burden of three combo boxes on
the developer and the user with the easier burden of a single combo box,
which displays the three names and saves the foreign key to the bound field
in the underlying table. Since the goal is a normalized data base, the three
names in separate combo boxes aren't bound to the underlying table anyway,
because you are already saving the foreign key, MR_Number, to this table.
The selection of a record in the single combo box as I suggested displays all
three of the names for the user, so that he can see which patient the rest of
the controls on the form apply to.
my 'idea' stemmed from the fact that much like social security numbers, mr
numbers are unique to a person, so therefore, even though there might be
multiple does, and multiple johns and multiple middle initials, there is only
one person in the database who has any one mr number and therefore, once the
appropriate person is coded into the three said fields, there is no doubt
over the mr number
Click to expand...

SSN's aren't unique to a person, so never try using them as a primary key --
even if it weren't such a security risk. Unless you've coded for each combo
box's OnAfterUpdate( ) event to reduce the choices in the other combo boxes
based upon the value selected in the current combo box, then the user will be
able to select a patient name that doesn't exist in the database and,
therefore, has no MR_Number. For example, if the following records were in
the table:

Edward M. Kennedy
John L. Thompson
Mark F. Bradshaw
Jacqueline A. Reese
et cetera . . .

Then the user could select either the former President of the United States
or former First Lady:

John F. Kennedy
Jacqueline L. Kennedy

.. . . when these two people aren't patients listed in the database. But
even if you have coded around this little problem, wouldn't it just be easier
to use the single combo box, which includes _only_ the valid combinations?
i think your idea somehow is based
on the idea that there are no replicates on the precursor fields.
Click to expand...

There should be no duplicate records in a normalized table. However, if
there are two patients named John M. Smith, how is your code below
differentiating one from the other? It isn't. At least with the combo box
bound to the query I suggested, the user can see that there are two John M.
Smiths (provided that he scrolls down far enough) and do some investigation
to determine whether it's a duplicate entry or find out which one is the
correct one to select in the combo box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Ted said:
hi gunny,

it's probably me, but i think we're going 'round in a little circle....

as i understood your solution, the query required the user to 'pick' the mr
number from the combobox 'indirectly' by selecting the patient's name. the
thing i tried to communicate in my posting is that the patient's last, first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
my 'idea' stemmed from the fact that much like social security numbers, mr
numbers are unique to a person, so therefore, even though there might be
multiple does, and multiple johns and multiple middle initials, there is only
one person in the database who has any one mr number and therefore, once the
appropriate person is coded into the three said fields, there is no doubt
over the mr number, making the selection (per se) of an mr number in a
combobox represent a (small) 'extra step'. i think your idea somehow is based
Hi, Ted.

If the record already exists in the table, then a common approach to
selecting the name is to use a query such as the following:

SELECT MR_Number, ([First Name] & " " & MI & " " & [Last Name]) AS FullName
FROM [ID Table]
ORDER BY [Last Name], [First Name], MI;

. . . and then use this query as the combo box's Row Source Property, the
first column as the Bound Column Property, and the primary key (this appears
to be MR_Number in your case) as the Control Source Property. The Column
Count Property would be set to 2, and the Column Widths Property would be set
to 0";1.2"

That way, the user selects the entire name from the combo box, and the
MR_Number field would be populated with this choice -- with no coding on your
part and no "burden" on the user's part.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Ted said:
in my a2k form, once the user has selected the appropriate values for the
patient's lastname, firstname, and middleinitial, from combobox controls, the
only the idea's crossed my mind that a2k may be able to automatically enter
that mr_number value and thus 'save' the user from the 'burden' of having to
actually use the arrow on the mr's combobox and select its value from the
list (consisting of always just one mr_number). i think i once hear someone
talk about a property of a combobox that allows the programmer to designate
which of a litany of choices is entered or possibly defaulted into a field
but the trail's gone a little cold. if the mr_number field were to be input
via vba code or whathaveyou then it's also occuring to me that the control
would not need to be a combobox but a textcontrol (and that possibly it might
be disabled/locked/skipped over since the user would not be directly inputing
the datum). i'm attaching below for reference purposes, the a) vba code on
the only event property i'm coding for mr_number and b) the sql version of
the "Row source' of the mr_number in hope it'll make the question more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hi gunny, i haven't used the form myself in a bit but i seem to believe that
when the user views the last names available to him as he completes the first
field in it, i.e. the last names, he is presented with all the pertinent
values of the patient's (last name, first name, mi, mr number) to enable him
to select the appropriate 'row' from which to select the patient (so a doubly
coded but meaningful ln/fn/mi combination would be differentiatable on sight.

i think the answer to this lies in the "itemdata" property (if that is the
term 4 it). there was a thread you were involved in around the 26th of last
month that seemed to remind me of this issue i'm grappling with ("auto-select
entry to combo box" was the subject heading).

-ted


'69 Camaro said:
Hi, Ted.
the
thing i tried to communicate in my posting is that the patient's last, first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
Click to expand...

I'm suggesting that you replace the current burden of three combo boxes on
the developer and the user with the easier burden of a single combo box,
which displays the three names and saves the foreign key to the bound field
in the underlying table. Since the goal is a normalized data base, the three
names in separate combo boxes aren't bound to the underlying table anyway,
because you are already saving the foreign key, MR_Number, to this table.
The selection of a record in the single combo box as I suggested displays all
three of the names for the user, so that he can see which patient the rest of
the controls on the form apply to.
my 'idea' stemmed from the fact that much like social security numbers, mr
numbers are unique to a person, so therefore, even though there might be
multiple does, and multiple johns and multiple middle initials, there is only
one person in the database who has any one mr number and therefore, once the
appropriate person is coded into the three said fields, there is no doubt
over the mr number
Click to expand...

SSN's aren't unique to a person, so never try using them as a primary key --
even if it weren't such a security risk. Unless you've coded for each combo
box's OnAfterUpdate( ) event to reduce the choices in the other combo boxes
based upon the value selected in the current combo box, then the user will be
able to select a patient name that doesn't exist in the database and,
therefore, has no MR_Number. For example, if the following records were in
the table:

Edward M. Kennedy
John L. Thompson
Mark F. Bradshaw
Jacqueline A. Reese
et cetera . . .

Then the user could select either the former President of the United States
or former First Lady:

John F. Kennedy
Jacqueline L. Kennedy

. . . when these two people aren't patients listed in the database. But
even if you have coded around this little problem, wouldn't it just be easier
to use the single combo box, which includes _only_ the valid combinations?
i think your idea somehow is based
on the idea that there are no replicates on the precursor fields.
Click to expand...

There should be no duplicate records in a normalized table. However, if
there are two patients named John M. Smith, how is your code below
differentiating one from the other? It isn't. At least with the combo box
bound to the query I suggested, the user can see that there are two John M.
Smiths (provided that he scrolls down far enough) and do some investigation
to determine whether it's a duplicate entry or find out which one is the
correct one to select in the combo box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Ted said:
hi gunny,

it's probably me, but i think we're going 'round in a little circle....

as i understood your solution, the query required the user to 'pick' the mr
number from the combobox 'indirectly' by selecting the patient's name. the
thing i tried to communicate in my posting is that the patient's last, first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
my 'idea' stemmed from the fact that much like social security numbers, mr
numbers are unique to a person, so therefore, even though there might be
multiple does, and multiple johns and multiple middle initials, there is only
one person in the database who has any one mr number and therefore, once the
appropriate person is coded into the three said fields, there is no doubt
over the mr number, making the selection (per se) of an mr number in a
combobox represent a (small) 'extra step'. i think your idea somehow is based
Hi, Ted.

If the record already exists in the table, then a common approach to
selecting the name is to use a query such as the following:

SELECT MR_Number, ([First Name] & " " & MI & " " & [Last Name]) AS FullName
FROM [ID Table]
ORDER BY [Last Name], [First Name], MI;

. . . and then use this query as the combo box's Row Source Property, the
first column as the Bound Column Property, and the primary key (this appears
to be MR_Number in your case) as the Control Source Property. The Column
Count Property would be set to 2, and the Column Widths Property would be set
to 0";1.2"

That way, the user selects the entire name from the combo box, and the
MR_Number field would be populated with this choice -- with no coding on your
part and no "burden" on the user's part.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

in my a2k form, once the user has selected the appropriate values for the
patient's lastname, firstname, and middleinitial, from combobox controls, the
only the idea's crossed my mind that a2k may be able to automatically enter
that mr_number value and thus 'save' the user from the 'burden' of having to
actually use the arrow on the mr's combobox and select its value from the
list (consisting of always just one mr_number). i think i once hear someone
talk about a property of a combobox that allows the programmer to designate
which of a litany of choices is entered or possibly defaulted into a field
but the trail's gone a little cold. if the mr_number field were to be input
via vba code or whathaveyou then it's also occuring to me that the control
would not need to be a combobox but a textcontrol (and that possibly it might
be disabled/locked/skipped over since the user would not be directly inputing
the datum). i'm attaching below for reference purposes, the a) vba code on
the only event property i'm coding for mr_number and b) the sql version of
the "Row source' of the mr_number in hope it'll make the question more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
Click to expand...
Click to expand...
Click to expand...
 
6

'69 Camaro

If you want to keep the four combo boxes, then in the last of the three
combo box's OnAfterUpdate( ) event, try:

Me!MR_Number.Requery
Me!MR_Number.Value = Me!MR_Number.ItemData(0)

You'll need to use VBA code to guarantee that this "last" combo box selected
for the name is the one you intend it to be.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Ted said:
hi gunny, i haven't used the form myself in a bit but i seem to believe
that
when the user views the last names available to him as he completes the
first
field in it, i.e. the last names, he is presented with all the pertinent
values of the patient's (last name, first name, mi, mr number) to enable
him
to select the appropriate 'row' from which to select the patient (so a
doubly
coded but meaningful ln/fn/mi combination would be differentiatable on
sight.

i think the answer to this lies in the "itemdata" property (if that is the
term 4 it). there was a thread you were involved in around the 26th of
last
month that seemed to remind me of this issue i'm grappling with
("auto-select
entry to combo box" was the subject heading).

-ted


'69 Camaro said:
Hi, Ted.
the
thing i tried to communicate in my posting is that the patient's last,
first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
Click to expand...

I'm suggesting that you replace the current burden of three combo boxes
on
the developer and the user with the easier burden of a single combo box,
which displays the three names and saves the foreign key to the bound
field
in the underlying table. Since the goal is a normalized data base, the
three
names in separate combo boxes aren't bound to the underlying table
anyway,
because you are already saving the foreign key, MR_Number, to this table.
The selection of a record in the single combo box as I suggested displays
all
three of the names for the user, so that he can see which patient the
rest of
the controls on the form apply to.
my 'idea' stemmed from the fact that much like social security numbers,
mr
numbers are unique to a person, so therefore, even though there might
be
multiple does, and multiple johns and multiple middle initials, there
is only
one person in the database who has any one mr number and therefore,
once the
appropriate person is coded into the three said fields, there is no
doubt
over the mr number
Click to expand...

SSN's aren't unique to a person, so never try using them as a primary
key --
even if it weren't such a security risk. Unless you've coded for each
combo
box's OnAfterUpdate( ) event to reduce the choices in the other combo
boxes
based upon the value selected in the current combo box, then the user
will be
able to select a patient name that doesn't exist in the database and,
therefore, has no MR_Number. For example, if the following records were
in
the table:

Edward M. Kennedy
John L. Thompson
Mark F. Bradshaw
Jacqueline A. Reese
et cetera . . .

Then the user could select either the former President of the United
States
or former First Lady:

John F. Kennedy
Jacqueline L. Kennedy

. . . when these two people aren't patients listed in the database. But
even if you have coded around this little problem, wouldn't it just be
easier
to use the single combo box, which includes _only_ the valid
combinations?
i think your idea somehow is based
on the idea that there are no replicates on the precursor fields.
Click to expand...

There should be no duplicate records in a normalized table. However, if
there are two patients named John M. Smith, how is your code below
differentiating one from the other? It isn't. At least with the combo
box
bound to the query I suggested, the user can see that there are two John
M.
Smiths (provided that he scrolls down far enough) and do some
investigation
to determine whether it's a duplicate entry or find out which one is the
correct one to select in the combo box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Ted said:
hi gunny,

it's probably me, but i think we're going 'round in a little circle....

as i understood your solution, the query required the user to 'pick'
the mr
number from the combobox 'indirectly' by selecting the patient's name.
the
thing i tried to communicate in my posting is that the patient's last,
first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
my 'idea' stemmed from the fact that much like social security numbers,
mr
numbers are unique to a person, so therefore, even though there might
be
multiple does, and multiple johns and multiple middle initials, there
is only
one person in the database who has any one mr number and therefore,
once the
appropriate person is coded into the three said fields, there is no
doubt
over the mr number, making the selection (per se) of an mr number in a
combobox represent a (small) 'extra step'. i think your idea somehow is
based
on the idea that there are no replicates on the precursor fields.

:

Hi, Ted.

If the record already exists in the table, then a common approach to
selecting the name is to use a query such as the following:

SELECT MR_Number, ([First Name] & " " & MI & " " & [Last Name]) AS
FullName
FROM [ID Table]
ORDER BY [Last Name], [First Name], MI;

. . . and then use this query as the combo box's Row Source Property,
the
first column as the Bound Column Property, and the primary key (this
appears
to be MR_Number in your case) as the Control Source Property. The
Column
Count Property would be set to 2, and the Column Widths Property
would be set
to 0";1.2"

That way, the user selects the entire name from the combo box, and
the
MR_Number field would be populated with this choice -- with no coding
on your
part and no "burden" on the user's part.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

in my a2k form, once the user has selected the appropriate values
for the
patient's lastname, firstname, and middleinitial, from combobox
controls, the
only the idea's crossed my mind that a2k may be able to
automatically enter
that mr_number value and thus 'save' the user from the 'burden' of
having to
actually use the arrow on the mr's combobox and select its value
from the
list (consisting of always just one mr_number). i think i once hear
someone
talk about a property of a combobox that allows the programmer to
designate
which of a litany of choices is entered or possibly defaulted into
a field
but the trail's gone a little cold. if the mr_number field were to
be input
via vba code or whathaveyou then it's also occuring to me that the
control
would not need to be a combobox but a textcontrol (and that
possibly it might
be disabled/locked/skipped over since the user would not be
directly inputing
the datum). i'm attaching below for reference purposes, the a) vba
code on
the only event property i'm coding for mr_number and b) the sql
version of
the "Row source' of the mr_number in hope it'll make the question
more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on
the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID
Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First
Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hi gunny,

the code i'm using is:


Private Sub Screening_ID_AfterUpdate()
Me.Screening_ID.Requery
Me.Screening_ID.Value = Me.Screening_ID.ItemData(0)
End Sub

because as i found mr_number is not uniquely determined by name since there
can be > 1 person with identical details in all the (first, last, middle)
name fields. ergo, i decided that screening_id would get the benefit of the
automatic entry. HOWEVER, the reason for posting this is that the code is
having no effect! any ideas to research?

with best regards,

-ted
'69 Camaro said:
If you want to keep the four combo boxes, then in the last of the three
combo box's OnAfterUpdate( ) event, try:

Me!MR_Number.Requery
Me!MR_Number.Value = Me!MR_Number.ItemData(0)

You'll need to use VBA code to guarantee that this "last" combo box selected
for the name is the one you intend it to be.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Ted said:
hi gunny, i haven't used the form myself in a bit but i seem to believe
that
when the user views the last names available to him as he completes the
first
field in it, i.e. the last names, he is presented with all the pertinent
values of the patient's (last name, first name, mi, mr number) to enable
him
to select the appropriate 'row' from which to select the patient (so a
doubly
coded but meaningful ln/fn/mi combination would be differentiatable on
sight.

i think the answer to this lies in the "itemdata" property (if that is the
term 4 it). there was a thread you were involved in around the 26th of
last
month that seemed to remind me of this issue i'm grappling with
("auto-select
entry to combo box" was the subject heading).

-ted


'69 Camaro said:
Hi, Ted.

the
thing i tried to communicate in my posting is that the patient's last,
first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.

I'm suggesting that you replace the current burden of three combo boxes
on
the developer and the user with the easier burden of a single combo box,
which displays the three names and saves the foreign key to the bound
field
in the underlying table. Since the goal is a normalized data base, the
three
names in separate combo boxes aren't bound to the underlying table
anyway,
because you are already saving the foreign key, MR_Number, to this table.
The selection of a record in the single combo box as I suggested displays
all
three of the names for the user, so that he can see which patient the
rest of
the controls on the form apply to.

my 'idea' stemmed from the fact that much like social security numbers,
mr
numbers are unique to a person, so therefore, even though there might
be
multiple does, and multiple johns and multiple middle initials, there
is only
one person in the database who has any one mr number and therefore,
once the
appropriate person is coded into the three said fields, there is no
doubt
over the mr number

SSN's aren't unique to a person, so never try using them as a primary
key --
even if it weren't such a security risk. Unless you've coded for each
combo
box's OnAfterUpdate( ) event to reduce the choices in the other combo
boxes
based upon the value selected in the current combo box, then the user
will be
able to select a patient name that doesn't exist in the database and,
therefore, has no MR_Number. For example, if the following records were
in
the table:

Edward M. Kennedy
John L. Thompson
Mark F. Bradshaw
Jacqueline A. Reese
et cetera . . .

Then the user could select either the former President of the United
States
or former First Lady:

John F. Kennedy
Jacqueline L. Kennedy

. . . when these two people aren't patients listed in the database. But
even if you have coded around this little problem, wouldn't it just be
easier
to use the single combo box, which includes _only_ the valid
combinations?

i think your idea somehow is based
on the idea that there are no replicates on the precursor fields.

There should be no duplicate records in a normalized table. However, if
there are two patients named John M. Smith, how is your code below
differentiating one from the other? It isn't. At least with the combo
box
bound to the query I suggested, the user can see that there are two John
M.
Smiths (provided that he scrolls down far enough) and do some
investigation
to determine whether it's a duplicate entry or find out which one is the
correct one to select in the combo box.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

hi gunny,

it's probably me, but i think we're going 'round in a little circle....

as i understood your solution, the query required the user to 'pick'
the mr
number from the combobox 'indirectly' by selecting the patient's name.
the
thing i tried to communicate in my posting is that the patient's last,
first
and middle names are already in their place on the form by the time the
cursor's on the mr number field.
my 'idea' stemmed from the fact that much like social security numbers,
mr
numbers are unique to a person, so therefore, even though there might
be
multiple does, and multiple johns and multiple middle initials, there
is only
one person in the database who has any one mr number and therefore,
once the
appropriate person is coded into the three said fields, there is no
doubt
over the mr number, making the selection (per se) of an mr number in a
combobox represent a (small) 'extra step'. i think your idea somehow is
based
on the idea that there are no replicates on the precursor fields.

:

Hi, Ted.

If the record already exists in the table, then a common approach to
selecting the name is to use a query such as the following:

SELECT MR_Number, ([First Name] & " " & MI & " " & [Last Name]) AS
FullName
FROM [ID Table]
ORDER BY [Last Name], [First Name], MI;

. . . and then use this query as the combo box's Row Source Property,
the
first column as the Bound Column Property, and the primary key (this
appears
to be MR_Number in your case) as the Control Source Property. The
Column
Count Property would be set to 2, and the Column Widths Property
would be set
to 0";1.2"

That way, the user selects the entire name from the combo box, and
the
MR_Number field would be populated with this choice -- with no coding
on your
part and no "burden" on the user's part.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

in my a2k form, once the user has selected the appropriate values
for the
patient's lastname, firstname, and middleinitial, from combobox
controls, the
only the idea's crossed my mind that a2k may be able to
automatically enter
that mr_number value and thus 'save' the user from the 'burden' of
having to
actually use the arrow on the mr's combobox and select its value
from the
list (consisting of always just one mr_number). i think i once hear
someone
talk about a property of a combobox that allows the programmer to
designate
which of a litany of choices is entered or possibly defaulted into
a field
but the trail's gone a little cold. if the mr_number field were to
be input
via vba code or whathaveyou then it's also occuring to me that the
control
would not need to be a combobox but a textcontrol (and that
possibly it might
be disabled/locked/skipped over since the user would not be
directly inputing
the datum). i'm attaching below for reference purposes, the a) vba
code on
the only event property i'm coding for mr_number and b) the sql
version of
the "Row source' of the mr_number in hope it'll make the question
more
meaningful to the forum's readers:

Private Sub MR_Number_AfterUpdate()
Me.MR_Number.Requery
Me.Screening_ID.SetFocus <-- the next field the cursor jumps to on
the form
Me.Screening_ID = 0
Me.Screening_ID.Requery
End Sub


SELECT [ID Table].MR_Number FROM [ID Table] WHERE ((([ID
Table].[Last
Name])=[Forms]![Screening Log (Edit Only)]![Last Name]) AND (([ID
Table].[First Name])=[Forms]![Screening Log (Edit Only)]![First
Name]) AND
(([ID Table].MI)=[Forms]![Screening Log (Edit Only)]![MI]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Case Statements & OldValue Property 8
Options Group, AfterUpdate Event, OldValue Property and ControlSou 6
Learning if a record exists in a table 1
unexpected parameter query 2
Appending Records to Another Table When Certain Values Get Updates 1
Testing for Changes in Table before Updating Values in Other Table 2
incrementing records in linked parent/child (main/subform) situati 1
RecordSet (I guess) woes 5

Top