Open Form Problem

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I changed SalesID is a Text Field and I get asked for its value. I'm
sure that I'm referencing it wrong. What is the Correct Syntax?

Thanks
DS

DoCmd.OpenForm "Payment", acNormal, "", "SalesID=" &
Forms!Tables!List38.Column(2), , acNormal
 
Because SalesId is a Text type field, you need quotes around the value in
the WhereCondition:
DoCmd.OpenForm "Payment", acNormal, "", "SalesID=""" &
Forms!Tables!List38.Column(2) & """"

Note that Column() is a zero-based property, so Column(2) is the 3rd column.
 
Allen said:
Because SalesId is a Text type field, you need quotes around the value in
the WhereCondition:
DoCmd.OpenForm "Payment", acNormal, "", "SalesID=""" &
Forms!Tables!List38.Column(2) & """"

Note that Column() is a zero-based property, so Column(2) is the 3rd column.
Click to expand...
Thanks Allen, I keep getting derailed over this Text or Numeric in
Criteria....Is there someplace that explains this in depth?
Once again. Thank You!
DS
 
A literal value in a SQL statement needs delimiters.

Number fields don't use anything as the delimiter.
Text fields use quote marks as delimiters.
Dates use # as the delimiter.

The problem with text fields is that you also need quotes around the whole
string, so you now have quotes inside quotes. To let VBA know it's not the
end of the string (which is how it interprets a quote), you have to double
the quotes up.

Example: To produce the string:
This string has a "word" in quotes
You code:
"This string has a ""word"" in quotes"

If you close the quotes straight after the literal quotes, you end up with 3
in a row, i.e.:
"This string has a word in ""quotes"""

From there, you can probably make sense of the example in the previous post.

It is possible to use a single-quote as the delimiter inside the quotes, but
this fails if the string contains an apostrophe, e.g. O'Brien, can't. The
double-quote inside quotes is far less common: you rarely see it other than
for inches or seconds.
 
Allen said:
A literal value in a SQL statement needs delimiters.

Number fields don't use anything as the delimiter.
Text fields use quote marks as delimiters.
Dates use # as the delimiter.

The problem with text fields is that you also need quotes around the whole
string, so you now have quotes inside quotes. To let VBA know it's not the
end of the string (which is how it interprets a quote), you have to double
the quotes up.

Example: To produce the string:
This string has a "word" in quotes
You code:
"This string has a ""word"" in quotes"

If you close the quotes straight after the literal quotes, you end up with 3
in a row, i.e.:
"This string has a word in ""quotes"""

From there, you can probably make sense of the example in the previous post.

It is possible to use a single-quote as the delimiter inside the quotes, but
this fails if the string contains an apostrophe, e.g. O'Brien, can't. The
double-quote inside quotes is far less common: you rarely see it other than
for inches or seconds.
Click to expand...
Thank You Allen....
This clears alot up for me.
DS
 
Allen

Good explanation on how to insert the quotes correctly. I can never keep all
that straight, so I just use the function Chr$(39) to concatenate the quotes.
As an added bonus, if a string contained single or double quotes it would
still parse correctly. The syntax from DS's example would be

DoCmd.OpenForm "Payment", acNormal, "", "SalesID=" & Chr$(39) &
Forms!Tables!List38.Column(2) & Chr$(39)
 
Back
Top