How to make both entries in a listbox with 2 columns both save.

[LoveDad]

I am creating a database to track and log patient's attendance in various groups. This data will be used to write a quarterly group participation note.


I have created a listbox using a query, it displays 2 columns (LastName,First Name and ID#). The query is based of my tblPatient.

I am able to get the listbox to display correctly on my form, however, I am unable to make both entries save in their respective columns in my tblHistory.

Thank you,
Ann

[NeoPa]

You can have only one bound column for a ListBox or ComboBox Ann. I'm not really clear on what you're looking for or why you would be using a ListBox to store multiple items of data.

If I had to gues I would say you are probably trying to store the name data instead of the ID. That idea is a problem in itself. Check through Database Normalisation and Table Structures for an explanation of why I say that. I suspect you need to redesign how you hold and access the data you're storing.

[LoveDad]

I was hoping to have both "name" and "ID #" store in my table history.

Reason:

My co-workers would be able to reference a record/patient based on their ID# (which is only 5 characters) vs. their full name.

However, I would like to be able to have their full name view (on forms and/or reports) when the patient is referenced by their ID#.

I have been able to build a relationship between tblPatient and tblHistory to help me on the reports, but I'm have been unsuccessful in making this work with forms.

Am I going about this in the wrong way by using a listbox? Should I be using a subform?

[NeoPa]

That link would have helped you understand the answer to this one Ann, but let me see if I can throw some more light on it for you :

What you store and what you show are entirely different from each other. While it makes good sense to show the name as well as the ID in your forms and reports, it makes very bad sense (None at all in reality) to store that name in related or linked tables.

One example to consider that may help perception of this problematic issue :
Consider a system where you have tens (or more even sometimes) of tables that hold data for an item (We'll consider a person here for easier understanding). A young lady is being reflected in this data. One day she marries and, as is quite frequently the case in western society, adopts the surname of her spouse. What happens now to all the tables where her data is stored? Instead of simply updating the record in the main table used for storing people's details, your project would need to involve multiple updates across all the tables where that data is stored. In a real system where multiple people are working in different areas simultaneously there's a good chance that one or more of the records across all those tables will be locked. What happens then? The only safe approach is to cancel the update and inform the user.

This is just a small look at some of the problems associated with non-normalised data. It gets messier and messier the more you build it up - the more interactions become dependent on each other.

A ListBox, and even a Sub-Form, are not required to show this detail. Simply include the linked data in your record source.

[LoveDad]

Thank you. After carefully reading your second post, and re-reading the first and the link I realize my thinking was wrong. I have some ideas and will try them at work on Monday.

[NeoPa]

Very pleased to hear that. Don't get me wrong, all you ask is possible. It just takes increasing amounts of complexity to make things happen that should be much simpler if done in a way prescribed by those who've worked this out for us.

Eventually you really wish you'd gone the other route. I just want to save you from getting to that point and having to start again after having travelled an already long road.