Setting focus on a control in a report (1 Viewer)

[ledgerr.rob]

Windows vista
access 2007

I've hitting a wall here. I'm populating a report with a query which pulls criteria from a form. When the 'run' button is pressed it opens the report, running the query, to filter the data. What i'm attempting (and it works if there is data present).

The data is text, which is a filename, which populates an image control. Most of my records have an image present but for the ones that don't I think I need to turn the image control's picture property to 'blank'.

I'm just now encountering problems with the records with no pictures so when i came up with this it worked with my tests which at that time only had images present....Sorry for the longwindedness...


I have two problems.
1) When I run the code as below i get Run-Time Error 2185; you can't reference a property or method for a control unless the control has the focus.
2) when i try to set the focus on the picture control in the report to see if there is text/value present i get runtime error 2478; database doesn't allow you to use this method in the current view.

I assume this is talking about me opening the report in acViewPreview mode but i thought i needed to do this so the images are displayed in the image control.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strDBPath As String
    Dim strRelativePath As String
    Dim strPath As String
      
   'rptOriginalOwnerCategoryItem!Picture.Text.SetFocus
   [B]Me!Picture.SetFocus[B]
   
    'Test to see if the record has a relative path stored
    'if IsNull(Me!Picture.Text)=True Then
    'If IsNull(Me!Picture)=True Then

    [B]If Me!Picture.Text = "" Then[/B]
        'If it does not (null) turn the picture value to nothing
        Me.imgControl1.Picture = ""
    Else
        'If it does (not null) then turn the picture value to the full path
        'store the relative path
        
        'Me!Picture.SetFocus
        strRelativePath = Me!Picture.Text
        
        'store the path of the database
        strDBPath = CurrentProject.path
        
        'concatenate the DB path, the image folder, and the relative path and store to strPath
        strPath = strDBPath & "\Images\" & strRelativePath
        
        'set the picture value to strPath
        Me!imgControl1.Picture = strPath
        
    End If
    
End Sub

I know this is a long post, and i'm probably dancing around the issue but just don't seem to see the solution. With the reading i've done on this I've read that determining if text is present in a queried result should be done in the query...Should I be checking for the presence there? How would I do this?

Thanks
Rob

 

[Bilbo_Baggins_Esq]

Before attempting #2 above, have you tried to designate a different view first, then after you're completed, setting the view to acViewPreview?

 

[ledgerr.rob]

I woke up this morning with a brainstorm that when i tried it this morning, solved my issue. @Bilbo_Boggins_ESQ, i did try your suggestion first and still got an error, but i might have missed something.

Because this forum is so great at helping and fostering the learning process I'll post my solution for the greater goods learning 'benefit'. Keep in mind i'm a newbie so some info might be wrong, but my project gives me the right info.

My solution was to look at the underlying query results for the situation that was giving me an error (when no pictures existed for a record). When i did this no result was provided. Because I was given no result (when i knew one record did in fact exist) I looked at my criteria in the query and saw my problem.

One of the criteria's in the query was set to select only the 'primary picture'. In my project the primary picture is the one of potentially many pictures selected in the data entry form that best represents the item in question. Because no picture existed, there was no 'primary picture', which means there is no record to return. The report was blowing up on me because no data was being given to the report by the query.

My solution was to create a UNION query.

SELECT tblOwner.OwnerFirst, tblOwner.OwnerLast, tblItem.Item, tblCategory.Category, tblItem.ItemDescription, tblAppraisal.AppraisalValue, tblManufacturer.Manufacturer, tblItem.ModelNumber, tblItem.SerialNumber, tblPicture.Picture, tblCondition.Condition

FROM tblOwner RIGHT JOIN (tblManufacturer RIGHT JOIN (((tblCategory RIGHT JOIN tblItem ON tblCategory.CategoryID = tblItem.CategoryID) LEFT JOIN (tblCondition RIGHT JOIN tblAppraisal ON tblCondition.ConditionID = tblAppraisal.ConditionID) ON tblItem.ItemID = tblAppraisal.ItemID) LEFT JOIN tblPicture ON tblItem.ItemID = tblPicture.ItemID) ON tblManufacturer.ManufacturerID = tblItem.ManufacturerID) ON tblOwner.OwnerID = tblItem.OriginalOwnerID

WHERE ((([B]tblPicture.PrimaryPicture)=True[/B]) AND ((tblOwner.OwnerID)=[Forms]![frmReportGenerator]![cboOriginalOwner3]) AND ((tblCategory.CategoryID)=[Forms]![frmReportGenerator]![cboCategory3]) AND ((tblItem.ItemID)=[Forms]![frmReportGenerator]![cboItem3]));

