Results 1 to 6 of 6
  1. #1
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64

    Changing value of bound field on event


    I have a contacts table with field for "Preferred." Many of my contacts are from the same firm, and I want to use this as a way of designating the firm's preferred point of contact.

    I have a form that is bound to the contacts table. I want to be able to update the "Preferred" field from this form with a checkbox. To that end, I initially made the "Preferred" field a "yes/no" field. However, I don't want the data in the "Preferred" field to show up as "Yes/No." I'd rather it show up as "P" or blank. To that end, I changed the "Preferred" field to short text, and I made the following event procedure on my Contacts form to update the "Preferred" field with a checkbox.

    Code:
    Private Sub PreferredContact_Change()If IsNull(Me!PreferredContact) Then
    Me.PreferredContact = "P"
    Else
    Me.PreferredContact = Null
    End If
    End Sub
    This almost works. However, instead of updating the field with "P" or null, it updates the field with -1 when checked and 0 when unchecked. My objective is to get it to update to "P" or null.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    so if preferred contact is null, set it to P. and if its not null set it to null. which would then make it set to P next time?

  3. #3
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    Quote Originally Posted by Perceptus View Post
    so if preferred contact is null, set it to P. and if its not null set it to null. which would then make it set to P next time?
    I suppose that is one consequence. But I really just want the "P" to stand out.

    Basically, three of the fields on my contacts table are going to populate a listbox on another form: contact name, firm and the "preferred" field. I want the "Preferred" field to show up as either P or blank (null) because the P will stand out more on the listbox. Right now, it looks like this. The third column represents the "preferred" field.



  4. #4
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    I figured it out. This code works much better

    Code:
    Private Sub PreferredContact_AfterUpdate()
    If (Me!PreferredContact) = True Then
    Me!Preferred = "P"
    Else
    Me!Preferred = Null
    End If
    Me.Refresh
    End Sub

  5. #5
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    Here's the after


  6. #6
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Good work!

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

Similar Threads

  1. Replies: 1
    Last Post: 11-23-2015, 07:18 PM
  2. Replies: 4
    Last Post: 02-27-2014, 03:39 PM
  3. Replies: 7
    Last Post: 10-03-2011, 12:44 PM
  4. Replies: 4
    Last Post: 05-18-2011, 03:24 PM
  5. Changing value of a field after an event.
    By mikethebass in forum Access
    Replies: 1
    Last Post: 06-16-2010, 04:42 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