Getting #Name? in subform field

[wardw]

I have a main form showing an employee name. A subform lists that employee's phone number(s).

I'd like to show in the subform what "type" each phone number is: Office, Home, or Mobile. So I have a table listing the three types, with a PhoneNumberTypeID as a primary key. I've added a Type of Number field to the subform to show the type. But it seems no matter how I tweak the Control Source for that field, it shows either nothing or #Name? there. I am able to show the PhoneNumberTypeID correctly, but that's not useful for form users.

In case it's useful, I've enclosed the relationship diagram showing the three tables in the database.

[Dal Jeanis]

Method 1: Your subform is bound to a query against the PhoneNumbers and PhoneNumberTypes tables:

SELECT PN.PhoneNumber, PN.PhoneNumberTypeID, PNT.PhoneNumberType
FROM  PhoneNumbers AS PN, PhoneNumberTypes AS PNT
WHERE PN.PhoneNumberTypeID = PNT.PhoneNumberTypeID;

Then you bind PhoneNumber to one txt field, PhoneNumberType to another txt field. The second field is visible, disabled.

If you want the user to be able to change the type, then you add a combo box control bound to the PN.PhoneNumberTypeID,
and on the AfterUpdate event, change the value of the second field.

The Row Source of the combo box control would be

SELECT PNT2.PhoneNumberTypeID, PNT2.PhoneNumberType
FROM  PhoneNumberTypes AS PNT2
ORDER BY PNT2.PhoneNumberType;

It would have bound column = 1 (PNT2.PhoneNumberTypeID), and Control Source would be PN.PhoneNumberTypeID from the subform's query, and columns would be 0.5";1.5" or something like that.

Make sense?

[wardw]

Thank you, Dal, for a thorough response. I created a query (PhoneNumbersQuery):

SELECT PhoneNumbers.PhoneNumber,
PhoneNumbers.PhoneNumberTypeID,
PhoneNumberTypes.PhoneNumberType
FROM PhoneNumbers AS PhoneNumbers,
PhoneNumberTypes AS PhoneNumberTypes;

The result of the query looks good: Each record shows a PhoneNumber, its PhoneNumberTypeID, and its PhoneNumberType (Office, Home, or Mobile).

Then I tried to create the subform using a control wizard. When I chose the fields to include, I chose Query: PhoneNumbersQuery, and then from that query I chose the fields PhoneNumber and PhoneNumberType. On the next page, I chose "Show PhoneNumbers for each record in AllContacts using PersonID". But when I clicked Next an error message appeared: "The link you selected can't be used. This link was created based on relationships between source tables. To use this link, you must go back to the previous page and include the missing fields." I've tried including other fields that seemed to make sense, but the error message continued to appear.

I tried building the subform manually as its own form, and then dragging it onto the main form, but it doesn't seem to connect to the main form properly. I assume I've made some mistake building the subform. Can you see where I've gone wrong?

[June7]

To retrieve PhoneNumberType you must join tables in query. There is no join clause in your query.

SELECT PhoneNumber, PhoneNumbers.PhoneNumberTypeID, PhoneNumberType FROM PhoneNumbers LEFT JOIN PhoneNumberTypes ON PhoneNumbers.PhoneNumberTypeID = PhoneNumberTypes.PhoneNumberTypeID;

The phone type descriptors are very short. Instead of saving the ID, alternative is to save the actual descriptor. This removes the issue of lookup alias and no need to join tables to view the related descriptor. That's what I would and do use quite frequently.

[Dal Jeanis]

Sorry, my fault. I accidentally deleted the WHERE condition while writing my prior post. It's back now. See post #2.

You probably actually got multiple records for each phone number, because there was no bind criteria.

By the way, Ward, you don't have to alias a table to its own name - I just alias them to short 2-3 letter aliases so that it's easier to read.

SELECT PhoneNumbers.PhoneNumber, PhoneNumbers.PhoneNumberTypeID, PhoneNumberTypes.PhoneNumberType
FROM PhoneNumbers, PhoneNumberTypes
WHERE PhoneNumbers.PhoneNumberTypeID = PhoneNumberTypes.PhoneNumberTypeID;

The one thing I'll point out is that, for this purpose, you want the PhoneNumberTypeID from the PhoneNumbers table, because that one you can change, and it will change what the record is bound to in the other table. Changing the other one would make a mess.

[wardw]

Thanks, June7. I ran your query, but Access had a complaint: "The specified field 'PhoneNumberType' could refer to more than one table listed in the FROM clause of your SQL statement." Also, the relationships diagram for the query appears to show a reversed join. Does this look right?



