Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms - Record source (2002)

    I would like to use an option box to select which query to use to open a form. I can't quite figure out what the code should be for each option to open the form with the correct query. Can anyone give me a sample that I can start with.

    Thanks

    Paul

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

    Re: Forms - Record source (2002)

    Do you want to use a different query as Record Source of the form depending on the choice, or do you want to filter the records displayed in the form?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms - Record source (2002)

    I want to use a different query to open the form based on which option you select. Each query will sort the same data a different way. Ex: one will sort by Names, one by Course, one by course #

    Thanks

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

    Re: Forms - Record source (2002)

    1. You don't really need different queries for that, you can specify the sort order in code. Example:
    On the calling form, you have an option group grpSort, and a command button cmdOpenForm.
    The On Click event procedure for the command button looks like this:

    Private Sub cmdOpenForm_Click()
    Dim strSort As String
    Select Case grpSort
    Case 1
    strSort = "Names"
    Case 2
    strSort = "Course"
    ...
    End Select
    DoCmd.OpenForm FormName:="frmOtherForm", OpenArgs:=strSort
    End Sub

    The On Load event of the form being opened ("frmOtherForm") checks the OpenArgs property:

    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
    End If
    End Sub

    2. If you prefer to use different queries, you can use code like this (employing the same names as in the example above):

    Code for the command button on the calling form:

    Private Sub cmdOpenForm_Click()
    Dim strSource As String
    Select Case grpSort
    Case 1
    strSource = "qrySortNames"
    Case 2
    strSource = "qrySortCourse"
    ...
    End Select
    DoCmd.OpenForm FormName:="frmOtherForm", OpenArgs:=strSource
    End Sub

    Code for the form being opened:

    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
    Me.RecordSource = Me.OpenArgs
    End If
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms - Record source (2002)

    So far so good.
    In the Select Case
    strSort = "Names" Does the word Names stand for the name of the field or the name of the textbox I want to sort from.

    I keep getting a type mismatch error.

    Thanks

    Paul

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

    Re: Forms - Record source (2002)

    In my first example, you must use the name of the field you want to sort on.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms - Record source (2002)

    Thanks Hans. The sort procedure works.
    Now can I go one step further.
    Case 2 is sorting by course. Is there a way to make the sort by course to sort by course with the names in each course sorted ABC.
    Somehow putting a second field in the strSort = "course" line.

    Thanks again.

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

    Re: Forms - Record source (2002)

    You can assemble the sort string. Examples:

    strSort = "Course, Names"
    strSort = "Course DESC, Names"
    strSort = "Course, LastName, FirstName"

    DESC after a field name specifies sorting in descending order.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms - Record source (2002)

    Thanks Hans-

    Works great!

    Paul

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms - Record source (2002)

    Hans can I go one step further. Below is my code to sort. Can I put something is Case 1 that would make a label to say "Sorted by Name "(lblsortNames) become visible on the frmCommentReview after the sort is done. I created the label on the form and made it not visible. Not sure how to code it to make it visible.

    Private Sub cmdOpenForm_Click()
    Dim strSort As String
    Select Case grpSort
    Case 1
    strSort = "Name"
    Case 2
    strSort = "Course, Name"
    Case 3
    strSort = "Course #, Name"
    End Select
    DoCmd.Close acForm, "frmCommentReview"
    DoCmd.OpenForm FormName:="frmcommentreview", OpenArgs:=strSort
    DoCmd.Close acForm, "frmSortList"
    End Sub

    Thanks again.

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

    Re: Forms - Record source (2002)

    In the On Load event of frmCommentReview:

    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
    Me.OrderBy = Me.OpenArgs
    Me.OrderByOn = True
    Me.lblSortNames.Caption = "Sorted by " & Me.OpenArgs
    Me.lblSortNames.Visible = True
    End If
    End Sub

  12. #12
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms - Record source (2002)

    Thanks again.

    It works great!

    Enough on this topic for awhile.

    Paul

Posting Permissions

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