sql strings

[Afrosheen]

Is there a better way of doing this?

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL = "UPDATE tblconfig SET tblconfig.roster = a"
strSQL1 = "UPDATE tblconfig SET tblconfig.adn = True"
strSQL2 = "UPDATE tblconfig SET tblconfig.print = 1"

CurrentDb.Execute strSQL, dbFailOnError
CurrentDb.Execute strSQL1, dbFailOnError
CurrentDb.Execute strSQL2, dbFailOnError

What I'm trying to do is to update my tblconfig table so I can use the
information else where.

I also keep getting an error: 3601 Too few parameters. Expected 2. In form
name. I just thought that it may be from the "adn" field. It is a check box.
So do I need to set that as a string?

Thanks for the help

 

[Allen Browne]

If roster is a Text field (not a number field), you must use quotes around
the literal, e.g.:
strSQL = "UPDATE tblconfig SET tblconfig.roster = 'a';"

If ADN is a Yes/No field, the 2nd one looks right.

"PRINT" is a reserved word in SQL Server, and could cause problems in the
context of a report in Access. Use square brackets around the field name,
e.g.:
strSQL2 = "UPDATE tblconfig SET tblconfig.[print] = 1;"
of if Print is a Text field:
strSQL2 = "UPDATE tblconfig SET tblconfig.[print] = '1';"

The 'too few parameters' message means there is a name in the query that
Access cannot match to a field or table. If you have something like:
[Forms].[Form1].[Text0]
in the actual query statements you are using, the Expression Service will
not be able to interpret that in the context of an Execute. Instead,
concatenate the value into the string.

If you don't have that kind of reference, it is something else such as a
misspelled table or field name, or a parameter, or square brackets needed
around a field/table name.

 

[fredg]

Is there a better way of doing this?

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL = "UPDATE tblconfig SET tblconfig.roster = a"
strSQL1 = "UPDATE tblconfig SET tblconfig.adn = True"
strSQL2 = "UPDATE tblconfig SET tblconfig.print = 1"

CurrentDb.Execute strSQL, dbFailOnError
CurrentDb.Execute strSQL1, dbFailOnError
CurrentDb.Execute strSQL2, dbFailOnError

What I'm trying to do is to update my tblconfig table so I can use the
information else where.

I also keep getting an error: 3601 Too few parameters. Expected 2. In form
name. I just thought that it may be from the "adn" field. It is a check box.
So do I need to set that as a string?

Thanks for the help

Your problem most likely is with the strSQL. You're updating the field
with the letter 'a' so it's a Text datatype field, but you have not
enclosed the letter within quotes.

strSQL = "UPDATE tblconfig SET tblconfig.roster = 'a'"

strSQL1 is OK if adn is a check box field.

strSQL2 is OK if blconfig.print is a Number datatype field.
If it is a Text datatype, then you need to enclose the 1 value in
quotes, i.e." ..... blconfig.print = '1'"

Note: I would not use the word "Print" as a field name. That word has
a special meaning within Access and VBA. Use something like "PrintMe"
or "Printed", etc. instead.

 

[John Spencer]

In addition to the other advice, you really need only one query to update the
three fields in the same table.

StrSQL= "UPDATE tblConfig SET Roster = 'a', adn = -1, [Print] = 1"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County