Subform Not Repainting, Refreshing or Something? 1

[misscrf]

I have a main form with a tab control. Each tab has 1 subform. On the first tab control the subform doesn't seem to refresh correctly, when I navigate records on the main form. If there are no records, the form sections are set to can grow/can shrink = yes. There is a text box control in the footer that calculates a nice label of current records with the following code:

 If Me.NewRecord Then
 Me!txtCurrRec = "New xxx Record"
 Else
 Me!txtCurrRec = CStr(Me.CurrentRecord) & " of " & _
               CStr(Me.RecordsetClone.RecordCount) & " xxx Records"

This textbox shows up towards the top of the subform object, when there are no records, which is fine. What isn't good, is when it shows the text box twice. Once with 1 of 1 and once with 0, with some controls 1/2 painted on the form, and all over the place. If I minimize access and then restore it, the form refreshes the view appropriately. I have tried everything, but it does not seem to be working. I have tried requery, repaint and refresh. the requery seems to work, but it gives a record in the subform where there isn't one.

This form is linked with parent/child links, but the subform record source is a union query. Do I need to do something extra because it is a union query? The form is read-only.

I appreciate any help.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Is there a reason you want to have your own record counter / navigation?

A form has this built in and can be set via the properties of the form in design mode.

Under the 'format' tab, there is the property 'Navigation Buttons', which enables the built in form record count / controls.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

It's just a nicer counter to have in the form footer. I use that in most of my forms in my apps. I am pretty sure that isn't the issue, since the other forms don't have an issue. I'm wondering if it might be something to do with the subform being bound to a union query. I can't seem to find many posts about issues with that though.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Have you tried a 'requery' on the form to refresh the data?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

I wanted to update with the function that I call when I navigate the main form. This is where I see the issue:

This is checking a toggle button. If the toggle button is true, then the add/edit form is visible and users are doing data entry or editing a record. If it is false, then the continuous view only form is visible. This is the form that has the union view as the record source. When I navigate the main form to the next, previous, first or last, the view only subform doesn't refresh/repaint right. The controls look funky. It will put the unbound recordset counter toward the top of the form and repeat it a bit down, and show a count that isn't right. If I minimize and restore the app, it shows the right information.

Any help is greatly appreciated.

Public Function TogDocs() As String
Dim strEditQuery As String
Dim strViewQuery As String
Dim intnewrec As Integer
Dim PID As String
Dim vsource As String
Dim DocEditSql As String

If Forms!frmProject.tgViewDoDocs.Value = True Then
    'TOGGLE BUTTON BEHAVIOR
    With Forms!frmProject.tgViewDoDocs
        .Caption = "Adding/Editing Docs (Click to View All)"
        .HoverColor = RGB(204, 102, 0)
        .PressedColor = RGB(204, 102, 0)
        .BackColor = RGB(204, 102, 0)
        .ForeColor = RGB(0, 0, 0)
        .HoverForeColor = RGB(255, 255, 255)
        .FontBold = True
    End With
    'SUB FORM OBJECT AND BEHAVIOR
    Forms!frmProject.frmProjectDocumentsEdit.Form.Visible = True
    Forms!frmProject.frmProjectDocumentsView.Form.Visible = False
        
    DocEditSql = "SELECT tblProjectDocs.ID, tblProjectDocs.FKRelationDoc, tblProjectDocs.FKRelationID, " _
     & " tblProjectDocs.FKDMLib, tblProjectDocs.intDMNum, tblProjectDocs.DocNotes FROM tblProjectDocs "
    
    Forms!frmProject.frmProjectDocumentsEdit.Form.RecordSource = DocEditSql
    With Forms!frmProject.frmProjectDocumentsEdit.Form
        .DataEntry = True
    End With

Else
    'TOGGLE BUTTON BEHAVIOR
    With Forms!frmProject.tgViewDoDocs
        .Caption = "Viewing Docs (Click to Add New Doc)"
        .HoverColor = RGB(181, 203, 136)
        .PressedColor = RGB(181, 203, 136)
        .BackColor = RGB(181, 203, 136)
        .ForeColor = RGB(0, 0, 0)
        .HoverForeColor = RGB(255, 255, 255)
        .FontBold = True
    End With
    
    'SUB FORM OBJECT AND BEHAVIOR
    Forms!frmProject.frmProjectDocumentsEdit.Form.Visible = False
    Forms!frmProject.frmProjectDocumentsView.Form.Visible = True
    
    If Forms!frmProject.Dirty Then
        Forms!frmProject.Dirty = False
        
        With Forms!frmProject.Form
            DoCmd.RunCommand acCmdSaveRecord
        End With
    End If
    
    Forms!frmProject.frmProjectDocumentsEdit.Form.RecordSource = ""
    Forms!frmProject.frmProjectDocumentsView.Form.Repaint
    
