Use of Visible property if matching record found

[Lewis Wood]

I am self taught and struggling with a particular problem.
I have created a database to record postcard details using Access 2000.

My main screen (POSTCARD_FORM) shows details of the card and allows users to add comments about the postcard eg "the spire on the church is 50 feet high" .

I have two buttons, an ADD_COMMENTS button and a VIEW_COMMENTS button. The comments are stored in COMMENT_TBL. I would like to be able to open the POSTCARD_FORM and automatically check to see if comments exist in the COMMENT_TBL for the record displayed. If not, I want to set the visible property of the VIEW_COMMENTS button to false. If the user either adds a comment and returns to the POSTCARD_FORM, or moves to the next postcard which already has comments, then the VIEW_COMMENTS button should reappear.

Thanks for your help on this and all the other answers which I have found on this site.

[MMcCarthy]

Welcome to thescripts.

I am moving your question to the Access forum where it should receive better attention.

Mary

[MMcCarthy]

I am self taught and struggling with a particular problem.
I have created a database to record postcard details using Access 2000.

My main screen (POSTCARD_FORM) shows details of the card and allows users to add comments about the postcard eg "the spire on the church is 50 feet high" .

I have two buttons, an ADD_COMMENTS button and a VIEW_COMMENTS button. The comments are stored in COMMENT_TBL. I would like to be able to open the POSTCARD_FORM and automatically check to see if comments exist in the COMMENT_TBL for the record displayed. If not, I want to set the visible property of the VIEW_COMMENTS button to false. If the user either adds a comment and returns to the POSTCARD_FORM, or moves to the next postcard which already has comments, then the VIEW_COMMENTS button should reappear.

Thanks for your help on this and all the other answers which I have found on this site.

OK I have a couple of questions.

Are the comments displaid in a subform?
If so what is the foreign key in the COMMENT_TBL which references the primary key of the main table and what is the name of the corresponding textbox on the form?

[Lewis Wood]

OK I have a couple of questions.

Are the comments displaid in a subform?
If so what is the foreign key in the COMMENT_TBL which references the primary key of the main table and what is the name of the corresponding textbox on the form?

Hi Mary,
Each button opens a separate form.

The ADD_COMMENTS_FRM accepts comments, contributors name and contact information

The VIEW_COMMENTS_FRM just displays the comments.

The relevant field in the COMMENTS_TBL is simply COMMENT.


One of the many options I tried is to open a query which counts COMMENT where the POSTCARD_ID matched on both tables but all I need to do is detect if just one comment exists as that would satisfy my criteria to keep the View button visible. Whilst that was inefficient, it did work, but I could not find a way to pass the result to an event in order to test if the criteria was satisfied. Also I am not certain if the On Current event is the correct one to use. My query also displayed on screen which is not what I want either.

Thanks for your help.


The POSTCARD_TBL is linked to the COMMENTS_TBL via POSTCARD_ID.

[MMcCarthy]

I think from what you have said the forms On Current event is the correct place.

You could try something like the following code.

If NZ(DCount("[COMMENT]","COMMENT_TBL","[POSTCARD_ID]=" & Me.POSTCARD_ID),0) = 0 Then
    Me.VIEW_COMMENTS.Visible = False
Else
    Me.VIEW_COMMENTS.Visible = True
End If
 

You may also need to add a refresh or requery in the close or unload event of the ADD_COMMENTS_FRM form. For example ...

Forms!POSTCARD_FORM.Requery

[Lewis Wood]

I think from what you have said the forms On Current event is the correct place.

You could try something like the following code.

If NZ(DCount("[COMMENT]","COMMENT_TBL","[POSTCARD_ID]=" & Me.POSTCARD_ID),0) = 0 Then
    Me.VIEW_COMMENTS.Visible = False
Else
    Me.VIEW_COMMENTS.Visible = True
End If
 

You may also need to add a refresh or requery in the close or unload event of the ADD_COMMENTS_FRM form. For example ...

Forms!POSTCARD_FORM.Requery

Hi Mary,

Thanks for your suggestion. It did not work first time but after I understood what NZ() is all about and worked through the DCOUNT requirements, I changed your line to the following which worked a treat.

If Nz(DCount("[COMMENT]", "Comments tbl", "[comments tbl]![Postcard_ID]= [Forms]![Postcard_Form]![Postcard_ ID]"), 0) = 0 Then etc......

Maybe Access 2000 is a little different from your version as my version does not like the '&' character and I could not quite follow the placement of the '='sign.

Thanks to your input I now have an elegant solution and understand a little more about Access so that's a good result all round.
Thanks again
Lewis

[MMcCarthy]

Not sure why you had a problem with the syntax in Access 2000 but at least you have it working for you now.

Mary