Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query integration with VBA 1

Status
Not open for further replies.

tekquest

Vendor
Feb 1, 2003
224
AU
Hi All,

I have a VBA script when run, its supposed to delete records that are more than 7 years old based on their max date.

It pops up with a question "you are about to delete 0 records from 0 fields from the specified table"

I know this isnt an error message, but for some reason it doesnt seem to be attempting to delete any records, even with the date in 2012, the select query shows all the data to be purged (which is a seperate item all together)

here is the new updated code:

------

Private Sub mybutton12355_Click()

Set Db = CurrentDb()

Dim strSQL As String
'
strSQL = "DELETE FROM RecTrans WHERE " & _
"ContractNumber IN SELECT " & _
"RECTRANS.ContractNumber " & _
"FROM RECTRANS " & _
"GROUP BY RECTRANS.ContractNumber " & _
&quot;HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));&quot;
'

strSQL = &quot;DELETE FROM ADV WHERE ContractNumber NOT IN (Select ContractNumber &quot; & _
&quot;FROM Rectrans);&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = &quot;DELETE FROM CBL WHERE ContractNumber NOT IN (Select ContractNumber &quot; & _
&quot;FROM Rectrans);&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = &quot;DELETE FROM LEASE WHERE ContractNumber NOT IN &quot; & _
&quot;(Select ContractNumber FROM Rectrans);&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = &quot;DELETE FROM PL WHERE ContractNumber NOT IN &quot; & _
&quot;(Select ContractNumber FROM Rectrans);&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

strSQL = &quot;DELETE FROM HP WHERE ContractNumber NOT IN &quot; & _
&quot;(Select ContractNumber &quot; & _
&quot;FROM Rectrans);&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Set Db = Nothing

End Sub

------

Does anyone have any ideas?

Thanks to RoyVidar for all his help. :-)
 
If this is all your code then it doesn't appear that you are doing the delete from &quot;RecTrans&quot; (i.e. you're not running that SQL. You're just defining it but there is no &quot;DoCmd.RunSQL strSQL&quot; following it.) Since nothing has been deleted from RecTrans, there are no records in other tables that are NOT IN RecTrans ... and nothing is deleted in those tables.
 
Golom,

I just deleted a record from the rectrans table, it finds and wants to delete it in the child record. Great!

How do I get the main statement to run the docmd.runsql?

I added that in the front of the code, but it errors with a runtime error 3129 &quot;invalid sql statement: expected delete, insert, procedure, select or update

heres the code:

------

DoCmd.RunSQL strSQL = &quot;DELETE FROM RecTrans WHERE &quot; & _
&quot;ContractNumber IN SELECT &quot; & _
&quot;RECTRANS.ContractNumber &quot; & _
&quot;FROM RECTRANS &quot; & _
&quot;GROUP BY RECTRANS.ContractNumber &quot; & _
&quot;HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));&quot;

------

Thanks for replying :-)

 
Look critically at your code. The last time someone had that problem, they had

DoCmd.RunSQL &quot;strSQL&quot;

instead of

DoCmd.RunSQL strSQL

I would also place parentheses around the sub-select in your delete statement.
 
Golom,

Got it, Thanks for all your help,

strSQL = &quot;DELETE FROM RecTrans WHERE &quot; & _
&quot;ContractNumber IN (SELECT &quot; & _
&quot;RECTRANS.ContractNumber &quot; & _
&quot;FROM RECTRANS &quot; & _
&quot;GROUP BY RECTRANS.ContractNumber &quot; & _
&quot;HAVING (((DateAdd('yyyy',7,Max([RECTRANS].[TransactionDate])))<Now())));&quot;
DoCmd.RunSQL strSQL
DoCmd.SetWarnings False

Thanks again for all your help :-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top