Hide Key? But it won't stay hidden!! 1

[sawilliams]

I have a subform that details phone numbers. I want to detail "phone_type", "phone_number" and "phone_note". The "phone_type" field on my tblPhones looks at a reference table tlkpPhoneType to get the type of phone (Home, Business, etc.) When I do the Lookup wizard in the table, both the "phone_type" and the "phone_type_id" (autonumber) get pulled. I hide the key at that point, as recommended by the wizard. At the table level all is fine. My problem is my form. When I add the field "phone_type" to the form and run it, I get the "phone_type_id" reporting. So instead of "Home" I get a number like "1".

Help would be appreciated.

 

[sawilliams]

Wait, I had one more thing to mention. If I add the control for "phone_type" to the form as a combo-box, it displays the "phone_type" and not the "phone_type_id" but I need it to be a text box since it is display only and not editable.

Really sloppy but I use a combo-box and hide the drop down arrow with a small text box the same colors as the background of the form. But I have to believe there is a better way.

 

[ChanceD2]

SAwilliams,

We'll do this the 'long way' so you can learn how to mess with settings in the future.

1. Go into design view on your form.

2. Single click on the combox that is bound to the phone_type

3. Look at the properties window for this object

4. Look for the field that says "Row Source Type". It should be currently set to "Table/Query" (do nothing, just notice this setting)

5. Look at the "Row Source" property. This should be either the name of the table or a query. Either way, click on the properties button (the button with ... in it). This will either show the query or it will let you create a query. Either way, look at which field has the ID number and which one has the data itself. (there should only be 2 fields for the example you are doing)

6. Let's make sure the that ID field is in column #1 and the data is in column #2.

7. Now look at the properties window again and find the field that says "Bound Column". It needs to say 2 (because the data is in column #2, right?)

8. IMPORTANT STEP. OK, column widths. Look for the field that says column widths. NOTE: if you do not list a width, Access will adjust according to the widest it need to (hense the data WILL be there). The measurements are in inches. You need to enter EXACTLY this:

;0"

The first column with be displayed. The semi colon represent a movement to the next-right column. The 0" hides the column.

That's it. Are you lost? Was I clear?

HTH

C-D2

 

[Hafa1961]

Have you tried going to the properties of this field(e.g combobox), Format and setting the column widths to hide the autonumber?

Format

Column Widths...... 0cm; 5cm

0cm for your phone_type_id
5cm for your phone_type

0cm hides your phone_type_id

 

[sawilliams]

C-D2:

Thanks for the quick response. This works if the control is a combo box but I need it to be a text box. The user actually adds and edits phone number info by clicking command buttons to open Add and Edit Phone forms. You see there is a bunch of other info that they store about phones (preferred, Assistant name, etc.) but I don't need all that showing in the phone subform. So the Phone Subform is read only but when the "phone_type" appears as a combo-box, users think they can click and make changes.

 

[ChanceD2]

I read your response twice, I don't understand. Take me thru, as a user, and tell me what I am doing and what the expected results are. (unless someone else is able to understand and answers)



C-D2

 

[sawilliams]

C-D2:

Thanks for you time. I have a tabbed input form for my customer. One tab has a phone-number subform on it. The subform has two command buttons, one opens a form to Add a new phone number for the customer. The other button opens a form to Edit the phone number for the customer. The edit button appears on each row for which a phone-number is displayed. The Add/Edit forms have fields for phone-type, phone-number, phone-note, assistant-name, preferred checkbox, etc. The phone-number subform only displays the phone-type, phone-number and phone-note. It is a continuous form, one phone number being displayed per row. Since the subform is used for display purposes only, not for edits, using the combo-box technique that you suggest, makes it appear as if the data can be edited on this form. Clicking on the combo-box even opens the drop-down tho' no selection can be made. If I add the phone-type field as a combo-box and then change it to a text-box, only the phone-type-ID (autonumber) appears. What I had to do was really sloppy. I just did away with the ID fields in my reference tables so "Home Phone", for example, became the key item, not an autonumber. Yeah, yeah, really messy. And of course as my application gets more and more complex, it is biting me in the butt. For some of my reference tables I have had to add back in the ID autonumber and then I use a combo-box to display the item I want and hide the drop-down arrow with a text-box cleverly colored to look like the form in the background.

This is a bigger problem than I describe because I have a dozen of these display subforms. I use this oddball Add/Edit button/form technique because we store so much minutia in each table that is useful for querying etc, but not something the user needs to see on the screen all the time. It boils down to just not enough real estate to display all the info on each of the subforms.

 

[PHV]

Why not joining the phone-type table in the subform's underlaying query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sawilliams]

I really dropped the ball on this thread but my problem persists. I don't know if I should start a new thread or just continue this one. Well, here goes. As the thread states. I have a continuous form that is displaying information from a underlying table. The table (tblPhone) looks up the PhoneType from a reference table tlkpPhoneType. If I add the field for phone type to the form as a combobox, I can get it to display just the PhoneType and not the PhoneTypeID. But if I change the combobox to a textbox, it will ONLY display the PhoneTypeID. So instead of getting a row that says:

HOME PHONE (212) 555-1212 "Note about the phone..."

It displays the PhoneTypeID:

2 (212) 555-1212 "Note about the phone..."

Am I being clear?

 

[PHV]

Did you make sense with my suggestion posted 16 Sep 06 13:52 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sawilliams]

My subform(s) are all pulling from tables.

 

[PHV]

So, pull from queries !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sawilliams]

I did, but the very same thing happens.

 

[PHV]

You did what ?

 

[sawilliams]

Sorry, I got booted out for a bit. OK, I did both.

I built my first form based on the underlying tblPhone table. When I add a combobox control for PhoneType, it's fine and displays the description ("Home Phone"). If I "change type" of the combobox to make it a textbox, I can only get the PhoneTypeID ("1") to display.

So, I built a query based on the tblPhone reporting PhoneType, PhoneNumber. I built a new form based on that query. Again, the PhoneType displays the description ("Home Phone") if I leave the PhoneType as a combobox, but if I change it to a textbox, only the PhoneTypeID ("1") displays. So, in both cases, I get just a number displaying instead of the description.

 

[PHV]

Build a query JOINING tblPhone and tlkpPhoneType.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sawilliams]

For heavens sake! Yes, that did it!!! It wasn't easy to get there but your solution is great. Thanks for bearing with me for so long.