Results 1 to 12 of 12
  1. #1
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41

    Missing operator in query expression

    Hi,



    Very new to ACCESS and trying to create a command to delete records simultaneously from multiple tables I fund some code which works but it deletes all records

    Private Sub Command98_Click()
    DoCmd.RunSQL "DELETE * from Associated_Process_table"
    DoEvents
    DoCmd.RunSQL "DELETE * from comments_table"
    DoEvents
    DoCmd.RunSQL "DELETE * from customer_table"
    DoEvents
    DoCmd.RunSQL "DELETE * from group_email_address_table"
    DoEvents
    DoCmd.RunSQL "DELETE * from task_table"
    DoEvents
    DoCmd.RunSQL "DELETE * from team_table"
    DoEvents
    DoCmd.RunSQL "DELETE * from main_table"
    DoEvents
    End Sub

    I only want it to delete the current record - i.e the one I am viewing so I tried to amend it as follows:
    Private Sub Command98_Click()
    DoCmd.RunSQL "DELETE CurrentRecord * from Associated_Process_table"
    DoEvents
    DoCmd.RunSQL "DELETE CurrentRecord * from comments_table"
    DoEvents
    DoCmd.RunSQL "DELETE CurrentRecord * from customer_table"
    DoEvents
    DoCmd.RunSQL "DELETE CurrentRecord * from group_email_address_table"
    DoEvents
    DoCmd.RunSQL "DELETE CurrentRecord * from task_table"
    DoEvents
    DoCmd.RunSQL "DELETE CurrentRecord * from team_table"
    DoEvents
    DoCmd.RunSQL "DELETE CurrentRecord * from main_table"
    DoEvents
    End Sub

    but now I get the Syntax error (missing operator) in query expression 'CurrentRecord'

    what is missing in my code and can I rectify this to make it work for what I need?


    Thanks

    Jen

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You need to add a WHERE condition to the DELETE statement. See: https://www.techonthenet.com/sql/delete.php
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    Thanks Bob Fitz, that is \ really useful site, can you use the WHERE condition to state WHERE = CurrentRecord? as this is not covered on that link?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The WHERE is used with one or more fields - WHERE ID=1 - or whatever. Always create the query in the query design window first, get it working, then copy and paste the SQL into your code.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The following link gives a better explanation and examples of using WHERE
    https://www.techonthenet.com/sql/where.php

    I have just been re-reading your original post.
    Can you tell us a little more about your table set up and what you are doing that makes this code needed. Are the tables related?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	37.7 KB 
ID:	28663 Hi Bob Fitz, I have created a contacts database for the specific needs of my team. the main table has the contact name, type (internal/external etc), email address, contact number, company. the other tables are related to it by the means of a contact ID primary key on the main table - some of the tables such as associated task and associated customer are in a one to many relationship - I have attached a screenshot of the relationships- hope this helps. I have designed a form to view and one to edit the contact details and I it is made up of subforms to represent the relevant tables and I need a command to delete a record in its entirety - i.e. remove everything from every table relating to that specified contact ID or current record

    hope this makes sense!

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    It looks as though you have relational integrity set on all your relationships. If you delete a record in the main table does Access not remove all the related records in other tables automatically.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    no because if i try to delete a row in the main table and it has related records in the other tables I get an error message stating 'The record cannot be deleted or changed because table '********' includes related records - this is just if I go into the main table itself and try and delete a record manually. when I tried adding a button to delete record using the wizard for add button on the form it said 'the command or action 'deleterecord' isn't available now - this is why I tried the code instead.

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If you try to edit the relationship in the Relationship window diagram make sure you have "ticked" the one which reads "Cascade Delete Related Records".
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    ok, so I have done this and ticked the "Cascade Delete Related Records". in the relationships and I can now delete a record from the main table and all other associated recordsa re removed. I can't seem to relate this to adding a delete button on the form though, is this because it is made up of sub forms?

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by moneypennie21 View Post
    ok, so I have done this and ticked the "Cascade Delete Related Records". in the relationships and I can now delete a record from the main table and all other associated recordsa re removed. I can't seem to relate this to adding a delete button on the form though, is this because it is made up of sub forms?
    Why? What happens if you create a button on the main form to delete the current record?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    moneypennie21 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    41
    I get the 'the command or action deleterecord isn't available right now error message. this is the code for my form if this helps - this is in the record source of the form

    SELECT Main_Table.*, Task_Table.Task, Team_Table.Team FROM (Main_Table LEFT JOIN Task_Table ON Main_Table.Contact_ID = Task_Table.Contact_ID) LEFT JOIN Team_Table ON Main_Table.Contact_ID = Team_Table.Contact_ID;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 3075 Missing Operator in query expression
    By PinkLady50 in forum Access
    Replies: 40
    Last Post: 02-24-2017, 03:33 PM
  2. Replies: 2
    Last Post: 03-31-2016, 04:28 PM
  3. Replies: 4
    Last Post: 03-16-2016, 12:24 PM
  4. Replies: 2
    Last Post: 02-17-2014, 10:27 AM
  5. Replies: 9
    Last Post: 01-22-2013, 04:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums