Results 1 to 14 of 14

Clearing a Column of Data

  1. #1
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7

    Clearing a Column of Data

    Need some help. I have browsed through this forum and can not find a similar situation to what I am doing. I have a form that has multiple rows and columns based on data in other tables. I am trying to create a button on the form that will clear all data selected in the combo boxes. I can not seem to find a Macro that will do this and I have tried writing an expression but that only clears the first row. Please see the screen shot to see what I am talking about. Any help would be greatly appreciated.



    Click image for larger version. 

Name:	Clear Data.jpg 
Views:	18 
Size:	92.0 KB 
ID:	34560

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,363
    Is each 'row' a record? Comboboxes are bound to fields? You want to remove data from table?

    Post your code for analysis.

    Perhaps you just need to run UPDATE query.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7
    My left most column is populated by a table called systems. Then under the Monday, Tuesday... That field is a combo box that is pulling data from a query of another table called dispatchers. Basically the query looks for dispatchers in a specific system and allows only the dispatchers in that system to be able to populate the field. I am not an access guru and have basically worked around my elbow to get to my you know what but I am now trying to make it easier to update the records on this form in order to change assignments on a regular basis.

    Click image for larger version. 

Name:	Clear 2.jpg 
Views:	17 
Size:	137.8 KB 
ID:	34561

    When the combo box is selected this drop down appears giving you the names, schedules for that day and how many techs currently assigned to that dispatcher on each row. The update button at the top is a MACRO that refreshes the data and updates the number of techs assigned to each dispatcher. The clear button has been added but as of yet I have not been able to figure out how to make it work.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,363
    So you are saving data to table? Form is bound to table where records are saved? Again, do you want delete data? Do you care to save historical data? You still haven't provided code. What do you mean by 'refreshes the data and updates'?

    Exactly what does 'clear' mean to you?
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7
    The data is stored in a table. I would like to clear the data that has been selected in the combo boxes. I dont want to delete any data. I have no need to save historical data. I have tried this option, but it only deletes the first row of data.

    Me.Monday_Dispatcher = Null
    Me.Tuesday_Dispatcher = Null
    Me.Wednesday_Dispatcher = Null
    Me.Thursday_Dispatcher = Null
    Me.Friday_Dispatcher = Null
    Me.Saturday_Dispatcher = Null
    Me.Sunday_Dispatcher = Null

  6. #6
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,215
    Can you show us an image of the form in design view and describe the recordsource for the form?
    Also the rowsource property for one of the comboboxes.
    If possible, a zipped copy of the db would provide faster results.

  7. #7
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7
    Here is the screen shot of the Form. It does have a Front end that opens up into the Form Maximized. Trying to hide the backend data from other users.
    Click image for larger version. 

Name:	Clear 3.jpg 
Views:	16 
Size:	99.1 KB 
ID:	34562

    Also I am attaching a zipped version of the file. I am sorry I am not more fluent in what you are asking.

    Dispatch Assignement New Shifts.zip

  8. #8
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7
    The forms that I want to add the clear button to are the MA Assignments, MS Assignments, TX Assignments and WS Assignments. The data is also stored on the table with the same names.

  9. #9
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,215
    As June7 mentioned in Post #2, here's the code. Modify your clear button code to this:
    If it works for MA, you can replicate for the other areas.

    Code:
    Option Compare Database
    Option Explicit
    Private Sub Command20_Click()
        Dim sSQL As String
        sSQL = "Update [MA Assignments] SET " _
        & "[Monday Dispatcher] ='" & vbNullString & "', " _
        & "[Tuesday Dispatcher] ='" & vbNullString & "', " _
        & "[Wednesday Dispatcher] ='" & vbNullString & "', " _
        & "[Thursday Dispatcher] ='" & vbNullString & "', " _
        & "[Friday Dispatcher] ='" & vbNullString & "', " _
        & "[Saturday Dispatcher] ='" & vbNullString & "', " _
        & "[Sunday Dispatcher] ='" & vbNullString & "'"
        Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
        Me.Requery
    '    Me.Monday_Dispatcher = Null
    '    Me.Tuesday_Dispatcher = Null
    '    Me.Wednesday_Dispatcher = Null
    '    Me.Thursday_Dispatcher = Null
    '    Me.Friday_Dispatcher = Null
    '    Me.Saturday_Dispatcher = Null
    '    Me.Sunday_Dispatcher = Null
    End Sub

  10. #10
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7
    You are awesome! Works perfectly! Thank you so much

  11. #11
    dsc1014 is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2018
    Posts
    7
    One more quick observation... When I added this code into my db on the 4 forms and ran it a few times, I noticed that the file size had doubled. Would this be storing the cleared data somewhere? I cant see that this little bit of code would account for an additional 3.5 meg of data.

  12. #12
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,215
    I can't see that happening either. You can do a compact/repair to reduce the size, then experiment to see if you can pin down what functions might be causing bloat.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,363
    If you are saving combobox inputs to table and you want to 'clear' these BOUND comboboxes and not save past assignments (history) then sounds like deleting data to me. Changing values in record to Null or empty string is removing data.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  14. #14
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,215
    Changing values in record to Null or empty string is removing data.
    I agree. Database is rather complex to see effect, and I was waiting to see if OP determined if this was a problem.

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

Similar Threads

  1. Pulling info from linked CSV then clearing data
    By Waterdog in forum Import/Export Data
    Replies: 5
    Last Post: 05-03-2018, 03:22 PM
  2. Data is clearing on form
    By marissadenae in forum Access
    Replies: 3
    Last Post: 04-14-2018, 05:19 AM
  3. Clearing and entire column
    By MaineLady in forum Access
    Replies: 2
    Last Post: 07-09-2016, 11:03 AM
  4. Clearing Form Data
    By s.carter in forum Forms
    Replies: 5
    Last Post: 06-28-2015, 04:32 PM
  5. acViewPreview clearing form of data in textboxes
    By Markb384 in forum Programming
    Replies: 9
    Last Post: 03-07-2014, 09:06 AM

Posting Permissions

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