Sort records on Subform using hidden control

[sword856]

I have a form with a subform. The main form is for Jobs, and the
subform lists the crew for each job. Each crew position has a number
assigned to it, lowest being the highest "rank" in the crew. The name
and postions of each crew member are selected by unbound combo boxes
that list all personnel names and all possible positions. The
position combo box has two columns, one with position title and the
other with position number. The position number is column is hidden,
since that field is only used on my side and not the user's. The
bound PositionNumber box is hidden for this same reason. It is filled
in on the AfterUpdate event with the associated position number once
the user picks a position.
I also have an "Add New" button on the subform to jump the
selector to a new record on the subform (rec. navigators are
disabled). The subform is in continuous view so the user can scroll
through the crew at a glance.

1.) I need to order the records in the subform in ascending
PositionNumber order so that the crew leader is on top and so forth.

2.) When I press the Add New button, I would like the subform to
show the new record fields. As of right now, if there are too many
crew members for the subform space, the user would have to scroll down
to the new entry. Though it seems like a small thing, it could be
frustrating to someone not familiar with the form. I would like the
add new button to shift the view in the subform to the new record
fields. Maybe a command to scroll to the bottom of the list or some
such.

Any help on these would be greatly appreciated.
Thanks,
George

 

[Bob Quintal]

(e-mail address removed) wrote in

I have a form with a subform. The main form is for Jobs,
and the
subform lists the crew for each job. Each crew position has a
number assigned to it, lowest being the highest "rank" in the
crew. The name and postions of each crew member are selected
by unbound combo boxes that list all personnel names and all
possible positions. The position combo box has two columns,
one with position title and the other with position number.
The position number is column is hidden, since that field is
only used on my side and not the user's. The bound
PositionNumber box is hidden for this same reason. It is
filled in on the AfterUpdate event with the associated
position number once the user picks a position.
I also have an "Add New" button on the subform to jump
the
selector to a new record on the subform (rec. navigators are
disabled). The subform is in continuous view so the user can
scroll through the crew at a glance.

1.) I need to order the records in the subform in ascending
PositionNumber order so that the crew leader is on top and so
forth.

In the form you use as a subform are the following (amongst
others) .orderBy and OrderbyOn. Set the orderBy = PositionNumber
and OrderByOn true in the form's open event and in the form's
afterUpdate event. Setting the OrderByOn true will resort the
records.

2.) When I press the Add New button, I would like the
subform to show the new record fields. As of right now, if
there are too many crew members for the subform space, the
user would have to scroll down to the new entry. Though it
seems like a small thing, it could be frustrating to someone
not familiar with the form. I would like the add new button
to shift the view in the subform to the new record fields.
Maybe a command to scroll to the bottom of the list or some
such.

Strange, in that doing an .addnew Access should scroll the form
to the new one. What is the code in the OnClick event of the
form used as the subform?

Any help on these would be greatly appreciated.
Thanks,
George

 

[sword856]

(e-mail address removed) wrote in






In the form you use as a subform are the following (amongst
others) .orderBy and OrderbyOn. Set the orderBy = PositionNumber
and OrderByOn true in the form's open event and in the form's
afterUpdate event. Setting the OrderByOn true will resort the
records.


Strange, in that doing an .addnew Access should scroll the form
to the new one. What is the code in the OnClick event of the
form used as the subform?

Thanks for answering, Bob!

1.) I tried inserting code like you described, but I got a parameter
prompt like for a parameter query with the text "1". I'm not real
familiar with VBA, I can only do things that I've done before, this
not being one of them. I guess I need to know where I need to put the
code, and what to put. Do I build event when in main form/subform
view under 'Subform'? Or do I open the subform by itself and write in
the code? Is the code

Me.OrderBy = PositionNumber
Me.OrderByOn = True

Or more like
[Forms]![Crew Subform].OrderBy = PositionNumber
[Forms]![Crew Subform].OrderByOn = True

I'm just learning VBA, so you'll have to bear with me if you can. If
you could write explicitly the code and an explanation of why it is
the way it is, that would be greatly appreciated. That way I could do
something like it again in the future with more understanding and not
bog down this community as much.

What is the code in the OnClick event of the
form used as the subform?

Here it is:
Private Sub AddCrew_Click()
On Error GoTo Err_AddCrew_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddCrew_Click:
Exit Sub

Err_AddCrew_Click:
MsgBox Err.Description
Resume Exit_AddCrew_Click

End Sub

The button says "Add New" but is named AddCrew. Not sure why I did
that.
The thing is, it DOES scroll to the new crew record IF I open a
previously existing Job record on the main form using an "Edit an
Existing Record" combo box I have. After it loads the new job and
crew, clicking "Add New" shows the blank new record. I just need it
to do that when entering a Job for the first time.

Again, your help is much appreciated,
George

 

[Bob Quintal]

(e-mail address removed) wrote in

Thanks for answering, Bob!

1.) I tried inserting code like you described, but I got a
parameter prompt like for a parameter query with the text "1".

Donno where that comes from.....

I'm not real familiar with VBA, I can only do things that
I've done before, this not being one of them. I guess I need
to know where I need to put the code, and what to put. Do I
build event when in main form/subform view under 'Subform'?
Or do I open the subform by itself and write in the code? Is
the code

Me.OrderBy = PositionNumber
Me.OrderByOn = True

That should go in the subform itself, in hte onOpen event and in
the subform's AfterUpdate event.

Or more like
[Forms]![Crew Subform].OrderBy = PositionNumber
[Forms]![Crew Subform].OrderByOn = True

When the VB interpreter in Access sees me! it will translate the
Me! to [Forms]![Crew Subform] if you are actually in the Crew
Subform. If you are in form Arrghhh, Access will translate me!
to Forms![Arrghhh]. It's just simpler to write the me! than the
whole name, especially if you have lots of controls or
properties to manipulate, or the name is long.

I also think that Me! is clearer in that it states "The form you
are currently programming", as opposed to some other object.



If the code is in the class module for [Forms]![Crew Subform]

I'm just learning VBA, so you'll have to bear with me if you
can. If you could write explicitly the code and an
explanation of why it is the way it is, that would be greatly
appreciated. That way I could do something like it again in
the future with more understanding and not bog down this
community as much.


Here it is:
Private Sub AddCrew_Click()
On Error GoTo Err_AddCrew_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddCrew_Click:
Exit Sub

Err_AddCrew_Click:
MsgBox Err.Description
Resume Exit_AddCrew_Click

End Sub

The button says "Add New" but is named AddCrew. Not sure why
I did that.
The thing is, it DOES scroll to the new crew record IF I open
a previously existing Job record on the main form using an
"Edit an Existing Record" combo box I have. After it loads
the new job and crew, clicking "Add New" shows the blank new
record. I just need it to do that when entering a Job for the
first time.

Again, your help is much appreciated,
George

 

[sword856]

(e-mail address removed) wrote in

Thanks for answering, Bob!

1.) I tried inserting code like you described, but I got a
parameter prompt like for a parameter query with the text "1".

Donno where that comes from.....

I'm not real familiar with VBA, I can only do things that
I've done before, this not being one of them. I guess I need
to know where I need to put the code, and what to put. Do I
build event when in main form/subform view under 'Subform'?
Or do I open the subform by itself and write in the code? Is
the code

Me.OrderBy = PositionNumber
Me.OrderByOn = True

That should go in the subform itself, in hte onOpen event and in
the subform's AfterUpdate event.

Or more like
[Forms]![Crew Subform].OrderBy = PositionNumber
[Forms]![Crew Subform].OrderByOn = True

When the VB interpreter in Access sees me! it will translate the
Me! to [Forms]![Crew Subform] if you are actually in the Crew
Subform. If you are in form Arrghhh, Access will translate me!
to Forms![Arrghhh]. It's just simpler to write the me! than the
whole name, especially if you have lots of controls or
properties to manipulate, or the name is long.

I also think that Me! is clearer in that it states "The form you
are currently programming", as opposed to some other object.

If the code is in the class module for [Forms]![Crew Subform]

I'm just learning VBA, so you'll have to bear with me if you
can. If you could write explicitly the code and an
explanation of why it is the way it is, that would be greatly
appreciated. That way I could do something like it again in
the future with more understanding and not bog down this
community as much.

Here it is:
Private Sub AddCrew_Click()
On Error GoTo Err_AddCrew_Click

DoCmd.GoToRecord , , acNewRec

Exit_AddCrew_Click:
Exit Sub

Err_AddCrew_Click:
MsgBox Err.Description
Resume Exit_AddCrew_Click

End Sub

The button says "Add New" but is named AddCrew. Not sure why
I did that.
The thing is, it DOES scroll to the new crew record IF I open
a previously existing Job record on the main form using an
"Edit an Existing Record" combo box I have. After it loads
the new job and crew, clicking "Add New" shows the blank new
record. I just need it to do that when entering a Job for the
first time.

Again, your help is much appreciated,
George

Thanks for clarifying that about the Me!s and such, Bob.

This is really strange. I made sure to put the code in the subform by
opening the subform outside the main form and building the events
there. In Open and AfterUpdate I put:
Me!OrderBy = PositionNumber
Me!OrderByOn = True

I got back this error message: "Microsoft Office Access cannot find
the field 'OrderBy' referred to in your expression."

