Subform shows a number, not text...

[jesusoneez]

It's my understanding of general database practice that an Autonumber field should be used as a primary key in normalisation tables. I realise my understanding could be wrong, and I'm quite happy to change that practice if need be.

I have my normalisation tables. For the most part these consist of an Autonumber field ([UID]) as the primary key, then an indexed, no duplicates text field with the normalisation data. For this case, we'll say it's a list of departments ([DepartmentID]).

I have a form for entering users. Again, the PK is an autonumber field. I used a wizard to create a combo box. I selected the fact that I want to store the value in [DepartmentID], NOT [UID].

This works fine, I can add my users and the combo boxes work, although I had to change the bound column to 2, as 1 is selected by default although it is [UID].

I then created a subform based on a query of all users. This subform is not bound to the main form in any way, it simply shows the result of the query.

When entering users, upon saving a record, the form is updated and the new user shown in the subform. Great, apart from "txtDepartment" in the subform showing [UID], and not [DepartmentID] as stipulated.

I have a horrible feeling this is to do with [UID] being my PK, but I'd like someone to tell me I'm wrong. I can quite happily remove the autonumber field [UID] and make the unique [DepartmentID] field the PK, but this just doesn't seem "correct" to me.

Should I do this? Should I have an autonumber field as the PK?

I realise this is more a design theory question than a form problem as such, but anything that clarifies my understanding of correct design practice will help.

EDIT:- I'd also like to say that I did it this way to get away from using the "LOOKUP" field type at table design time, specifically to avoid this very problem of numbers showing up when I want the text there!

 

[gemma-the-husky]

i think you are misunderstanding the lookup keys - try posting the dbs if its not too large - its not connected with the autonumber keys

a bit of explanation

your sub table should like to the main table via the lookup key of the main table. This OUGHT to be the numeric (autonumber) reference, not a text string. In this way, you can change the text string without having any side effects on the database

your combobox shows the the numericreference and the textdescription (or more depending on how you used the wizard, but hides the first column

the wizard does this (and recommends this), by using the numeric ref, but displaying the text ref. by changing the bound column to column 2 you are stopping this working correctly.

now in your form based on the main form, you are now only seeing the numeric value of the lookup. a few options here

either - base this form on a query that draws the numeric lookup back together with the textdescription. (probably the most usual way)

or - if the combo box is available on this or another form (perhaps a parent form), you can get at the text column by using the appropriate column of the combobox (zero based)

or even - use a dlookup to get the appropriate text value when you need it

if you are referring to a combo box
combobox.column(0) is the first column (often hidden) in the combo box
combobox.coumn(1) is the second column of the combo box

 

[jesusoneez]

Sorry, I made a mistake...my bound column in the combo box is 1, and so is looking at the text [DepartmentID].

In the subform, I removed entries in the "Link Child/Master" so as to allow the query to show results.

This may have been a low-down and dirty way of doing things, but beforehand, the query showed nothing.

I've attached the database. Zeus is the backend and Hades is the front end...you may need to link the tables again...not sure.

Thanks for having a look.

 

[jesusoneez]

Hi, I don't know if by saying "Sorry, I made a mistake..." that you thought I'd solved the problem. I just made a mistake in stating that I'd bound column 2 in the combo box, not column 1. I've still got the same issue of numbers in my subform and can't figure out why.

I've tried some of the stuff you said but I still can't see where the problem lies.

Thanks,

Steve