Results 1 to 9 of 9
  1. #1
    elmister is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    12

    Flash green or red

    Hello Access Gods.

    I have a form with one (field1) on it. Where users will enter data.



    I would like to have a unbound textbox on the same form that will flash green if the data entered in field1 matches a value in Table. Or flash red if it doesnt find a match.


    I tried the conditional formating but i think my expressions are off.

    Any suggestions.
    thanks you.

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I already answered this in your other post:

    I have only used conditional formatting in Reports. Never tried to use it on Forms.

    My first instinct would be to write code to:

    1. Open a recordset and loop through it looking for your matching data in the appropriate field.
    It looks something like this - but you'll have to fill in real logic:

    Code:
     
    Dim db As DAO.Database
    Dim rs As DAO.Recordset 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 
    With rs
    Do While Not rs.EOF
    'Do your processing here. Eg:
     
    'Get the value in the field you are searching in the current record.
    strValueFromDB = rs![FieldName] 
     
    'Compare value on form with field name to see if there's a match.
    If strValueFromDB = Me.ControlNameOnForm Then 
     
    'Code to change BackColor to Green
    'Match found - so Close up shop & Exit. 
     
    End If 
    .MoveNext 'Move to next row of data 
    Loop 
    End With
    rs.Close
    Set db = Nothing
    ** This is just guidelines - not fully operational code.

    2. If a match is found, then change the BackColor property of the textbox to green.
    Usually, if you type in 'Me.ControlName.' - as soon as you type in the last dot, Access will drop down a list of properties that you can modify for that control. Scroll through the list and find BackColor. You should end up with something like Me.ControlName.BackColor = vbGreen

    3. If no match is found, then change the BackColor property of the textbox to red.

    I hope this gives you a starting point.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's another example:

    This is code related to the form (see attached jpg)

    Private Sub txtFld_AfterUpdate()
    Debug.Print "AfterUpdate " & Me.txtFld.Value
    If DCount("*", "NewHires", "Lastname = '" & Me.txtFld & "'") > 0 Then
    Me.Box2.BackColor = vbGreen
    Else
    Me.Box2.BackColor = vbRed
    End If
    End Sub
    The Dcount is testing if the value of the txtFld is in the table.
    In my case, my table is called NewHires and the field involved is Lastname.
    You could adjust as needed.

    You didn't say what field you were comparing, so I created a sample of my choosing.

    In my form the text8 is just a list showing the Lastnames in NewHire. It's for convenience only.

    You can see a sample of the Green box RedGreen.jpg

    and the red box (not in table) elm.jpg

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Orange,
    I like your solution!
    A LOT cleaner than mine!
    I've used DLookup before - never DCount.
    Thanks.
    Last edited by Robeen; 08-24-2011 at 12:43 PM. Reason: Wanted to add a Note.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There's more than 1 way to do just about anything. Glad you liked it.

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Is there a 'class' of useful 'functions' like DLookup & DCount that I can search through to educate myself on various Access 'utilities'?

  7. #7
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might also check out ELookup() by Allen Browne:

    http://allenbrowne.com/ser-42.html

    There are also other replacements: EAvg() and ECount()

    see "Tips for Programmers" at http://allenbrowne.com/tips.html

  8. #8
    elmister is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    12
    orange,
    thanks that looks like it might work. Thank you!!

  9. #9
    elmister is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    12
    Orange , that worked exactly like i wanted thank you!! very much. .

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

Similar Threads

  1. Help, I get a green thing when i put a label.
    By Starstruck00 in forum Forms
    Replies: 2
    Last Post: 06-02-2011, 10:47 PM
  2. Replies: 9
    Last Post: 11-23-2009, 09:20 PM
  3. Printing Flash Cards
    By EricMK in forum Reports
    Replies: 1
    Last Post: 07-08-2009, 12:24 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