Here are the join properties:



I tried being more specific in the query, so I changed it to this:

SELECT PhoneNumbers.PhoneNumber, PhoneNumbers.PhoneNumberTypeID, PhoneNumbers.PhoneNumberType
FROM PhoneNumbers 
      LEFT JOIN PhoneNumberTypes 
         ON PhoneNumbers.PhoneNumberTypeID = PhoneNumberTypes.PhoneNumberTypeID;

No error message, and the query result shows phone numbers and phone number type IDs, but blanks in the PhoneNumberType field.

[Dal Jeanis]

The last item in the select clause has the wrong table name. It shouldn't even be in that table.

[June7]

If you are saving the PhoneNumberID into PhoneNumbers table, why save the PhoneNumberType? Both fields in PhoneNumbers is redundant.

See my last comment in post 4.

"Include all records from PhoneNumbers ..." is the correct join, presuming the PhoneNumberType data is not actually in PhoneNumers table. If it is in there, then no join is needed.

[wardw]

Access is such a black box to me that if I deleted a field from a table I probably wouldn't make changes necessary in other parts of the DB, and some obscure (to me) error would result. Once I get the machine operating properly I'll trim off the redundant stuff.

Right, at this point the PhoneNumberType data is not actually in PhoneNumers table.

If you are saving the PhoneNumberID into PhoneNumbers table, why save the PhoneNumberType? Both fields in PhoneNumbers is redundant.

See my last comment in post 4.

"Include all records from PhoneNumbers ..." is the correct join, presuming the PhoneNumberType data is not actually in PhoneNumers table. If it is in there, then no join is needed.

[wardw]

Thanks for the correction, Dal. I used the query code you suggest below, and constructed the subform using the query, selecting PhoneNumberType and PhoneNumber to appear in the subform.

But on the next page I'm asked which fields link the main form to the subform; an embarrassing question, because the main form has only PersonID, FirstName, LastName, from the AllContacts table. None of those fields are in the subform. If I go ahead regardless and on the next page "Show PhoneNumbers for each record in AllContacts using PersonID," I get the old error: "The link you selected can't be used. This link was created based on relationships between source tables. To use this link, you must go back to the previous page and include the missing fields."

So I (perhaps foolishly) added to the subform the PersonID from the AllContacts table, to act as a link between the forms. No error message, but the result isn't pretty (I made everyone's phone number match their PersonID, like 222-222-2222, regardless of phone number type, just to check results):



The subform should show only a Home, Office, and Mobile phone number (all 222-222-2222) for Harold.

For your amusement, I've enclosed a zipped version of the database so you can examine it better. Thank you for your patience, Dal and June7.

Sorry, my fault. I accidentally deleted the WHERE condition while writing my prior post. It's back now. See post #2.

You probably actually got multiple records for each phone number, because there was no bind criteria.

By the way, Ward, you don't have to alias a table to its own name - I just alias them to short 2-3 letter aliases so that it's easier to read.

SELECT PhoneNumbers.PhoneNumber, PhoneNumbers.PhoneNumberTypeID, PhoneNumberTypes.PhoneNumberType
FROM PhoneNumbers, PhoneNumberTypes
WHERE PhoneNumbers.PhoneNumberTypeID = PhoneNumberTypes.PhoneNumberTypeID;

The one thing I'll point out is that, for this purpose, you want the PhoneNumberTypeID from the PhoneNumbers table, because that one you can change, and it will change what the record is bound to in the other table. Changing the other one would make a mess.

[Dal Jeanis]

This is a clear case that shows why you don't let the control names stay as Access's default names, because then you can't tell the difference between the control and the underlying field. I always change the control names to prefix them with their control type (txtPersonID, for a text box, for example).

You don't need all that crud in the subform's record source. Here's a query to use for the subform:

PhoneContactsQuery

SELECT 
   AC.PersonID AS PhonePersonID, 
   PN.PhoneNumberID, 
   PN.PhoneNumber, 
   PN.PhoneNumberTypeID, 
   PNT.PhoneNumberType
FROM 
   PhoneNumberTypes AS PNT 
   INNER JOIN 
      (AllContacts AS AC 
       INNER JOIN 
       PhoneNumbers AS PN 
       ON AC.[PersonID] = PN.[PersonID]) 
   ON PNT.[PhoneNumberTypeID] = PN.[PhoneNumberTypeID];

You'll notice that I've aliased PersonId in the query above to be called ACPersonID, so you'll be sure exactly what you're linking to.

1) Create the above query,
2) change the Record Source of the subform to the above query, change the bound field of the subform to ACPersonID, and save the subform.
3) Then on the main form, click the subform link control (just outside the top left corner of the subform on the main form) and change the link child field to ACPersonID. Save the changes.

Now test. Should be good.

[wardw]

Thanks for the tip about prefixing the control names, Dal; I'll use that next time I build a database.

Your suggestions do result in correct information in the subform; nice to have that! The subform displays the phone numbers/types for all contacts, but what I'd really like is to have it display only the phone numbers/types for the person shown on the main form, just so users don't have to look through the whole subform to find Harold's or whoever's phone numbers. Assuming that involves a Where clause in the code, I experimented with that by appending to the code "WHERE AC.PersonID=PN.PersonID", but that failed and sent me down another rabbit trail of patching up damage, to no avail.

The Access books sure make subforms sound straightforward; I'm finding that any but the simplest ones really aren't. What I'm trying to do seems such an obvious thing to have, you'd think there'd be lots of examples of it on the Web. I've tried looking at the NorthWinds Traders example, but their interface is way more elegant than mine--subforms are on tabs, not with the main forms, and I can't figure out how to display their property sheets.

Anyway, thanks very much for your help so far, Dal. If you could point me in the right direction to limit my query as described, my toils will end.

[Dal Jeanis]

Okay, fewest steps that will work.

1) Start with the database you uploaded, and make only these changes.

2) create the following query.

PhoneContactsQuery

SELECT 
   AC.PersonID, 
   PN.PhoneNumberID, 
   PN.PhoneNumber, 
   PN.PhoneNumberTypeID, 
   PNT.PhoneNumberType
FROM 
   PhoneNumberTypes AS PNT 
   INNER JOIN 
      (AllContacts AS AC 
      INNER JOIN PhoneNumbers AS PN 
      ON AC.[PersonID] = PN.[PersonID]) 
   ON PNT.[PhoneNumberTypeID] = PN.[PhoneNumberTypeID];

3) change the Record source of the subform to that query.

4) test.

worked for me.

[June7]

I am confused. Why does the subform need a complex query as RecordSource? Why not just bind it to PhoneNumbers table? The Master/Child links properties of the subform container control will synchronize the related main form and subform records.

And have a combobox in the subform for selecting the Type?

A query joining the tables would be means to retrieve the Type descriptor for a report output.

[wardw]

Thank you, Dal; your new query now displays in the subform just the phone no. info for the main form person. My users will need to enter new contacts in the main form, and this works correctly too. But there were some problems with data entry in the subform:

--They'll need to enter additional phone nos. for existing contacts who have fewer than three nos. I tried that, but there were some problems:
1. A PersonID can't be specified because it's an autonumber, and just leaving (New) in the field doesn't save the correct PersonID.
2. (minor) Attempting to fill in the PhoneNumber field gets a message: "The value cannot be added to this new row until the row has been committed. Commit the row first and then try adding the value." Committing the row first does work; the new no. can be typed in.
3. The new info. isn't saved; moving along to the next person in the main form, and then returning to the original contact doesn't display the new phone no. info, and checking the PhoneNumbers table shows the new info isn't saved properly.

I thought I'd at last accommodate June7's comments, so as he suggested I changed the Record Source for the subform to PhoneNumbers, and then changed its Link Master Fields and Link Child Fields to PersonID.

As with Dal's version, this displayed just the phone no. info for the main form contact. In the main form, I was able to successfully add a new contact and the PersonID incremented there and automatically appeared in the subform. But when I tested data entry there were some problems with the subform:

--When the main form showed an existing contact, in the subform the PhoneNumberType field was blank for each of the phone numbers. I was unable to fill in that field for existing or new phone nos.; just a "ping" sound resulted--no error message. (I tried to fix this; see below.)

--The new contact main form information was saved properly in the AllContacts table. In the PhoneNumbers table, the PhoneNumber and PersonID were saved, but (of course) no PhoneNumberTypeID could be saved.

I've tried to fix the blank PhoneNumberType field by making its Control Source the result of PhoneNumbersQuery; i.e., =[PhoneNumbersQuery]![PhoneNumberType], but that just results in #Name? in the subform PhoneNumberType field.

Ideally, in the subform I'd like a list box in the PhoneNumberType field to limit user input to just "Office", "Home", and "Mobile".

I've enclosed the June7'ized database with my PhoneNumberType tweaks, for your amusement.