Code Syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text type. I know
the following is missing apostrophe/quotes but I haven't figured how that
works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where [tbl 1
Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code like this
example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their help. Not
used to is yet.
 
Chris said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text type. I know
the following is missing apostrophe/quotes but I haven't figured how that
works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where [tbl 1
Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code like this
example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their help. Not
used to is yet.
Click to expand...

As long as it's only one table, you can shorten the SQL to

"DELETE FROM [tbl 1 Client] Where ClientID = " & Me.ClientID

Note that here I'm concatenating the value of the control into the
string, while you had

....ClientID = & Me.ClientID"

where the reference is within the string.

The above should work if the ClientID field is numeric. For text field,
you'd need something like

....Where ClientID = '" & Me.ClientID & "'"

or

....Where ClientID = """ & Me.ClientID & """"

You will often also get the advice of exeucing it on the current
database, in stead of using the DoCmd thingie, i e

currentdb.execute "DELETE FROM [tbl 1 Client] Where ClientID = " & _
Me.ClientID, dbFailOnError
 
If ClientID is a Number field, you will want to concatente the number from
the form into the string.

This kind of thing:

Dim db As DAO.Database
Dim strSql As String
strSql ="DELETE FROM [tbl 1 Client] Where [tbl 1 Client].ClientID = " &
Me.ClientID & ";"
Set db = CurrentDb()
db.Execute strSql, dbFailOnError
Set db = Nothing

For an explanation of how to do quotes within quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

For an example of why Execute is preferred over RunSQL, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
 
Ray, I do appreciate the help. I get errors using each line separately.


Compile error, syntax error:
"DELETE FROM [tbl 1 Client] Where ClientID = " & Me.ClientID
"DELETE FROM [tbl 1 Client] Where ClientID = '" & Me.ClientID & "'"
"DELETE FROM [tbl 1 Client] Where ClientID = """ & Me.ClientID & """"

3061, too few parameters, expected 1:
CurrentDb.Execute "DELETE FROM [tbl 1 Client] Where ClientID = " & _
Me.ClientID, dbFailOnError

Did I miss something?
--
Thanks for your help,
Chris


RoyVidar said:
Chris said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text type. I know
the following is missing apostrophe/quotes but I haven't figured how that
works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where [tbl 1
Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code like this
example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their help. Not
used to is yet.
Click to expand...

As long as it's only one table, you can shorten the SQL to

"DELETE FROM [tbl 1 Client] Where ClientID = " & Me.ClientID

Note that here I'm concatenating the value of the control into the
string, while you had

....ClientID = & Me.ClientID"

where the reference is within the string.

The above should work if the ClientID field is numeric. For text field,
you'd need something like

....Where ClientID = '" & Me.ClientID & "'"

or

....Where ClientID = """ & Me.ClientID & """"

You will often also get the advice of exeucing it on the current
database, in stead of using the DoCmd thingie, i e

currentdb.execute "DELETE FROM [tbl 1 Client] Where ClientID = " & _
Me.ClientID, dbFailOnError
Click to expand...
 
Alan, thanks for the tip pages. Very informative.

My ClientID is text. That's where my problem is. I cannot figure out how
or why text would format one way and numeric another -- well, yes I can see
there would be issues -- but I can't figure out how to accomplish anything
that uses such code. I have cut and pasted from this NG, edited and been
successful, but I don't know what is going on to make that difference an
issue. Thus, I cannot successfully code that type of thing.

I am aware of the quote/double-quote issue, just not sure of formatting in
this kind of statement. What requires quote/double-quote, what doesn't? Why
the difference?

Thanks

Chris


Allen Browne said:
If ClientID is a Number field, you will want to concatente the number from
the form into the string.

This kind of thing:

Dim db As DAO.Database
Dim strSql As String
strSql ="DELETE FROM [tbl 1 Client] Where [tbl 1 Client].ClientID = " &
Me.ClientID & ";"
Set db = CurrentDb()
db.Execute strSql, dbFailOnError
Set db = Nothing

For an explanation of how to do quotes within quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

For an example of why Execute is preferred over RunSQL, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Chris said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text type. I
know
the following is missing apostrophe/quotes but I haven't figured how that
works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where [tbl 1
Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code like this
example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their help.
Not
used to is yet.
Click to expand...
Click to expand...
 
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text
type. I know the following is missing apostrophe/quotes but I
haven't figured how that works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where
[tbl 1 Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code
like this example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their
help. Not used to is yet.
Click to expand...
If you are trying to delete the current record, it's simpler to use
the following instead of a query.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

or even simpler,
Me.Recordset.Delete
 
Are you sure that the field in the table is named ClientID and not, say,
Client Id? (If the name has embedded blanks, you'll need to use square
brackets around it, like you are with the table name)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Chris said:
Ray, I do appreciate the help. I get errors using each line separately.


Compile error, syntax error:
"DELETE FROM [tbl 1 Client] Where ClientID = " & Me.ClientID
"DELETE FROM [tbl 1 Client] Where ClientID = '" & Me.ClientID & "'"
"DELETE FROM [tbl 1 Client] Where ClientID = """ & Me.ClientID & """"

3061, too few parameters, expected 1:
CurrentDb.Execute "DELETE FROM [tbl 1 Client] Where ClientID = " & _
Me.ClientID, dbFailOnError

Did I miss something?
--
Thanks for your help,
Chris


RoyVidar said:
Chris said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text type. I
know
the following is missing apostrophe/quotes but I haven't figured how
that
works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where [tbl 1
Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code like
this
example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their
help. Not
used to is yet.
Click to expand...

As long as it's only one table, you can shorten the SQL to

"DELETE FROM [tbl 1 Client] Where ClientID = " & Me.ClientID

Note that here I'm concatenating the value of the control into the
string, while you had

....ClientID = & Me.ClientID"

where the reference is within the string.

The above should work if the ClientID field is numeric. For text field,
you'd need something like

....Where ClientID = '" & Me.ClientID & "'"

or

....Where ClientID = """ & Me.ClientID & """"

You will often also get the advice of exeucing it on the current
database, in stead of using the DoCmd thingie, i e

currentdb.execute "DELETE FROM [tbl 1 Client] Where ClientID = " & _
Me.ClientID, dbFailOnError
Click to expand...
Click to expand...
 
Thanks, Bob. I tried your "simpler version" and found it works
like a champ.

Chris
Click to expand...
Glad to have helped.

Q
Bob Quintal said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text
type. I know the following is missing apostrophe/quotes but I
haven't figured how that works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where
[tbl 1 Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code
like this example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in
their help. Not used to is yet.
Click to expand...
If you are trying to delete the current record, it's simpler to
use the following instead of a query.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

or even simpler,
Me.Recordset.Delete
Click to expand...
Click to expand...
 
Thanks, Bob. I tried your "simpler version" and found it works like a champ.

Chris


Bob Quintal said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text
type. I know the following is missing apostrophe/quotes but I
haven't figured how that works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client] Where
[tbl 1 Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code
like this example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their
help. Not used to is yet.
Click to expand...
If you are trying to delete the current record, it's simpler to use
the following instead of a query.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

or even simpler,
Me.Recordset.Delete
Click to expand...
 
If ClientID is a Text type field, you need quotes around the value.

So you need to generate something like this:
DELETE FROM [tbl 1 Client] Where [tbl 1 Client].ClientID = "Acme";

So, to get those quotes inside the string, the code would be:
strSql ="DELETE FROM [tbl 1 Client] Where [tbl 1 Client].ClientID = """
& Me.ClientID & """;"

The weakness with RunSQL is that you don't know if it succeeded or not.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Chris said:
Alan, thanks for the tip pages. Very informative.

My ClientID is text. That's where my problem is. I cannot figure out how
or why text would format one way and numeric another -- well, yes I can
see
there would be issues -- but I can't figure out how to accomplish anything
that uses such code. I have cut and pasted from this NG, edited and been
successful, but I don't know what is going on to make that difference an
issue. Thus, I cannot successfully code that type of thing.

I am aware of the quote/double-quote issue, just not sure of formatting in
this kind of statement. What requires quote/double-quote, what doesn't?
Why
the difference?

Thanks

Chris


Allen Browne said:
If ClientID is a Number field, you will want to concatente the number
from
the form into the string.

This kind of thing:

Dim db As DAO.Database
Dim strSql As String
strSql ="DELETE FROM [tbl 1 Client] Where [tbl 1 Client].ClientID =
" &
Me.ClientID & ";"
Set db = CurrentDb()
db.Execute strSql, dbFailOnError
Set db = Nothing

For an explanation of how to do quotes within quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

For an example of why Execute is preferred over RunSQL, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

Chris said:
Will someone check my code and correct it? Please?

I am deleting the current record on a form; key field is text type.
I know the following is missing apostrophe/quotes but I haven't
figured how that works.

DoCmd.RunSQL "DELETE [tbl 1 Client].* FROM [tbl 1 Client]
Where [tbl 1 Client].ClientID = & Me.ClientID"

Also, is there a source to educate me on how to correctly code
like this example where the item may be text or number?

I have Access 2007 and am very frustrated by some changes in their
help.
Not used to is yet.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top