Sort ascending on different form fields depending on where form call came from

[vicissitude]

Would really appreciate some help on this, cant seem to find the solution anywhere.

I have a database dealing with timetables and transport needs.

I have 2 different forms (Timetable and TransportMenu) which call another form (UpdateJourneys) via command buttons which filter info via macros and queries.

When 'Timetable' form calls 'UpdateJourneys' form I want it to sort ascending by [Day] field

When 'TransportMenu' calls 'Updatejourneys' form I want it to sort ascending by [Surname] field

I can work out how to set the sorting characteristics in the form properties but i can't work out how to get it to sort depending on which command button is pressed / Macro runs. Is there a command you can run from the macro?

Many thanks for any help.

 

[vbaInet]

Try this:

Me.OrderBy = "[Day] ASC"
Me.OrderByOn = True

 

[vicissitude]

Thanks for the code, however, it gives this error message:

"The Object does not contain the automation object 'Me'

You tried to run a Visual Basic procedure to set a property or method for an object.

However, the component doesn't make the property or method available for Automation operations. Check component's documentation for information on the properties and methods it makes available for Automation operations."

Had a look at the properties of the form and cannot find anything to do with automation.

I put this code in the macro with 'runcode' and then into the expression builder. Is this ok?

Many thanks.

 

[vbaInet]

You put the code in the Click event of the "Sort by Day" button. Code builder. If the name (not the caption) of your button is called DaySort then the event would look like this:

Private Sub DaySort_Click()
    Me.OrderBy = "[Day] ASC"
    Me.OrderByOn = TrueEnd Sub

 

[vicissitude]

OK I have put this on the on_click event of button

Private Sub LookUpJourneysForWeek_Click()

stDocName = "UpdateJourneys"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.OrderBy = "[Day] ASC"
Me.OrderByOn = True

End Sub



And it opens the form "UpdateJourneys" form but now asks for the parameter [Day] even thought [Day] is a field name on the "UpdateJourneys" form. I have tried changing to Me.OrderBy = "[TextBoxName] ASC" but to no avail. Sorry I am new to this and it is getting a bit confusing.

Thanks.

 

[vicissitude]

have also tried giving [Day] the full path [Forms]![UpdateJourneys].[Day] but no luck, it just asks for the full path parameter. Need to go and think about this for a while and work out why it is not looking at the [Day] field.

 

[vbaInet]

Forms("UpdateJourneys").OrderBy = "[[COLOR=Red]NameOfTable[/COLOR]].[Day] ASC"
    Forms("UpdateJourneys").OrderByOn = True

Amend the bit in red with the name of your table in which Day is a field.

NB: Remember to set the Filter On Load property of your form to Yes in design view. Change the name of DAY to something else. It is supposed to be a reserved name which would cause conflicts if you use it.

 

[vicissitude]

Happy Days! got it working, he he that took awhile! Thanks!

It did not like [Day], kept giving me a friday first for some reason, but then i tried it on [Date] instead and it worked perfectly. So just need to add in the filter query to the openform call and that is it done (for now!)

Many Thanks again!

 

[vicissitude]

Thanks! Worked like a charm.

'------------------------------------------------------------
' Timetable_TimetableAllDaysButton
'
'------------------------------------------------------------
Function Timetable_TimetableAllDaysButton()
On Error GoTo Timetable_TimetableAllDaysButton_Err

DoCmd.OpenForm "UpdateJourneys", acNormal, "Call UpdateJourneys Form From Timetable Button", "", , acNormal
Forms("UpdateJourneys").OrderBy = "[Service Journeys].[Date] ASC"
Forms("UpdateJourneys").OrderByOn = True


Timetable_TimetableAllDaysButton_Exit:
Exit Function

Timetable_TimetableAllDaysButton_Err:
MsgBox Error$
Resume Timetable_TimetableAllDaysButton_Exit

End Function

 

[vbaInet]

You're welcome. But I would ask that you review what you've written because I'm not convinced it's doing exactly what you expect.

Firstly, remember I mentioned about not using the word "Day" to name your fields, also do not use "Date". Here's a list of reserved keywords that you should never use:
http://allenbrowne.com/AppIssueBadWord.html

DoCmd.OpenForm "UpdateJourneys", acNormal, "Call UpdateJourneys Form From Timetable Button", "", , acNormal

The Filter argument isn't written like that. Look at the access help files. An example:
DoCmd.OpenForm "UpdateJourneys", acNormal, "[FieldID] = 20", "", , acNormal

Forms("UpdateJourneys").OrderBy = "[Service Journeys].[Date] ASC"

Rename your DATE field in your table to something else.

 

[vicissitude]

Yea i think i understand what you mean about the filter. I got this code from doing it as a macro first and then converting the macro into a module.

DoCmd.OpenForm "UpdateJourneys", acNormal, "Call UpdateJourneys Form From Timetable Button", "", , acNormal

The Query in red is used to filter surname, supplier and day, It has only those 3 fields. I could not get it to work without using a query, I tried just using code with a list of And's but am not that good with code so could not get it to work. There is the usual query under the "updatejourneys" form which i take it looks after the rest of the record.

Going to try and tidy up the code a bit and take out the reserved Keywords, hope that does not hurt too much! there is rather a lot of them!

Thanks.

 

[vbaInet]

Alright, I didn't know that was the name of a query. That's a rather long name for a query. Another thing is avoid using spaces when naming queries, tables, reports, macros, modules, or fields.

With regards renaming the reserved keywords, I would advise you do it on a backup copy first and after you've finished testing and all is working you can use that as your main db. Look out for the row source's of list/combo boxes if you've used that field in there.

Good luck.