DCount count records based on field value?

[Guest]

Can DCount be used to count the records in a table with a particular value in
one of the fields?

I have a form that's bound to my main table. On that form is a cmd button
to open a form that's bound to a related table. When I click the button,
just the records with the pk value in the main form will display in the
second form. What I want is an If statement attached to the current form
event of the main form such that if there are any related records in the
second table (the one bound to the form that opens with the cmd button on the
main form), then I want a color change in a box on the main form.

This is what I tried when my mind was asleep:

If DCount("*", "tblReportLinks") > 0 Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbBlack

End If

However, that doesn't limit the records to just the ones related to the
record displaying in the main form. How do I limit the records counted to
just those associated with the ID (pk) in the main form? The ID in the main
form is a fk in the form that opens with the command button...

Hope that's understandable.

Thanks,
CW

 

[Guest]

In any case, you can limit the DCount like this. Assuming Me.txtPrime is the
control on your form that has the value you want to filter on:

If DCount("*", "tblReportLinks", "[SomeField] = " & Me.txtPrime) > 0 Then

 

[Guest]

Thanks for the reply,

It doesn't seem to be working. I keep getting a syntax error "missing
operator" that seems to be targeted to the "[somefield] = " part of the code.

It's probably the messed up way I've set things up, but I can't figure out
why.

The cmd button on my main form really opens a form with a subform. One of
the tblReportLinks fields is in the form that opens, while the rest are in
the subform. I don't really see why that would matter.

I'm assumming [Somefield] is the field in the tblREportLinks and the
Me.txtPrime is the control on the main form? I've tried switching them
around but that's all that makes sense.

I'll play around with it and figure it out sooner or later.

Thanks again for the help,
CW

In any case, you can limit the DCount like this. Assuming Me.txtPrime is the
control on your form that has the value you want to filter on:

If DCount("*", "tblReportLinks", "[SomeField] = " & Me.txtPrime) > 0 Then

Can DCount be used to count the records in a table with a particular value in
one of the fields?

I have a form that's bound to my main table. On that form is a cmd button
to open a form that's bound to a related table. When I click the button,
just the records with the pk value in the main form will display in the
second form. What I want is an If statement attached to the current form
event of the main form such that if there are any related records in the
second table (the one bound to the form that opens with the cmd button on the
main form), then I want a color change in a box on the main form.

This is what I tried when my mind was asleep:

If DCount("*", "tblReportLinks") > 0 Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbBlack

End If

However, that doesn't limit the records to just the ones related to the
record displaying in the main form. How do I limit the records counted to
just those associated with the ID (pk) in the main form? The ID in the main
form is a fk in the form that opens with the command button...

Hope that's understandable.

Thanks,
CW

 

[Guest]

I'm assumming [Somefield] is the field in the tblREportLinks and the

Me.txtPrime is the control on the main form?

Yes, the assumption is correct. Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime & "'") > 0
Then

Thanks for the reply,

It doesn't seem to be working. I keep getting a syntax error "missing
operator" that seems to be targeted to the "[somefield] = " part of the code.

It's probably the messed up way I've set things up, but I can't figure out
why.

The cmd button on my main form really opens a form with a subform. One of
the tblReportLinks fields is in the form that opens, while the rest are in
the subform. I don't really see why that would matter.

I'm assumming [Somefield] is the field in the tblREportLinks and the
Me.txtPrime is the control on the main form? I've tried switching them
around but that's all that makes sense.

I'll play around with it and figure it out sooner or later.

Thanks again for the help,
CW

In any case, you can limit the DCount like this. Assuming Me.txtPrime is the
control on your form that has the value you want to filter on:

If DCount("*", "tblReportLinks", "[SomeField] = " & Me.txtPrime) > 0 Then

Can DCount be used to count the records in a table with a particular value in
one of the fields?

I have a form that's bound to my main table. On that form is a cmd button
to open a form that's bound to a related table. When I click the button,
just the records with the pk value in the main form will display in the
second form. What I want is an If statement attached to the current form
event of the main form such that if there are any related records in the
second table (the one bound to the form that opens with the cmd button on the
main form), then I want a color change in a box on the main form.

This is what I tried when my mind was asleep:

