[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access 2007 VBA Coding Question

Posted on 2012-08-21
Medium Priority
Last Modified: 2012-08-21
Hello Everyone,

I am getting a message on this VBA Coding when the computer is executing the code:

The Code:  

Forms![InvoiceNewSUbform]![Licensee Name] = [ProductOrdersRec]![Licensee Name]
Forms![InvoiceNewSubform]![StoreNo] = [ProductOrdersRec]![StoreNo]

I am getting  Run-time error '2465':
Microsoft Office Access can't find the field '[' referred in your expression.

What is causing this error and why doesn't my VBA Code work?

Any helpe is appreciated.  Trying to learn VBA and VB.Net at the same time.

Getting confused.

Question by:jjc9809
LVL 66

Expert Comment

by:Jim Horn
ID: 38317104
>[ProductOrdersRec]![Licensee Name]
What is ProductOrdersRec?

Author Comment

ID: 38317143

ProductsOrdersRec is a select query where we are using the Product and Product Orders tables linked by a Primary Key on the Product Table which is Code.

Please see attached.


Author Comment

ID: 38317174
We are trying to capture the Licensee name and Store Number as orders are taken.  We have an initial Orders Table that is not populatkng with the Licensee Name and Store No.  We need these two items populated by each order taken so we can do reports on te data.

In COBOL we would have Working Storage Area for Lciensee Name and STore Number as records are added we would store each order and move the record each time to a working storage area before writing the record out each time somewhere.

This is what I am trtying to do.

I need VBA coding to do this.

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 40

Expert Comment

ID: 38317237
Where are you trying to use this code? On form's event? As I can see InvoiceNewSubform - is some subform on your form. May be better to use master-child relation between main form and subform?
LVL 14

Expert Comment

by:Bill Ross
ID: 38317244

You are referencing a form on the left side of the expressions. On the right side of the expressions there is no reference to a form so where does this code reside?

Forms![InvoiceNewSubform]![Licensee Name] = [ProductOrdersRec]![Licensee Name]
Forms![InvoiceNewSubform]![StoreNo] = [ProductOrdersRec]![StoreNo]

The code should read:
Forms![InvoiceNewSubform]![Licensee Name] = Forms![ProductOrdersRec]![Licensee Name]
Forms![InvoiceNewSubform]![StoreNo] = Forms![ProductOrdersRec]![StoreNo]

if the right side of the expressions is referring to another open form.  If not then what is [ProductOrdersRec]?


LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38317689
ProductsOrdersRec is a select query
You can't refer to a Query in this manner. You would need to somehow manifest the query through a Recordset or Form, and then you could use that to fill the values in your form:

Dim rst As DAO.Recordset
Set rst = OpenRecordset("SELECT * FROM ProductsOrdersRec WHERE SomeField=SomeValue")

Forms![InvoiceNewSUbform]![Licensee Name] = rst("Licensee Name")

I'd encourage you to NOT use spaces in your names, however - Licensee Name should be LicenseeName, or Licensee_Name.

Trying to learn VBA and VB.Net
Why? You can't use VBA in .NET, and you can't use .NET in VBA. Some of the concepts apply (like loops, arrays, etc) but they are two different and distinct languages, and share very little.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question