Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Check whether control is empty

    I have two forms in a database. What I want to do is in a Name control in form 1, I want the text to be Red, if a control in form 2 contains data, but black if it is empty. I know how to refer to a control on the same form or subform, but not another form in the same database.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,482
    Thanks
    3
    Thanked 41 Times in 41 Posts
    The syntax for referencing a form in Access VBA is Forms!FormName!ControlName so you could use the IsNull() function to test the content of the control in form2. Are you certain that Form2 will always be open?
    Wendell

  4. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ha! I forgot that. No, form two won't be open at the same time. Need to be able to check the table don't I?

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,482
    Thanks
    3
    Thanked 41 Times in 41 Posts
    Good point - you cannot check the value of a control on a form if it isn't open.
    Wendell

  6. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    How do I check a field in a table? I've tried looking up DLookup, but can't see anything that relates to my problem. From the form, I want to check if a field in the table has a Null value. If so, format the text in the form as black. If it contains data, format the text in the form to Red.
    Last edited by robm; 2014-02-10 at 10:40.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,916
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Rob,

    I messed around with this and came up with this code which seems to work.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
      
      If Not CurrentProject.AllForms("Contracts1").IsLoaded Then
        MsgBox "The form Contracts1 is not currently loaded!", _
               vbOKOnly + vbInformation, "Error: Missing Form"
      End If
      
      If IsNull(Forms!Contracts1!FormName) Then
        Me!ReleaseDate.BackColor = RGB(0, 0, 0)
      Else
        Me!ReleaseDate.BackColor = RGB(255, 0, 0)
      End If
    
    End Sub
    
    Private Sub Form_Activate()
    
      Me.Refresh
      
    End Sub
    Note: The Refresh in the Form_Active routine does NOT work and it also doesn't work if placed in a Form_GotFocus event! However, if you press F5 it works as it should, go figure! Hopefully, one of our Access Gurus will come up with a solution.

    red.JPG black.JPG
    Of course you'll have to adjust the Form & Field names as fit your situation. Note: the code goes in the form which gets the color change!
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the code, but looking at it, doesn't both forms have to be open? I don't want two forms open. I don't want to confuse the user. That's why I need to search the table.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,916
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Rob,

    Yes both forms have to be loaded/open the code even checks to make sure the second form is loaded. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by robm View Post
    I don't want two forms open. I don't want to confuse the user. That's why I need to search the table.
    Is there no way I can check the value of a field in a table then?

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,916
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Rob,

    Ok this code works for me.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
      
      If IsNull(DLookup("DeceasedDte", "Hospital", "[PatientNo] = " & Me!PatientNo)) Then
        Me!ReleaseDate.BackColor = RGB(0, 0, 0)
      Else
        Me!ReleaseDate.BackColor = RGB(255, 0, 0)
      End If
      
    End Sub

    The DLookup statement breaks down as follows:
    DeceasedDte = The fieldname I want the value from.
    Hospital = The data table where I'm searching
    [PatientNo] = The fieldname I'm searching in Hospital.
    Me!PatientNo = The search value from the form.

    Note: beware of the quote marks as they are essential!

    Replace the above values with those appropriate to your situation.

    Also don't forget to replace the Me!ReleaseDate.BackColor statements with the field name of your form that gets the color change.

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ok, I've messed around with the tables, etc. and managed to get the data into the form using a query, so I have managed to get the control I need to check, in the form. I still can't get anything to work using IsNull. I have a combo box (combo6) which selects the surname. A control on the form is called SIA. What I want is, after a Surname is selected, if control SIA contains any data, the background changes, but I can't get it to work. I've got the Event in the After Update property of combo6, but I'm not sure if this is right.

  13. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks retiredgeek. Didn't see your reply until after I posted. I'll try your method. It looks better than what I'm trying to do. Just one thing I'm not sure of, where do I put this code. As I said above, this is where I may be going wrong.
    Last edited by robm; 2014-02-12 at 06:30.

  14. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry if I'm a bit thick but, I don't understand the 'The search value from the form' bit. This is the code I've got

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
      
      If IsNull(DLookup("SIA", "tblMaster Sheet", "[FORNAME] = " & Me!FORENAME)) Then
        Me!SURNAME.BackColor = RGB(0, 0, 0)
      Else
        Me!SURNAME.BackColor = RGB(255, 0, 0)
      End If
    
    
    End Sub
    but it's not working. What I want to happen is when I select the surname from the combo, it checks the SIA in the tblMaster Sheet, to see if it contains data. If it does the background of the Surname is Red, if not, black.

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,916
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Rob,

    Ok, place the code in the AfterUpdate event of your combo box vs the Form_Current event! HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  16. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Still not working. Let me get this clear. DeceasedDte is a field on the table Hospital. PatientNo is a field on the table, and Me!Patient No is control on the form? So you're checking that the patient numbers match, then check that the DeceasedDte is Null? Have I got it right?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •