Access 2007 VBA Coding Question

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.

jjc9809
jjc9809Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>[ProductOrdersRec]![Licensee Name]
What is ProductOrdersRec?
0
 
jjc9809Author Commented:
jimhorn,

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.

jjc9809
ProductOrdersRec.jpg
ProductOrders-table.jpg
Product-Table.jpg
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jjc9809Author Commented:
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.

jjc9809
0
 
als315Commented:
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?
0
 
Bill RossCommented:
Hi,

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]?

Regards,

Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.