By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Understanding syntax (misc.)

100+
P: 176
I've been working with the OpenForm method with criteria, and couldn't combine it correctly, so I've searched online and found this as being the correct:

DoCmd.OpenForm "Customers2", , , "Id=" & Me.Customers1!Id

and not the one I tryed at first:
DoCmd.OpenForm "Customers2", , , Id = Me.Customers1!Id

I'll appritiate an explanation as to what is the reasoning behind the correct syntax. I don't understand why there are the inverted commas for "Id=" and not around the whole criteria, and why there's the & sign.

Where could I learn about correct syntaxes for other cases/methods?

Thanks!
Dec 7 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
"Id=" & Me.Customers1!Id


The "Id=" refers to the record on the form you are opening.
& is a way to join two string values together.
Me.Customers1!Id refers to the control on the current form.

This code will operate when the new form is opened but it won't be able to see the value on the current form so you have to pass the value into the string.

Assuming the Id Value on the current form is 11425 then when the new form opens the criteria will be set to

"Id=11425"

Mary
Dec 7 '06 #2

MSeda
Expert 100+
P: 159
I'm so glad I'm not the only one who had problems with this issue. It had gotten to the point of being embarrassing that I just couldn't understand why the quotes were placed the way they were. I finally had an aha moment not to long ago.

Your dealing with two different programming languages!

You are using VB to concatenate an SQL statement.

What goes inside the quotes are the SQL parts, however when you use a term that SQL does not understand like a variable or anything in a me container you have to close the quotes and enter the VB environment to use those terms you can then and another & and an opening Quote " and reenter your SQL statement.

you may have found that if you refer to your ID text box differently like this Forms![Form1]![Customers1].Form![ID] then the whole statement does go inside the quotes because SQL can recognize the forms notation.

it's when you refer to it in the me container that SQL needs help from VB since SQL does support me.

Basically anything SQL can recognize goes in Quotes and anything VB recognizes goes outside the quotes but has to be married to the SQL string via an &.
Dec 7 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
That pretty well describes it MSeda (Full Member :)).
It sometimes helps to understand what's going on underneath the hood though.
For instance, take :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Customers2", , , "Id=" & Me.Customers1!Id
DoCmd.OpenForm takes various parameters, but the fourth (WhereParameters) is a string.
A string can be referred to in various ways.
A string literal - "blah blah".
A string Variable - strSQL (or Me.Customers1!Id)
A string calculation (using concatenation usually &) - "First bit" & "Second bit" (This equals "First bitSecond bit")
So the code above (and let us assume for the moment that Me.Customers1!Id=312) uses a string calculation to add 312 to the end of "Id=" to give "Id=312".
"Id=312" is THEN interpreted by the SQL engine in OpenForm as selection criteria.

It is important, then, to understand which 'bit' is going to process what.
First you need to formulate the code for VBA then, bearing in mind how VBA will treat it, organise it so that VBA passes on exactly what you want it to - to the function or SQL engine.
Dec 7 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Great explanation Mseda.

Much more precise than mine. I'm always at a loss of how to explain this kind of rule to a non programmer. Sometimes I go too simplistic. I liked your explanation.

Mary
Dec 8 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.