Help with Delete SQL Code

[pewilson]

I am having trouble with the following code. When I hit the delete the record I get a runtime error stating that "No value given for one or more required parameter." Can anyone see what I'm doing wrong? Thank you in advance.

Private Sub cmdRemoveTop10_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic


With rst
.Open "SELECT * FROM tblTopPick WHERE [FirstOfCompany Number]= " & "'" & txtID & "'"
.Delete
.Close
End With

Set rst = Nothing

lstView.Requery

End Sub Paul
paul_wilson74@hotmail.com

 

[therayster]

Where are you setting the value for 'txtID'?

 

[Russie]

It's probably your SQL statement.

Try the following with you locals window open. Step through the code and see what value is assigned to strSQL.

Public Sub showSQL()
Dim conDatabase As ADODB.Connection
Dim strSQL As String

Set conDatabase = CurrentProject.Connection

strSQL = "SELECT * FROM tblTopPick" & _
"WHERE [FirstOfCompany Number]= " & "'" & txtID & "'"


conDatabase.Execute strSQL

conDatabase.Close
Set conDatabase = Nothing

End Sub

 

[pewilson]

I am setting my value for txtID from a listbox.
txtID = lstView.Column(0) Paul
paul_wilson74@hotmail.com

 

[Russie]

probably better to write:

Me.txtID.value = lstView.Column(0)

Substitute 'Me' for the form reference (i.e [Forms]![Form..]if useing a module.

Also, if you've declared this variable in a private function or sub you will not be able to read its value from another sub.

Declare it at the top of your form or module like:

Public txtID as string

...or whatever variable type you want it to be..

 

[pewilson]

I actually found what the problem. I was referencing the wrong primary value in the SQL statement. I took that SQL statement from another area and neglected to change the primary value [FirstOfCompany Number] to [StoreID]. But it works fine now. Thank you for your help! Paul
paul_wilson74@hotmail.com