If DCount("*", "tblReportLinks") > 0 Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbBlack

End If

However, that doesn't limit the records to just the ones related to the
record displaying in the main form. How do I limit the records counted to
just those associated with the ID (pk) in the main form? The ID in the main
form is a fk in the form that opens with the command button...

Hope that's understandable.

Thanks,
CW

 

[Guest]

Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime & "'") > 0
Then

The data types are both numbers. Well, in the main form it's an autonumber
while in the other form/subform it's a number (long integer).

I think maybe I know what the problem is. My form that opens up (the one
with the subform) has a default value for the [ID] field (the field in
question, here) based on the value of the [ID] field in the main form. I
think there's another way to get that value from the main form to the second
form besides putting something in for a default value. I know when I added
the subform via the subform toolbox button/wizard, it allowed me to specify
that I only wanted to show the records in the subform that corresponded to
the [ID] field from the main form.

Hopefully, if I can figure out how I've put those values from one form to
another besides putting an expression in for default value of the second
form's field, it might straighten everything out.

Again, I appreciate the help. I think I'm heading in the right direction.
CW

 

[Guest]

I still can't get this thing to work, and I might be losing my mind. There
has to be a simple explanation. On the chance that someone will look at it,
I'm going to update the "premise" fully since some things have changed.
First, here's the code I'm trying to get to work:

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0 Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbWhite

End If

Now, it's slightly modified reflecting the different things I was trying.
The two things that were modified was I moved the last set of quotation marks
around and instead of using me.ID I tried the full approach to referencing
the control. The original code is provided above by Klatuu.

This code is located in the "on_current" event of my main form "Issues". It
is suppose to count the records in a table, tblReportLinks, associated with
the file that's currently displayed in the main form. You actually get to
those files by pressing a command button on the Issues form that opens
another form, frmReportDocLinks. That command button has the following code
in it's "on_click" event:

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

This code opens the second form and displays only the records from the table
(tblReportLinks) that correspond with the record displayed in the main form.
The "ID" is the common field (pk of the main form, fk in the second form).

The button works, and the second form opens and displays the correct records
and otherwise functions fine. When I first load the main form, it opens to a
new (blank) record and the box is red. When I use the regular access buttons
to navigate to other records on the main form, the box turns white (for all
of them) and is white regardless of whether or not there are records in the
second table (viewable in the second form if/when I click the command
button) associated with the record I'm viewing in the main form.

The idea, seemingly so simple, was to count the records in the second table
associated with the currently displayed record in the main form, and change
the color of a box on the main form depending on if there were any ( >0)
records meeting that criterion.

Any help would be greatly appreciated. It's become the white whale and even
if I catch it it's going to turn out to be a minnow but I WILL catch it!

CW

Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime & "'") > 0
Then

The data types are both numbers. Well, in the main form it's an autonumber
while in the other form/subform it's a number (long integer).

I think maybe I know what the problem is. My form that opens up (the one
with the subform) has a default value for the [ID] field (the field in
question, here) based on the value of the [ID] field in the main form. I
think there's another way to get that value from the main form to the second
form besides putting something in for a default value. I know when I added
the subform via the subform toolbox button/wizard, it allowed me to specify
that I only wanted to show the records in the subform that corresponded to
the [ID] field from the main form.

Hopefully, if I can figure out how I've put those values from one form to
another besides putting an expression in for default value of the second
form's field, it might straighten everything out.

Again, I appreciate the help. I think I'm heading in the right direction.
CW

 

[Ian B]

Butting in to the middle of this thread - Why the " after
[Forms]![Issues]![ID]
The compiler will spit that!

HTH

Ian B

I still can't get this thing to work, and I might be losing my mind. There
has to be a simple explanation. On the chance that someone will look at it,
I'm going to update the "premise" fully since some things have changed.
First, here's the code I'm trying to get to work:

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0 Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbWhite

End If

Now, it's slightly modified reflecting the different things I was trying.
The two things that were modified was I moved the last set of quotation marks
around and instead of using me.ID I tried the full approach to referencing
the control. The original code is provided above by Klatuu.

