INSERT INTO

[RedHeadedMonster via AccessMonster.com]

I've got the following code built onto an AfterUpdate event on a form:

Dim SQL As String

If CDRLID = 9 Or CDRLID = 10 Then
DoCmd.SetWarnings (0)
SQL = "INSERT INTO Notifier (ID, NameID)" & _
"VALUES (Me.ID, 99)"
DoCmd.RunSQL SQL

Else
End If

Basically, I want to automatically insert a NameID into my Notifier table if
the CDRLID = 9 or 10. Me.ID is Project ID on the current form.

When I run the code a message box comes up wanting me to enter the Me.ID
number. Other than that it works just the way I want. How the heck do I get
it to see and pull the ID number off the form to put into the Table.

I also tried:

Dim SQL As String
Dim xyz as Long
Set xyz = Me.ID

If CDRLID = 9 Or CDRLID = 10 Then
DoCmd.SetWarnings (0)
SQL = "INSERT INTO Notifier (ID, NameID)" & _
"VALUES (xyz, 99)"
DoCmd.RunSQL SQL

Else
End If

And get an object required error on the set xyz =

Any help, suggestions would be greatly appreciated.

RHM

 

[J_Goddard via AccessMonster.com]

Hi -

Because you have Me.ID inside the quotes, it is not using the value (number)
in that control; it is using "Me.ID" just as text. Try this:

If CDRLID = 9 Or CDRLID = 10 Then
DoCmd.SetWarnings (0)
SQL = "INSERT INTO Notifier (ID, NameID)" & _
"VALUES (" & Me.ID & ", 99)"
DoCmd.RunSQL SQL

Else
End If

This assumes me.ID is numeric.

HTH

John

 

['69 Camaro]

Hi.

When I run the code a message box comes up wanting me to enter the Me.ID
number.

You need to concatenate the control name within the string to pass to Jet.
Jet has no idea what that control is on a form, since the syntax you've used
is for a table named Me (which doesn't exist) and column named ID.

SQL is a reserved word. Don't use it as an identifier. And don't turn the
warnings off. It's too easy to forget to turn them back on. Use the
Execute method instead, with the dbFailOnError argument to alert the user if
something went wrong (and roll back the transaction), but otherwise remain
silent. Besides, it's faster. You also need to ensure that error handling
is included.

Try:

Dim sSQL As String

If CDRLID = 9 Or CDRLID = 10 Then
sSQL = "INSERT INTO Notifier (ID, NameID) " & _
"VALUES (" & Me.ID & ", 99)"
CurrentDb().Execute sSQL, dbFailOnError

Else

' Whatever . . .

End If

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.