Combo box to select record & show browsed record

[JJeffH]

I have a form for inputting activities. The main form is based on tblPatients with a subform based on qryActivity showing services provided to patients by volunteers. The query includes all the fields in tblActivity plus the PID field in tblPatients. The main form is not for updating tblPatients information, just the subform is used to update tblActivity.



The relevant fields in tblPatients are PatientID (Primary Key) and PID (a unique alphanumeric identifier using the patient’s initials and a 2-digit number). tblPatients and tblActivity are related 1 to many on PatientID and PatientID_FK respectively.

I want the user to be able to select a patient record by clicking or typing in a PID field drop down box which displays the PID, First Name, and Last Name, sorted by PIDs in ascending order.

The idea is that the users will know the patient’s initials, so typing the initials will bring them to the place in the drop down list where everyone with those initials are and they can pick the correct name. Solution #2 below does that, but I also want users to be able to use the navigation spinner at the bottom of the main form to browse records and #2 doesn’t reflect records selected in that manner.

I’ve made several unsuccessful attempts. For testing, now, I have 2 combo boxes on the main form to compare.

1. The first uses the PID field in tblPatients for the Control Source, with a Row Source using PatientID, PID, First, Last in order to sort on PID.

2. The second has no Control Source, just an unbound combo box with a Row Source configured as a Lookup field. The Lookup uses the PID, First, Last, and PatientID fields from tblPatients.

#2 works well for selecting a patient record by clicking or typing, as I want it to work. But when I use the navigation spinner at the bottom of the form to scroll through records, this field doesn’t change.

#1 does reflect the record number of the navigation spinner but it can’t be used to select a record. And worse, if I type in that box, it changes the tblPatients PID field of whatever record I’m on to the PatientID number of the record whose PID I typed.

I’ve tried every combination I can think of over the last few days and found explanations online, but there’s a critical piece I’m missing.

I’m new to this forum. I hope I’ve explained it ok and someone can steer me in the right direction.

Thanks,
- Jeff

[Gicu]

Hi Jeff,
Welcome to the forum! I think #2 is the right solution, a "navigation" combo box should not be bound (because if it is you will be altering the data every time you do a search as you realized it is happening for #1). Put the Search combo box in the Header of the form and label it properly (Search Patient by PID) and empty it after each search to avoid confusing the user.

Cheers,
Vlad

[JJeffH]

Thanks Vlad! What a simple, effective solution.

Can I ask one more thing? I don't know the best way to clear the field after a selection.

Here's my story: I'm pretty good with Excel/VBA and I have quite a few years experience in that arena (not to imply I'm expert -- it's more like a hobby). I have already created this database in Excel for a hospice volunteer organization, and it does everything I want it to. The thing is, for many years I've wanted to gain some solid grounding in Access. When I was working for a multi-program human services agency, I made many Excel and Access databases for various uses around the agency. Trouble is I have never "clicked-in" to Access, much to my regret. So now, in retirement, I'm hoping to transition this hospice Excel database into Access. I'm starting with the bare bones basics, mostly trying to understand how to create effective relationships and leverage them as the application grows.

- Jeff

[Gicu]

Hi Jeff,
In the AfterUpdate event of the combo box just add Me.comboName=Null after the line(s) of code that do the searching. Here is an example:

Private Sub Combo2_AfterUpdate()
On Error Resume Next
Me.PatientID.SetFocus 'assumes you have a visible text box bound to PatientID
DoCmd.FindRecord Me.Combo2
Me!Combo2.SetFocus
Me.Combo2= Null
End Sub

Cheers,
Vlad

[JJeffH]

Yes sir! That does it. Thanks Vlad.

- Jeff

[Gicu]

You're welcome Jeff,stay safe!

[JJeffH]

Vlad, I have an update. I ran into trouble when I tried this in the Volunteers form. I created the combo box in the form header and it worked for the lookup at first. But when I added the code for clearing the field it stopped finding the record.

Creating the combo box automatically set up an event procedure for the control, but I couldn't figure out how to work with the Macro Builder to edit it. So, just like on the Activity form, I deleted the event procedure and used the "..." to select Code Builder (VBE) which is more familiar to me.

The Form_frmVolunteers class object was already created and I navigated to the "SelectVID" control and selected AfterUpdate. Then I copied in the exact code that worked in the Activity form and updated it for the Volunteers form, but it wouldn't find the record.

Private Sub SelectVID_AfterUpdate()
    On Error Resume Next
    Me.VID.SetFocus 'the VolunteerID text box is present but not visible
    DoCmd.FindRecord Me.SelectVID
    Me!SelectVID.SetFocus
    Me.SelectVID = Null
End Sub

I added a debug.print to be sure it was capturing the correct record number from the SelectVID control (and it was). But then the DoCmd.FindRecord Me.SelectVID line simply didn't work.

Any thoughts?

- Jeff

[Gicu]

It doesn't work if the control is hidden, you need to make it visible (you can lock it if you want to prevent accidental edits).
Cheers,

[JJeffH]

Hmm. I made the VolunteerID visible and changed the code and it worked. I'm still puzzled because I used the PID field instead of the PatientID on the other form and it worked perfectly.

Anyway, sorry to bother you again without having at least tried that. I appreciate your help.

- Jeff

[Gicu]

Hi Jeff, you cannot set focus on a hidden control and Docm.FindRecord searches the active control (actually its control source), therefore the issue.
No bother at all!
Cheers,