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

    Variation on Combo Box Search & Filter (A2K)

    This was a situation previously visited in <post#=424187>post 424187</post#> and was ably handled by Hans. Like he could do it any other way. Now, here is the variation, I think.


    Main Form: frm DPM Corp contains Corporation Data
    Subform: frm DPM Corp - SubForm 03
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variation on Combo Box Search & Filter (A2K)

    As in the reply to the post you referred to, you will have to modify the Row Source of the combo box, in this case as you move from record to record in the main form. The On Current record of the main form is the event to use for this.

    Private Sub Form_Current()
    Me!subFormPersonnel!cboPersonnel.RowSource = _
    "SELECT PSurname FROM tblPersonnel WHERE CorporationID = " & Me.CorporationID
    End Sub

    You will have to adapt this for your situation (substitute the correct table name etc.)

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

    Re: Variation on Combo Box Search & Filter (A2K)

    Hans,

    I've obviously gone into BrainDead Mode. Based on your suggestion, I put the following in the OnCurrent event of the Main Form

    Me!subFormPersonnel!cboPersonnel.RowSource = _
    "SELECT PSurname FROM [qry DPM Corp - Personnel] WHERE CorporationKey = " & Me.CorporationKey

    and I get

    Run-time error '2455':
    You entered an expression that has an invalid reference to the property Form/Report

    The row source in cboPersonnel is: SELECT [qry DPM Corp - Personnel].[PSurname] FROM [qry DPM Corp - Personnel];

    I'm sure that it's something obvious, but .....
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variation on Combo Box Search & Filter (A2K)

    Is subFormPersonnel the exact name of the subform as a control on the main form? You can check this by opening the main form in design view and clicking once on the subform. The control name will be displayed in the Formatting toolbar, and in the title bar of the Properties window. This name need not be the same as the name of the subform in the database window.

    Is cboPersonnel the exact name of the combo box on the subform?

    Is CorporationKey the exact name of the field that identifies the corporation in both tables?

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

    Re: Variation on Combo Box Search & Filter (A2K)

    Hans,

    Unfortunately the answer is yes on all counts. I've attached a Word document with screen shots of the referred to areas. Maybe you'll see something I can't. I appreciate your taking the time with my problem.
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variation on Combo Box Search & Filter (A2K)

    I'm afraid I'd have to see the database to solve this.

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

    Re: Variation on Combo Box Search & Filter (A2K)

    Hans,

    I appreciate the offer. How exactly can I send you a zipped copy of my database? I really don't care to post on the board.
    Cheers,
    Andy

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variation on Combo Box Search & Filter (A2K)

    See <post#=401925>post 401925</post#> for instructions on how to post a database in a thread. As an alternative, if you can put the database on a web server, you can PM me the URL.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Variation on Combo Box Search & Filter (A2K)

    The On Current event of the main form occurs at least once before the subforms have been loaded. To avoid this problem, just insert

    On Error Resume Next

    above the instruction that sets the row source. Since this is the only instruction, it has no adverse side effects, and since the On Current event will occur again when the subforms have been loaded, the row source will be set correctly.

Posting Permissions

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