Using If DCount in one table to color box in form based on diff table

[Tarnish]

Hi all, nice forums!

I'm really sorry for the long question.

Goal: I'm trying to color a box on one form (my "main" form) based on whether or not there are records in a table (not the table the "main" form is based on) that match a field in the main form. Specifically, I have an "ID" field in my main table that appears on the main form. Users view the form in single form view, and each record has an ID. I want to use that ID number to filter out just the records in a second table, and then DCount them and react to the count.

I already have a command button that opens a form that displays the records in the second table that correspond to the [ID] in the main form, but I decided I wanted to make it so when you were in the main form you could tell if there were any records that would come up using the button without having to actually use it to find out. So I added a little box below it and I wanted to color it based on using the DCount function to count the records in the "second" table with the criterion of a matching ID with the currently displayed main form record. If the count was >0, I wanted the box on the main form to display one color. If not >0, then the box would display another color.

This is wrong (hence the question), but here's what is currently in the "on_current" event of my main form that is suppose to be a solution:

If DCount("*", "tblReportLinks", "[ID]='" & Me![ID]')>0 Then
Me.Box117.BackColor = RGB(0, 0, 0)
Else
Me.Box117.BackColor = RGB(255, 255, 255)

End If

Where: box117 is the control on my main form that should change colors, tblReportLinks is my "second" table, [ID] is a field (the same field) on both the tables and both the forms

All the problems I've had deal with the first line. I've tried putting quotations around the last apostrophe, using & "'" instead of just the last apostrophe, not using apostrophes (either of them) at all, and a bunch of other things related to the quotations and apostrophes, and I still can't get it to work.

The use of the apostrophes came from another post on this site:

http://www.tek-tips.com/viewthread.cfm?qid=1208506&page=1

That post had more than one criterion, so I tried varying my code to account for that fact, but still no luck.

I've tried finding answers on another forum, but no luck. The closest I've felt was reading that linked thread above, but that's old now so...

I mostly get some type of syntax error (the above code produces syntax error but doesn't give any more info about what the problem is).

My application doesn't utilize DCount anywhere else, and, as I said, it's in the 'on_current' event (sub) of the main form. Can it not be in a sub? I have the reference checked to Microsoft ActiveX Data Objects 2.1 Library (in the references list in the vba code window), but there are numerous others that aren't checked in the reference window, like the 2.0, 2.5, 2.6, 2.7, 2.8 versions of the above, and something called Microsoft ActiveX Data Objects Recordset 2.8 Library. Are any of those required to use DCount?

I'm still thinking it's the code, but I'm trying to disclose enough info that if it's not, someone might be able to recognize that fact.

Anyway, any help getting this to work would be appreciated.
I'm just stuck here.

Thanks,
T

 

[randy700]

How about...

If DCount("ID", "tblReportLinks", "ID = " & me!ID) > 0 Then
   me.Box117.BackColor = vbWhite
Else
   me.Box117.BackColor = vbRed
End If

What you're looking for is the result of your DCount function.. that is, is it greater than 0. If yes, make the back color white. If not, make it red. Another possibility, if you want additional color options would be...

SELECT Case DCount("ID", "tblReportLinks", "ID = " & me!ID)
   Case < 0:
      me.Box117.BackColor = vbGreen
   Case = 0:
      me.Box117.BackColor = vbWhite
   Case > 0:
      me.Box117.BackColor = vbRed
End Select

If ID is not numeric, change that portion of the DCount to
"ID = ' " & me!ID & " ' "


Randy

 

[Tarnish]

Thanks for the reply, Randy.

Both those result in the all too familiar following error:

Run-Time Error 3075
Syntax Error (missing operator) in query expression 'ID ='.

I just don't get it.

Thanks again,
T

 

[Tarnish]

Could it be getting messed up because the form opens on a new record and, therefore, there is no value for [ID]?

T

 

[Tarnish]

Wow! Finally got it to work. Thanks!

I just put that select code into an "if/then" statement like this:

If me.newrecord=true then
Goto NewR
Else
'Your select code here
NewR:
EndIF

Thanks again....I feel like I've been let out of prison.

T

 

[randy700]

Glad you got it working.


Randy