1

I have a combo box called "manufacturer" that will update the combo box called "productName" but when I select the manufacturer, I get a prompt asking for the parameter. The SQL statement for the ProductName query is below.

SELECT ProductsTable.ProductID, ProductsTable.ProductName, 
ProductsTable.ManufacturerID 
FROM ProductsTable 
WHERE (((ProductsTable.ManufacturerID)=[Forms]![NewComplaint]![manufacturer])) 
ORDER BY ProductsTable.ProductName;

When the Manufacturer is selected, this is the prompt that is displayed.

Enter Parameter Value (Image)

Andre
  • 26,751
  • 7
  • 36
  • 80
Benjamin Mills
  • 41
  • 5
  • Double-check form name and control name. And if it's on a subform, you'll have to address that too. – Andre Oct 11 '17 at 21:49
  • Form and control names are both correct as these have been checked and suggested by the Expression Builder in Access. There is also no subform. – Benjamin Mills Oct 11 '17 at 21:50
  • Hmm. Is there code in the AfterUpdate event of `manufacturer` to requery `productName`? -- Also, a screenshot of design view of the form might give a hint. -- Or as last resort, if you can upload a sample database to a file hoster. – Andre Oct 11 '17 at 22:00
  • Do you have a product name in the combo box? That is not an ID. In the properties of the combobox, look for the bound column value. – smoore4 Oct 11 '17 at 22:01
  • Andre: the code to request is there in the afterUpdate. Everything works when I substitute the value in the prompt but what appears to be happening is it not taking the manufacturerID from the "manufacturer" combo box – Benjamin Mills Oct 11 '17 at 22:05
  • 1
    There are some things that can go wrong with multi-column cascading combo boxes. But this prompt is simply Access' way of saying *I can't resolve `[Forms]![NewComplaint]![manufacturer]` - please give me the value*. – Andre Oct 11 '17 at 22:28
  • So how would I resolve it? For some bizarre reason, if I make a new database with only 2 combo boxes and different tables the same method works perfectly? – Benjamin Mills Oct 12 '17 at 05:59
  • This is one of those question that usually can only be solved in two ways: either you find the problem yourself, or you upload a sample database to a file hoster where someone can take a look at it. – Andre Oct 12 '17 at 07:05
  • I'll upload the database asap. It's one I'm using to experiment with so the content isn't really a big issue as this will be changed later – Benjamin Mills Oct 12 '17 at 11:21
  • I uploaded the database to here: http://www.filehosting.org/file/details/699093/A1_DB1.accdb – Benjamin Mills Oct 12 '17 at 12:58

1 Answers1

0

The solution was quite unexpected: your database is corrupted in a way that I haven't seen yet (AFAIR).

After opening the form, I tried a few things in the Immediate Window that usally work without problems, and got various errors:

? Forms!NewComplaint.Name

Automation Error

? Form_NewComplaint.Name

Not enough memory

Compact & Repair and Decompile didn't help.

Solution: Create a new database, import everything from the existing database.

Now the combo boxes work without problems.

See http://allenbrowne.com/ser-47.html
(and for possible future problems: http://allenbrowne.com/recover.html)

Andre
  • 26,751
  • 7
  • 36
  • 80