I have two unbound combos on a form that allow the user to change the
size of a sub form. Some users' computers are set at different
resolutions and I want to allow the user to maximize the size of a
rather wide sub as much as possible.

It all works wonderfully.

But the catch, as you may have realized, is that they will have to
adjust the size every time they enter the form. Many may have it open
all day but they will find it annoying nonetheless.

I don't believe there is a way for the form to change the value on
close, right?—so that is out.

My next idea would be to save the values in a table on everyones'
frontends.

If that is the way to go, what would be the code to change the subform
size on open?

(I will create a form that only allows one record to fill in this
table. The form can have on it columns called "WidthEntryfrm" and
"HeightEntryfrm" and be called EntryFormSettingstbl.)

If there is a better solution, please let me know.

Matt

I just got done doing this

In the mainform, on open, use dlookup or the like to get the default size
for that user. Then change the width and height properties of the subform
control accordingly. This should happen fast enough that the user never sees
any evidence, and you can update the table that holds the current size for
that user on the mainform close, just in case they change their mind and set
it to something else.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"(E-Mail Removed)" wrote:

> I have two unbound combos on a form that allow the user to change the
> size of a sub form. Some users' computers are set at different
> resolutions and I want to allow the user to maximize the size of a
> rather wide sub as much as possible.
>
> It all works wonderfully.
>
> But the catch, as you may have realized, is that they will have to
> adjust the size every time they enter the form. Many may have it open
> all day but they will find it annoying nonetheless.
>
> I don't believe there is a way for the form to change the value on
> close, right?—so that is out.
>
> My next idea would be to save the values in a table on everyones'
> frontends.
>
> If that is the way to go, what would be the code to change the subform
> size on open?
>
> (I will create a form that only allows one record to fill in this
> table. The form can have on it columns called "WidthEntryfrm" and
> "HeightEntryfrm" and be called EntryFormSettingstbl.)
>
> If there is a better solution, please let me know.
>
> Matt
>

On Fri, 12 Jun 2009 08:20:10 -0700 (PDT), "(E-Mail Removed)" <(E-Mail Removed)>
wrote:

>But the catch, as you may have realized, is that they will have to
>adjust the size every time they enter the form. Many may have it open
>all day but they will find it annoying nonetheless.

If you don't have the database split (so that each user has their own
"frontend" with the forms, reports, code etc., all sharing the same "backend"
with the tables), split it right away.

With that you can give each user their own customized frontend with the form
sized appropriately, or let them fix it and save it the first time they use
it.

This will require that the user have a .mdb or .accdb file and the full
version of Access, rather than a (safer) .mde/.accde and runtime, however; or
else a larger maintenance chore whenever you roll out a new version.
--

John W. Vinson [MVP]

Another helpful trick is to design the subform control so that it is small
enough that the user with the least resolution won't be opening the mainform
with the control being large enough to warrant the main opening with any
scrollbars. When done like this and the width/height properties are changed
on open, in a normal environment the user shouldn't see any evidence that the
subform was ever any smaller than what the table setting is.

However, if in design view the control is large enough to warrant a
scrollbar in the main form, the user may see a flicker of a scrollbar before
everything is resized accorindingly.

Sample code:

Private Sub Form_Open (Cancel As Integer)
Me.Subformcontrol.Width = _
DLookup("fldSubWidth", "tblUserSettings", "UserID = " & UserID)
Me.Subformcontrol.Height = _
DLookup("fldSubHeight", "tblUserSettings", "UserID = " & UserID)
End Sub




--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"Jack Leach" wrote:

> I just got done doing this
>
> In the mainform, on open, use dlookup or the like to get the default size
> for that user. Then change the width and height properties of the subform
> control accordingly. This should happen fast enough that the user never sees
> any evidence, and you can update the table that holds the current size for
> that user on the mainform close, just in case they change their mind and set
> it to something else.
>
> hth
> --
> Jack Leach
> www.tristatemachine.com
>
> "I haven't failed, I've found ten thousand ways that don't work."
> -Thomas Edison (1847-1931)
>
>
>
> "(E-Mail Removed)" wrote:
>
> > I have two unbound combos on a form that allow the user to change the
> > size of a sub form. Some users' computers are set at different
> > resolutions and I want to allow the user to maximize the size of a
> > rather wide sub as much as possible.
> >
> > It all works wonderfully.
> >
> > But the catch, as you may have realized, is that they will have to
> > adjust the size every time they enter the form. Many may have it open
> > all day but they will find it annoying nonetheless.
> >
> > I don't believe there is a way for the form to change the value on
> > close, right?—so that is out.
> >
> > My next idea would be to save the values in a table on everyones'
> > frontends.
> >
> > If that is the way to go, what would be the code to change the subform
> > size on open?
> >
> > (I will create a form that only allows one record to fill in this
> > table. The form can have on it columns called "WidthEntryfrm" and
> > "HeightEntryfrm" and be called EntryFormSettingstbl.)
> >
> > If there is a better solution, please let me know.
> >
> > Matt
> >

