How to change subform size for different users

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

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)
 
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.
Click to expand...

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.
 
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)
 
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
 
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.
Click to expand...

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.
 
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 said:
...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 said:
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
Click to expand...
[quoted text clipped - 23 lines]
Matt
Click to expand...
Click to expand...
Click to expand...
 
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
 
Also, how do I address the BackColor of the Detail section from the
Pop?

(I also want the users to choose their own color.) The code works
well with an unbound control on the main.

Matt
 
This seems to work for any regular form that is currently open

Access.Forms("frmLogin").Section("Detail").BackColor = 0

(assuming the form name is frmLogin and you want a black backcolor anyway)

or from the forms module could be modified to

Me.Section("Detail").Backcolor = #

--
Jack Leach
www.tristatemachine.com

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

try this instead

Forms("EntryForm2").Section("Detail").Height = DLookup(....

I'm not really sure why once's better than the other, but...


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Forms!Entryform2!Detail.Height = DLookup("(([HeightSet] + 4) * 1440) +
900", "ENTRYFORM2Settingstbl", "[CurrentGuy] = CurrentUser")

(Without any line break you may see here.)
Click to expand...

Also, just an FYI, the line break in vba makes code a lot easier to
understand sometimes. Use <space>_ to continue a line in vba. The standard
is not to exceed the 80th column position for any line. This is mostly due
to the fact that most printers in a monospace font will print 80 characters
before wrapping, and there's not too much worse of a programming task than
staring at thick code trying to find out if its supposed to be a break or if
its just a wrap.

Forms("EntryForm2").Section("Detail").Height = _
DLookup("(([HeightSet] + 1) * 1440) + 900), _
"ENTRYFORM2Settingstbl", _
"[CurrentGuy] = CurrentUser")

Come to think of it... whats "[CurrentGuy] = CurrentUser"? If CurrentUser
is a function it needs to be removed from the literal string (set of quotes).
Or if it's a string variable you would need it outside quotes as well. This
may be the issue, rather than my previous suggestion of a different syntax
calling the form property.

Something like "[CurrentGuy] = """ & CurrentUser & """"")

or the somewhat simpler but error prone

"[CurrentGuy] = '" & CurrentUser & "'")

--
Jack Leach
www.tristatemachine.com

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



[email protected] said:
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
Click to expand...
 
Why "CurrentGuy"? Well, when I first created the table that would
have a field called "CurrentUser", I had problems filtering it to the
CurrentUser in a query. So I thought I shouldn't use one of those key
or program words. I then changed the field to CurrentGuy just as a
test and it gave me no problem. So I kept CurrentGuy which isn't a
field any user will ever see---I'm hiding it.

So it's sort of like not naming a field "Null" or "Query".

I believe I didn't end up using that query but it still seemed wise
not to call the field "CurrentUser" still.

Thanks for the code,

Matt
 
Back
Top