Multiple records in subform?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I want to display multiple records in form veiw in my subform. How might I
do that?

Thanks,

Dustin
 
Dustin said:
I want to display multiple records in form veiw in my subform. How might I
do that?
Click to expand...


Since form view can only display a single set of bound
controls with values in the current record, you will either
need to use unbound controls or multiple subforms.

Using a bunch of unbound controls is a fairly advanced and
complex technique with relatively few benefits so I don't
revommend it without a lot of very good reasons.

Using multiple copies of the same subform reduces to just
the problem of arranging things for each subform control's
Link Master property. If you need help with this, I'll need
more information about how you determine which records to
display.

Are you sure you can not use a continuous form to achieve
the desired effect? About the only significant difference
is that continuous view doesn't provide header/footer
sections for each record and several properties can not be
set for one record and not for others.
 
I see now. Continous is what I wanted. But how do I order the records
according to date of entry. I would like to see it like this 7/26/07, then
down to 7/25/07 and so on. I figure I have to use the Order By row. But not
sure what to type.

Thanks Dustin
 
If at all possible you should use the form's record source
query to sort the records. If that's not feasible, the use:

Me.OrderBy = "[your date field] DESC"
Me.OrderByOn = True
 
Ok I did it by query and that does make more sense. Thanks, but now I need to
be able to edit a past record by clicking a Edit command button. I see that
in properties of the form that Allow Edits is already set to no and Data
Entry is set to no. So I tried to code the button to [FormName].Allow Edits =
True but it gave me this error. Run time error 424 object required.

What am I doing wrong?

Thanks for responding Dustin

Marshall Barton said:
If at all possible you should use the form's record source
query to sort the records. If that's not feasible, the use:

Me.OrderBy = "[your date field] DESC"
Me.OrderByOn = True
--
Marsh
MVP [MS Access]

I see now. Continous is what I wanted. But how do I order the records
according to date of entry. I would like to see it like this 7/26/07, then
down to 7/25/07 and so on. I figure I have to use the Order By row. But not
sure what to type.
Click to expand...
Click to expand...
 
A form name by itself is just an (undefined?) variable to
VBA. You need to specify what collection the name can be
resolved through (in this case the collection of open
forms):

Forms![Form Name].AllowEdits = True

Don't forget to replace Form Name by the actual name of the
form you want to manipulate.

If the code is in the same form's module, then you should
just refer to the code's form object:

Me.AllowEdits = True
--
Marsh
MVP [MS Access]

Ok I did it by query and that does make more sense. Thanks, but now I need to
be able to edit a past record by clicking a Edit command button. I see that
in properties of the form that Allow Edits is already set to no and Data
Entry is set to no. So I tried to code the button to [FormName].Allow Edits =
True but it gave me this error. Run time error 424 object required.


Marshall Barton said:
If at all possible you should use the form's record source
query to sort the records. If that's not feasible, the use:

Me.OrderBy = "[your date field] DESC"
Me.OrderByOn = True

I see now. Continous is what I wanted. But how do I order the records
according to date of entry. I would like to see it like this 7/26/07, then
down to 7/25/07 and so on. I figure I have to use the Order By row. But not
sure what to type.


Dustin wrote:
I want to display multiple records in form veiw in my subform. How might I
do that?


:
Since form view can only display a single set of bound
controls with values in the current record, you will either
need to use unbound controls or multiple subforms.

Using a bunch of unbound controls is a fairly advanced and
complex technique with relatively few benefits so I don't
revommend it without a lot of very good reasons.

Using multiple copies of the same subform reduces to just
the problem of arranging things for each subform control's
Link Master property. If you need help with this, I'll need
more information about how you determine which records to
display.

Are you sure you can not use a continuous form to achieve
the desired effect? About the only significant difference
is that continuous view doesn't provide header/footer
sections for each record and several properties can not be
set for one record and not for others.
Click to expand...
Click to expand...
Click to expand...
 
Thanks that worked. I am using the second code you gave me
Me.AllowEdits=True. The Edit control button is seen in each record
(continuous subform). How do I get it to unlock just the record I am in?
Because right now it allows edits in all the records of the subform after I
click the button.

