Forms and Conditional formats

[jlgray0127]

I am entering part numbers into a form. (table 1) Each time I enter a value, I want the form to look at table 2 and search for that same part number. If the record exsists, I want that record in the form to be highlighted. Any thoughts on how to do this?

[Bob Fitz]

Can you give some clarification please.

If the record exsists, I want that record in the form to be highlighted.

The record in which form? Do you mean the current record of the form you are entering data in to?

[jlgray0127]

Yes. I am entering part numbers into table 1, via a form. If the part number is in my list, table 2, I want the field in the form (table 1) where I just entered the number to highlight yellow.
I believe when I tab outta the record when I enter the number, that I can have it highlight the record if the number exsists on table 2...
I will need to retain that info though, for future reference...

[Bob Fitz]

Try this in the After Update event of your tex box:

If DCount("[NameOfFieldInTable1]", "Table1", "[NameOfFieldInTable1]='" & Me.ActiveControl & "'") > 0 Then
Me.ActiveControl.BackColor = 8454143 'yellow
Else
Me.ActiveControl.BackColor = 16777215 'white
End If

[jlgray0127]

Doesn't there have to be a reference to table 2? I am not very good with code yet, so get confused very easily with it! lol
I am great in access when it comes to all the predefined controls and making them work! This is definately above my head! I appreciate your help!

[Bob Fitz]

Sorry, my mistake
Should be:

If DCount("[NameOfFieldInTable2]", "Table2", "[NameOfFieldInTable2]='" & Me.ActiveControl & "'") > 0 Then
Me.ActiveControl.BackColor = 8454143 'yellow
Else
Me.ActiveControl.BackColor = 16777215 'white
End If 

[jlgray0127]

It works!!!! Thank you!!!!!

[Bob Fitz]

Glad you have what you need .

You may want to put the code in the form's OnCurrent event as well.

[jlgray0127]

Ok. I will try that and see how it works. Again, thank you!

[Bob Fitz]

Glad we have been able to help. Let us know how you get on.

[jlgray0127]

Ok... now, I have this down. Now I want to try something else, using this same code, for another record in the form...

If DCount("[NameOfFieldInTable2]", "Table2", "[NameOfFieldInTable2]='" & Me.ActiveControl & "'") > 0 Then
Me.ActiveControl.BackColor = 8454143 'yellow
Else
Me.ActiveControl.BackColor = 16777215 'white
End If

Instead of changing the format, I want to run a macro that I wrote, but am not sure how to word the command? Anythoughts?

[Bob Fitz]

Hi
I don't really use macros, but you could try this line of code:

DoCmd.RunMacro "NameOfYourMacro"