Use combo box to search/add new record

[CuriousMark]

I'm trying to use a combo box to enable the user to begin typing in a name,
autofill if the name is in the database, or create a new record if the name
is not in the database. To abbreviate, I am working with two tables:

tblPatients
PatientID
PatientLastName
PatientFirstName
etc.

tblEncounter
EncounterID
PatientID
EncounterDate
EncounterCode

Each patient can have many encounters, so the encounters are a subform of
the Patient form. The goal: To enter a new encounter, the user types the last
name in the combo box for the PatientLastName. If the patient is in the
database, the combo box autofills and the user selects that name, and the
remaining fields in the Patient form populate appropriately. If the patient
is not in the database, the user keeps typing and a new record is created.

I have two problems: 1) Typing a new name simply changes the LastName field
for the existing record, rather than creating a new one; and 2) If I do
select a name from the combo box, the other fields don't populate.

Any help is appreciated. Thanks in advance.

CM

 

[Barry Gilbert]

There are several things going on here. First of all, if the only purpose of
you combobox is to select names, it should be unbound. That is, you should
clear the Control Source property. This prevent users from changing the last
name field of the selected record.

To use the value they select (assuming it's already in the list), put some
code in the combobox's AfterUpdate event. Something like:

Me.Filter = "PatientLastName = '" & Me.cboPatientLastName & "'"
Me.FilterOn = True

This will limit the form's records to only the one(s) that have the same
last name as the one selected. You'll also have to set the combobox's
LimitToList property to true.

Finally, to allow them to add a record when the last name they type isn't in
the list, you'll use the combobox's NotInList event. This event fires if they
enter a name not on the list. You need some code like:

If MsgBox(Me.cboPatientLastName & " is not in the database. Do you want to
add it?",VbOkCancel+ vbQuestion,"Confirm Add") = vbOk Then
'Code here to add the patient to tblPatients.
End If

I hope this helps,
Barry

 

[Michel Walsh]

Hi,


Use an unbound combobox, probably in the header or the footer section of the
form rather than in the detail section. Being unbound, typing something in
it won't change anything in any table.

Use the AfterUpdate event of the combobox to push the data where you want.
Assuming the combobox rowsource contains all the data in various columns
(visible or not):


Me.OtherControl1 = Me.ComboBox.Column( 0) ' first column is column 0
Me.OtherControl2 = Me.ComboBox.Column(4) ' ie, the fifth column
...


THAT will change the content of the actually displayed record, if the
controls are bound. If you just want a navigation tool, use the appropriate
Wizard when creating a new combo box.



Hoping it may help,
Vanderghast, Access MVP

 

[CuriousMark]

Thanks very much Barry. I think this is exactly what I need. I will work on it.

CM

 

[CuriousMark]

Thanks Mike. I'm following you...kind of. I'm not sure if this tells me how
to let the use create a new record if the name entered is not already in the
database.

CM

 

[Michel Walsh]

Hi,


To add a new record if a value "not in the list" is entered, you can use the
NotInList event handler subroutine. John L. Viescas, in his Inside MS
Access, or in his Running MS Access (for older versions) has a very nice
example about how to do just that. Basically, you first ask if it is ok (can
be a typo error, after all), and if the end user confirm it is a value to
add, you just add. Sure, you also play with the Response argument of the
NotInList event to tell Access what to do next. As condensed example is also
supplied at


http://msdn.microsoft.com/library/d...us/vbaac11/html/acevtNotInList_HV05187720.asp




Hoping it may help,
Vanderghast, Access MVP