Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter form query (2k)

    Hi,

    I have a specific sequence for opening the attached database.
    When the database is initially entered the user is asked to select their role from FrmStartup and dependant upon the role selected it will either display FrmPassword for Support, or CboName for Lawyer or Caseworker.
    The issue I have relates to CboName from FrmStartup.
    Once the user selects their corresponding initial from CboName, they then select CmdOpenAreaForm (currently hidden behind FrmPasswordSub).
    FrmMain is then opened filtered on the selected name. This can relate to either the Lawyer or Caseworker role by either StaffID or CaseworkerID.

    What I have the issue with is quite minor but annoying. Cbodefendant (on the right hand side of FrmMain) is there to select individual records relating to initials selected from CboName on FrmStartup.
    This has the following code on it:

    Private Sub cboDefendant_AfterUpdate()
    If IsNull(Me.cboDefendant) Then
    Me.RecordSource = "QryMain"
    Else
    Me.RecordSource = "QryMainDef"
    End If
    Me.cboDefendant = Null

    End Sub

    QryMain works fine and therefore this drop down is available for when support has been selected from FrmStartUp.
    QryMainDef is meant to filter based on the initial selected, however this can related to LawyerID or CaseworkerID. I canít work out how to have the query dynamic so that it can compare the value selected from CboName to either one or the other.

    On a side note, Iíd also like to automate the value in Combo78 (currently titled Archive?) based on the value in FrmDirectionsSub>DateServed, and if this is not null then the value in Combo78 (or rather the Archive field in FrmMain) equals true.

    Thanks for any help/advice.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filter form query (2k)

    Here is some code that should do the second job, but I could not test it as you did not include the relevant table/query.


    [Pre]
    Private Sub DateServed_AfterUpdate()
    If Not IsNull(Me.DateServed) Then
    Me.Parent.Combo78 = True
    Else
    Me.Parent.Combo78 = False
    End If
    End Sub
    {/pre]

    Does this query do what you want? It seems to work for me.
    Attached Images Attached Images
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter form query (2k)

    Hi,
    I think I've managed to condense the database down to what you need and I've attached a fresh copy.
    I couldn't get the coding to work on the DateServed field, to alter the Archive status.
    I still can't get the combobox to work either.
    Attached Files Attached Files

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filter form query (2k)

    Here it is back again.
    The query for showing caseworkers cases is as posted previously.

    I have fixed the Date Served stuff. There were two problems:
    * You wanted me to set a value in a combo box, but you could not see the value in the combo. Instead you see a text box next to the combo that was itself set in the after update event of the combo. So the code in Date Served also has to set a value for the text box.

    * The combo box is bound to a yes/no field, but the values in its row source were 0 and 1 rather than 0 and -1.
    Attached Files Attached Files
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter form query (2k)

    Sorry, I'm still having a problem getting the combobox to work. I've amended the QryMainDef to reflect your suggestion. I've also set the RowSource to the same query, still no luck.
    The archive on the value of DateSent works fine, thanks. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Slightly unsure of what to do next.
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filter form query (2k)

    Sorry I had missed part of the question, but when I look back at it I am unclear what you mean by:
    <hr>Cbodefendant (on the right hand side of FrmMain) is there to select individual records<hr>

    Do you mean by this you want to be able to Find a Case based on the Defendant?
    I think so as next to the combo it says "Search by Defendant"

    In this case the query qryDefendants needs to be modified as below. Note it is CaseID in the first column, not DefendantID.

    And the After Update code needs to be this.

    <pre>Private Sub cboDefendant_AfterUpdate()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CaseID] = " & Me![cboDefendant]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Set rs = Nothing

    End Sub
    </pre>

    Attached Images Attached Images
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter form query (2k)

    Hi,

    Thanks for that, it work a treat and does do precisely what I was after.

    Regarding the previous issue, is there any reason I can only get it to open if I remove this:

    =opencalendar("FrmDirectionsSub","DateServed")

    from the on click event?

    The above function simply opens an autofill calendar, but with it in place, it seems to clash with the archiving code.

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

    Re: Filter form query (2k)

    There is no function opencalendar in the database that you attached.

    You use "FrmDirectionsSub" in the expression on FrmDirections. There is no form FrmDirectionsSub in the database that you attached.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter form query (2k)

    FrmDirectionsSub is the sub form on FrmMain, and the field I was referring to is DateSent which has the onclick event as previously described.
    For size purposes I have had to delete the actual calendar that would normally pop up.
    I was curious as to whether the two commands would conflict with one another.

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

    Re: Filter form query (2k)

    There's no way for us to know, but I don't think opencalendar will work this way since FrmDirectionsSub is not the name of a form, but the name of a subform control.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filter form query (2k)

    I had just ignored the calendar stuff, as you had not included it.

    However, the code that does the archiving is in the after update event. The after update event does not happen if you set the value via code, so using the calendar would not lead to archiving.
    Is that the problem you encountered?
    Regards
    John



  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter form query (2k)

    Thats the problem I'm getting. Presumably there is no way around it, currently I have disabled the calendar facility on this field as the archive facility is more important.

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

    Re: Filter form query (2k)

    You could call the After Update code from the calendar, but that wouldn't be an attractive option for a general purpose calendar.

Posting Permissions

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