This code is located in the "on_current" event of my main form "Issues". It
is suppose to count the records in a table, tblReportLinks, associated with
the file that's currently displayed in the main form. You actually get to
those files by pressing a command button on the Issues form that opens
another form, frmReportDocLinks. That command button has the following code
in it's "on_click" event:

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

This code opens the second form and displays only the records from the table
(tblReportLinks) that correspond with the record displayed in the main form.
The "ID" is the common field (pk of the main form, fk in the second form).

The button works, and the second form opens and displays the correct records
and otherwise functions fine. When I first load the main form, it opens to a
new (blank) record and the box is red. When I use the regular access buttons
to navigate to other records on the main form, the box turns white (for all
of them) and is white regardless of whether or not there are records in the
second table (viewable in the second form if/when I click the command
button) associated with the record I'm viewing in the main form.

The idea, seemingly so simple, was to count the records in the second table
associated with the currently displayed record in the main form, and change
the color of a box on the main form depending on if there were any ( >0)
records meeting that criterion.

Any help would be greatly appreciated. It's become the white whale and even
if I catch it it's going to turn out to be a minnow but I WILL catch it!

CW

Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime & "'") > 0
Then

The data types are both numbers. Well, in the main form it's an autonumber
while in the other form/subform it's a number (long integer).

I think maybe I know what the problem is. My form that opens up (the one
with the subform) has a default value for the [ID] field (the field in
question, here) based on the value of the [ID] field in the main form. I
think there's another way to get that value from the main form to the second
form besides putting something in for a default value. I know when I added
the subform via the subform toolbox button/wizard, it allowed me to specify
that I only wanted to show the records in the subform that corresponded to
the [ID] field from the main form.

Hopefully, if I can figure out how I've put those values from one form to
another besides putting an expression in for default value of the second
form's field, it might straighten everything out.

Again, I appreciate the help. I think I'm heading in the right direction.
CW

 

[Guest]

That's why I pointed to the code posted by Klatuu and stated that I had
changed things around with what was included in my last post in an attempt to
find a solution after not being able to get the original (posted by Klatuu)
to work.

The bottom line is I can't get Klatuu's code to work and I don't know where
to go from here.

Thanks for the reply.
CW

 

[John Spencer]

Without reading the entire thread and following all of it, I can say that
the first line below

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0 Then

should probably read (note the repositioning of the quote marks.

If DCount("*", "tblReportLinks"," [ID] = "& [Forms]![Issues]![ID]) > 0 Then

Is the form where you are executing this code named Issues? Does it have a
control named ID?

I still can't get this thing to work, and I might be losing my mind. There
has to be a simple explanation. On the chance that someone will look at
it,
I'm going to update the "premise" fully since some things have changed.
First, here's the code I'm trying to get to work:

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0
Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbWhite

End If

Now, it's slightly modified reflecting the different things I was
trying.
The two things that were modified was I moved the last set of quotation
marks
around and instead of using me.ID I tried the full approach to referencing
the control. The original code is provided above by Klatuu.

This code is located in the "on_current" event of my main form "Issues".
It
is suppose to count the records in a table, tblReportLinks, associated
with
the file that's currently displayed in the main form. You actually get to
those files by pressing a command button on the Issues form that opens
another form, frmReportDocLinks. That command button has the following
code
in it's "on_click" event:

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

This code opens the second form and displays only the records from the
table
(tblReportLinks) that correspond with the record displayed in the main
form.
The "ID" is the common field (pk of the main form, fk in the second form).

The button works, and the second form opens and displays the correct
records
and otherwise functions fine. When I first load the main form, it opens
to a
new (blank) record and the box is red. When I use the regular access
buttons
to navigate to other records on the main form, the box turns white (for
all
of them) and is white regardless of whether or not there are records in
the
second table (viewable in the second form if/when I click the command
button) associated with the record I'm viewing in the main form.

The idea, seemingly so simple, was to count the records in the second
table
associated with the currently displayed record in the main form, and
change
the color of a box on the main form depending on if there were any ( >0)
records meeting that criterion.

Any help would be greatly appreciated. It's become the white whale and
even
if I catch it it's going to turn out to be a minnow but I WILL catch it!

CW

Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the
correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime & "'")
0
Then

