UtterAccess Discussion Forums > 1 Of 2 Subforms Only Populates 1st Row Of Data?

Full Version: 1 Of 2 Subforms Only Populates 1st Row Of Data?

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

vadad

Dec 20 2011, 09:49 PM

Good evening! I'm new to UA, but have followed this form for months; reading and learning - just like to say thanks to all of you who contribute to solve our problems.

Background: I have a Form/subform, it is loosely modeled after Allen Browne's Search form. The search function performs flawlessly, Both queries are localized to the subform (ie. not a named query), and both run without error. The problem I am having is one of the subforms only shows one record. I'm stumped.

Mainform: The section in question has a dropdown box called cboType. In this combo box a user selects 1 of 2 subforms to search (subform is in unbounded "subfrm" field), then the appropriate subform populates. 1 of the 2 runs smoothly, sfrm_search-pro should be showing 1500 records, it is only showing the first one.. I have performed the following steps tyring to T/S:
1. Read through many posts, here and across the Google (internet)
2. Run Debug frequently, and the right values are being passed.
3.Compared properties of sfrm_search-rcm & sfrm_searchPro, cannot find any differences. Both are continuous forms.

cboType_AfterUpdate Code:

CODE

Private Sub cboType_AfterUpdate()  'Select the source -- Works
Dim tbl As String                             'The table string
Dim strSubform As String                 'Subform string
On Error GoTo Err_cboType_AfterUpdate

    If Me!cboType.Value = "tbl_rcm" Then
        tbl = "tbl_rcm"
        'Debug.Print Me!cboType.Value         'T/S: Open immediate window (CTRL+G) to view results.
        'Debug.Print tbl                                'T/S: Open immediate window (CTRL+G) to view results.
    End If

    If Me.cboType.Value = "tbl_pro" Then
       ' MsgBox "Selection not available yet", vbOKOnly, "Invalid Selection"     'T/S: Selection not initiated.
        tbl = "tbl_pro"
        Debug.Print Me!cboType.Value       'T/S: Open immediate window (CTRL+G) to view results.
    End If

Me.RecordSource = tbl
Me.Requery
'Debug.Print "Record source= "; tbl        'T/S: Open immediate window (CTRL+G) to view results.

'Grab proper subform
    If Me!cboType.Value = "tbl_rcm" Then
        strSubform = "sfrm_search-rcm"
        'Debug.Print strSubform
    End If

    If Me!cboType.Value = "tbl_pro" Then
        strSubform = "sfrm_search-pro"
        'Debug.Print strSubform
    End If

'Load subform
Forms!frm_search![subfrm].SourceObject = strSubform
Me.[subfrm].Form.Requery

Exit_cboType_AfterUpdate:
    Exit Sub
Err_cboType_AfterUpdate:
    'MsgBox Err.Description
    Call LogError(Err.Number, Err.Description, "Search--cboType_AfterUpdate()")
    Resume Exit_cboType_AfterUpdate
End Sub

I appreciate any assistance you may provide; I'm stumped!

vadad

Dec 21 2011, 02:27 PM

Hello everyone! I hope everyone is getting ready for a good Christmas!

I'm still researching the fix for this, but have cleaned up the code quite a bit, so I figured I'd repost it.
I'll post the symptoms again:
1. Upon initial load of the form, if tbl_rcm is selected, sfrm_search-rcm loads fully & correctly
2. If I press RESET, and then select tbl_pro, the sfrm_search-pro only displays the first record.
3. If I press RESET again, and select tbl_rcm, I am prompted for sfrm_search-pro field values. Is it possible that the forms are not being fully removed from memory? If so, any suggestions on how to remove them?

One final request, could someone walk me through a detailed loading of the form, so I could better understand where this may be going astray?

CODE

    '------------------------------------------------------------------------------------------
    '----- Select the record source for the main form, and load the appropriate sub form  -----
    '------------------------------------------------------------------------------------------
Private Sub cboType_AfterUpdate()       'Select the source -- Works
On Error GoTo Err_cboType_AfterUpdate   'Error handling
Dim tbl As String                       'The table string
Dim strSubform As String                'Subform string

'Property being passed and record source show empty prior to the cbo box being filled.
'Both upon initial load and also after RESET button has been pressed.
Debug.Print "cbo property being passed is: " & tbl; "; Record Source Property is: " & Me.RecordSource

tbl = Me!cboType.Value                  'Grab the cbo value
Me.RecordSource = tbl                   'Match record set to cbo value

Select Case tbl                         'Grab proper subform and edit main form controls
    Case "tbl_rcm"
        strSubform = "sfrm_search-rcm"
        Me.cbosubsys.Visible = False
        Me.cbosubunit.Visible = False
        Me.cbocomp.Visible = False
        Me.txtmpc.Visible = False
        Me.cboCat.Visible = False

    Case "tbl_pro"
        Me.RecordSource = tbl
        strSubform = "sfrm_search-pro"
        Me.cbosubsys.Visible = True
        Me.cbosubunit.Visible = True
        Me.cbocomp.Visible = True
        Me.txtmpc.Visible = True
        Me.cboCat.Visible = True
    Case Else: MsgBox "Invalid selection!", vbCritical, "Danger....Danger..."   'Trap stupidity
