Hi Colin
I like to assign a variable that represents the value contained by a form
control.
Then I totally seperate any variables from the textual portion of the SQL
string when doing this kind of thing.
Assigning a variable to a form's control also allows you to inspect it's
contents while in break mode.
Set a breakpoint in your code, at say the "StrSql = " line . Then run the
procedure, and you'll see what I mean.
"Hover" your mouse cursor over the "MyProductName" variable, and it's
contents will displayed in a "tooltip"
I'd also suggest using the .Execute method ( including the dbFailOnError)
rather than RunSQL.
The problem with using RunSQL is that some (but not all) records will be
changed by the action query, until such time that an error occurs.
If an error occurs with RunSQL, you have to look through the table and
determine which records have (or have not) been changed.
The dbFailOnError is an "all-or-nothing" approach to updating records. If an
error should occur, all changes are "rolled back".
All of the above is *my understanding* of how things work.
That being said, try it like this:
*************************************************
....
Dim MyDB as DAO.Database
Set MyDB = CurrentDb
Dim MyProductName As String
MyProductName = Me![TxtProductName]
StrSql = ""
SrtSql = StrSql & "INSERT INTO [Products] (ProductName) " 'You were missing
a space between [Products] and (ProductName)
SrtSql = StrSql & "VALUES ("
SrtSql = StrSql & MyProductName 'No quotes here, JUST the variable name.
SrtSql = StrSql & ");"
Debug.Print StrSql
'Open the Debug (Immediate) Window
'Copy the resulting SQL string
'Create a new query based on the Products Table
'Switch to SQL View, and paste in the SQL string (copied from Debug Window)
'Attempt to switch to Datasheet view.
' If there are any errors, Access will tell you and will even highlight the
offending portion
MyDB.Execute StrSql, dbFailOnEror
Set MyDB = Nothing
....
*************************************************
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"ColinWard" <je*********@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .
Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" 'SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values ('" & TxtProductName.Text &
"');"
thanks guys
Colin
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!