When I am in main form with subform design view and right click on the
border of the subform and open properties, the only events listed are
"On Enter" and "On Exit". I have to click the empty space below the
subform footer to get to "On Open" and "After Update". I put the
above code in the subform "Build Event" after right-clicking on the
border of the subform (and only seeing Enter and Exit). I just
manually typed in "Open" and "AfterUpdate". It didnt affect
anything. I have seen the OrderBy code seen elsewhere on these
forums, and I don't understand why it isn't working. Can you think of
anything that may be the cause of this problem that maybe I didn't
mention, like another, previously unmentioned, property of the
PositionNumber box, or something?

Thanks,
George

 

[Bob Quintal]

(e-mail address removed) wrote in

Thanks for clarifying that about the Me!s and such, Bob.

This is really strange. I made sure to put the code in the
subform by opening the subform outside the main form and
building the events there. In Open and AfterUpdate I put:
Me!OrderBy = PositionNumber
Me!OrderByOn = True

I got back this error message: "Microsoft Office Access
cannot find the field 'OrderBy' referred to in your
expression."

The ! should be a dot(.) not a bang(!)
Me.OrderBy = PositionNumber
Me.OrderByOn = True

When I am in main form with subform design view and right
click on the border of the subform and open properties, the
only events listed are "On Enter" and "On Exit".

The object hierarchy starts from the parent form, to the subform
object to the form which is used as a subform.
Think wall, picture frame on wall, picture in frame.

The on enter and on exit properties are for the frame.
When in design view, double-click inside the frame and the form
used as subform will open,


I have to

click the empty space below the subform footer to get to "On
Open" and "After Update".

Those are probably properties of the parent form.

I put the above code in the subform

"Build Event" after right-clicking on the border of the
subform (and only seeing Enter and Exit). I just manually
typed in "Open" and "AfterUpdate". It didnt affect anything.
I have seen the OrderBy code seen elsewhere on these forums,
and I don't understand why it isn't working. Can you think of
anything that may be the cause of this problem that maybe I
didn't mention, like another, previously unmentioned, property
of the PositionNumber box, or something?

Thanks,
George

I think it's just a matter of getting your bearings,
understanding the hiearchy.

 

[sword856]

(e-mail address removed) wrote in








The ! should be a dot(.) not a bang(!)
Me.OrderBy = PositionNumber
Me.OrderByOn = True




The object hierarchy starts from the parent form, to the subform
object to the form which is used as a subform.
Think wall, picture frame on wall, picture in frame.

The on enter and on exit properties are for the frame.
When in design view, double-click inside the frame and the form
used as subform will open,

I have to


Those are probably properties of the parent form.

I put the above code in the subform



I think it's just a matter of getting your bearings,
understanding the hiearchy.

Now I've run into that problem again. I put

Me.OrderyBy = PositionNumber
Me.OrderByOn = True

in the on open and afterupdate events. When I opened the form, I got
that parameter prompt box with the text"1". When I go to properties
and check the "Order By" property, there is a "[1]". When I debug it,
by holding the cursor over the Me.OrderBy = PositionNumber, it shows
"Me.OrderBy = [1]". This must be where the prompt is coming from

Ok, so i got it to work like this: place PositionNumber in the
properties of the form instead of in the code. I left the "OrderByOn"
set =true in the code, however. It works now, and there is no code to
set OrderBy to postion number.

So no solution to 2.)?

 

[Bob Quintal]

(e-mail address removed) wrote in

(e-mail address removed) wrote
in








The ! should be a dot(.) not a bang(!)
Me.OrderBy = PositionNumber
Me.OrderByOn = True




The object hierarchy starts from the parent form, to the
subform object to the form which is used as a subform.
Think wall, picture frame on wall, picture in frame.

The on enter and on exit properties are for the frame.
When in design view, double-click inside the frame and the
form used as subform will open,

I have to


Those are probably properties of the parent form.

I put the above code in the subform



I think it's just a matter of getting your bearings,
understanding the hiearchy.

--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

Now I've run into that problem again. I put

Me.OrderyBy = PositionNumber
Me.OrderByOn = True

in the on open and afterupdate events. When I opened the
form, I got that parameter prompt box with the text"1". When
I go to properties and check the "Order By" property, there is
a "[1]". When I debug it, by holding the cursor over the
Me.OrderBy = PositionNumber, it shows "Me.OrderBy = [1]".
This must be where the prompt is coming from

Ok, so i got it to work like this: place PositionNumber in
the properties of the form instead of in the code. I left the
"OrderByOn" set =true in the code, however. It works now, and
there is no code to set OrderBy to postion number.

So no solution to 2.)?

I don't remember what was 2?

 

[Bob Quintal]

(e-mail address removed) wrote in

(e-mail address removed) wrote
in