The data types are both numbers. Well, in the main form it's an
autonumber
while in the other form/subform it's a number (long integer).

I think maybe I know what the problem is. My form that opens up (the one
with the subform) has a default value for the [ID] field (the field in
question, here) based on the value of the [ID] field in the main form. I
think there's another way to get that value from the main form to the
second
form besides putting something in for a default value. I know when I
added
the subform via the subform toolbox button/wizard, it allowed me to
specify
that I only wanted to show the records in the subform that corresponded
to
the [ID] field from the main form.

Hopefully, if I can figure out how I've put those values from one form to
another besides putting an expression in for default value of the second
form's field, it might straighten everything out.

Again, I appreciate the help. I think I'm heading in the right
direction.
CW

 

[Guest]

Thanks for the reply,

I've tried that code (and tried it again just now). I get this error:

Runtime Error 3075
Syntax Error (missing operator) in Query Expression '[ID]='.

And yes, The main form is called "Issues" (where this code is attached to
the "on_current" event) and the control on Issues is called [ID] (as is,
sloppily, the name of the field on the underlying table).

It just doesn't work.

thanks,
CW

Without reading the entire thread and following all of it, I can say that
the first line below

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0 Then

should probably read (note the repositioning of the quote marks.

If DCount("*", "tblReportLinks"," [ID] = "& [Forms]![Issues]![ID]) > 0 Then

Is the form where you are executing this code named Issues? Does it have a
control named ID?

I still can't get this thing to work, and I might be losing my mind. There
has to be a simple explanation. On the chance that someone will look at
it,
I'm going to update the "premise" fully since some things have changed.
First, here's the code I'm trying to get to work:

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0
Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbWhite

End If

Now, it's slightly modified reflecting the different things I was
trying.
The two things that were modified was I moved the last set of quotation
marks
around and instead of using me.ID I tried the full approach to referencing
the control. The original code is provided above by Klatuu.

This code is located in the "on_current" event of my main form "Issues".
It
is suppose to count the records in a table, tblReportLinks, associated
with
the file that's currently displayed in the main form. You actually get to
those files by pressing a command button on the Issues form that opens
another form, frmReportDocLinks. That command button has the following
code
in it's "on_click" event:

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

This code opens the second form and displays only the records from the
table
(tblReportLinks) that correspond with the record displayed in the main
form.
The "ID" is the common field (pk of the main form, fk in the second form).

The button works, and the second form opens and displays the correct
records
and otherwise functions fine. When I first load the main form, it opens
to a
new (blank) record and the box is red. When I use the regular access
buttons
to navigate to other records on the main form, the box turns white (for
all
of them) and is white regardless of whether or not there are records in
the
second table (viewable in the second form if/when I click the command
button) associated with the record I'm viewing in the main form.

The idea, seemingly so simple, was to count the records in the second
table
associated with the currently displayed record in the main form, and
change
the color of a box on the main form depending on if there were any ( >0)
records meeting that criterion.

Any help would be greatly appreciated. It's become the white whale and
even
if I catch it it's going to turn out to be a minnow but I WILL catch it!

CW

:

Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the
correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime & "'")
0
Then


The data types are both numbers. Well, in the main form it's an
autonumber
while in the other form/subform it's a number (long integer).

I think maybe I know what the problem is. My form that opens up (the one
with the subform) has a default value for the [ID] field (the field in
question, here) based on the value of the [ID] field in the main form. I
think there's another way to get that value from the main form to the
second
form besides putting something in for a default value. I know when I
added
the subform via the subform toolbox button/wizard, it allowed me to
specify
that I only wanted to show the records in the subform that corresponded
to
the [ID] field from the main form.

Hopefully, if I can figure out how I've put those values from one form to
another besides putting an expression in for default value of the second
form's field, it might straighten everything out.

Again, I appreciate the help. I think I'm heading in the right
direction.
CW

 

[John Spencer]

Which piece of code is generating the error. It sounds like it may be the
code that is calling the other form

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If ID is not a field in the form frmReportDocLinks then that might generate
the error. OR if the source for frmReportDocLinks has fields in two or more
tables with the name ID that could cause an error.

Also, is ID a number field or is it a text field?

