Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching SubForm Records via ComboBox (A2K)

    Records created in SubForm work perfectly with respect to matching record on MainForm.
    I created a combobox on SubForm which was to enable me to lookup ONLY the matching records which were created within the SubForm.
    I created the combobox using the "Find a record on my form based on the value I selected in my combobox" selection.

    The field that I wish to match to: Lot_No_01B is a numeric field

    The code generated looks like this:

    Private Sub Combo116_AfterUpdate()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Lot_No_01B] = " & Str(Me![Combo116])
    Me.Bookmark = rs.Bookmark
    End Sub

    The Problem:
    The combobox looks at ALL of the records created for ALL of the MainForm records and not those SPECIFIC to the matching SubForm Records.

    When testing on the SubForm on its own and not when it is embedded in the MainForm, it goes to the correct record when selected.

    When testing the SubForm when it is embedded in the MainForm, I click on the combobox, ALL of the records are displayed and
    when I click on one of the selections when I AM NOT on the MainForm that created that record, I get:

    Run-time error '3021'
    No current record

    When I run the same test when I AM on the MainForm that created that matching record, it works.
    My guess is that there has to be a reference change made within the AfterUpdate in the SubForm routine
    that somehow tells it that it should only look at those records that match the MainForm.
    Sounds semi-confusing, but I know what I mean.

    As always, would be appreciative of any help coming my way.
    Cheers,
    Andy

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    Your "problem" is the rowsource for your combobox. It needs to use the same filter Access uses on the subform; that is, it needs a WHERE condition based on the record on the mainform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    Mark,

    I appreciate your quick reply. The data setup on this problem looks like this:

    MainForm DataSource: "qry DPM Projects" using PJ_ProjectKey as the matching key field

    SubForm DataSource: "qry DPM Projects
    Cheers,
    Andy

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    I don't know what the rowsource of the Find combo box is now, but I suspect it is "qry DPM Projects
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    Mark,

    I realize that I'm being repetitive and dumb as a post and I hope I don't get kicked
    off this board for being dull, but it may help in solving this problem
    and getting through to me. I'm obviously missing something basic.

    The MainForm/Subform Linkage is based on the following:

    MainForm DataSource: qry Projects
    DataName: PJ_ProjectKey
    ControlName: txtPJ_ProjectKey

    SubForm DataSource: qry Projects
    Cheers,
    Andy

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    This is what I was talking about. Your rowsource for the combobox is"
    SELECT [qry DPM Projects - Lots].[Lot_No_01B] FROM [qry DPM Projects - Lots]

    This selects all the records in the table. You need a selection criteria to tell it that you only want the lots for a specific project. Somewhere on the mainform, there is a control that contains the Project#, let's assume this is a textbox named [txtProject]. Your combobox rowsource then needs to become:
    SELECT [qry DPM Projects - Lots].[Lot_No_01B] FROM [qry DPM Projects - Lots] WHERE Lot_ProjectKey=[txtProject]

    (assuming I've got the names correct)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    Mark,
    Thanks again for the quick response. I'm almost, but not quite there. I have to drop this until tomorrow, but I did have one further question. When you are referring to a control on the MainForm like I'm trying to do, should I be including the MainForm name in the equation?
    Cheers,
    Andy

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    If the combobox is in the subform, then you would have to include the reference to the mainform.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    Hey Mark,

    Here's me back in the barrel. I've tried any number of variations on the same theme, but I do believe I've exhausted
    all possible error messages. Based on something I saw in one of the other postings, the following seemed correct,
    but the system doesn't seem to think so.

    I've tried referring to the datasource

    SELECT [qry DPM Projects - Lots].[Lot_No_01B] FROM [qry DPM Projects - Lots] WHERE [Lot_ProjectKey]=forms!frm DPM Project.[PJ_ProjectKey]

    And I've tried referring to the controlname

    SELECT [qry DPM Projects - Lots].[Lot_No_01B] FROM [qry DPM Projects - Lots] WHERE [Lot_ProjectKey]=forms!frm DPM Project.txtPJ_ProjectKey

    Both of the above throw up on me electronically with a " syntax error (missing operator) message.
    Any, as always, valuable suggestions?
    Cheers,
    Andy

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    >>SELECT [qry DPM Projects - Lots].[Lot_No_01B] FROM [qry DPM Projects - Lots] WHERE [Lot_ProjectKey]=forms!frm DPM Project.txtPJ_ProjectKey<<

    Any time you have an object or a control name that includes spaces and/or special characters (like dashes, slashes, etc.), you need to encase that name in brackets. So above you needed: ....=Forms![frm DPMProject].txtPJ_ProjectKey
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching SubForm Records via ComboBox (A2K)

    Mark,
    Thanks again for your quick response. Still having a bit of a problem, but I have to bail out for the moment, but will be back to it tonight or tomorrow afternoon.
    Cheers,
    Andy

Posting Permissions

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