DoCmd.RunSQL issue

[DavSam]

Hi All,
Being a newbie to VB, I'm not sure this is the right area for this post, but I'm putting it out there anyway.

I have a REMOVE command button that removes a record from a table when clicked. What is supposed to happen is that when the button is clicked, the record gets removed from one table and pupt into another table (on the off chance that it may get used again). this part works, but when the record is not deleted from the first table. I get a run-time error #3464 (whatever this means): Data type mismatch in criteria expression. Here's the code:
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblType_ID WHERE ID = '" & Me.fldID & "'"
DoCmd.SetWarnings True

Here's the kicker, if I change the ID field to text, the code works; however, due to my customer's needs, this field must stay as Number/Long Integer.

Is there a way to make this work? I'm using Access97.
Thanks in advance.

 

[vbaInet]

Here's the kicker, if I change the ID field to text, the code works; however, due to my customer's needs, this field must stay as Number/Long Integer.

It's happening because at the time you run the code, Me.fldID is a zero-length string and Number fields don't accept zero-length strings. You will need to perform some validation on that control's value.

 

[DavSam]

Thanks for the help. Being a VB and Access newbie, how do I run a validation on it? I've noticed a 'validation' option on the form fields, but shied away because I had no idea how to use it.

 

[vbaInet]

You can use an IIF() function or a IF block to validate. I don't use the Validation property so I can't remember how it fully works because it's not very flexible.

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblType_ID WHERE ID = '" & IIF(Len(Me.fldID & vbNullString)=0, 0, Me.fldID) & "'"
DoCmd.SetWarnings True

 

[r.harrison]

Just checking, but with Access VBA, isn't it more effective to use

Currentdb.execute(SQLStatement)

?

 

[boblarson]

If the ID field is numeric then no quotes would be the order of the day:

DoCmd.RunSQL "DELETE * FROM tblType_ID WHERE ID = " & Me.fldID


And yes, Currentdb.Execute would be a better choice.

 

[vbaInet]

Just checking, but with Access VBA, isn't it more effective to use

Currentdb.execute(SQLStatement)

?

It's an alternative and has more db options. This is what a lot of experienced folks use.

 

[Galaxiom]

The Execute Method only knows about tables and queries. It does not understand references to controls on Forms so all values must be concatenated into the SQL string.

RunSQL is often simpler to use because it can refer directly to a control. This also avoids having to format dates and include quote marks around strings. I recommend it for new developers. Just means having to turn off warnings and back on again.

Note with this step it is important to include DoCmd.SetWarnings True in the Exit of the procedure. Otherwise an error that exits during while Warnings are off can cause warnings to remain off. This means all warnings not just sql warnings.

When using Execute it is important to include the optional argument, dbFailOnError. Without it, any errors with the command will be quietly ignored.

 

[DavSam]

At long last and a few additional gray hairs later, everything is working great. The boss was surprised that the project was completed ahead of schedule. Thank all of you for all of your guidance and help; I couldn't have done it without you. You're all the best!!

 

[vbaInet]

Good job DavSam!