Solved Setting control visibility for each individual record in continuous form

[mikenyby]

Hello,

I have a continuous form in which I'd like to set the visibility for a button "cmdViewImages" individually for each record on the form.

The form shows the names of a bunch of items in table "Items". The items may or may not have images associated with them in table "Images". If there are Images associated with the item, I want the cmdViewImages button visible.

I'm using this code:

Private Sub Form_Load()
    DoCmd.OpenQuery "qryFindImageByItem", acViewNormal
    If DCount("*", "qryFindImageByItem") > 0 Then
        Me.cmdViewImages.Enabled = True
        Me.cmdViewImages.Visible = True
    Else
        Me.cmdViewImages.Enabled = False
        Me.cmdViewImages.Visible = False
    End If
    DoCmd.Close acQuery, "qryFindImageByItem", acSaveNo
End Sub

The problem is it only runs the query for the first record on the form and sets the visibility of the button ALL records based on the results for just the first record.

I've tried something similar by instead of running the query, throwing the ItemIDFK from the Image table into the current record and set the visibility based of IsNull(ItemIDFK). Same deal, every record is affected based on the results on the first record. I assume I'm using the wrong event. Any ideas?

 

[Pat Hartman]

I can explain the problem but might not have a solution.
1. Forms that are continuous or DS (whether they are main forms or subforms is irrelevant) are multiple instance of the form object. That means that there is only one set of properties for the form object which is why each instance of the form object shows the same properties. The only way to control this is with conditional formatting and that is quite limited.
2. Code that runs in the load event of a form runs ONCE and the data it uses comes from the FIRST record that will be displayed.
3. If you have code that is dependent on a data value, it needs to run for every record so you could use the Current event to run the code. In this case, the code will work but as you move from record to record, the properties of every record will change in sync to match the property of the current record.

A potential solution is to add the dCount() to the Form's RecordSource query so that it retrieves a value for each record. That value can be used by conditional formatting to format the individual form instances.

 

[CJ_London]

see this link

Button visible/invisible in a Continuous Form

I'd forgotten this - so simple & effective - have just used it for a web article https://www.isladogs.co.uk/paint-me-transparent/index.html Thanks again @Saphirah

www.access-programmers.co.uk

 

[mikenyby]

@CJ_London this is good information, but I don't think I can get it to work as described because the value that determines the visibility (or transparency) of the button is not a field on the subform, it's a separate query.

What I would like to do is create a control that displays a value calculated from:

SELECT COUNT(ImageIDFK)
FROM PersonImage
INNER JOIN Images ON PersonImage.ImageIDFK =Images.ImageID
WHERE Images.ItemIDFK = Forms!frmViewImage!ImageID AND PersonImage.PersonIDFK=Me.PersonIDFK;

And then I can just do

Private Sub Detail_Paint()
    IF Me.SelectCount > 0 THEN
        Me.cmdViewImages.Transparent = False
    Else
        Me.cmdViewImages.Transparent = True
End Sub

But I can't figure out how to create that control.

 

[mikenyby]

I can explain the problem but might not have a solution.
1. Forms that are continuous or DS (whether they are main forms or subforms is irrelevant) are multiple instance of the form object. That means that there is only one set of properties for the form object which is why each instance of the form object shows the same properties. The only way to control this is with conditional formatting and that is quite limited.
2. Code that runs in the load event of a form runs ONCE and the data it uses comes from the FIRST record that will be displayed.
3. If you have code that is dependent on a data value, it needs to run for every record so you could use the Current event to run the code. In this case, the code will work but as you move from record to record, the properties of every record will change in sync to match the property of the current record.

A potential solution is to add the dCount() to the Form's RecordSource query so that it retrieves a value for each record. That value can be used by conditional formatting to format the individual form instances.

Thank you Pat, I'm experimenting with working a DCount into it now, but I don't know how to put an aggregate function into the current record source. Will this have to be a union query?

 

[mikenyby]

@Pat Hartman

Here's the current SQL record source:

SELECT PersonItem.PersonIDFK, PersonItem.ItemIDFK, Persons.FamilyName, Persons.GivenName, Persons.CityOfResidence,
[FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName, PersonItem.PageNumbers, Persons.Position
FROM Persons
INNER JOIN PersonItem ON Persons.PersonID = PersonItem.PersonIDFK
WHERE (((PersonItem.ItemIDFK)=[Forms]![frmViewItem]![ItemID]))
ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

And here's the aggregate I need:

SELECT COUNT(ImageIDFK)
FROM PersonImage
INNER JOIN Images ON PersonImage.ImageIDFK =Images.ImageID
WHERE Images.ItemIDFK = Forms!frmViewImage!ImageID
AND PersonImage.PersonIDFK=Me.PersonIDFK;

 

[CJ_London]

And here's the aggregate I need:

include as a subquery to your form recordsource - or (since you don't actually need to know the actual count number) use the Exists predicate in your form query

Or use a dcount function in the form

Or use a function to populate a hidden control called on some event (maybe form current, maybe after a control is updated)

Why are you ordering by a calculated string, would be faster just to use
ORDER BY [FamilyName], [GivenName] ,[Position], [CityOfResidence];

 

[mikenyby]

include as a subquery to your form recordsource - or (since you don't actually need to know the actual count number) use the Exists predicate in your form query

Or use a dcount function in the form

Or use a function to populate a hidden control called on some event (maybe form current, maybe after a control is updated)

Why are you ordering by a calculated string, would be faster just to use
ORDER BY [FamilyName], [GivenName] ,[Position], [CityOfResidence];

The ORDER BY syntax was just what Access autofilled into the SQL after I used the query design feature.

I've tried adding hidden controls that run the aggregate function but I keep getting an error that the subquery syntax is invalid, and I don't know why. I've checked the syntax in a plain old query and it works fine, it just doesn't work on the when added to a text box record source. Some functionality of Access that I'm not aware of is preventing it. Is it because the Images table and PersonImage table aren't in the form record source?

I'm smacking my head for not even thinking of using EXISTS. Thanks. My work day is now over so I'll give that a go tomorrow and see if I can get it to work.

 

[Pat Hartman]

SELECT PersonItem.PersonIDFK, PersonItem.ItemIDFK, Persons.FamilyName,
Persons.GivenName, Persons.CityOfResidence,
[FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence] AS FullName,
PersonItem.PageNumbers, Persons.Position,
IIf(DCount("*", "qryFindImageByItem") > 0, True, False) As ShowFlg

FROM Persons
INNER JOIN PersonItem ON Persons.PersonID = PersonItem.PersonIDFK
WHERE (((PersonItem.ItemIDFK)=[Forms]![frmViewItem]![ItemID]))
ORDER BY [FamilyName] & ", " & [GivenName] & " | " & [Position] & " | " & [CityOfResidence];

Use the ShowFlg in the Conditional Formatting dialog to show/hide the control.

 

[Pat Hartman]

I used the dcount to show the concept but you probably need to make qryFindImageByItem a sub query and use Exists to set the True/False value instead.

HOWEVER, I don't understand why you want to hide the control. What if someone wants to add an image? How would they do it?

 

[isladogs]

What you want to do is not difficult using the transparency property for command buttons
Look at my article which expands on the idea in more detail

Paint Me Transparent

This article describes a very simple method for selectively showing / hiding command buttons and other controls in a continuous form.

www.isladogs.co.uk

Here are two screenshots from that article:

 

[mikenyby]

I used the dcount to show the concept but you probably need to make qryFindImageByItem a sub query and use Exists to set the True/False value instead.

HOWEVER, I don't understand why you want to hide the control. What if someone wants to add an image? How would they do it?

The interface I'm building is for an archives, most users will only ever need to view information. I have a bunch of forms for adding/editing records but they will be available only to privileged users.

I'll try to play around with your suggestions tomorrow. Thanks for the help.

 

[CJ_London]

I keep getting an error that the subquery syntax is invalid, and I don't know why

Nor do we unless you provide it

 

[arnelgp]

another suggestion...

remove first cmdViewImages button.
you add ImageIDFK to your Query.
then add an Unbound textbox to your form (txtDummy), set it's "Tab Stop" property to No.
now add conditional formatting to to txtDummy:

Expression: DCount("1","Images","ImageID=" & [ImageIDFK])=0
and Choose to Disable the control.

next add cmdViewImages command button and make it same size as with txtDummy.
set txtDummy's Left and Top same as with the command button.
set "Transparent" property to Yes.
set it's "Position"=> Bring to Front

now add this to the Click event of cmdViewImages button:

Private Sub cmdViewImages_Click()
    If DCount("1", "Images", "ImageID=" & Nz([ImageIDFK], 0)) <> 0 Then
        MsgBox "The textbox is enabled so you can do whatever you like!"
    End If
End Sub

see demo db.

 

[mikenyby]

another suggestion...

remove first cmdViewImages button.
you add ImageIDFK to your Query.
then add an Unbound textbox to your form (txtDummy), set it's "Tab Stop" property to No.
now add conditional formatting to to txtDummy:

Expression: DCount("1","Images","ImageID=" & [ImageIDFK])=0
and Choose to Disable the control.

next add cmdViewImages command button and make it same size as with txtDummy.
set txtDummy's Left and Top same as with the command button.
set "Transparent" property to Yes.
set it's "Position"=> Bring to Front

now add this to the Click event of cmdViewImages button:

Private Sub cmdViewImages_Click()
    If DCount("1", "Images", "ImageID=" & Nz([ImageIDFK], 0)) <> 0 Then
        MsgBox "The textbox is enabled so you can do whatever you like!"
    End If
End Sub

see demo db.

Winner winner chicken dinner! This ended up being the solution, with slight adjustments.

I had to change it a bit because the conditional formatting wasn't working. Every single text box was enabled for some reason. To see why I put an unbound text box on the form with record source DCount("1","Images","ImageID=" & [ImageIDFK]) and figured out why. Instead of returning 0, records that should have been 0 were returning #Error. I may be wrong but I'm guessing this might be because many of the PersonID's don't have corresponding PersonIDFK's in the PersonImage table at all, and may never--depending on whether any items they appear in are digitized. I worked around this by instead of conditionally disabling the text box I conditionally enabled it on DCount("1","Images","ImageID=" & [ImageIDFK])>= 1. Worked like a charm.

I also eliminated the button entirely and have the _click event on the text box. Set the hyperlink property to yes for cursor change on hover and now it's exactly as I hoped for.

Thanks to everyone for the suggestions!