Delete Underlying Record from Table Using Command Button 1

[Ajwebb]

Not Sure if this is possible, but i have a form with a list box on it. The record source for the list box is from a table.

Is it possible to select one of the rows in my list box and then delete using a command button.

When i say delete the record in the list box, i mean to delete the underlying record in the table.

Thanks for any help

Anthony

 

[jrbarnett]

Hi

It is certainly possible to delete a record from a table programmatically.

There are multiple ways of approaching the problem: the first is to issue an SQL DELETE statement to remove the record from within VBA. You must then issue a Requery for the listbox to allow it to refresh the contents to omit the deleted record.

The second technique will only work if the form is bound to the table, so it won't work in your case because the listbox won't be taking its data from the form's recordset.
The way to do this is to run a

DoCmd.RunCommand acCmdDeleteRecord

command from within VBA - which will delete the active record.

John

 

[Ajwebb]

Thanks for replying John.

That would seem the way to go, however my knowledge of SQL is really basic.

So far what i have is:-

DoCmd.RunSQL "Delete From [Learning Activity Dataset] where [learning activity dataset].[learn_id] =

The rest i am unsure of.

I need to say where Learn_id =

List3.column(0) but am unsure of what to put that as in sql.

Any ideas?

Thanks

Ant

 

[jrbarnett]

Hi,

If the value of List3.Column(0) is numeric, then you can do the following:

DoCmd.RunSQL "Delete From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=" Me!List3.Column(0)

or if it is text, you can do the following:

DoCmd.RunSQL "Delete From [Learning Activity Dataset] where [learning activity dataset].[learn_id]='" Me!List3.Column(0) & "'"

Either way, you can use
DoCmd.SetWArnings False
beforehand and DoCmd.SetWarnings True
afterwards to disable the Access warning confirmation that this will delete x records.

John

 

[Ajwebb]

Hi John,

Thanks again, however this time i have the following message:-

Compile Error

Syntax Error.

Any ideas.

Is it because that i start the statement with " after runsql and there is another " after =.

Ant

 

[MakeItSo]

Hi AJ.

faq701-4432 might help you further. (Using listboxes for queries).

Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Ajwebb]

Hi John,

Thanks Again, however again there is an error. This time it says Compile Error: Expected End of Statement.

The it highlights Me!

Not sure whats wrong

Ant

 

[MakeItSo]

The line break was only cause by the posting, the original should be
Me!List3.Column(0) & "'"
in John's code, without a line break between Me! and the rest. ;-)

Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Ajwebb]

Hi MakeItSo and John,

Still no luck, i have looked at the FAQ and i still am having problems.

Table Name - Learning Activity Dataset
List Name - List3
Column Which includes Learn_id - Column(0)
Field Name to link to list - Learn_id

Am Really confused now.

Want to delete from Learning Activity Dataset where [Learning Activity DAtaset].learn_id = list3.column(0)

?

 

[MakeItSo]

Hi AJ,

DELETE on its own is not enough:
You must tell Access What to delete:

DoCmd.RunSQL "Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]='" Me!List3.Column(0) & "'"

This is valid, if learn:id is a text field, else you must leave away the single quotes:
DoCmd.RunSQL "Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=" Me!List3.Column(0)

Hope this helps,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Ajwebb]

Thanks Again Andy,

I copied and paste what you wrote, but i'm still getting a compile error.

Regards

Ant

 

[MakeItSo]

What kind of error?
Can you post the message here?

 

[Ajwebb]

I get two errors.

The one i am receiving now is Complile Error - Syntax Error

The other is Complile Error - Expected - End of Statement.

On the second error the cursor is flashing after the " but before the Me!

Ant

 

[MakeItSo]

Hmpff - must be getting old:

There's an ampersand missing:

DoCmd.RunSQL "Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=" & Me!List3.Column(0)

Sorry! ;o)

Andy

 

[Ajwebb]

Thanks Andy,

Think its getting close now.

When i click my command button now it says:-

Enter Parameter Value
Ablid0424

The Ablid0424 is the value in my list box, but seems to be the name of the parameter now.

Ant

 

[MakeItSo]

Do the following:
Add a breakpoint in the code exactly at the DoCmd.RunSQL line.
Run your form and double click a value in your listbox.
Select your entire statement
[blue] "Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=" & Me!List3.Column(0)
[/blue]
in your code, right click and say "Add Watch".
In your Watch values window, copy the runtime SQL and paste it here. Let's see, where the error is.

 

[Ajwebb]

Hi Andy,

I think i did what you said correctly and received the following:-

Change: : &quot;Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=&quot; & Me!List3.Column(0) : <Out of context> : Variant/Empty : Form_Learning Activity Form - Main.Comm

Basically it said the Value is Out of Context and the Type is Variant/Empty.

Ant

 

[MakeItSo]

<Out of context>
means, that the code was not running.
You must set a breakpoint, and then run the form as usual.
When choosing an item from the listbox, the code will appear with the DoCmd.RunSQL line highlighted.

Then do not stop the code in any fashion. Just go into the Watch and paste the value from there.

 

[Ajwebb]

Got Ya.

Think this is correct.

Watches: &quot;Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=&quot; & Me!List3.Column(0)

Value: &quot;Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=ABLID0424&quot;

Type: Variant/String

 

[MakeItSo]

Aaaah yeah! )
Hmpff - &quot;mis&quot;corrected it first....

learn_id is a tetx field, so its:
&quot;Delete * From [Learning Activity Dataset] where [learning activity dataset].[learn_id]=[red]'[/red]&quot; & Me!List3.Column(0) [red]&&quot;'&quot;[/red]

(Added single quotes ' before the closing quote before & Me.. and added & &quot;(single quote)&quot; at the end.

Phewww! ;-)