Seth,
My first rule in writing SQL expressions is "keep it simple"; this
practically means, strip out anything unnecessary that Access puts in
the SQL expression of a query. In this case, this includes (a) repeating
the table name before the field names (not needed since there is only
one table in the FROM clause), and (b) getting rid of redundant
parentheses around criteria in the WHERE clause. Applying those, the SQL
expression becomes quite simple to follow:
strSQLDeleteStd = "DELETE * FROM tblTestRprtStds " _
& "WHERE fldTestRprtStdID = '" & Me!txtTestRprtID & "' " _
& "AND fldTestrprtStdRefNum= '" & intStdId & "'"
Now, how it works:
An SQL expression in VBA code is essentially a string expression; this
is what a DoCmd.RunSQL or CurrentDb.Execute expects as an argument.
Therefore, it is enclosed in double quotes, to denote the value assigned
to the strSQLDeleteStd variable as such (string). In other words, double
quotes denote String.
An expression can be broken up into several lines for readability; to
denote it is continued over to the next line, a " _" (space and
underscore, without the quotes) is add ed at the end.
A string can be made up of parts, concatenated with ambersands.
The two previous points explain how space-underscores and ambersands are
used to put together a string expression, made up of parts extending
over several lines.
Some parts of the expression are left outside the double quotes; this
tells VBA that those are actually variables, functions or references to
other database objects (form textboes, in this case) so VBA "reads"
their value and concatenates the value into the string.
The single quotes denote that the text between them within the overall
string, is to be treated as a string on its own right; they are used
where you would normally have double quotes in the query SQL. For
instance, if part of) your query SQL was:
WHERE CustName = "Smith"
You would use single quotes around Smith in the SQL string in VBA:
strSQL = "SELECT Somefield FROM Table1 WHERE CustName = 'Smith')
With the above in mind, the syntax proposed at the beginning assumes
that both fldTestRprtStdID and fldTestrprtStdRefNum are type Text;
otherwise, the single quotes around the respective control references
should be removed. For instance, if the latter is numeric, the
expression should become:
strSQLDeleteStd = "DELETE * FROM tblTestRprtStds " _
& "WHERE fldTestRprtStdID = '" & Me!txtTestRprtID & "' " _
& "AND fldTestrprtStdRefNum= " & intStdId
HTH,
Nikos