On another note I have another problem. A subform called "FollowUpSubform"
not linked to the main form is based on a query that selects when "follow up"
is selected in the CallTrackerSubForm, above. In the FollowUpSubform, I want
to filter from a combo box the people who took the calls. And just show the
follow ups they are recorded to do. How can I do this if continous can't have
a form header?

Thanks Dustin

Marshall Barton said:
A form name by itself is just an (undefined?) variable to
VBA. You need to specify what collection the name can be
resolved through (in this case the collection of open
forms):

Forms![Form Name].AllowEdits = True

Don't forget to replace Form Name by the actual name of the
form you want to manipulate.

If the code is in the same form's module, then you should
just refer to the code's form object:

Me.AllowEdits = True
--
Marsh
MVP [MS Access]

Ok I did it by query and that does make more sense. Thanks, but now I need to
be able to edit a past record by clicking a Edit command button. I see that
in properties of the form that Allow Edits is already set to no and Data
Entry is set to no. So I tried to code the button to [FormName].Allow Edits =
True but it gave me this error. Run time error 424 object required.


Marshall Barton said:
If at all possible you should use the form's record source
query to sort the records. If that's not feasible, the use:

Me.OrderBy = "[your date field] DESC"
Me.OrderByOn = True


Dustin wrote:
I see now. Continous is what I wanted. But how do I order the records
according to date of entry. I would like to see it like this 7/26/07, then
down to 7/25/07 and so on. I figure I have to use the Order By row. But not
sure what to type.


Dustin wrote:
I want to display multiple records in form veiw in my subform. How might I
do that?


:
Since form view can only display a single set of bound
controls with values in the current record, you will either
need to use unbound controls or multiple subforms.

Using a bunch of unbound controls is a fairly advanced and
complex technique with relatively few benefits so I don't
revommend it without a lot of very good reasons.

Using multiple copies of the same subform reduces to just
the problem of arranging things for each subform control's
Link Master property. If you need help with this, I'll need
more information about how you determine which records to
display.

Are you sure you can not use a continuous form to achieve
the desired effect? About the only significant difference
is that continuous view doesn't provide header/footer
sections for each record and several properties can not be
set for one record and not for others.
Click to expand...
Click to expand...
Click to expand...
 
If you want to disallow edits to a record until the user
clicks on the "edit" button, then use the form's Current
event:
Me.AllowEdits = False

You must have misunderstood something. Continuous subform
can have a header/footer section. It's Datasheet forms that
do not display their header/footer sections.
--
Marsh
MVP [MS Access]

Thanks that worked. I am using the second code you gave me
Me.AllowEdits=True. The Edit control button is seen in each record
(continuous subform). How do I get it to unlock just the record I am in?
Because right now it allows edits in all the records of the subform after I
click the button.

On another note I have another problem. A subform called "FollowUpSubform"
not linked to the main form is based on a query that selects when "follow up"
is selected in the CallTrackerSubForm, above. In the FollowUpSubform, I want
to filter from a combo box the people who took the calls. And just show the
follow ups they are recorded to do. How can I do this if continous can't have
a form header?

Marshall Barton said:
A form name by itself is just an (undefined?) variable to
VBA. You need to specify what collection the name can be
resolved through (in this case the collection of open
forms):

Forms![Form Name].AllowEdits = True

Don't forget to replace Form Name by the actual name of the
form you want to manipulate.

If the code is in the same form's module, then you should
just refer to the code's form object:

Me.AllowEdits = True

Ok I did it by query and that does make more sense. Thanks, but now I need to
be able to edit a past record by clicking a Edit command button. I see that
in properties of the form that Allow Edits is already set to no and Data
Entry is set to no. So I tried to code the button to [FormName].Allow Edits =
True but it gave me this error. Run time error 424 object required.
Click to expand...
Click to expand...
Click to expand...
 
Yep I did misunderstand. Thanks for that. But now how do I use a combo box as
a filter? I want the employee to be able to select his or her name from the
combo box and filter all the followups calls that they must make. I think I
would have to event the after update of the combo box. But how would I type
that event in VB?

