Results 1 to 14 of 14
  1. #1
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92

    Creating a sort order for a tabular form not working correctly

    I have a tabular form. I would like the records to show up in that form in order, based on a field that contains the date. I have created the following for the OnLoad of that form:



    Code:
    Private Sub Form_Load()
    
    Me.OrderBy = "WeekEnding"
    
    Me.OrderByOn = True
    
    End Sub
    The field (and text box) that contains the date is called WeekEnding.

    Whenever I open the form, it asks me to enter a value, like so:

    Click image for larger version. 

Name:	EnterParameter.png 
Views:	14 
Size:	2.3 KB 
ID:	31217

    I don't understand why it is asking me this. This is my first time trying to use OrderBy, so I'm guessing I'm probably doing something wrong, but from the research I've done on the web, this does seem like the correct way to use it. Anyone have any ideas how to stop this Enter Parameters window from popping up?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks like the recordsource to your form is a query and is incorrect. Suggest supply the sql to your query

  3. #3
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    My recordsource for this form is a table.

    The strange thing is, until I started trying to do this OrderBy, this form opened with no problems. But now, even if I remove the Sub Form_Load altogether, I still get this Enter Parameters message.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why use VBA? Could set the OrderBy property in design, no need for VBA. Why use OrderBy anyway? Could set the sort order in the RecordSource by using an SQL statement. Is WeekEnding a calculated field?

    If you still get the error, make sure the OrderBy property is empty then save the form, code sets the property and the form will close with the setting saved.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    No, WeekEnding is not calculated field.

    I'm trying to do something I've never done before, so I thought this might be the correct way to do it. Let me give you a full explanation of what I'm trying to do, and you can tell me if VBA and/or OrderBy is the way to go.

    I created a Form that I want to use as a Subform on another Form. That subform shows all the time sheets for a particular employee, and one of the items it shows is the date (week ending date of the time card). So, when I view that form as a subform, it is automatically filtering records to only show ones that are related to that employee. Not sure that's important in this case, but wanted to mention it in case it is.

    Since an employee may have more than one time sheet, I was hoping the subform would order the time sheet listings by date (in ascending order). Before I started to try to get it to display in order, the subform worked perfectly. But now that I have started to try to get it to display in date order, I am getting this Enter Parameters popup.

  6. #6
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    Right now, I have two time sheets that have the same WeekEnding date, and that is 11/11/2017. So maybe that is why it is showing me this popup? I still don't understand why it would be doing that, but maybe that has something to do with it?

    UPDATE: Nope, that isn't it. I deleted one of the records on the table so I only had one time sheet record with WeekEnding equal to 11/11/2017, but the popup still occurs.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Somewhere an expression is using "11/11/2017" as the name of something and Access can't find anything with that name so it treats the reference as a popup input. Suggest you double check the Filter and OrderBy properties for a start.

    What do you mean by 'two time sheets' - records, tables, forms, reports?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    The table that holds the time sheet data holds ALL time sheet data. So there can be many, many that have the same WeekEnding date, because there may be 50 employees with a time sheet for that week.

    I'll look through the code and see if I can find something that using that name, and will report back.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not certain this would be the cause, but it may be because of ambiguity, although I would have thought you'd get the error "Ambiguous Name Detected". In other words, I never allow the control and its field to have the same name. Usually it's because of an overlooked spelling error between the referenced field and the name written in code.

    Not sure I agree with relying on the sort order of a table as it's oft been said here and elsewhere that a table is basically nothing but a bucket of records having no particular or reliable sort although it usually appears reliable. I do agree that basing the form on a query with the sort applied there would be simpler than trying to affect it in the form loading - unless the desired sort is not always on the same field.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A table can be saved with a sort order setting but I don't rely on it. I use an SQL statement as RecordSource.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    I tried opening each Form separately, to see where I get the popup. It's only on two: the Form in question (which I'll call Subform here), and the Form that uses the Subform. Here is all the VBA code used inside each form.

    SUBFORM:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub OpenButton_Click()
    
    Dim recordID As Integer
    
    recordID = Me.TimecardID
    
    DoCmd.OpenForm "Timecards", , , "TimecardID = " & recordID
    
    End Sub
    FORM:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Command65_Click()
    
    If Me.Dirty Then Me.Dirty = False
    
    End Sub
    
    Private Sub Command66_Click()
    
    If Me.Dirty Then Me.Dirty = False
    
    If MsgBox(Prompt:="Are you sure you wish to delete this record?", Buttons:=vbYesNo, Title:="Confirm Deletion") = vbYes Then
        On Error Resume Next
        DoCmd.RunCommand acCmdDeleteRecord
        If Err.Number = 0 Then
            MsgBox Prompt:="Record Deleted.", Buttons:=vbOKOnly, Title:="Deletion Successful"
        Else
            MsgBox Prompt:="No deletion occurred.", Buttons:=vbOKOnly, Title:="Error"
        End If
    Else
        MsgBox Prompt:="The record was not deleted.", Buttons:=vbOKOnly, Title:="Canceled"
    End If
    
    End Sub
    
    Private Sub NewTimeCardButton_Click()
    
    DoCmd.OpenForm FormName:="Timecards", View:=acNormal, DataMode:=acFormAdd, WindowMode:=acWindowNormal, OpenArgs:=Me.EmployeesOnJobsID
    
    End Sub
    I don't see anything in either of them that could be causing this problem. You?

  12. #12
    mcomp72 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    92
    I just figured it out. I went into Properties on the Subform, and inside Order By, it had [11/11/2017]. I have no idea why it would have had that in there! I never put that in there. I deleted it, then re-input my code for OnLoad, and now it works.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't see anything in code. Must be properties as suggested in post 7.

    If you want to provide db for analysis, follow instructions at bottom of my post.

    EDIT: See you found the solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02 .........

    Virtually all of my forms & reports use saved queries, never tables. It is easy to modify the query by adding a calculated column if I need it; can't do that with a table.
    I use saved queries because I can use a query for a form record source and for a report record source.
    I sometimes will use an SQL statement as the record source instead of a query.... if I know that I won't need/use it for another form/report.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update query w/form not working correctly!
    By breakingme10 in forum Queries
    Replies: 3
    Last Post: 11-05-2014, 02:27 PM
  2. Calculated field on a form not working correctly
    By danielrogers1 in forum Access
    Replies: 5
    Last Post: 07-28-2014, 01:24 PM
  3. Sort function not working correctly
    By kristyspdx in forum Reports
    Replies: 2
    Last Post: 10-09-2012, 11:04 AM
  4. Sort order in a sub form
    By roar58 in forum Forms
    Replies: 1
    Last Post: 03-17-2012, 08:57 PM
  5. Split Form not working correctly
    By Brian62 in forum Access
    Replies: 29
    Last Post: 02-16-2010, 05:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums