delete records via code 1

[impulse24]

Hi,

I have a table with various records, via code I copy the object to a backup table. I then want to delete all the records in the main table. How do I do this via code? or using a macro

 

[WildHare]

strSql = "Delete * from {tablename} ; "

Docmd.RunSql StrSql

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[qureshi]

You should also try executing the SQL string with the following command

db.execute "delete * from tablename"

 

[eljakim]

Actually, that last post will not work. The object
'db' that you are using does not exist... You would
have to use

dim db as database
set db = currentdb()
db.execute "delete * from TABLE"

I usually use docmd... To suppress the warning you
can use:
docmd.setwarnings false
docmd.runsql "delete * from TABLE"
docmd.setwarnings true

Yours,

Kim

 

[snowyej]

Hi, I'm trying to do something similar. I posted my question yesterday, but no one has responded yet (Thread705-234961).

I have a form that has a combo box with a list of applications. There is a REMOVE button beside the list. Within the form is a subform that shows records linked to the main record. One field in the subform records is application. When a user selects an application from the combo box and clicks REMOVE, I'd like for it to remove the record(s) in the subform that contain that application. I'm getting a error:

"Run-time error '3075':
Syntax error in query expression 'application = <application name>'.

I've double checked my code and field names and everything is correct. The code I'm using to delete is:


Dim StrSQL As String
Dim rsGroup As DAO.Recordset, db As DAO.Database

Set db = CurrentDb
Set rsGroup = db.OpenRecordset(&quot;select * from [tblSubform] where LoadsetLinkID =&quot; & Me.LoadsetRecID)

StrSQL = &quot;Delete * From rsGroup Where application =&quot; & Me.cboAppList

DoCmd.RunSQL StrSQL


Does anyone see any problems with this code?

Thanks in advance!!

Elizabeth

 

[impulse24]

great responses. Thanks for all the help

 

[eljakim]

Elisabeth,

the solution is simple: Your SQL query actually
reads:

Delete * from rsGroup Where application = xxxx

This will not work if application is a string
value. You would need an SQL query that looks
like

Delete * from rsGroup Where application = "xxxx"

To achieve this you have to use double quotes
in your string generation:

StrSQL = "Delete * From rsGroup Where application =""" & _
         Me.cboAppList & """"

Hope this helps...

Kim

 

[snowyej]

Ok, that seemed to fix that error. However now I'm getting an error with my &quot;rsGroup&quot; item. Is it ok to do what I did in my code where I define rsGroup as a set of records where the subform id equals the main form id and then use rsGroup to pull the record I want to delete from:

delete * from rsGroup where application = .....

Thanks!
Elizabeth

 

[WildHare]

Liz, it may be too late for this, but in situations like yours, I let Access do the work. Design a Delete query that is criteria'ed to your APPLICATION control in your form. Make a note of how you'd refer to it:

=Forms!MainForm!subform-control-name.FORM!Application

Then save the query, and CALL it on your button click.



Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[eljakim]

You cannot run an SQL query on a recordset.

Why don't you just use:

Dim StrSQL As String
strSQL = "delete * from [tblSubform] " & _
         "where (LoadsetLinkID =" & Me.LoadsetRecID) & ")" & _
         "and (application=""" & me.cboApplist & """)"
DoCmd.RunSQL StrSQL

Does this solve your problem?

Kim

 

[snowyej]

Kim,

YES! That worked!!

One question though. What are the underscores for? I get &quot;invalid character&quot; when I put those in, so I did the code without that and also had to remove the &quot&quot; from your code...

THanks much! That worked great!

Elizabeth

 

[snowyej]

Kim, you have been so helpful. I hope you don't mind if I ask one more question pertaining to the same form. I also asked in in Thread705-234961 but no one replied to it.

On this same form, I also have an ADD button to add an application to the subform. So it's doing the opposite of what you just helped me with.

I have it mostly working, but it's delayed when it adds the app to the subform. For example, I first select MS Office from the combo box and click ADD, nothing happens. I select another app, say Photoshop, and click ADD and then it adds MS Office to the subform. The next app I select will add Photoshop, and so on. So it's delayed for some reason.

I can paste the code in here if you'd like, but let me know if you know of a simple solution to this.

THanks much!!
Elizabeth

 

[eljakim]

Elizabeth,

I use the _ to mark that a statement has not
been finished at the end of the line. This is
an Access feature that provides you with the
possibility to spread a statement over several
lines... (for readability purposes)

ie

docmd.runsql xxx & yyy & zzz

might be too long to fit on your screen. The
following will not work:

docmd.runsql xxx &
yyy &
zzz

To achieve the desired (formatting) effect you
can use the underscore symbol:

docmd.runsql xxx & _
yyy & _
zzz

Yours,

Kim

 

[eljakim]

Re: Add delay
Are you sure that you tell your subform to
refresh?

ie,

Docmd.RunSQL "insert blah blah blah"
Me.Sub0.Requery

this should solve your problem.

Kim

 

[snowyej]

Actually I have that in my code...the same thing I have on the Remove button to requery the subform. It is requerying...just not immediately.

My code basically says IF an application is selected from the combo box, THEN add that text to the application field on the subform (and there is other data I add to that subform record at this point as well). ELSE pop up a message that says to select an application. After that I have:


Me.frmSubLoadset.Requery
cboAppList.Value = nul
cboAppList.SetFocus


The last two of the above 3 statements work fine, it's the requerying that is not immediate.

Does that make sense?

Thanks!!