runtime error '-2147217904 (80040e10)' --should be easy thing Version: 2003 (11.0)

[parkerk]

Alright. I'm trying to do something very simple here. Apparently, I don't know enough VBA to get it done.

I have a form, frmContaminant. For each new contaminant, I want it to add a record to a different table, tblAnalyte. These tables are set up similarly, as so:

tblContaminant
ContaminantID
Contaminant

tblAnalyte
AnalyteID
Analyte

So...I put an event in the AfterUpdate where new contaminants are entered, here is my code:
Code:
Private Sub Contaminant_AfterUpdate()

Dim connDB As ADODB.Connection
Dim qry As New Command
Dim sql As String

Set connDB = CurrentProject.Connection
qry.ActiveConnection = connDB

sql = "INSERT INTO tblAnalyte (Analyte) SELECT " & Me.Contaminant & " FROM tblContaminant WHERE Analyte = " & Me.Contaminant & ""

qry.CommandText = sql
qry.Execute

End Sub


Im getting this runtime error, that says no value given for one or more required parameters. I hit debug and it points me at qry.Execute.

I have tried doing this without the WHERE claus, but I got errors about expected parameters. I tried using CurrentDB.Execute...kept getting errors.

What am I doing wrong???

Thanks!

 

[pbaldy]

Does Me.Contaminant really contain both a field name and the value?

 

[MSAccessRookie]

Please correct me if I am wrong, but in the case where Me.Contaminant ="Mud", then the statement translates to

INSERT INTO tblAnalyte (Analyte) SELECT Mud FROM tblContaminant WHERE Analyte = Mud

Is that what you wanted? I think one of the Muds needs to be something else

PS I think Paul said the same thing with a lot less words

 

[parkerk]

Aha!

I need the VALUE. Thanks for pointing me in the right direction. I used this:

sql = "INSERT INTO tblAnalyte (Analyte) VALUES (""" & Me.Contaminant & """)"

works great.

Thanks guys.

 

[MSAccessRookie]

There you go. If Anything I said was helpful, then you are welcome.