Thanks for the reply,

I've tried that code (and tried it again just now). I get this error:

Runtime Error 3075
Syntax Error (missing operator) in Query Expression '[ID]='.

And yes, The main form is called "Issues" (where this code is attached to
the "on_current" event) and the control on Issues is called [ID] (as is,
sloppily, the name of the field on the underlying table).

It just doesn't work.

thanks,
CW

Without reading the entire thread and following all of it, I can say that
the first line below

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0
Then

should probably read (note the repositioning of the quote marks.

If DCount("*", "tblReportLinks"," [ID] = "& [Forms]![Issues]![ID]) > 0
Then

Is the form where you are executing this code named Issues? Does it have
a
control named ID?

I still can't get this thing to work, and I might be losing my mind.
There
has to be a simple explanation. On the chance that someone will look
at
it,
I'm going to update the "premise" fully since some things have changed.
First, here's the code I'm trying to get to work:

If DCount("*", "tblReportLinks", [ID] = "& [Forms]![Issues]![ID]") > 0
Then
Me.Box117.BackColor = vbRed
Else
Me.Box117.BackColor = vbWhite

End If

Now, it's slightly modified reflecting the different things I was
trying.
The two things that were modified was I moved the last set of quotation
marks
around and instead of using me.ID I tried the full approach to
referencing
the control. The original code is provided above by Klatuu.

This code is located in the "on_current" event of my main form
"Issues".
It
is suppose to count the records in a table, tblReportLinks, associated
with
the file that's currently displayed in the main form. You actually get
to
those files by pressing a command button on the Issues form that opens
another form, frmReportDocLinks. That command button has the
following
code
in it's "on_click" event:

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

This code opens the second form and displays only the records from the
table
(tblReportLinks) that correspond with the record displayed in the main
form.
The "ID" is the common field (pk of the main form, fk in the second
form).

The button works, and the second form opens and displays the correct
records
and otherwise functions fine. When I first load the main form, it
opens
to a
new (blank) record and the box is red. When I use the regular access
buttons
to navigate to other records on the main form, the box turns white (for
all
of them) and is white regardless of whether or not there are records
in
the
second table (viewable in the second form if/when I click the command
button) associated with the record I'm viewing in the main form.

The idea, seemingly so simple, was to count the records in the second
table
associated with the currently displayed record in the main form, and
change
the color of a box on the main form depending on if there were any (
0)
records meeting that criterion.

Any help would be greatly appreciated. It's become the white whale and
even
if I catch it it's going to turn out to be a minnow but I WILL catch
it!

CW

:

:

Now, the question is, what is the data type
of [SomeField]? If it is a text field rather than a numeric the
correct
syntax would be
If DCount("*", "tblReportLinks", "[SomeField] = '" & Me.txtPrime &
"'")
0
Then


The data types are both numbers. Well, in the main form it's an
autonumber
while in the other form/subform it's a number (long integer).

I think maybe I know what the problem is. My form that opens up (the
one
with the subform) has a default value for the [ID] field (the field in
question, here) based on the value of the [ID] field in the main form.
I
think there's another way to get that value from the main form to the
second
form besides putting something in for a default value. I know when I
added
the subform via the subform toolbox button/wizard, it allowed me to
specify
that I only wanted to show the records in the subform that
corresponded
to
the [ID] field from the main form.

Hopefully, if I can figure out how I've put those values from one form
to
another besides putting an expression in for default value of the
second
form's field, it might straighten everything out.

Again, I appreciate the help. I think I'm heading in the right
direction.
CW

 

[Guest]

John,

I do really appreciate the help. This is driving me nuts. I've begun
typing "All work and no play makes jack...."

The code you commented on earlier is what's causing the error. Without it
everything's fine except I don't accomplish the small bit of "slick" I was
trying to add. I probably should have given up already, but you probably
know how that goes...

There's a fairly large module on the main form. Here's the code from the
on_current event in full:

Private Sub Form_Current()
If Me.NewRecord = True Then
lblNewRec.Visible = True
txtHeadCat.Visible = False
txtHeadReqPty.Visible = False
Me.cmdDateP.Enabled = True
Me.AllowEdits = True

Else
lblNewRec.Visible = False
txtHeadCat.Visible = True
txtHeadReqPty.Visible = True
Me.cmdDateP.Enabled = False
Me.AllowEdits = False

End If

If DCount("*", "tblReportLinks", " [ID] = " & [Forms]![Issues]![ID]) > 0
Then
[Box117].BackColor = vbRed
Else
[Box117].BackColor = vbBlack
End If

End Sub

PS: I wrote the rest of the code above, and it was one of my first efforts.
There's probably a better way to do what I did, but it works and doesn't give
any errors. I'm thinking about changing it to not use the allowedits, but I
digress...

Here's the code on the button in the main form that opens the form that's
based SOLELY on the tblReportLinks:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

I believe the above code was written by the cmd button wizard.

There's a box on the main form, Box117, that was added using the toolbar
control. It's suppose to change colors depending on the DCount function
that's not working. What's suppose to happen is that when my single (main)
form Issues triggers the "on_current" event, the DCount should run and count
the number of records in the tblReportLinks that match the ID in the issue
currently shown in the main "Issues" form. If it's >0, meaning there are
matching records in the second table, that's when the box should display
color red indicating there's related documents to be seen by clicking the
button.

The [ID] field appears in both forms, and both underlying tables (though, in
reality, the main form is based on a query.....but the [ID] is in there too).

I'd be glad to provide any other info. I wrote out a long explanation above
but I'd be more than willing to respond to anything else.

I understand if you tire of this, believe me. I just can't believe there's
not a simple explanation.

Thanks again,
CW

Which piece of code is generating the error. It sounds like it may be the
code that is calling the other form

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If ID is not a field in the form frmReportDocLinks then that might generate
the error. OR if the source for frmReportDocLinks has fields in two or more
tables with the name ID that could cause an error.

Also, is ID a number field or is it a text field?

 

[Guest]

Could it be possible that I don't have a required reference to use the DCount
function (or some other part of that code)?

I was reading about working with recordsets in VBA (which I know is
something a little different), and there was mention of needing to have the
ADO reference. I have an ADO reference (2.1), but there looks like there are
higher ones in the reference list up to maybe 2.8.

Just spitballing. If that's not it, I'm going to see if I can't just use a
recordset to get a list of records and then try the dcount on that
recordset...

CW

John,

I do really appreciate the help. This is driving me nuts. I've begun
typing "All work and no play makes jack...."

The code you commented on earlier is what's causing the error. Without it
everything's fine except I don't accomplish the small bit of "slick" I was
trying to add. I probably should have given up already, but you probably
know how that goes...

There's a fairly large module on the main form. Here's the code from the
on_current event in full:

Private Sub Form_Current()
If Me.NewRecord = True Then
lblNewRec.Visible = True
txtHeadCat.Visible = False
txtHeadReqPty.Visible = False
Me.cmdDateP.Enabled = True
Me.AllowEdits = True

Else
lblNewRec.Visible = False
txtHeadCat.Visible = True
txtHeadReqPty.Visible = True
Me.cmdDateP.Enabled = False
Me.AllowEdits = False

End If

If DCount("*", "tblReportLinks", " [ID] = " & [Forms]![Issues]![ID]) > 0
Then
[Box117].BackColor = vbRed
Else
[Box117].BackColor = vbBlack
End If

End Sub

PS: I wrote the rest of the code above, and it was one of my first efforts.
There's probably a better way to do what I did, but it works and doesn't give
any errors. I'm thinking about changing it to not use the allowedits, but I
digress...

Here's the code on the button in the main form that opens the form that's
based SOLELY on the tblReportLinks:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

I believe the above code was written by the cmd button wizard.

There's a box on the main form, Box117, that was added using the toolbar
control. It's suppose to change colors depending on the DCount function
that's not working. What's suppose to happen is that when my single (main)
form Issues triggers the "on_current" event, the DCount should run and count
the number of records in the tblReportLinks that match the ID in the issue
currently shown in the main "Issues" form. If it's >0, meaning there are
matching records in the second table, that's when the box should display
color red indicating there's related documents to be seen by clicking the
button.

The [ID] field appears in both forms, and both underlying tables (though, in
reality, the main form is based on a query.....but the [ID] is in there too).

I'd be glad to provide any other info. I wrote out a long explanation above
but I'd be more than willing to respond to anything else.

I understand if you tire of this, believe me. I just can't believe there's
not a simple explanation.

Thanks again,
CW

Which piece of code is generating the error. It sounds like it may be the
code that is calling the other form

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If ID is not a field in the form frmReportDocLinks then that might generate
the error. OR if the source for frmReportDocLinks has fields in two or more
tables with the name ID that could cause an error.

Also, is ID a number field or is it a text field?

 

[Guest]

Finally figured out the problem and figured I'd post the answer back here.

The code I received here, as I suspected, was fine. However, since my form
opens on a new record, and new records don't have [ID] values, not only was
the code thrown off, but I couldn't even figure out that it was a problem
with only new records because every time I tried to open the form I got the
error (because it always opens in a new record). I could never see that the
code would work with existing records.

Anywho, I used some slightly different code, but I wrapped it with an
IF/Then statement like this:

If Me.NewRecord=True Then
Goto NewR
Else
'Rest of code
NewR:
End If

Works perfectly.

Thanks again for all the help,
CW

Could it be possible that I don't have a required reference to use the DCount
function (or some other part of that code)?

I was reading about working with recordsets in VBA (which I know is
something a little different), and there was mention of needing to have the
ADO reference. I have an ADO reference (2.1), but there looks like there are
higher ones in the reference list up to maybe 2.8.

Just spitballing. If that's not it, I'm going to see if I can't just use a
recordset to get a list of records and then try the dcount on that
recordset...

CW

John,

I do really appreciate the help. This is driving me nuts. I've begun
typing "All work and no play makes jack...."

The code you commented on earlier is what's causing the error. Without it
everything's fine except I don't accomplish the small bit of "slick" I was
trying to add. I probably should have given up already, but you probably
know how that goes...

There's a fairly large module on the main form. Here's the code from the
on_current event in full:

Private Sub Form_Current()
If Me.NewRecord = True Then
lblNewRec.Visible = True
txtHeadCat.Visible = False
txtHeadReqPty.Visible = False
Me.cmdDateP.Enabled = True
Me.AllowEdits = True

Else
lblNewRec.Visible = False
txtHeadCat.Visible = True
txtHeadReqPty.Visible = True
Me.cmdDateP.Enabled = False
Me.AllowEdits = False

End If

If DCount("*", "tblReportLinks", " [ID] = " & [Forms]![Issues]![ID]) > 0
Then
[Box117].BackColor = vbRed
Else
[Box117].BackColor = vbBlack
End If

End Sub

PS: I wrote the rest of the code above, and it was one of my first efforts.
There's probably a better way to do what I did, but it works and doesn't give
any errors. I'm thinking about changing it to not use the allowedits, but I
digress...

Here's the code on the button in the main form that opens the form that's
based SOLELY on the tblReportLinks:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmReportDocLinks"

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub

I believe the above code was written by the cmd button wizard.

There's a box on the main form, Box117, that was added using the toolbar
control. It's suppose to change colors depending on the DCount function
that's not working. What's suppose to happen is that when my single (main)
form Issues triggers the "on_current" event, the DCount should run and count
the number of records in the tblReportLinks that match the ID in the issue
currently shown in the main "Issues" form. If it's >0, meaning there are
matching records in the second table, that's when the box should display
color red indicating there's related documents to be seen by clicking the
button.

The [ID] field appears in both forms, and both underlying tables (though, in
reality, the main form is based on a query.....but the [ID] is in there too).

I'd be glad to provide any other info. I wrote out a long explanation above
but I'd be more than willing to respond to anything else.

I understand if you tire of this, believe me. I just can't believe there's
not a simple explanation.

Thanks again,
CW

Which piece of code is generating the error. It sounds like it may be the
code that is calling the other form

stLinkCriteria = "[ID]=" & Me![ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

If ID is not a field in the form frmReportDocLinks then that might generate
the error. OR if the source for frmReportDocLinks has fields in two or more
tables with the name ID that could cause an error.

Also, is ID a number field or is it a text field?