Me.OrderBy not working

[Rramjet]

Hi all -



back again... Little problem. Huge frustration.

The first OrderBy works perfectly - the second does not. The fields have exactly the same parameters (in fact they are the same field located on different forms). If manually right click - I can Sort A-Z ... so if I can do that - then there should be nothing preventing a sort command from working, so why doesn't it work?

Private Sub BITSEA_New_Record_button_Click()
Me.OrderBy = "BITSEA_ID ASC"
Me.OrderByOn = True
DoCmd.GoToRecord , , acLast
DoCmd.Close acForm, "BITSEA_Add_New", acSaveYes
DoCmd.OpenForm "BITSEA"
DoCmd.GoToControl "BITSEA_ID"
Me.OrderBy = "BITSEA_ID ASC"
Me.OrderByOn = True
DoCmd.GoToRecord , , acLast
End Sub

[June7]

These two forms have same dataset? Why use two forms?

[Rramjet]

*Sigh* - it's a workaround to get around Access many many limitations and bugs. The main form(s) display demographic information from a CLIENT table (in the header) and allow data entry in the body for the various outcome measures the clients complete (one form per outcome measure). Unfortunately this structure does not allow for the creation of new records where a client does not already have an outcome measure (it runs into the "notinList" error). So ...I create another form, accessed by a "New Record (manual creation)" button that allows a new Client URN (for that particular OM) to be entered (like a URN). THEN what I have to do is close that form and re-open the primary OM form - and find that new record so data can be entered - and the easiest way to do THAT is to sort by the autogenerated ID number and go to the last record... BUT, The command simply will not work... hence my question. What could possibly stop it from working?

[June7]

Sorry, can't follow that. I don't understand the 'limitations and bugs' you feel must be worked around. If you want to provide db for analysis, follow instructions at bottom of my post.

[Rramjet]

LOL. Yeah - just blowin' off a little steam (but get an error in your form and it can turn "sticky" on you and you have to wipe the whole form and start again is frustrating...). ...and Access just is frustrating. Just when you think you have it pinned, it stops working on you. ..and that VBa code is seriously difficult to fathom... (at least some of the logic of it escapes me ..it's like they did it that way not because it makes sense - but just because they could!) But then - what do I know = nothing obviously...

All I really want to know are the probable causes for a field that is accessible and not locked not to be able to be sorted using the OrderBy code. Is it possible for the query sort order to prevent the code from operating for example? What other things might it be? The placement of the field (header/body/footer), the fact that there is a tab control on the form containing a subform on one of the tabs ...I need some clues - like "have you thought about..."

[June7]

Never seen code like this and I have no experience with this issue so I need to do testing with the structure. Since I don't already have this and don't want to build, you could provide yours for analysis. I still don't understand why two forms.

Access is the only database I have any real experience with and I don't find it so daunting but then ignorance is bliss? I have worked a little with old DOS dbase4 when we migrated from that to Access. Now if you want a challenge as an amateur developer, give that one a go. No GUI tools and wizards to hold your hand.