On Jul 13, 6:09 pm, Bob Quintal <[email protected]>
wrote:
(e-mail address removed) wrote
(e-mail address removed)
m:

On Jul 13, 3:14 pm, Bob Quintal <[email protected]>
wrote:
The button says "Add New" but is named AddCrew. Not
sure why I did that.
The thing is, it DOES scroll to the new crew record
IF I open a previously existing Job record on the
main form using an "Edit an Existing Record" combo
box I have. After it loads the new job and crew,
clicking "Add New" shows the blank new record. I
just need it to do that when entering a Job for the
first time.

Thanks for clarifying that about the Me!s and such, Bob.

This is really strange. I made sure to put the code in
the subform by opening the subform outside the main form
and building the events there. In Open and AfterUpdate
I put:
Me!OrderBy = PositionNumber
Me!OrderByOn = True

I got back this error message: "Microsoft Office Access
cannot find the field 'OrderBy' referred to in your
expression."

The ! should be a dot(.) not a bang(!)
Me.OrderBy = PositionNumber
Me.OrderByOn = True

When I am in main form with subform design view and
right click on the border of the subform and open
properties, the only events listed are "On Enter" and
"On Exit".

The object hierarchy starts from the parent form, to the
subform object to the form which is used as a subform.
Think wall, picture frame on wall, picture in frame.

The on enter and on exit properties are for the frame.
When in design view, double-click inside the frame and the
form used as subform will open,

I have to

click the empty space below the subform footer to get to
"On Open" and "After Update".

Those are probably properties of the parent form.

I put the above code in the subform

"Build Event" after right-clicking on the border of the
subform (and only seeing Enter and Exit). I just
manually typed in "Open" and "AfterUpdate". It didnt
affect anything. I have seen the OrderBy code seen
elsewhere on these forums, and I don't understand why it
isn't working. Can you think of anything that may be the
cause of this problem that maybe I didn't mention, like
another, previously unmentioned, property of the
PositionNumber box, or something?

I think it's just a matter of getting your bearings,
understanding the hiearchy.

PA is y I've altered my email address.

- Show quoted text -

Now I've run into that problem again. I put

Me.OrderyBy = PositionNumber
Me.OrderByOn = True

in the on open and afterupdate events. When I opened the
form, I got that parameter prompt box with the text"1".
When I go to properties and check the "Order By" property,
there is a "[1]". When I debug it, by holding the cursor
over the Me.OrderBy = PositionNumber, it shows "Me.OrderBy
= [1]". This must be where the prompt is coming from

Ok, so i got it to work like this: place PositionNumber in
the properties of the form instead of in the code. I left
the "OrderByOn" set =true in the code, however. It works
now, and there is no code to set OrderBy to postion number.

So no solution to 2.)?

I don't remember what was 2?

--
Bob Quintal

PA is y I've altered my email address.

--
-
Hide quoted text -

- Show quoted text -

Thanks for asking, Bob.

2.) When I press the Add New button, I would like the
subform to show the new record fields. As of right now, if
there are too many crew members for the subform space, the
user would have to scroll down
to the new entry. Though it seems like a small thing, it
could be frustrating to someone not familiar with the form. I
would like the add new button to shift the view in the subform
to the new record fields. Maybe a command to scroll to the
bottom of the list or some such.


That was 2.). But I worked around it by resizing the subform
in such a way that it has to show the new record when you hit
the button. Thanks for all your help!

Funny, when I do an addnew, (docmd.gotorecord acNewRec )
all my subforms scroll up to display the new record.

 

[sword856]

(e-mail address removed) wrote in

Now I've run into that problem again. I put

Me.OrderyBy = PositionNumber
Me.OrderByOn = True

in the on open and afterupdate events. When I opened the
form, I got that parameter prompt box with the text"1". When
I go to properties and check the "Order By" property, there is
a "[1]". When I debug it, by holding the cursor over the
Me.OrderBy = PositionNumber, it shows "Me.OrderBy = [1]".
This must be where the prompt is coming from

Ok, so i got it to work like this: place PositionNumber in
the properties of the form instead of in the code. I left the
"OrderByOn" set =true in the code, however. It works now, and
there is no code to set OrderBy to postion number.

So no solution to 2.)?

I don't remember what was 2?

Thanks for asking, Bob.

2.) When I press the Add New button, I would like the subform to
show the new record fields. As of right now, if there are too many
crew members for the subform space, the user would have to scroll
down
to the new entry. Though it seems like a small thing, it could be
frustrating to someone not familiar with the form. I would like the
add new button to shift the view in the subform to the new record
fields. Maybe a command to scroll to the bottom of the list or some
such.


That was 2.). But I worked around it by resizing the subform in such
a way that it has to show the new record when you hit the button.
Thanks for all your help!