UNION 

SELECT tblOwner.OwnerFirst, tblOwner.OwnerLast, tblItem.Item, tblCategory.Category, tblItem.ItemDescription, tblAppraisal.AppraisalValue, tblManufacturer.Manufacturer, tblItem.ModelNumber, tblItem.SerialNumber, tblPicture.Picture, tblCondition.Condition

FROM tblOwner RIGHT JOIN (tblManufacturer RIGHT JOIN (((tblCategory RIGHT JOIN tblItem ON tblCategory.CategoryID = tblItem.CategoryID) LEFT JOIN (tblCondition RIGHT JOIN tblAppraisal ON tblCondition.ConditionID = tblAppraisal.ConditionID) ON tblItem.ItemID = tblAppraisal.ItemID) LEFT JOIN tblPicture ON tblItem.ItemID = tblPicture.ItemID) ON tblManufacturer.ManufacturerID = tblItem.ManufacturerID) ON tblOwner.OwnerID = tblItem.OriginalOwnerID

WHERE (((tblOwner.OwnerID)=[Forms]![frmReportGenerator]![cboOriginalOwner3]) AND ((tblCategory.CategoryID)=[Forms]![frmReportGenerator]![cboCategory3]) AND ((tblItem.ItemID)=[Forms]![frmReportGenerator]![cboItem3]));

By making the UNION query it looks for the same record with a picture and without a picture then combines the result. Since I'm telling the query to search for only a single item on my report generator it will only find a single item (whether it has a picture or not).

Once the query is run, the information is passed to the report. Here is my working code to do that.

Private Sub btnRun3_Click()

'run report rptOriginalOwnerCategoryItem
DoCmd.OpenReport "rptOriginalOwnerCategoryItem", acViewPreview

'DoCmd.OpenReport "rptOriginalOwnerCategoryItem", acViewReport

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strDBPath As String
    Dim strRelativePath As String
    Dim strPath As String
     
    'store the path of the database
    strDBPath = CurrentProject.path
      
    'Test to see if the record has a relative path stored
    If IsNull(Me!Picture) Then
    
        'If it does not (null) turn the picture value to nothing
        Me.imgControl1.Picture = ""
    Else
        'If it does (not null) then turn the picture value to the full path
        
        '1-store the relative path
        strRelativePath = Me!Picture.Text
        
        '2-concatenate the DB path, the image folder, and the relative path and store to strPath
        strPath = strDBPath & "\Images\" & strRelativePath
        
        '3-set the picture value to strPath
        Me!imgControl1.Picture = strPath
        
    End If
    
End Sub

I've attached an image of my report generator. I have a number of different ways to search for an item and by keeping my naming convention universal i've been able to use this same code for all the different search methods, as each method can return a value with a picture or without. The particular example i'm using here is the 'Items by Owner and Category' with the three cascading combo boxes in the bottom left. I've now applied this solution (the union query approach) to the other 5 scenarios.

I know this is a specific example that might not be able to be used for anyone else but wanted to show my solution to the community. My take home is DON'T GET HUNG UP ON THE CODE BUT LOOK AT THE UNDERLYING DATA, WHETHER THAT'S IN A TABLE OR A QUERY RESULT FIRST. I'm learning the code thing and it's such a focus to me now i was forgetting the basics. as it turns out my initial code is what worked but my query was the problem.

Hope this helps someone just getting started as countless other examples found here have helped me.

rob

 

[missinglinq]

Two things:

If Me!Picture.Text = "" Then

.Text,as the error said, is only available when a Control has Focus, and you cannot set Focus to a Control on a Report! Unlike straight VB, in Access VBA .Text is very seldom used. .Value is used to determine data what a given Field is storing, and since it is the Default Property for Textboxes, Comboboxes, etc. it can be omitted entirely.

Also note that doing something like this

If Me!Picture.Text = "" Then

is testing to see if the Control holds a Zero-Length String, and the majority of the time, a 'blank' or 'empty' Field in Access doesn't contain a Zero-Length String, it is Null. There are several ways to check for both Zero-Length String and Nulls, one of which is

If Nz(Me.ControlName,"") = "" Then

This part of your code would have worked if you'd used

If Nz(Me.Picture.Value,"") = "" Then

or, because Value is the Default Property, and can be omitted, simply

If Nz(Me.Picture,"") = "" Then

Linq ;0)>

 

[ledgerr.rob]

missinglinq- thanks for clearing up checking for zero length strings and the ability or lack there of, of setting focus to a control on a report. Those were a couple things I wasn't sure about even after me finding my solution.