End If

End Function

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Sorry, I didn't see your 2nd response. I did try that and whether I use refresh, repaint, or requery on the subform object or the subform in the subform object, nothing seems to work.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

I'm finding it hard to visualise the problem, do you have a screen shot?

I can't quite tell but you seem to have two forms as sub forms that you are toggling visibility?

I assume both are bound to the parent for the link between master record and child records, but you are then setting the record source to blank for one of them? Perhaps your counter is getting confused as to which form its counting? Though you seem to be creating a recordset clone and trying to get a record count without moving to the last record.

If you create a recordset object and instantly issue a recordcount you will only ever get either zero if there aren't any records or one as that's the current cursor location, you must move the cursor to the last record before trying to count how many there are.

If Me.NewRecord Then
    Me!txtCurrRec = "New xxx Record"
Else
    Dim rs As DAO.Recordset
    Dim cnt as Integer

    Set rs = Me.RecordsetClone
    rs.MoveLast
    cnt = rs.RecordCount
    Set rs = Nothing
    Me!txtCurrRec = CStr(Me.CurrentRecord) & " of " & CStr(cnt) & " Records" 
End If

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

You are correct that I have 2 subforms and I am toggling the visibility. the txtcurrRec is just being set on the view subform, not the edit. I realized I had to take the record source off the edit form, because if I had the view form visible, the edit form was editing a record and the parent/child links were changing the id in the source table. It was seriously funky!

I also updated the txtcurrRec text box to be invisible if there are no records, and only to be visible with the nice # of ## records label if there are records.

Now it seems to be working. I'm going to just be happy and move on. Eventually as I move things into functions and clean up the code, I may be able to see the forest through the woods.

Thanks so much for all your help.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Now it seems to be working. I'm going to just be happy and move on.

Glad you got it working and I was able to help.

I realized I had to take the record source off the edit form, because if I had the view form visible, the edit form was editing a record and the parent/child links were changing the id in the source table. It was seriously funky!

The way I deal with this is instead of having two bound forms and toggling visibility and the problems with two bound forms to the same dataset, which you have found.

Try dynamically toggling the SourceObject of the bound subform control to load the form you want bound to the parent.

I do this with an app that uses the old tabbed page control. I found that if you have a load of tables and each tab page has many bound subforms, although when the page opens you are only viewing the 1st tab, all the other tab pages are loading all the bound data in the background.

To stop this all my subform controls are unbound, when the tab control is clicked to change page I programmatically remove the binding to the subforms on the current page and load the subforms for the tab just clicked... (here is a small extract...)

Public Sub TabCtl0_Change()
    
    Select Case iCurrent_Tab
        
        Case Is = 3
            Me.SubQualifications.SourceObject = ""
            Me.Training_Visits.SourceObject = ""
    
        Case Is = 4
            Me.subVisits.SourceObject = ""
            Me.subVisitsNotes.SourceObject = ""
            
        Case Is = 5
            
            Me.[Fin_Pro subform].SourceObject = ""
            Me.[Fin_Pro subform1].SourceObject = ""
            Me.[Fin_Pro_Hist subform].SourceObject = ""
            
               
    End Select
    
    Select Case Me.TabCtl0
        
        Case Is = 3
            Me.SubQualifications.SourceObject = "SubQualifications"
            Me.Training_Visits.SourceObject = "SalesVisits subform"
    
        Case Is = 4
            Me.subVisits.SourceObject = "subVisits"
            Me.subVisitsNotes.SourceObject = "subVisitsNotes"
            Call RatingColour
            
        Case Is = 5
            
            Me.[Fin_Pro subform1].SourceObject = "Fin_Pro subform1"
            Me.[Fin_Pro subform].SourceObject = "Fin_Pro subform"
            Me.[Fin_Pro_Hist subform].SourceObject = "Fin_Pro_Hist subform"          
            [Forms]![contacts]![FP_Subform].[Form].RecordSource = "SELECT * FROM FP_Register WHERE [MemNo] ='" & Me.MemNo & "' ORDER BY [Rec_ID] DESC"
            
    End Select
    
    iCurrent_Tab = Me.TabCtl0
End Sub

You'll see I also can use this to dynamically load the record source required for a particular subform rather than using a bound query.

Hopefully you get the idea and can extrapolate for your purpose.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

I like that solution. Thanks. In this instance, the 2 forms that toggle visibility are not exactly tied to the same record source. One is tied to a table - where the data is entered. The other is tied to a union view that pulls the information entered in that table for all related information to the current main record. Either way, your solution is still a great way handle this.



misscrf

It is never too late to become what you could have been ~ George Eliot