Results 1 to 13 of 13
  1. #1
    funkygoorilla is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    93

    Clear some fields and not others


    Hi,
    I have a form for adding new records to a table. However, I have some fields that will need to remain the original data entered while some will need to be re-entered. Is there a setting for keeping a field the current value even if added to a table?
    For example; Field 1 is A, Field 2 is B, Field 3 is C. I need to add all three fields to the table, but field A can always be A until the form closes.
    Thanks for any help!
    FunkyG

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Unless you want to set a default value in the table for the field, there is no setting for this. Requires VBA code to retain a value from current record in a variable then set the value of field in new record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Interesting suggestion. The code sets the Default Value property of control. I might have to give this a try.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You would do it in the AfterUpdate event of the controls you want to "Carry Over".

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Doesn't have to be the AfterUpdate event for each control. I tested it by coding in a command button Click event that has the code to go to new record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    funkygoorilla is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    93
    Hi, thanks for the replies. I still cant get it to work. Would I put it in the afterUpdate part of the field I want to carry over, or the button that submits the data to the table?
    FunkyG

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Either should work. Post your code for analysis. Why doesn't it work - error message, wrong result, nothing happens?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    funkygoorilla is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    93
    Ok, here is the code that I am trying to use.

    Private Sub districtResult_AfterUpdate()
    Const cQuote = """"
    Me!Control.DefaultValue = cQuote & Me!Control.Value & cQuote
    End Sub

    What goes in the quotes?

    I put it in the afterupdate area of the Event column in the fields properties. Now when I open the form, the debugger opens.

    FunkyG

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    In the AfterUpdate event property, select [Event Procedure], double click the ellipses (...) and this will take you to the VBA editor. Place the code in the procedure.

    Nothing goes in the quotes. That line is establishing a constant that is then referred to in the following line. This insures that the quote mark is literally included in the string that the DefaultValue is set to. Could just as easily be:
    Me!Control.DefaultValue = """" & Me!Control.Value & """"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    funkygoorilla is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    93
    OK, I have the code in the correct place now, and this is what I am using.
    Private Sub districtResult_AfterUpdate()
    Const cQuote = """"
    Me!Control.DefaultValue = """" & Me!Control.Value & """"
    End Sub

    It doesnt save the value though? Sorry If I am missing something obvious.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    When you move to new record the value is not carried forward?

    With that revision the Const line is not needed.

    In my test of this I used button Click event and then referred to the controls by name. Include the """" if field is text datatype. A date field requires "#".

    Me.controlname.DefaultValue = Me.controlname
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    funkygoorilla is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    93

    Got it!

    Ok, that worked. I removed the ! and added the name of the control. Works like a charm. Thanks so much for your help!

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

Similar Threads

  1. Form Fields Clear
    By rajulasb in forum Access
    Replies: 1
    Last Post: 08-06-2011, 01:11 AM
  2. Clear fields in a record
    By stryder09 in forum Access
    Replies: 9
    Last Post: 05-12-2011, 01:34 PM
  3. How to clear the form?
    By Mrcams in forum Access
    Replies: 3
    Last Post: 01-03-2011, 12:15 AM
  4. Clear all checkboxes?
    By thekruser in forum Forms
    Replies: 2
    Last Post: 09-16-2010, 09:50 AM
  5. clear field values
    By surrendertoo in forum Queries
    Replies: 0
    Last Post: 02-23-2008, 10:57 PM

Tags for this Thread

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