Deleting record using number in textbox 2

[CWebster]

I've had no problems deleting records elsewhere in this program that I'm writing, only when trying to get the info from this textbox. This particular problem starts when I fill certain records into various control array textboxes. When I click on the "delete" button, I want VB to delete the entrant that has the "jobref" number from the relevant textbox in the array.

Here's the code I've been using:
================================================
Private Sub cmdDelete_Click(Index As Integer)
SQLQuery = "SELECT * FROM tblDiary WHERE EntryID LIKE '*txtID(Index).Text*'"
Set rsDiary = dbMadeUp.OpenRecordset(SQLQuery)

rsDiary.Edit
rsDiary.Delete
rsDiary.Update

End Sub
================================================

Any help will be much appreciated!

Colin

 

[phzero]

Colin,

Your code is defintely incorrect. For starters, I assume that for each textbox you have a corresponding command button with the same index, since that's what your code is saying, since the Index in your code actually refers to the Index of the command button and not the Index of the textbox. with this intact, here is what your query should look like:
"select * from tblDiary where EntryID = '" & txtID(Index).Text & "'". If EntryID is you PK then I suggest using the equal to sign and not the keywork LIKE and even if its not, the = sign is your best bet. I'm not sure what you're trying with the asterix sign in your code. I'm under the impression that you got it confused with the & sign.
Cheers,

 

[CWebster]

Hi Phzero. Thanks for your reply. You're correct with everything that you assumed. I changed the code to your suggestion but still get an error message:

Run-time error '3464':
Data type mismatch in criteria expression.

When I DEBUG, it highlights this line:

Set rsDiary = dbMadeUp.OpenRecordset(SQLQuery)

Any ideas?

I'm still a little green with this VB lark (as you could see with a couple of basic errors in my code), so I appreciate your help!

 

[phzero]

Colin,

Drop the single quotes from the query. This leads me to believe that the column EntryID is of type numeric. Just so you know, putting a search criteria in single quotes means that you're querying a value of type string in the database. Do this and let me know of the results.

 

[hookbob]

If EntryID is a Value rather than a string field then you should leave out the ' on both sides of the statement. ie.

"select * from tblDiary where EntryID = " & txtID(Index).Text

 

[CWebster]

Woohoo! That works guys, thanks!