How to indicated record(s) exist

[Darrell Childress]

I have a form that contains a lot of order-specific information. One of
the fields is "custno". I also have a button that will open a separate
form (linking field is custno) to show any notes specific to that
customer (special shipping instructions, etc). Several customers have
notes, most do not. I'm trying to find out if there is a way that I can
show some indicator that there are note(s) for this customer when the
user pulls up the order (perhaps highlight the custno field or something)
Thanks,
Darrell

 

[Klatuu]

You could use the button for opening the notes form to let the user know
whether notes are available. I am assuming the same button would be used to
add new notes for a customer that currently has no notes.

A way to do that would be to use the Form Current event to check for
existing notes and change the button's Caption to indicate the availability
of notes.

If DCount("*", "NotesTable", "[custno] = " & Me.txtCustNo) > 0 Then
Me.cmdNotes.Caption = "View/Add Notes"
Else
Me.cmdNotes.Caption = "Add Notes"
End If

 

[Darrell Childress]

Below is the code that I put in. Please note that the field name on the
current form is CustomerNo and the field name in the Notes table
(tblCustNotes_SS) is custno. When I open the form, I get an error
stating that "Run-time error '2001' You canceled the previous operation"

If DCount("*", "tblCustNotes_SS", "[custno] = " & Me.CustomerNo) > 0 Then
Me.Command309.Caption = "View"
Else
Me.Command309.Caption = "Add"
End If

And yes, the same button is used to add notes for a customer that
currently has no notes.

You could use the button for opening the notes form to let the user know
whether notes are available. I am assuming the same button would be used to
add new notes for a customer that currently has no notes.

A way to do that would be to use the Form Current event to check for
existing notes and change the button's Caption to indicate the availability
of notes.

If DCount("*", "NotesTable", "[custno] = " & Me.txtCustNo) > 0 Then
Me.cmdNotes.Caption = "View/Add Notes"
Else
Me.cmdNotes.Caption = "Add Notes"
End If

 

[Klatuu]

What is the data type of custno?

The error you are getting is because there is a problem with the DCount and
Jet cannot resolve it.
--
Dave Hargis, Microsoft Access MVP

Below is the code that I put in. Please note that the field name on the
current form is CustomerNo and the field name in the Notes table
(tblCustNotes_SS) is custno. When I open the form, I get an error
stating that "Run-time error '2001' You canceled the previous operation"

If DCount("*", "tblCustNotes_SS", "[custno] = " & Me.CustomerNo) > 0 Then
Me.Command309.Caption = "View"
Else
Me.Command309.Caption = "Add"
End If

And yes, the same button is used to add notes for a customer that
currently has no notes.

You could use the button for opening the notes form to let the user know
whether notes are available. I am assuming the same button would be used to
add new notes for a customer that currently has no notes.

A way to do that would be to use the Form Current event to check for
existing notes and change the button's Caption to indicate the availability
of notes.

If DCount("*", "NotesTable", "[custno] = " & Me.txtCustNo) > 0 Then
Me.cmdNotes.Caption = "View/Add Notes"
Else
Me.cmdNotes.Caption = "Add Notes"
End If

 

[Darrell Childress]

The data type is text (for both custno and CustomerNo). I didn't think
about that. This is linked to our Accounting system (written in Visual
FoxPro) and our customer numbers are in the format ABC01T.

What is the data type of custno?

The error you are getting is because there is a problem with the DCount and
Jet cannot resolve it.

Below is the code that I put in. Please note that the field name on the
current form is CustomerNo and the field name in the Notes table
(tblCustNotes_SS) is custno. When I open the form, I get an error
stating that "Run-time error '2001' You canceled the previous operation"

If DCount("*", "tblCustNotes_SS", "[custno] = " & Me.CustomerNo) > 0 Then
Me.Command309.Caption = "View"
Else
Me.Command309.Caption = "Add"
End If

And yes, the same button is used to add notes for a customer that
currently has no notes.

You could use the button for opening the notes form to let the user know
whether notes are available. I am assuming the same button would be used to
add new notes for a customer that currently has no notes.

A way to do that would be to use the Form Current event to check for
existing notes and change the button's Caption to indicate the availability
of notes.

If DCount("*", "NotesTable", "[custno] = " & Me.txtCustNo) > 0 Then
Me.cmdNotes.Caption = "View/Add Notes"
Else
Me.cmdNotes.Caption = "Add Notes"
End If

I have a form that contains a lot of order-specific information. One of
the fields is "custno". I also have a button that will open a separate
form (linking field is custno) to show any notes specific to that
customer (special shipping instructions, etc). Several customers have
notes, most do not. I'm trying to find out if there is a way

that I
can

show some indicator that there are note(s) for this customer when the
user pulls up the order (perhaps highlight the custno field or something)
Thanks,
Darrell

 

[Klatuu]

That is the problem. The syntax I posted would be if custno were a numeric
field. The correct syntax would be:

If DCount("*", "NotesTable", "[custno] = """ & Me.txtCustNo & """") > 0 Then
Me.cmdNotes.Caption = "View/Add Notes"
Else
Me.cmdNotes.Caption = "Add Notes"
End If

--
Dave Hargis, Microsoft Access MVP

The data type is text (for both custno and CustomerNo). I didn't think
about that. This is linked to our Accounting system (written in Visual
FoxPro) and our customer numbers are in the format ABC01T.

What is the data type of custno?

The error you are getting is because there is a problem with the DCount and
Jet cannot resolve it.

Below is the code that I put in. Please note that the field name on the
current form is CustomerNo and the field name in the Notes table
(tblCustNotes_SS) is custno. When I open the form, I get an error
stating that "Run-time error '2001' You canceled the previous operation"

If DCount("*", "tblCustNotes_SS", "[custno] = " & Me.CustomerNo) > 0 Then
Me.Command309.Caption = "View"
Else
Me.Command309.Caption = "Add"
End If

And yes, the same button is used to add notes for a customer that
currently has no notes.

Klatuu wrote:
You could use the button for opening the notes form to let the user know
whether notes are available. I am assuming the same button would be used to
add new notes for a customer that currently has no notes.

A way to do that would be to use the Form Current event to check for
existing notes and change the button's Caption to indicate the availability
of notes.

If DCount("*", "NotesTable", "[custno] = " & Me.txtCustNo) > 0 Then
Me.cmdNotes.Caption = "View/Add Notes"
Else
Me.cmdNotes.Caption = "Add Notes"
End If

:
I have a form that contains a lot of order-specific information.
One of
the fields is "custno". I also have a button that will open a separate
form (linking field is custno) to show any notes specific to that
customer (special shipping instructions, etc). Several customers have
notes, most do not. I'm trying to find out if there is a way that I
can
show some indicator that there are note(s) for this customer when the
user pulls up the order (perhaps highlight the custno field or
something)
Thanks,
Darrell

 

[Darrell Childress]

Absolutely perfect!!! Thank you so much, Dave, especially for helping me
troubleshoot this when it didn't work the first time. There are several
places that I can use this.
Darrell