Re: updating table from a text box

Tech-Archive recommends: Fix windows errors by optimizing your registry

ok I tried your way and I am getting an error... (because I had to add a
primary key)

Now i get an error saying "didn't add 1 record(s) to the table due to key
violations."

I added a primary key called noteid. [Id] that was already there is a
reference to the primary key of another table and has duplicates in notes, so
after upsizing to sql I needed a primary key and added it.

Since then I have the error listed above. Prior to adding the key, both
yours and my codes worked, but since yours is obviously better I chose to use
it.

Douglas J. Steele wrote:
That's surprising: it shouldn't work!

The Jet engine has no idea where to find the values [notes] and [ID]. Based
on your previous post, I'm assuming they're the names of controls on your
form. I'll further assume that notes is a text field, and that ID is a
numeric field. Another possible complication is that the notes field might
contain apostrophes or double quotes. That means your SQL should be:

"INSERT INTO notes ([notes], [id]) " & _
"VALUES ('" & Replace(Me![notes], "'", "''") & "', " & _
Me![ID] & ")"

Exagerated for clarity, that middle row is

"VALUES ( ' " & Replace(Me![notes], " ' ", " ' ' ") & " ', " & _

And in my opinion, you're better off using the Execute method of the DAO
Database object. That avoids needing to futz with SetWarnings, plus you can
have it raise a trappable error if something goes wrong:

CurrentDb.Execute "INSERT INTO notes ([notes], [id]) " & _
"VALUES ('" & Replace(Me![notes], "'", "''") & "', " & _
Me![ID] & ")", dbFailOnError

I found this and it works instantly...

[quoted text clipped - 5 lines]

I turned the warnings off because they popped up a box each time.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200809/1

.

Relevant Pages

  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Binding and adding rows with SQL identity column
    ... Each time you call AddNew then the DataTable generates a new ... Each time an update is done then the real primary key ... > generated by the built-in wizard in VS2003. ... > identity column on the sql table, perhaps this would all the fine and I ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: What happens after 2^32 autonum keys are exhausted?
    ... Tony Rogerson, SQL Server MVP ... generated primary key despite the risk of a hardware or software error or a fault in the space-time continuum. ... identifier and have to invent one, follow the data design (data ...
    (comp.databases)
  • Re: sliced find in Linq
    ... by over all of the returned columns. ... Since the original query was unordered, I'd like the above SQL to ... (Perhaps your table doesn't have a primary key though?) ... didn't specify the ordering, so any ordering could have been used. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Can a query find info X numbr of rows from target info?
    ... "Ann Scharpf" wrote: ... > each row is sequentially numbered, giving me the desired explicit sort order. ... > an exact comparison between my SQL & your advice. ... Do you have Row names (Primary Key). ...
    (microsoft.public.access.queries)