Order by property and OrderByOn

[Guest]

I want to have my form set to sort by a particular field each time it opens.
My reference book says to use Order by property in the table and
OrderByProperty in the form.
Problem is book doesn't say how to use the Order by property and I can't
even find the OrderByOn property for the form. Please advise. Thanks.

 

[Rick Brandt]

I want to have my form set to sort by a particular field each time it
opens. My reference book says to use Order by property in the table
and OrderByProperty in the form.
Problem is book doesn't say how to use the Order by property and I
can't even find the OrderByOn property for the form. Please advise.
Thanks.

The OrderByOn property is not contained in the property sheet. It can only
be set using VBA code. You woud have the following in the form's Open
event.

Me.OrderBy = "SomeFieldName"
Me.OrderByOn = True

You could also simply bind the form to a query with the desired sort order.

 

[Ed Robichaud]

One of the simpler methods is to create a query that sorts your data however
you like, then set the record source of your form to that query.
-Ed

 

[Guest]

Thanks, I've tried that now and it didn't work. There is already another
instruction in the On Open event. I just added the two lines you specified
under the other. Is there something I need to do to separate the code
instructinos? Also, in the underlying tables, I put [specified field] in the
Order by property.
I have not been able to make this form work when I base it on a query. I
can't remember why. I'll try again. But anything further on the above will
be appreciated.

 

[Guest]

I've not been able to make a query work.
The form is now based on a TableA that is made up of underlying tables that
have no related fields:
TableA contains a table of student names and demographics, a table of
teacher names, and several other tables, again, that have no related fields.
It has worked best to build the form on TableA.
When I try to build a query on TableA, the query returns a record for (what
appears to be) every combination of fields. As a newbie, I'm not familiar
with the different types of queries. I'm confused when I add the tables in
Query Design view, that there's no way to associate the tables and thus I
can't control the join properties.

Another point I've forgot to mention before is that the tables, regardless
of what I do in the Order by property and the OrderByOn code, are being
sorted on a numeric field in the TableA.

 

[BruceM]

You have lost me (and probably quite a few others as well) when you said
that the table is made up of other tables. What do you mean by that? A
table could have been created by combining two or more tables, but a table
is a container for data, not for other tables. It would help if you list
your table fields and relationships, and include a brief general description
of your database's purpose. You would probably do better if you created the
realtionships before trying to create the query. Table relationships will
be carried through to any queries based on those tables.
If you cannot get a query to work, that issue is worth the effort of
addressing. Attempting to work around something that should not have been a
problem in the first place is likely to lead you nowhere.

 

[Guest]

My objective is to create a behavior incident reporting and tracking database
for the school in which I work. Staff members will input descriptions of
incidents as they come up. The first form is for staff input. Another form
which is built on the same table as the first but with added fields, will be
used by administrators and detention room folks who deal with each incident.
The Tables are composed of:
Table 1) Student names with with home address, phone, etc.
Table 2) Staff names, which includes all staff in our school.
Table 3) Incident description Table 4) General time of day (during class,
during lunch, between classes, etc., and Table 5) a list of consequences
applied by staff
As I started this task, I noticed that there were no common fields in
these tables upon which to build relationships. So my efforts to build
queries have not worked.
Instead, I built another table, the TableA referred to earlier which is
otherwise called tbStuldent_Behavior_Log. This table is built the
"underlying tables", i.e., contains fields from first five tables. In this
table, the unrelated data that would describe a single behavior incident is
combined together in records. The records are created by staff members and
stored in tbStuldent_Behavior_Log.
I've not seen how to accomplish that same thing using queries.
A NEW observation is that when Table 1 mentioned above (students with
addresses) includes the 5 digit student ID number, that field overrides any
attempt I'm made based on previous suggestions, to sort by a different field.
When I went back to the Table 1 and deleted the student ID field, the sort
order remained as imported, which is ascending by student last name. But
when the student ID field is present, Access sorts by it. Problem is, while
not totally necessary, I'd like to have the student ID field in the table.
While I'm sure there are more elaborate approaches to this goal, as a
newbie, I can't think of any other way to do it and I don't see the
forthcoming hidden problem with this approach.
I do appreciate all you the patience and will appreciate any further
comment/direction.

 

[BruceM]

First, do not attempt to sort a table for anything other than temporary
convenience, especially during the design process. The sort order will not
"stick". I prefer to use queries to organize data. Make a query based on
the table, select a field, and choose Ascending or Descending for Sort.
Above all, DO NOT make tables that duplicate information in other tables.
Use queries to combine tables, but I don't think that is quite what you
need. You may be stuck in thinking of Access almost as an elaborate
spreadsheet. Relational databases are entirely different from spreadsheets,
and must be approached differently.
Second, relationships are built based on fields that you add for the
specific purpose of creating relationships. You are on track with a
separate table for Students, Staff, and Incidents. A simple version of the
database may use table design something like the following:

tblStudent
StudentID (primary key, or PK)
LastName
FirstName
etc.

tblStaff
StaffID (PK)
LastName
FristName
etc.

tblIncident
IncidentID (PK)
StudentID (foreign key, or FK)
StaffID (FK)
IncidentDate
TimeOfDay
IncidentDescription

tblConsequences
ConsequenceID (PK)
IncidentID (FK)
ConsequenceDescription

The PK is assigned in Table design view. The FK is a field of the same type
as the corresponding PK. For instance, if StudentID in tblStudents is a
Number field, StudentID in tblIncident must be a Number field. If the PK is
autonumber, the FK can only be Number. The FK is established by its
relationship to a PK field. Establish relationships between the PK fields
and the corresponding FK fields (the ones with the same names as the PK
fields). See Help for more on relationships. Be sure to click Enforce
Referential Integrity.
I am assuming that a single student can have multiple incidents, that each
staff member may be involved with multiple incidents involving several
students, and that each incident may have several consequences. This would
be a many-to-many relationship, so tblIncident serves as a junction table to
resolve this relationship. Build a form (frmStudent) based on tblStudent,
another (fsubIncident) based on tblIncident, and another (fsubConsequence)
based on tblConsequences. The form fsubIncident would include a combo box
using tblStaff as the Row Source. In form design view, drag the icon for
fsubConsequences onto fsubIncident, then drag the icon for fsubIncident onto
frmStudents. Create an Incident for a student, then invent a Consequence.
Click the New Record arrow at the bottom of tblIncident, then add a couple
of consequences. Do the same with another student. Get familiar with how
this works. Remember that you do not need to populate the FK fields. They
will be populated with the PK from the related table. Probably best not to
have the FK fields on the forms.
Another possibility is that an Incident may involve more than one Student,
in which case you need to alter the design. Another table
(tblStudentIncident) would be added. It would contain StudentIncidentID as
the PK, and have IncidentID as the FK. The main table would be tblIncident,
and the main form frmIncident; the subform would be based on
tblStudentIncident. Students and Staff would be selected from combo boxes
on fsubStudentIncident, and a subform based on tblConsequences would be
added (same as before). Students and Staff would probably be selected from
combo boxes. Something like that.
What if the consequences are different for each Student in an incident
involving more than one? All of these problems are solvable, but you have
chosen a complex project on which to cut your teeth. Clarify the details
and the general approach you need. In specifying table fields, all that is
needed for explanation is something like I have suggested. It is not
necessary, for instance, to specify address fields and so forth for Students
and Staff.

 

[BruceM]

I think I remember you asking in another post about newsgroup etiquette. On
that topic, it is good form to acknowledge responses, even if you cannot use
them. I for one will not bother to answer your posts in the future.