John,
The database is split. I have over 70 users or something. I don't
want to teach them this as it also involves moving some other boxes
out of the way. My code does all this.

Jack,
Thanks but I don't understand. I have no idea what dLookUp is or how
to use it. I think I would rather handle it my way. All I need to
know is how to reference a value in a table that is not linked to the
form. I only know how to reference the data in a table if I put it in
as a subform, invisible or not. But I want to avoid having another
sub on the form even if invisible.

Currently, I have this code on the after update of a combo:

OrderDetailssub.Width = SubWidth * 1440 + 330

It works. It changes the width to the subform called
OrderDetailssub. But I want to edit it to refer not to the value in
SubWidth (an unbound combo on the mainform) but to a field in a table,
one that only has one record in it but several fields.

So I tried changing the line to this:

OrderDetailssub.Width = Table!EntryForm2Settingstbl!Widthset * 1440 +
330

But I didn't think it would work as referencing tables like this is
more difficult than forms. So my question is how do I reference some
data in a table that isn't connected to a subform?

After I know this, I can then put the code on different events.

Thanks,

Matt

On Fri, 12 Jun 2009 11:54:32 -0700 (PDT), "(E-Mail Removed)" <(E-Mail Removed)>
wrote:

>I have no idea what dLookUp is or how
>to use it. I think I would rather handle it my way. All I need to
>know is how to reference a value in a table that is not linked to the
>form.

How to reference a value in a table that is not linked?

DLookUp. That's how.

Type Ctrl-G to open the VBA editor (so you can see the VBA help) and press F1,
and search for DLookUp.

Jack is giving you the correct answer to the question you asked. If you choose
to reject that correct answer, neither of us can help you.
--

John W. Vinson [MVP]

Got it. I misread his first email and thought dLookup had something to
do with finding out the size of each users' screen and automatically
setting the subform size.

His next post came in while I was writing a post that looked like a
reply.

So I heard him then and have completed my design.

Thanks,

Matt

For the record, I would recommend Allen Browne's ELookup alternative to
DLookup. The reasons for this are explained in his article.

http://allenbrowne.com/ser-42.html

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"(E-Mail Removed)" wrote:

> Got it. I misread his first email and thought dLookup had something to
> do with finding out the size of each users' screen and automatically
> setting the subform size.
>
> His next post came in while I was writing a post that looked like a
> reply.
>
> So I heard him then and have completed my design.
>
> Thanks,
>
> Matt
>

Great tip... I've using this in batches for a long time, never once thought
to try it in vba. That will clean a lot of things up for me.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"JimBurke via AccessMonster.com" wrote:

> ...Then change the width and height properties of the subform
> control accordingly. This should happen fast enough that the user never sees
>
> any evidence,...
>
> I doubt you'll have to worry about this here, but FYI if it was a case where
> the user sees the form changing (or anything else going on that you don't
> want them to see) you can use 'DoCmd.Echo False' before you execute the code,
> then 'DoCmd.Echo True' after the code so the user doesn't see anything funny
> happening. In the cases where I've used it I typically turn Echo off at the
> beginning of a form's Open event, then turn it back on at the end of the Open
> event proc. This can come in handy sometimes if you have code that makes the
> screen change appearances in any way and you don't want the users to have to
> see it.
>
> Jack Leach wrote:
> >I just got done doing this
> >
> >In the mainform, on open, use dlookup or the like to get the default size
> >for that user. Then change the width and height properties of the subform
> >control accordingly. This should happen fast enough that the user never sees
> >any evidence, and you can update the table that holds the current size for
> >that user on the mainform close, just in case they change their mind and set
> >it to something else.
> >
> >hth
> >> I have two unbound combos on a form that allow the user to change the
> >> size of a sub form. Some users' computers are set at different
> >[quoted text clipped - 23 lines]
> >>
> >> Matt
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200906/1
>
>

I got the code to work but when I relocated the code to a popup form,
I ran into a bit of difficulty. Part of my code changes the height of
the detail section on the main form (the one with the subform we are
changing. This is the line on the code that isn't working:

Forms!Entryform2!Detail.Height = DLookup("(([HeightSet] + 4) * 1440) +
900", "ENTRYFORM2Settingstbl", "[CurrentGuy] = CurrentUser")

(Without any line break you may see here.)

It doesn't like the "Detail" after the bangs. How do I edit this to
make it work?

Thanks,

Matt