Thanks so much for your help

Dustin

Marshall Barton said:
If you want to disallow edits to a record until the user
clicks on the "edit" button, then use the form's Current
event:
Me.AllowEdits = False

You must have misunderstood something. Continuous subform
can have a header/footer section. It's Datasheet forms that
do not display their header/footer sections.
--
Marsh
MVP [MS Access]

Thanks that worked. I am using the second code you gave me
Me.AllowEdits=True. The Edit control button is seen in each record
(continuous subform). How do I get it to unlock just the record I am in?
Because right now it allows edits in all the records of the subform after I
click the button.

On another note I have another problem. A subform called "FollowUpSubform"
not linked to the main form is based on a query that selects when "follow up"
is selected in the CallTrackerSubForm, above. In the FollowUpSubform, I want
to filter from a combo box the people who took the calls. And just show the
follow ups they are recorded to do. How can I do this if continous can't have
a form header?

Marshall Barton said:
A form name by itself is just an (undefined?) variable to
VBA. You need to specify what collection the name can be
resolved through (in this case the collection of open
forms):

Forms![Form Name].AllowEdits = True

Don't forget to replace Form Name by the actual name of the
form you want to manipulate.

If the code is in the same form's module, then you should
just refer to the code's form object:

Me.AllowEdits = True


Dustin wrote:
Ok I did it by query and that does make more sense. Thanks, but now I need to
be able to edit a past record by clicking a Edit command button. I see that
in properties of the form that Allow Edits is already set to no and Data
Entry is set to no. So I tried to code the button to [FormName].Allow Edits =
True but it gave me this error. Run time error 424 object required.
Click to expand...
Click to expand...
Click to expand...
 
Dustin said:
But now how do I use a combo box as
a filter? I want the employee to be able
to select his or her name from the
combo box and filter all the followups
calls that they must make. I think I
would have to event the after update of
the combo box. But how would I type
that event in VB?
Click to expand...


If you are not using the Filter property in the main or
other subforms, the easiest way is:

