How to indicated record(s) exist

  • Thread starter Thread starter Darrell Childress
  • Start date Start date
D

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
 
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
 
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
Click to expand...

 
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


Darrell Childress said:
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
Click to expand...
Click to expand...
 
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.
Click to expand...


-- Dave Hargis said:
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
Click to expand...

Darrell Childress said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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


Darrell Childress said:
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.
Click to expand...


-- Dave Hargis said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top