asked on

Microsoft Access 2013 / SQL Server 2012 back end subform with #Name?

I have a subform that displays a list of orders and/or quotes based on the parent form criteria selected. Many criteria on the parent form can be selected to query specific records. the SQL is created with VBA concatenating Insert statements inserting records in a local Access table. The recordsource for the subform is a microsoft query. The query executes fine with no errors. The subform, however, displays #Name? in all fields except one. The one field displays "Type".

The recordsource for the one field displaying "Type" is:

=IIf([sob]=1,"NC",IIf([sob]=2,"KI",IIf([sob]=3,"FC",IIf([sob]=4,"PT"))))

When the form is first opened and criteria selected and the "Display Records" button is clicked, the form displays fine with no issues.

However, when the criteria changes (i.e. choosing quotes vs orders) the #Name? displays. Users have to exit and reload the form to remover the error.

I made sure that the control names on the subform are not the same as the field names.

Thanks for any suggestions.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phil

ASKER

The SQL is created with VBA and it will change with each modified selection of criteria. When the form first opens and quotes with the company name having the characters "pet" in the name, it works fine. Then they select orders with the same name criteria and we get the errors. Then if we reselect to the original criteria the errors persist. So the exact same record source works when the form first opens and then fails from that point on if the recordsource is changed.

SOLUTION

Adam D

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phil

ASKER

Yes, the recordsource was the problem. I set the recordsource to "" (nothing) and the reconnected to my SQL and it works fine now. Thanks so much to you all.