MS Access Combobox "#Name?" error

0
votes

I have a problem with a combobox in a form, which displays a "#Name?" error whenever I enter it to select a value.

I am creating a form in MS Access (specifically in Microsoft Office Professional Plus 2010) that is a general user interface, meaning that the form itself is not linked to a RecordSet of any kind. In that form, I create a Combobox, which I link to a "TblSubsystem" table. The table is very simple and designed like that:

  • ID (primary key, autonumber)
  • SysShortName, text
  • SysFullName, text

When I try to select a value in the dropdown list, the testbox of the combobox just displays "#Name?" (actually as soon as I select the combobox, even before I select any value)

The relevant (I think) parameters of the combobox are:

  • Control Source= "=[TblSubsytem]![ID]"
  • Row Source= TblSubsystem
  • Row Source Type= Table/Query
  • Bound Column= 2
  • Column Count= 2
  • Column Width= 0cm;1cm (I am trying to select and show the short code, but to get the ID as a value for the combobox)

I have another form in the same project, in which a identical setup (also on TblSubsystem) works just fine, with the same values for these parameters, but that Form is linked to a RecordSet (another table). As far as I can tell it is the only difference, so I guess I must be missing something around that, which will seem obvious once I see it...

I have looked for variation of "Ms Access combobox #Name? Error", on stackoverflow and more broadly on the net, but in two days, I haven't seen any post answering my questions. Some are close, but refer to a much more complicated setup, while I think mine should have been pretty straight forward. Trying to adapt the solution to my case (includind recreating the combobox or decompiling the database) didn't help.

Here it was, long question, sorry about that, but hopefully precise enough for some of you guys to help on that. I thank you in advance for any help I can get on that.

1
formsms-accesscombobox

1 Answers

0
votes

#Name is a binding issue. Try setting your bound column to one, that way it only records your primary key and see if that works.

Edit: You've got the combobox referencing your table's primary key as your control source. Keep in mind the control source is the value that is being altered by the application user. Here are some key things to think about when working with comboboxes:

  • The control source should (typically) reference the foreign key of your table
  • The row source should reference the text that you want displayed in the box
  • Bound column should be set to 1, unless your application design requires you to update two fields simultaneously, which is an uncommon practice.
  • Set your column width to 0;1. This will hide the first field in your row source, which should be your foreign key

An example would look like this: Control Source: EmployeeID --123456 Row Source: EmployeeID, EmployeeName --123456 | John Doe

A bound column set to 1 would record the value 1234556. A bound column set to 2 would record 123456, John Doe