Checking control properties

  • Thread starter Thread starter Stephen Glynn
  • Start date Start date
S

Stephen Glynn

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
 
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
 
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
 
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
 
Afraid not. Now it's stopping at VALUES and complaining "expected end
of statement"

Steve
 
Try it like this:

currentdb.execute "INSERT INTO tblTemp (ControlName," & _
" ControlTag) VALUES ('" & _
ctl.Name & "','" & ctl.tag & "')"

HTH
- Turtle
 
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
 
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
 
Back
Top