Full Version: Record Order For Nested Forms
UtterAccess Forums > Microsoft® Access > Access Forms
New Project: Inspections DB, We need something better than index cards!
The Inspector inspects for code violations and keeps track of occupants and thier inspections.
Locations have occupants that change regularly and those occupants often require multiple inspections.
I have a main form (tblLocation), subform (tblOccupant), sub-subform (tblInspection).
I want the location form to load by tblLocation.TaxPIN, the subform by last tblOccupant.idGISoccupant and the sub-subform by tblInspection.InspID
Question: Sort order vs order by, what's the difference?
I want the main forms records to load in a sorted fashion, so as I navigate the records #1 is first and #999 is last.
And I want the last occupant to be displayed first and that occupant's last inspection to be displayed first.
But I want the sort order to remain sort order to remain first to last so when I use the navigation tools, the left arrow goes to the previous occupant...
All help is greatly appreciated.
TIA, Phil
Daryl S
Sort Order is a property of reports, where OrderBy can be used on forms and reports. You can also use ORDER BY in a query that could be the record source for a form or report. If you use OrderBy, then you need to make sure the OrderByOn property of the form or report is set to True. Any of the order statements can take DESC at the end to indicate descending sort order, rather than the default ascending.
oes that help?
- Daryl
That helps a little, thanks.
o I clicked on the TaxPIN control in the form and sorted A-Z, in the Order By property I have [tblGISlocations].[TaxPIN] and Yes in Order by On Load. That works fine.
How do I get the Sub and Sub-sub forms to show thier last record for each location?
Thanks, Phil
You're going to need some way to tell which occupant record is "last" and which inspection record is "last". I am guessing that the inspection record would have a date indicating when the inspection was carried out. Does it also have a link to its corresponding occupant record? It does not have to be a direct link, it can be an indirect link, using a "join" table.
Daryl S
You can set the OrderBy property of the subforms (and make sure OrderbyOn is true), or you can look at the recordsource property for the subforms, and sort them there. If you want the latest first, put DESC after the field name in the OrderBy. If you are sorting at the recordsource, you will click on the RecordSource property of the subform, and then on the elipsis (...) to the right of the record source. If your record source is a query, you just choose Ascending or Descending in the sort row of the grid below the column you want to sort by. If your record source is a table, then this will invoke query builder on the table, where you can add all the fields, and then choose Ascending or Descending in the sort row below the column you want to sort by. You do not save this query independently, but when you close the query, it will ask if you want to save it as the record source, and that is how it gets set for the form.
oes that help?
- Daryl
Sorry, I'm having trouble.
For the Sub and Sub-sub I want the Sort Order to be A-Z, but I want to show the Last record when it is loaded, like "On Load: Show Last".
For the Main form the sort order is A-Z and shows the first record when loaded, that's working.
When I do the Desending order for the Sub and Sub-sub, it loads the last record but the navigation is backwards, I click the right arrow and instead giving me a new record, it shows the older record and then the next older... and finally shows a new record. Not the way I want it to work. I should be able to click the right arrow to a new record and the left arrow gives me the next oldest....
Sorry for being hard headed and simple minded.
THANKS, for the help.
Daryl S
Sorry - it might have been how I interpreted 'last' record. It sounds like you still want the subform sorted ascending, but you want to have the last record as the current one. You can use the On Current event for each of the subforms to do what you want. Use a Dummy Macro and set it to GoToRecord Last. Keep it sorted ascending.
Is that what you needed?
- Daryl
Yes, you got it.
id the macro, it works for the initial load when the form comes up, but when I go to the next location record it is not giving me that locations last occupant.
Maybe "On Load" is not the right place for the macro. I'll try others, On Current...?
All help is greatly appreciated!
Thanks, Phil
Daryl S
Use the On Current event for each subform to move to the last record on that subform.
verytime I try to change a record, it keeps going back the last record of the table.
Ocan't go next or previous.
Still thinking this sholud not be hard, just got to find the magic bullet.
Thanks, Phil
Daryl S
Actually you want this to run on the parent record's On Current event. So you open the main Location form, and that is sorted and works as you want it to. When you click on a location on the main form, the subform works and is sorted the way you want, but you want to add the piece to go to the last record (Occupant) in the subform. But you only want this to happen when you click on the location, not while you are working within the Occupants subform. So, in the parent's OnCurrent event, instead of a dummy macro, you will use an Event Procedure, and add code like this (but use your subform name):
Private Sub Form_Current()
' Go to subform, and make the last record in the subform the current one
DoCmd.GoToRecord , , acLast
End Sub
Remember to remove the dummy macro from the subform. Test this out and see if it works. Then move down to the subform and sub-subform.
- Daryl
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.