Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need to INSERT default value into multiple tables

    Experts:



    I currently use the below function to delete *all* records (from all tables where prefix = "FF_").

    Code:
    Public Sub DeleteRecordsFreeForm()
    
        Dim T As TableDef
        Dim n As Integer
        
        DoCmd.SetWarnings False
        
        For Each T In CurrentDb.TableDefs
            If T.Name Like "FF_*" Then
                DoCmd.RunSQL "DELETE * FROM " & T.Name
                n = n + 1
            End If
        Next T
        
        DoCmd.SetWarnings True
        
        MsgBox "All records were deleted from " & n & " tables.", vbInformation, "Delete Status"
    
    
    End Sub
    I'd like to modify the code so that after the record deletion, I re-insert a default record into each table. Each table contains field [COMMENT]. Thus, I modified the VBA to include the additional INSERT statement:
    Code:
    Public Sub DeleteRecordsFreeForm()
        
            Dim T As TableDef
            Dim n As Integer
            
            DoCmd.SetWarnings False
            
            For Each T In CurrentDb.TableDefs
                If T.Name Like "FF_*" Then
                    DoCmd.RunSQL "DELETE * FROM " & T.Name
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " ([COMMENT]) VALUES ('---')"
                    n = n + 1
                End If
            Next T
            
            DoCmd.SetWarnings True
            
            MsgBox "All records were deleted from " & n & " tables.", vbInformation, "Delete Status"    
        
    End Sub
    Unfortunately, the default value = "---" is currently NOT added to my comment field. What am I missing?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    different tables may require a key field.
    modify insert sql accordingly.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your code works as written on a 2 field table with no PK so no idea what the problem is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ok, I tested the following outside the For loop:

    Code:
    'DoCmd.RunSQL "INSERT INTO FF_Gender (Gender, TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES ('---', '---', '---', '---');"
    This INSERT statement works. However, how do I choose the correct insert statement given the following?
    - The 1st field (the one that is unique to every table) could be text, double, long, date, etc.

    So, how can I include, e.g., a CASE statement -- as part of the FOR LOOP -- which would the data type? So, when going through the loop and it comes down to

    [Gender] table, I want to use: "INSERT INTO FF_Gender (Gender, TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES ('---', '---', '---', '---');"
    alternatively for [Age] table, I want to use: "INSERT INTO FF_Gender (Age, TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (0, '---', '---', '---');"

    Naturally, I don't want to define all tables names individually. Again, the first field (the one that is unique to each table) is set as the primary key.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Micron - I have 4 fields where last 3 fields are identical (same name & same data type) across all FF_ tables.

    However, the 1st field in each table varies. That is, fieldnames match table name (after FF_ prefix) and data types vary as well. See previous post for details.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see what post 4 and all those other fields or data types has to do with anything. If you have a field named COMMENT in every table whose name begins with FF_ then your code should work as I noted - if what you want to do is what you previously stated.
    I'd like to modify the code so that after the record deletion, I re-insert a default record into each table. Each table contains field [COMMENT].
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Apparently, if I won't insert a value into to the primary key field, I get a record violation and thus the record is NOT created.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Tom,
    What "default" value would you add for a Date PK? You can add code to inspect the data type of the PK field (https://docs.microsoft.com/en-us/off...property-dao):
    Code:
    Select Case T.Fields(0)
    Case dbText  
    'default"-----"
    Case dbLong
    'default 0
    ...
    ....
    Or you could just as easily apply some brute force and have three INSERT statements (one for text, one for numbers and one for dates), add an On Error Resume Next Just before them (don't forget to reset it after) and see what sticks....

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- brute force will work for me. WRT to date, would it be possible to insert a date =Today()?

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I attempted to use the VBA below... it results in an "invalid operations" error. The URL btw did not load.

    Code:
    Public Sub DeleteRecordsFreeForm()
    
        Dim T As TableDef
        Dim n As Integer
        
        DoCmd.SetWarnings False
        
        For Each T In CurrentDb.TableDefs
            If T.Name Like "FF_*" Then
                
                
                DoCmd.RunSQL "DELETE * FROM " & T.Name
                
                Select Case T.Fields(0)
                
                    Case dbText
                    MsgBox "text"
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0) & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES ('M', 'x', 'y', '---');"
                    
                    Case dbLong
                    MsgBox "long"
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0) & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (0, 'x', 'y', '---');"
                    
                End Select
    
                n = n + 1
            End If
        Next T
        
        DoCmd.SetWarnings True
        
        MsgBox "All records were deleted from " & n & " tables.", vbInformation, "Delete Status"
        
        
    End Sub
    How should the VBA be tweaked for text, number, and date fields?

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Here are two versions:
    Code:
    Public Sub DeleteRecordsFreeForm()
    
    
        Dim T As TableDef
        Dim n As Integer
        
        DoCmd.SetWarnings False
        
        For Each T In CurrentDb.TableDefs
            If T.Name Like "FF_*" Then
                
                
                DoCmd.RunSQL "DELETE * FROM " & T.Name
                
                Select Case T.Fields(0)
                
                    Case dbText
                    MsgBox "text"
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES ('M', 'x', 'y', '---');"
                    
                    Case dbLong
                    MsgBox "long"
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (0, 'x', 'y', '---');"
                    
                End Select
    
    
                n = n + 1
            End If
        Next T
        
        DoCmd.SetWarnings True
        
        MsgBox "All records were deleted from " & n & " tables.", vbInformation, "Delete Status"
        
        
    End Sub
    
    
    Public Sub DeleteRecordsFreeFormBruteForce()
    
    
        Dim T As TableDef
        Dim n As Integer
        
        DoCmd.SetWarnings False
        
        For Each T In CurrentDb.TableDefs
            If T.Name Like "FF_*" Then
                
                
                DoCmd.RunSQL "DELETE * FROM " & T.Name
                
                On Error Resume Next
                
                DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES ('M', 'x', 'y', '---');" 'text             
                DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (0, 'x', 'y', '---');" 'number
                
     	    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (Date, 'x', 'y', '---');" 'Date
                End Select
    
    
                n = n + 1
            End If
        Next T
        
        DoCmd.SetWarnings True
        
        MsgBox "All records were deleted from " & n & " tables.", vbInformation, "Delete Status"
        
        
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    With dbDate
    Code:
    ublic Sub DeleteRecordsFreeForm()
    
    
        Dim T As TableDef
        Dim n As Integer
        
        DoCmd.SetWarnings False
        
        For Each T In CurrentDb.TableDefs
            If T.Name Like "FF_*" Then
                
                
                DoCmd.RunSQL "DELETE * FROM " & T.Name
                
                Select Case T.Fields(0)
                
                    Case dbText
                    MsgBox "text"
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES ('M', 'x', 'y', '---');"
                    
                    Case dbLong, dbDouble,dbInteger,dbSingle
                    MsgBox "long"
                    DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (0, 'x', 'y', '---');"
                    
            Case dbDate
            DoCmd.RunSQL "INSERT INTO " & T.Name & " (" & T.Fields(0).Name & ", TARGET_TABLE, TARGET_FIELD, COMMENT) VALUES (Date, 'x', 'y', '---');"
    
    
                End Select
    
    
                n = n + 1
            End If
        Next T
        
        DoCmd.SetWarnings True
        
        MsgBox "All records were deleted from " & n & " tables.", vbInformation, "Delete Status"
        
        
    End Sub
    https://docs.microsoft.com/en-us/off...numeration-dao

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Apparently, it still doesn't work as anticipated. Allow me to attach the sample database.

    Process
    - DB opens with form "F01_MainMenu"
    - First, click on listbox menu item "Generate All FF Tables". It will result in three (3) message boxes.
    - Second, click on listbox menu item "Delete FF Tables (non-FreeForm)". Click "Yes" when prompted to delete the tables.
    - Next, click on listbox menu item "Add Target Fields to FF Tables". It will result in one (1) message box.

    The above has been the existing process. I now added the menu item "Delete All Records". Well, these are only those from the tables having an "FF_" prefix.

    At this point, I get the error w/ the date field. If I click it five (5) times, I will get a message indicating success.

    Now, when reviewing the four FF_ tables (Education, FirstName, Gender, Lastname), there are actually 3 new records in each table.

    Instead, I'd like the following:
    1. *Single* record for any tables where the first field = text. Values should be '---', '---', '---', '---' for all 4 text fields.
    2. *Single* record for any tables where the first field = num. Values should be 0, '---', '---', '---' for all 4 text fields.
    3. *Single* record for any tables where the first field = date. Values should be 01/01/2021, '---', '---', '---' for all 4 text fields.


    Again, the issue is that I'm currently adding all 3 records into each table. Instead, I need a single record depending on the data type.

    How can this be fixed?
    Attached Files Attached Files

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I gather then that you are not using an autonumber as a PK and you must be using meaningful data instead. That's another reason to stick with AN if that is the case. Vlad is way ahead of me so I'll step out.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad - I went back to used the function provided in post #12. This was seems to be using the CASE statement to ensure only a single record is entered. However, upon execution, line
    Code:
    Select Case T.Fields(0)
    is highlighted showing an error "Invalid operations".
    Attached Thumbnails Attached Thumbnails Capture.JPG  

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-06-2020, 02:09 AM
  2. Delete query to delete records that meet criteria
    By neill_long in forum Queries
    Replies: 1
    Last Post: 06-11-2018, 02:41 PM
  3. Replies: 4
    Last Post: 12-13-2016, 09:48 AM
  4. Delete does not delete records in evey table
    By LaughingBull in forum Access
    Replies: 5
    Last Post: 09-01-2015, 04:05 PM
  5. Replies: 2
    Last Post: 01-24-2012, 02:16 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