2

I am trying to source a value in a form from the table in the code below 'tblJobTitles'. The value I would like to source is the value 'JobTitle' Unfortunately, in the control box i keep getting #Name? error. I am trying to adust the properties but everthing i try gives me the same error.

Note: if i attempt to do this without joining 'tblContacts' to 'tblJobTitles' and using only 'tblContacts', I can successfully retrieve the job title in the field on the form, only that it returns the numerical unique ID number from the job title table. Is it possible to reformat it to return the text value?

sql_get = "SELECT tblContacts.*, tblJobTitles.* FROM tblJobTitles INNER JOIN (tblContacts INNER JOIN tblTeams ON tblContacts.Team = tblTeams.ID) ON tblJobTitles.ID = tblContacts.JobTitle WHERE [tblTeams].[team]= '" & cboDepartments.Value & "'"
Me.frmstaticdatadepartments08.Form.RecordSource = sql_get
Improve this question
16
  • Do us a favor, after sql_get, write Debug.Print sql_get. Put a breakpoint on that line. Let us know what it prints out Commented May 1, 2015 at 12:00
  • you want me to put Debug.Print slq_get AFTER the line 'Me.frmstaticdatadepartments08.form.recordsource = sql_get' ? Commented May 1, 2015 at 12:04
  • No, before please. Between sql_get and for form record source assignment Commented May 1, 2015 at 12:05
  • SELECT tblContacts.*, tblJobTitles.* FROM tblJobTitles INNER JOIN (tblContacts INNER JOIN tblTeams ON tblContacts.Team = tblTeams.ID) ON tblJobTitles.ID = tblContacts.JobTitle WHERE [tblTeams].[team]= 'Milan Tax Services' Commented May 1, 2015 at 12:08
  • 1
    That is your problem then. What you can do is select the fields like you've done, and give them aliases. tblContacts.JobTitle AS ContactsJobTitle, tblJobTitle.JobTitle AS [Job_Title] or something. The fact that there's 2 similar fields and you're simply selecting all (*), it's confusing Access. Commented May 1, 2015 at 12:30

1 Answer 1

Reset to default
2

The #Name error can have a couple different sources. This means Access can't delineate a field or property because :

  1. It shares a name with something, whether that be a control name and a control source, etc.
  2. It can't find the field you're referring to (such as in this scenario)

So, in your query, you're saying, "Give me everything from these 2 tables."

When you have 2 identical fields and you run the query and view the results, Access will pre-pend the table name to the field. (tblContact.JobTitle, and tblJobTitle.JobTitle). This is because it recognizes you have overlapping (identical) field names in both tables, so it needs to identify them as unique values. You probably could have gotten away with adding the table name in front of your control source, but it's better to be explicit where you can be.

When you were referencing the field JobTitle in your control, Access was wondering which field you were referring to, because JobTitle is in both of your tables. This is a reason some people uses aliases e.g: tblContact.JobTitle AS ContactJobTitle, tblJobTitle.JobTitle AS [Job_Title]. This let's you be very explicit and tell Access exactly which field you're referring to.

You should be explicit when selecting fields from a query as well.

SELECT Table.* is known to have performance issues, and you lose the ability to adequately refer to which field you want to select from if you're use these fields in a Control (when there's overlap in the tables).

Thanks to @HansUp as well for the correction.

Hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The query returned one field named tblContact.JobTitle and another field named tblJobTitle.JobTitle. His Control Source asked for a field named JobTitle. As far as Access was concerned the query result set did not include a field named just JobTitle, hence name error. It's not a circular reference problem.
@HansUp I thought that made it a circular reference error, because Access can't determine which JobTitle he was referring to? I do understand exactly what you're saying though and can see how my words could not make sense in this scenario
Circular is different. SELECT fld1 & fld2 AS fld1 would probably trigger a circular reference error. The #Name error is simply because the query does not include a field named JobTitle. He would get the same error if he changed the Control Source to look for BOGUS. Nothing circular about it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.