RE: a bit of vba, a bit of sql

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.

"'69 Camaro" wrote:

> 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" wrote:
>
> > 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]));
.