Create "record selector" form "combo box" from related table 1

[DAGOLF]

I have a one to many relationship between a table (tblPatients) whose primary key is "PatientID" and a second table (tblPatEncounter) which has "EncounterID" as its primary key field. The field "PatientID" is related in a one to many relationship with the second table (tblPatEncounter). The fields "PatFirstName" and "PatLastName" exist as text fields in the (tblPatient) table which contains the PK "PatientID". Upon opening the "tblPatEncounter" table I am able to view the PatFirstName & PatLastName values located in the "tblPatients" table by the following SQL statement:

SELECT DISTINCTROW tblPatients.PatientID, [PatLastName] & ", " & [PatFirstName] AS Patients
FROM tblPatients;

I am now attempting to create a form which is specific to the "PatientID" and is tied or bound to the "EncounterID" of the tblPatEncounter table. While I am able to do this successfully, I am only able to view the value of the PatientID in the form (i.e., 3,4,5,6) when I would like to see the values of PatFirstName & PatLastName which show up in the table as described above.

On a practical level - the form I am designing is to render information on an encounter with a patient. I would like to be able to select the encounter form by a combo box showing the patient's name. I am only able to create a combo box from this relationship that will reveal the (autonumber) assigned PatientID located in the original Patient table.

I have tried many combinations of SQL statements similar to the one above without success. I have also attemtped an unbound text box and unbound combo box.

Bottom line - Is there a way to do this?

Thanks, DAG :-D

 

[petrosky]

Hi Dag,

Here is what I have found worked in the past.

If you already have a form set up from this one to many relationship IE. one patient has many encounters. I would put an unbound combo in your forms header to use as the search field.

Invoke the query builder for the rowsource of the Unbound combo. Add tblPatients to the query builder then drag PatientID, PatFirstname, Patlastname into the builder.
Choose ascending under the Last name to make your searching easier if you like.

Now close the query builder.
Make your Column count 2 and your coloum widths 0,2.5"2.5"
Make your bound column 1

In the after update Event of your new combo put this code...

Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PatientID] = " & Me![Combo8]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

'Replace Combo8 with your ComboName

Now when you select a name from the drop down your other info should be visible.

If I have missed your point please let me know.

Regards,

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[DAGOLF]

Peter:

I really I appreciate your info which was right on the money. I have one more question (a common but all to often cry from the unknowledgable). Now that I am able to select an Encounter record from the combo box by patient name, I would like to scroll through various Encounter records with the ability to see the patients name scroll with it. The Unbound Combo box brings me to the patient's encounter record and I do have a bound combo box which scrolls when I navigate with the unbound combo box. However, if I use the navigation buttons to move through the records the unbound combo selection box obviously does not update itself. Is there a way to have a bound text box do that work for me since I do not want the user to change the PatientID on the Encounter record by using the bound combobox. The result would be obviously that the user could change the encounter to apply to a different patient. Any suggestions as a work around? Sorry to bother again and appreciate your help on the first go around.

Sincerely,

DAG

 

[petrosky]

Hi,

If your encounter info is entered into a sub form you could change the subform to have encounter info entered in via a combo box.

This means that you would go to design mode on the subform and right click on the text box/s you enter the encounter info into and change to combo.

Now set this/these combo boxes row sources to the encounter table to display the appropriate info using the query builder (again you can sort the records ascending)

Now when you start to type in an encounter it will go to the previously typed encounter info quickly.

HTH

Peter
Remember- It's nice to be important,
but it's important to be nice

 

[wma]

Okay I have three forms laid out one is Character, other Faction and last job. Ok I have on the character form the faction is in a combo box. when you click on the arrow you can see the factions and click on one, but it coninues for every record as the same faction. which I don't want it to do I want to be able to select a new faction for each character is there some way I can do this.