DCount function.

[TAN]

Dear NG,

I would like to get some help on the above function with
ref. to my structured DB.
I've three main tables:

Materials:
IDMaterial (Counter)

Sizes:
SizeIDMaterial (Integer)
Size (Text)

SpecSheet
SpecIDMaterial (Integer)
SpecSize (Text)

I made a new form from table Material with 2 subforms, one
for Sizes and one for SpecSheet; in the last two I want to
see all existing sizes for the selected material and, above
all, if the selected material has its own Spec Sheet or
not.
In order to avoid the user from scrolling all existing
sizes of the selected material before finding which one
have spec sheet I would like to highligh these ones with a
different colour. I tryed to verify if this condition is
true or false using the following code on the Sizes
subform:

Private Sub Form_Current()

Dim SizeSpecExist

SizeSpecExist = DCount("[SpecIDMaterial]", "SpecSheet", _
"[SpecIDMaterial]=" & Me!SizeIDMaterial & _
" and [SpecSize]=" & Chr(34) & Me!Size & Chr(34))

If (SizeSpecExist <> 0) Then
Me!Size.ForeColor = 255

End If
End Sub

Even if there are datas in SpecSheet table which are right
for this test, it seems not to check correctly the
conditions; in fact Size field is always black or always
red.
I would greatly appreciate some help on that matter.
Thanks you,

TAN

 

[Marshall Barton]

I would like to get some help on the above function with
ref. to my structured DB.
I've three main tables:

Materials:
IDMaterial (Counter)

Sizes:
SizeIDMaterial (Integer)
Size (Text)

SpecSheet
SpecIDMaterial (Integer)
SpecSize (Text)

I made a new form from table Material with 2 subforms, one
for Sizes and one for SpecSheet; in the last two I want to
see all existing sizes for the selected material and, above
all, if the selected material has its own Spec Sheet or
not.
In order to avoid the user from scrolling all existing
sizes of the selected material before finding which one
have spec sheet I would like to highligh these ones with a
different colour. I tryed to verify if this condition is
true or false using the following code on the Sizes
subform:

Private Sub Form_Current()

Dim SizeSpecExist

SizeSpecExist = DCount("[SpecIDMaterial]", "SpecSheet", _
"[SpecIDMaterial]=" & Me!SizeIDMaterial & _
" and [SpecSize]=" & Chr(34) & Me!Size & Chr(34))

If (SizeSpecExist <> 0) Then
Me!Size.ForeColor = 255

End If
End Sub

Even if there are datas in SpecSheet table which are right
for this test, it seems not to check correctly the
conditions; in fact Size field is always black or always
red.

This can be done by using a query for the form's record
source. The query only needs to use an outer join on the
two tables so the records that have a matching entry will
have a value and the ones without a match will have Null.

The query will be something like:

SELECT Sizes.*, SpecSheet.SpecSize
FROM Sizes LEFT JOIN SpecSheet
ON Sizes.Size = SpecSheet.SpecSize

You can then use Conditional Formatting with a test for the
SpecSize field being Null or not.