INSERT INTO Question

[peashoe]

I have a subform within my form that has a Team text box. I need to
insert that value into a seperate table on the Lost_Focus event. I have
the following:

Private Sub Team_LostFocus()
DoCmd.RunSQL "INSERT INTO [Team/Mascots] ([Name]) VALUES ([Team]);"
End Sub

This is doing the job, but a pop-up box appears when I loose focus and
I have to enter the data again to have it insert (after it gives me a
warning that it will be updating a row and I have to click OK). Is
there a way to completely remove that pop-up box and just have it do
the insert?

Thanks in advance
Lisa

 

[Granny Spitz via AccessMonster.com]

Is
there a way to completely remove that pop-up box and just have it do
the insert?

CurrendDb.Execute "<Action query SQL statement>", dbFailOnError

You shouldn't use Name as a column name, since it's a reserved word. And you
shouldn't use *divided by* in a column name because Jet will try to do just
that in some queries and you'll either wonder why it's complaining that it
can't find the field Mascots, or you'll just get Expr1000 with an invalid
value if Mascots actually exists in your table. Better to use only
alphanumerics and the underscore in your names. You won't need to use
brackets.

 

[peashoe]

Ok I made the changes you suggested - and I get an error: "Runtime
error 3061:too few parameters. Expected 1.

This is what I have:
Private Sub Team_LostFocus()
CurrentDb.Execute "INSERT INTO TeamMascot (TeamMascotName) VALUES
(Team.Value);"
End Sub

any ideas?

 

[peashoe]

nevermind - I got it.

Private Sub Team_LostFocus()
CurrentDb.Execute ("INSERT INTO TeamMascot (TeamMascotName) VALUES
('" & Team.Value & "')")
End Sub

I googled and found the answer - thank you SO much for your help

~L~

 

[Granny Spitz via AccessMonster.com]

any ideas?

If it's a string value you're inserting into a column, you have to delimit it
with quotes, like this:

CurrentDb.Execute "INSERT INTO TeamMascot (TeamMascotName) " & _
"VALUES ('" & Me!Team.Value & "');", dbFailOnError

Don't forget the dbFailOnError argument when executing an action query. It's
important because it will roll back the transaction if there are any problems
and alert you to what the problem is, instead of failing silently and
possibly stopping partway through, making your data inconsistent.

 

[peashoe]

Granny,
Ok will do - thanks so much for the info

~L~