Unanswered: Help storing combbox text value not ID
I have a db (attached)
which has three comboboxs which are all related to their relevent tables.
i.e Employee combobox links to employee table etc...
the three comboboxes output to a table calls CMS Compliance Table, (which is where i want to store my records)
however the data stored in their is the "ID" from the tables and not the text values of the fields.
I want to be able to store the text values in the table
for example i select (the numbers are the ID column which is hidden)
In the properties for your combo boxes you have set the bound column as column 1. The first column in the result set from your query is the id. Hence that is the value of the combo box even though you have hidden it and displayed the other two columns instead.
The solution is simply to set the bound column to 2 (on the data tab of the properties dialog) or adjust your SELECT query so that the name is the first column.
However as a comment about best practice it is normal to store the foreign key (id) and not any of the attributes. One reason (unlikely in your case) is if the name changes (oh, I don't know - perhaps a female emplyee gets married). The id remains the same and you automatically retrieve the new name. Another reason is that you are certain of the backward audit trail. Say you have more than one 'Fred' on your source table, you don't know which one it is.
I appriciate the benefits and good practice of a normalised database, however on this occasion, i use the table to export into a excel pivot table hence the requirements for text value of the comment box. the form is used for data input only at the moment.
do you know if i can get the text into the table or can you suggest a way of using a normalised database, but when exporting it fetches the text values via a query.
Sorry im a bit lost, do you mean ammend my SELECT query or create a query that will bring all the data together?
if its the later can you explain how i can do this becauase when i try this in the wizard it says i cant bring in data from two different tables
I don't know the structure of yor project, so I can't be helping too detailed. but you could create a query without wizard and add the tables you want into this query. then select the fields you want to display. then export this query to excel.
Methinks this man knows too much, is he here to test us. I never said 'normalised' and I'm sure neither did you. The use of the 's' indicates he comes from the east side of the Pond.
scottemotive: yes, wonderful example, one of the few situations where you need to carry the actual, resolved value forward to the next process. Pivot tables, eh? In my days in market research they used to call them cross tabs.
Great observation, Yes im from UK, thank you for your appriciation, however im really just finding my way with this database, ive not really created one before so im learning things and possibilities as i go.
I have two paths i can either stick with a database that stores the values (ID's) to which i will need a query to tie in the information when i export
or i need someone to look at my code for the three comboboxes, i can change the bound column on the employee cbo and this will store the selected result (i.e the employee text) but this causes problems with the selection code of my other cbo's even after changing the bound columns on them.