Me.Filter = "[emp name] = """ & Me.thecombo & """"
Me.FilterOn = True

Another way is to construct the record source SQL statement
with the above in the WHERE clause.
 
That worked thanks.

But back to my edit button control. I have the form set to not Allow Edits
so it is not allowing me to use my combo box searches until I click the edit
control button. How do I get the VB to lock the form execpt the combo
searches and the subforms?

Thanks Dustin

Marshall Barton said:
Dustin said:
But now how do I use a combo box as
a filter? I want the employee to be able
to select his or her name from the
combo box and filter all the followups
calls that they must make. I think I
would have to event the after update of
the combo box. But how would I type
that event in VB?
Click to expand...


If you are not using the Filter property in the main or
other subforms, the easiest way is:

Me.Filter = "[emp name] = """ & Me.thecombo & """"
Me.FilterOn = True

Another way is to construct the record source SQL statement
with the above in the WHERE clause.
Click to expand...
 
Dustin said:
But back to my edit button control. I have the form set to not Allow Edits
so it is not allowing me to use my combo box searches until I click the edit
control button. How do I get the VB to lock the form execpt the combo
searches and the subforms?
Click to expand...


To selectively prevent editing in some controls, then you
can not use AllowEdits to lock every control. Instead you
need to lock (or disable) individual controls. An easy way
to do this is to set the control's Tag property to a
specific string such as LOCK. Then create a sub procedure
to set the property of the controls with that Tag. E.g.

Sub GroupLock(GroupID As String, OnOff As Boolean)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag Like GroupID Then
ctl.Lock = OnOff
End If
Next ctl
End Sub

Then you can call the procedure from the form's Current
event to lock the controls that you want locked:
GroupLock "LOCK", True
and your edit button can unlock them with:
GroupLock "LOCK", False
 
Hey Marshall,

I tried to do what you told me and the debugger didn't like this line,
ctl.Lock OnOff.

Also I understand what the end result of this code is going to be. But I
would like to understand what each part of the code is doing. So I can start
learning this language.

Thanks so much Dustin
 
You miscopied that line, there's supposed to be an = in
there.

OTOH, I only partially generalized the code for multiple
groups. It won't make any difference in your specific
question, but I meant to write:
If ctl.Tag Like "*" & GroupID & "*" Then

The procedure just loops through all the controls on the
form looking for ones that have LOCK in its Tag property.
When such a control is found, it either locks or unlocks it
depending on the value in the OnOff argument.

The procedure could be done more quickly (if the form has a
lot of controls) by populating a private collection in the
form's Load event. The procedure can then loop through the
controls in the private collection without wasting time
checking all the controls. Just log this last bit in the
back of your mind in case you notice a delay in when
navigating in the form.
 
I see. Thanks.

My form is getting long and I was wondering if it is possible to put command
buttons on my form header that when clicked will take me to a certain area of
the form. Is this possible?

Thanks for all you help

Dustin

Marshall Barton said:
You miscopied that line, there's supposed to be an = in
there.

OTOH, I only partially generalized the code for multiple
groups. It won't make any difference in your specific
question, but I meant to write:
If ctl.Tag Like "*" & GroupID & "*" Then

The procedure just loops through all the controls on the
form looking for ones that have LOCK in its Tag property.
When such a control is found, it either locks or unlocks it
depending on the value in the OnOff argument.

The procedure could be done more quickly (if the form has a
lot of controls) by populating a private collection in the
form's Load event. The procedure can then loop through the
controls in the private collection without wasting time
checking all the controls. Just log this last bit in the
back of your mind in case you notice a delay in when
navigating in the form.
--
Marsh
MVP [MS Access]

I tried to do what you told me and the debugger didn't like this line,
ctl.Lock OnOff.

Also I understand what the end result of this code is going to be. But I
would like to understand what each part of the code is doing. So I can start
learning this language.
Click to expand...
Click to expand...
 
Without seeing your form, I would guess that you can do that
by using a line of code that just sets the focus to the
control you want to make active.

OTOH, what I think you are describing sounds a lot like you
should be using a Tab control to manage the "areas" of the
form.
 
Ok, I've used them before but never thought about that for some reason.
Thanks for the reminder.

I want to use a toggle button to identify that the user needs to call the
customer back. And then when after he does he can click it off. I see that I
can add a toggle button in the forms design view but how do I connect it to
the table and what Data Type do I use in the table?

Thanks Dustin

Marshall Barton said:
Without seeing your form, I would guess that you can do that
by using a line of code that just sets the focus to the
control you want to make active.

OTOH, what I think you are describing sounds a lot like you
should be using a Tab control to manage the "areas" of the
form.
--
Marsh
MVP [MS Access]

I see. Thanks.

My form is getting long and I was wondering if it is possible to put command
buttons on my form header that when clicked will take me to a certain area of
the form. Is this possible?
Click to expand...
Click to expand...
 
Dustin said:
I want to use a toggle button to identify that the user needs to call the
customer back. And then when after he does he can click it off. I see that I
can add a toggle button in the forms design view but how do I connect it to
the table and what Data Type do I use in the table?
Click to expand...


An individual toggle button (not a member of an Option
Group) can be bound to a field in the form's record source.
Normally the field would be a YesNo type field.
 
I guess the toggle button is not what I want after trying it. Are you
suggesting that I use option buttons? I need something that that user can
click to say that he needs to call back that customer. And thengo to a
subform that is based on a query that shows the calls that need to be
returned. I want to be able to click the button again in the Call Back
subform to take it off the query. Any suggestions?

Thanks Dustin
 
You haven't provided any information about why a toggle
button is not what you want and I have no idea what you do
want.

It's possible that we're struggling with terminology
confusion. A toggle button, check box or radio button each
display a YesNo value in their own unique way, but their
functionality is the same. An Option Group can contain
several of those controls, but the functionality is
comlpetely different.

Beyond that I am having trouble visualizing what you are
asking about now.

Note that I may not be the best person to deal with your
current question. The general guideline is to confine a
thread to a single topic instead of jumping around with
every issue you are trying to trying to resolve, especially
when you are asking for UI design suggestions.
 
Back
Top