Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a form that is used for data entry. We have to go back through and add data to these records. Is there a way to pull up the form that groups the records by field "A" and sorts by field "B"? This would essentially order the forms A1-1, A1-2, etc, making adding data easier.

Right now I am using DoCmd.OpenForm to only display records with certain values in certain fields. Do I just need to modify this a bit?

Thanks for the help!

[Edit]

I would like this to load the form on button click so I have

Private Sub btnDataEntry_Click() 
    DoCmd.OpenForm "Data Sheet", acNormal, , , acFormEdit, , OpenArgs:="MapNumber"
End Sub

Then as suggested

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

This is not working for me. If possible I would also like it to group all map numbers together and then have all Item numbers ascending. So there could be 10 entries with map number 1 and item numbers 1-10.

share|improve this question
Please tell us more about "not working". Does the code compile? Error message? If so, which line triggers the error? – HansUp Jul 17 at 15:00
by not working I mean that it does not "sort" the data. What is displayed in the forms is the same no matter what I enter (sorted by the ID field) – Steven 'Catfish' Catlett Jul 17 at 15:03
Try with this: DoCmd.OpenForm FormName:="Data Sheet", View:=acNormal, DataMode:=acFormEdit, OpenArgs:="MapNumber" – HansUp Jul 17 at 15:10
What is Main in Main.OrderBy and Main.OrderByOn? – HansUp Jul 17 at 15:11
That is left over from me trying things. Main is the from that has the buttons on it. It has been switched back to Me – Steven 'Catfish' Catlett Jul 17 at 15:22
show 1 more comment

1 Answer

up vote 1 down vote accepted

OpenForm doesn't include an option to specify the sort order. However you could use its OpenArgs option to pass in sort information, then apply that during form load.

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

Then to open YourForm sorted by a field named id in ascending order ...

DoCmd.OpenForm "YourForm", OpenArgs:="id"

Include DESC for descending order ...

DoCmd.OpenForm "YourForm", OpenArgs:="id DESC"

Use this version of Form_Load to troubleshoot why the form opens without the sorting you expect.

Private Sub Form_Load()
    MsgBox "Me.OpenArgs: " & Nz(Me.OpenArgs, "Null")
    If Not IsNull(Me.OpenArgs) Then
        Me.OrderBy = Me.OpenArgs
        Me.OrderByOn = True
    End If
    MsgBox "Me.OrderBy : '" & Me.OrderBy & "'"
    MsgBox "Me.OrderByOn: " & Me.OrderByOn
End Sub
share|improve this answer
Thanks, I'm trying to make sense of this but I'm not quite getting there. I have added more information to my original question. More research for me! Thanks again. – Steven 'Catfish' Catlett Jul 17 at 14:58
Me.OpenArgs:Null Me.OrderBy: " Me.OrderByOn:False – Steven 'Catfish' Catlett Jul 17 at 15:40
I don't understand how that is possible with the OpenForm command I suggested earlier ... unless the form where you saved that Form_Load code ... is that a subform on another form? – HansUp Jul 17 at 15:43
All the code is on Form_Main, the other form "Data Sheet" is a separate form with no code. – Steven 'Catfish' Catlett Jul 17 at 15:50
Wow, I got it now. Amazing how this stuff can make me feel like such an idiot. Thank you so much! – Steven 'Catfish' Catlett Jul 17 at 15:52
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.