Access 2007 VBA Coding Question

Posted on 2012-08-21
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 65

    Expert Comment

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

    Author Comment


    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

    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.

    LVL 39

    Expert Comment

    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

    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 84

    Accepted Solution

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now