Re: Checking control properties
From: MacDermott (macdermott_at_NoSpam.com)
Date: 06/20/04
- Next message: Ivan Palčić: "Invoice (Report)+ Numbers+problem"
- Previous message: Stephen Glynn: "Re: Checking control properties"
- In reply to: Stephen Glynn: "Re: Checking control properties"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 20 Jun 2004 15:32:27 -0400
First of all, the SQL statement you execute with CurrentDB.Execute must be a
string.
Therefore, it starts out with a double quote, and ends with one.
Second, you can build a string from pieces.
Consider this code:
Public Function InsertFriend (MyFriend as string) As String
InsertFriend = "My good friend " & MyFriend & " is a wonderful person."
End Sub
If you evaluate
InsertFriend("Stephen")
you'll get
My good friend Stephen is a wonderful person.
If you evaluate
InsertFriend("Stephen")
you'll get
My good friend Stephen is a wonderful person.
If you evaluate
InsertFriend("Mr. Glynn")
you'll get
My good friend Mr. Glynn is a wonderful person.
That's what the ampersands are doing - gluing together the various parts.
Finally -
String values inside SQL statements must be delimited by quotes.
If I wrote
UPDATE MyTable SET MyField = Stephen WHERE MyField = Turtle
Jet could not resolve this, because you might be wanting to set MyField
to
Stephen WHERE MyField = Turtle
for each row.
Or you might have 3 fields, MyField, Stephen, and Turtle,
and want to change the instances of MyField which match Turtle to
match Stephen instead.
The proper syntax would be
UPDATE MyTable SET MyField = "Stephen" WHERE MyField = "Turtle"
Now it gets a little hairy when you build a SQL string like this:
"UPDATE MyTable SET MyField = "Stephen" WHERE MyField = "Turtle""
because Access thinks "UPDATE MyTable SET MyField = " defines a string,
so it doesn't know what to do with Stephen.
There are many ways to approach this problem; the simple one I chose in
this case is to use single quotes instead of double quotes inside the SQL
string. What I'm trying to create is this:
"UPDATE MyTable SET MyField = 'Stephen' WHERE MyField = 'Turtle'"
This works fine unless there are single quotes (or apostrophes) inside
whatever's in the place of Stephen and Turtle.
I'd suggest you take a look at the code that's working, and see if the above
3 rules help you understand.
If you still have questions, please post back.
(It's fun to watch others learning...)
- Turtle
"Stephen Glynn" <stephen.glynn@ntlworld.com> wrote in message
news:2jlug9F1354mtU1@uni-berlin.de...
> It works! Thanks so much for your help.
>
> Would you mind explaining the VB syntax to me (told you I was a baby at
> this)? I understand the SQL but the ampersands and quote marks (single
> and double) get me very confused.
>
> Steve
>
>
> MacDermott wrote:
>
> > Try it like this:
> >
> > currentdb.execute "INSERT INTO tblTemp (ControlName," & _
> > " ControlTag) VALUES ('" & _
> > ctl.Name & "','" & ctl.tag & "')"
> >
> > HTH
> > - Turtle
> >
> >
> > "Stephen Glynn" <stephen.glynn@ntlworld.com> wrote in message
> > news:2jl4ndF12kdcmU1@uni-berlin.de...
> >
> >>Afraid not. Now it's stopping at VALUES and complaining "expected end
> >>of statement"
> >>
> >>Steve
> >>
> >>MacDermott wrote:
> >>
> >>>Ah -
> >>> that old word wrap!
> >>>The line that you see starting with VALUES belongs at the end of the
> >>>previous one.
> >>> Make sure there's a space before VALUES.
> >>>
> >>>HTH
> >>> - Turtle
> >>>
> >>>"Stephen Glynn" <stephen.glynn@ntlworld.com> wrote in message
> >>>news:2jjsb8F11208bU1@uni-berlin.de...
> >>>
> >>>
> >>>>Sorry, I'm a real baby at this.
> >>>>
> >>>>It's falling over at
> >>>>
> >>>>VALUES ('" & ctl.Name & "','" & ctl.tag & "')"
> >>>>
> >>>>In particular it's stopping at the first single quote mark.
> >>>>
> >>>>Please help.
> >>>>
> >>>>Steve
> >>>>
> >>>>MacDermott wrote:
> >>>>
> >>>>
> >>>>>WARNING: AIR CODE
> >>>>> (Create tblTemp, with two text fields, ControlName and ControlTag,
> >>>>>before executing this.)
> >>>>>
> >>>>>Public sub ListControls()
> >>>>> dim ctl as Control
> >>>>> for each ctl in Me.Controls
> >>>>> if TypeOf ctl Is Checkbox then _
> >>>>> currentdb.execute "INSERT INTO tblTemp (ControlName,
> >>>
> >>>ControlTag)
> >>>
> >>>
> >>>>>VALUES ('" & _
> >>>>> ctl.Name & "','" & ctl.tag & "')"
> >>>>> endif
> >>>>> next
> >>>>>End Sub
> >>>>>
> >>>>>
> >>>>>"Stephen Glynn" <stephen.glynn@ntlworld.com> wrote in message
> >>>>>news:2jitktF12ga8vU1@uni-berlin.de...
> >>>>>
> >>>>>
> >>>>>
> >>>>>>I'm trying to check a very large form.
> >>>>>>
> >>>>>>It contains a lot of unbound check boxes, the tag property of each
of
> >>>>>>which (I hope) corresponds to the ItemID field (primary key) in
> >>>>>>tblMyTable and the name property of which corresponds with the
> >>>>>>appropriate ItemCode field in the same table.
> >>>>>>
> >>>>>>The form's been constructed manually and proof-read manually. Is
> >
> > there
> >
> >>>>>>a way of extracting the controls' names and tags and inserting them
> >>>>>>into a temporary table so I can then run a find unmatched query with
> >>>>>>tblMyTable? (I'm hoping that there will be no unmatched entries).
> >>>>>>
> >>>>>>Steve
> >>>>>
> >>>>>
> >>>>>
> >>>
> >
> >
- Next message: Ivan Palčić: "Invoice (Report)+ Numbers+problem"
- Previous message: Stephen Glynn: "Re: Checking control properties"
- In reply to: Stephen Glynn: "Re: Checking control properties"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