End Select

' the property is being passed, and record source is updating. Shows correct in debug.
Debug.Print "cbo property being passed is: " & tbl; "; Record Source Property is: " & Me.RecordSource

'Load subform; the subforms record set is set in the subform, query against table ID'd in the variable - tbl
Forms!frm_search![subfrm].SourceObject = strSubform
Me.Requery          'Didn't see a difference between using requery or refresh.

Exit_cboType_AfterUpdate:
    Exit Sub
Err_cboType_AfterUpdate:
    Call LogError(Err.Number, Err.Description, "Search--cboType_AfterUpdate()")
    Resume Exit_cboType_AfterUpdate
End Sub

And the RESET button's code:

CODE

Private Sub cmdShowAll_Click()  'Resets the form -- Works
On Error GoTo Err_cmdShowAll_Click

Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
        ctl.Value = Null
        ctl.Visible = True
    Case acCheckBox
        ctl.Value = False
        ctl.Visible = True
    End Select
Next
    Me.subfrm.Form.FilterOn = False                       'Removes filter on sub form
    Forms!frm_search![subfrm].SourceObject = ""   'Removes subform
    Me.FilterOn = False                                          'Removes filter on form
    Me.RecordSource = ""
    MsgBox "My mainform record source is: " & Me.RecordSource      'This shows blank during RESET

Exit_cmdShowAll_Click:
    Exit Sub
Err_cmdShowAll_Click:
    'MsgBox Err.Description
    Call LogError(Err.Number, Err.Description, "Search--cmdShowAll_Click()")
    Resume Exit_cmdShowAll_Click
End Sub

Once again, Thank you for your assistance. Merry Christmas!

vadad

Dec 22 2011, 08:39 AM

Good morning everyone, I got my code to work, yea!!

This has been a good learning exercise, and if nothing else, putting my question out there helped me think through the problem. I do wonder why no one responded; was it that obvious of a problem? Did I show bad etiquette? Or is it just the way forums work?

The error was in the AfterUpdate event. I had a suspicion it was related to the record set, as it didn't look like it was clearing properly (that's why I added the RESET function in the last post). For some reason, I didn't think about including the recordset in my CASE statement, I was trying to do it via the cboType. Once I put the SQL into the CASE statement, and removed the reference that tied the record set to the cboType value, worked like a champ. Thanks to a thread on here that pointed me in the right direction. My final code is...

CODE

Private Sub cboType_AfterUpdate()       'Select the source -- Works
On Error GoTo Err_cboType_AfterUpdate   'Error handling
Dim tbl As String                       'The table string
Dim strSubform As String                'Subform string
Dim strSQL As String

tbl = Me!cboType.Value                  'Grab the cbo value
Select Case tbl                         'Grab proper subform and edit main form controls
    Case "tbl_rcm"
        strSubform = "sfrm_search-rcm"
        strSQL = " SELECT tbl_rcm.RCMID, tbl_rcm.System, tbl_rcm.rcmStart, tbl_rcm.rcm_due, tbl_rcm.rcmFinish, tbl_rcm.rcmLead FROM tbl_rcm;"
        Me.cbosubsys.Visible = False
        A whole bunch of controls/settings follow....removed for brevity.

    Case "tbl_pro"
        strSubform = "sfrm_search-pro"
        strSQL = " Removed the rest for brevity."
    Case Else: MsgBox "Invalid selection!", vbCritical, "Danger....Danger..."   'Trap stupidity
End Select

'Load subform; the subforms record set is set in the subform, query against table ID'd in the variable - tbl
Forms!frm_search![subfrm].SourceObject = strSubform
Me.subfrm.Form.RecordSource = strSQL

' Verify properties are being passed.  Shows correct in debug.
'Debug.Print "cbo property being passed is: " & tbl; "; Record Source Property is: " & Me.RecordSource

'Error trapping follows...Removed for brevity.
End Sub

I really appreciate all everyone does on these forums -- I wish everyone a Merry Christmas!

Trivial

Dec 22 2011, 01:08 PM

QUOTE (vadad @ Dec 22 2011, 08:39 AM)

Congratulations on solving the problem.

I can only answer for myself.

I'm unfamiliar with Allen Browne's method and was uncertain precisely of what you were trying to do - When I need to search a subform, I search the subform. Perhaps I'm misunderstanding something.

Regardless, don't hesitate to post in the future.

Take care and have a happy holiday.

vadad

Dec 22 2011, 02:05 PM

Trivial; thanks for the feedback!

I will try and explain things better in the future.

Have a great Christmas and New Year!!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.