Listbox deletion

[Tom Wickerath]

For the present time, comment out the line of code that begins with
DoCmd.RunSQL. Declare a string variable and add a Debug.Print statement to
your procedure, like this:

Dim strSQL As String

strSQL = "delete * from fits " & _
"where [RefUnitId] = """ & _
PartsSetupPartsAssignment_lbx & _
""" AND [RefPartNo] = """ & PartsSetupUnitID_Cbx & _
""";"

Debug.Print strSQL
Stop


Then run the procedure (perhaps it is part of the click event of a command
button?). Open the Immediate Window to inspect the result. In the Visual
Basic Editor (VBE), this is done by either clicking on View | Immediate
Window, or by pressing the Control and G keys simultaneously, ie. <Ctrl><G>

You should see the SQL (Structured Query Language) statement for a Delete
query. In the example you provided, the resulting SQL statement will be valid
if, and only if, both values from the controls are text. If one of both are
numeric data types, then you will need to adjust the quotes appropriately (no
quotes for numeric fields). In addition, the values that are inserted from
the controls cannot contain apostrophes in the text strings (for example:
Alfred's Futterkiste would cause the SQL statement, as written, to bomb out).

Click on the Stop button (or Run | Reset) to stop the execution of code,
after the Debug.Print statement is executed. Inspect the resulting SQL
statement. If all looks okay, select the entire SQL statement in the
Immediate Window, and copy it to your clipboard ( <Ctrl><C> ). Then create a
brand new query in design view. Dismiss the Add Tables dialog, without adding
any tables. In query design view, click on View | SQL View. Replace the word
SELECT, shown in the SQL View, by pasting the result from your clipboard.
Make an attempt to run the query and/or to switch back into normal query
design view (View | Design View).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Bob Quintal]

Houston, we have a problem. Is there a way that I can send you the
portion of the db that's giving me a fit?

Sure, Decypher my email address
(e-mail address removed)
PA is y I've altered my email address.

try to keep the file to under a couple of megabytes.

Q,

[quoted text clipped - 10 lines]

vb

DoCmd.RunSQL "delete * from fits " & _
"where [RefUnitId] = """ & _
Me.PartsSetupPartsAssignment_lbx & _
""" AND [RefPartNo] = """ & Me.PartsSetupUnitID_Cbx & _
""";"

You were missing a couple of quotes and a space in front of the
AND

 

[vbnetman via AccessMonster.com]

Hi Tom,
Thanx for all of you input. I have the issue resolved and just wanted to
touch base.

vb

For the present time, comment out the line of code that begins with
DoCmd.RunSQL. Declare a string variable and add a Debug.Print statement to
your procedure, like this:

Dim strSQL As String

strSQL = "delete * from fits " & _
"where [RefUnitId] = """ & _
PartsSetupPartsAssignment_lbx & _
""" AND [RefPartNo] = """ & PartsSetupUnitID_Cbx & _
""";"

Debug.Print strSQL
Stop

Then run the procedure (perhaps it is part of the click event of a command
button?). Open the Immediate Window to inspect the result. In the Visual
Basic Editor (VBE), this is done by either clicking on View | Immediate
Window, or by pressing the Control and G keys simultaneously, ie. <Ctrl><G>

You should see the SQL (Structured Query Language) statement for a Delete
query. In the example you provided, the resulting SQL statement will be valid
if, and only if, both values from the controls are text. If one of both are
numeric data types, then you will need to adjust the quotes appropriately (no
quotes for numeric fields). In addition, the values that are inserted from
the controls cannot contain apostrophes in the text strings (for example:
Alfred's Futterkiste would cause the SQL statement, as written, to bomb out).

Click on the Stop button (or Run | Reset) to stop the execution of code,
after the Debug.Print statement is executed. Inspect the resulting SQL
statement. If all looks okay, select the entire SQL statement in the
Immediate Window, and copy it to your clipboard ( <Ctrl><C> ). Then create a
brand new query in design view. Dismiss the Add Tables dialog, without adding
any tables. In query design view, click on View | SQL View. Replace the word
SELECT, shown in the SQL View, by pasting the result from your clipboard.
Make an attempt to run the query and/or to switch back into normal query
design view (View | Design View).

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Houston, we have a problem. Is there a way that I can send you the portion of
the db that's giving me a fit?