Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Unanswered: SubForm based on ComboBox

    Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[MembersID] = " & Str(Nz(Me![Combo6], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    On the Combo6_LostFocus & GotFocus the code is
    Me.Combo6.Requery
    and for Form_GotFocus()

    I don't get any errors - I just get that bell which basically doesn't tell me anything.
    There's two tables: tblDues & tblMembers
    Primary Key MembersID
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Make the Combobox as an Unbound Combobox, i.e. remove the Control Source property value (MembersID).

    Since, you are using only the Unbound Combobox on the Main Form it is not necessary to use tblMembers as Recordsource to the Form.

    MembersID field on the tblMembers Table is the PrimaryKey field. When you open the main form, the first record with MembersID value 1 is active. When you attempt to select another MembersID from the Combobox and try to overwrite the PrimaryKey MSAccess prevents that attempt and gives the warning bell. By removing the MemberID field name from the Control Source property of the Combobox corrects this condition.

    Besides that set the Default Value Property Value of the Combobox to 1 as well.

    Change the VBA statement: Dim rs As Object in your Program to Dim rs As Recordset.
    Last edited by apr pillai; 06-08-13 at 14:34.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Jun 2013
    Posts
    3

    After change

    apr pillai thank you for your help. I will attempt to fix these errors on my own, but I just wanted to update where I am with this...

    The first thing that happens is it asks for MemberID
    Then I get error "Run-time error '91': _ Object Variable or With block variable not set.

    Ok, I've fixed that. Had to restore Recordsource for main form back to tblMembers. Now I need to get the subform to update after I change item in combobox.
    Last edited by JZeig1; 06-08-13 at 17:25. Reason: update status

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Add few records in tblDues Table with MemberID, related to MembersID in tblMembers.

    These related records will appear in the sub-form when you select a MembersID from the Combobox.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  5. #5
    Join Date
    Jun 2013
    Posts
    3

    Updated Form

    Here's my latest version of form.
    It does what I instruct after change in combobx, but I can't navigate through the table.
    None of the information change as it relates to action by the navigation bar.
    I would like to be able to at least see the records change as they relate to the payments.
    I was thinking about adding a text box to the footer of the sform. That would allow me to nav thru subform; subsequently, allow me to id who this list of payments belong to.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •