docmd.runsql help required
Quote:
> I am trying to write an access database (my first) to catalogue the
> computers, and their software at work.
> I have a form to remove software from the repository when it is no longer
> needed, using 3 combo boxes, which are fed by SELECT DISTINCT queries based
> on earlier choices.
> Once I have chosen the Manufacturer, Product & Version, I click on the OK
> button which triggers the following VBA code...
> DoCmd.RunSQL "DELETE FROM tbl_software WHERE manufacturer =
> cmb_manufacturer.Text AND product = cmb_product.Text AND version =
> cmb_version.Text;"
> This always says that it is going to delete 0 records. I want to know if
> anyone has any idea why. If I run the same SQL code, but alter the cmb_*
> combo box references, it prompts me for the details, and if I enter them, it
> will delete 1 record. Why will it not pick up the .Text from the combo
> boxes?
> Thanks in advance for any help,
> Andy Jeffrey
Where using SQL in code like this you need to build the string using the
combo boxes values like this:
DoCmd.RunSQL "DELETE FROM tbl_software WHERE manufacturer = """ & _
cmb_manufacturer & """ AND product = """ & _
cmb_product & """ AND version = """ & cmb_version & """"
Note the extra " in the string, using "" will preserve the little value
of the " in your string (assuming all the required fields are text, if
they're numbers then the quotes aren't required.).
The above string translates as:
DELETE FROM tbl_software WHERE manufacturer = "someManufacturer" AND
product = "someProduct" AND Version = "someVersion"
for all numeric values use:
DoCmd.RunSQL "DELETE FROM tbl_software WHERE manufacturer = & _
cmb_manufacturer & " AND product = " & _
cmb_product & " AND version = " & cmb_version
--
Hope this helps
Tony Oakley (MVP)
