By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

How do I Move Objects using VBA code and also, once the objects are moved, how do I m

P: 10
I'm building forms and reports using Mircrosoft Access. I've ran into this issue a few times now and I'm tired of working around it. I need to be able to move objects by changing the left and top position in an access report/form. This is an example of my code. I've tried declaring variables as variant, long, integer, and double. Should I just keep rotating through the different types or is there a better way of doing this? When the form/report opens, the object just goes to zero and the value shown in properties is "0.0028"

Public Sub Form_Load()
Dim lngTop As Long
Dim lngLeft As Long

lngTop = 4.4
lngLeft = 4.4
With Forms!frmTest!Label0
.Left = 4.4
.Top = 4.4
End With
End Sub
Dec 19 '16 #1

✓ answered by PhilOfWalton

You need to use twips as the unit of measurement.
There are 567 twips per cm
There are 1440 twips per inch.

Assuming you are using English measurements, for clarity I tend to use code like this:-
Expand|Select|Wrap|Line Numbers
  1. Public Sub Form_Load()
  2.     Const TwipsPerInch = 1440
  3.  
  4.     With Forms!frmTest!Label0
  5.         .Left = 4.4 * TwipsPerInch
  6.         .Top = 4.4 * TwipsPerInch
  7.     End With
  8. End Sub
  9.  
Phil

Share this Question
Share on Google+
8 Replies


PhilOfWalton
Expert 100+
P: 1,430
You need to use twips as the unit of measurement.
There are 567 twips per cm
There are 1440 twips per inch.

Assuming you are using English measurements, for clarity I tend to use code like this:-
Expand|Select|Wrap|Line Numbers
  1. Public Sub Form_Load()
  2.     Const TwipsPerInch = 1440
  3.  
  4.     With Forms!frmTest!Label0
  5.         .Left = 4.4 * TwipsPerInch
  6.         .Top = 4.4 * TwipsPerInch
  7.     End With
  8. End Sub
  9.  
Phil
Dec 19 '16 #2

P: 10
Thanks PhilOfWalton. I've been doing dba stuff for 7/8 years and never heard of this. Thanks for the help.
Dec 19 '16 #3

P: 10
Oh fail. Is there a way to make the changes on said form/report permanent?
Dec 19 '16 #4

PhilOfWalton
Expert 100+
P: 1,430
How confusing!!!
Your original question was about dynamically moving a control as the form opened (Incidentally, I think it might be better to run the code on the OnOpen event rather than the OnLoad event)

Your second question asks how to position the control permanently.This, as I'm sure you are well aware, is done in Design View (Positionig here is done in Inches or Cms. - NOT Twips) and you don't need the code.

Am I missing the point of your question?

Phil
Dec 19 '16 #5

P: 10
I apologize. I have about 800 objects on a single report. they are in sets of 4. and the position of each set is determined by 1 of those 4. I am unsure of how much time I've spent moving every single object and if I were to use a loop to position each set accordingly, it'd save me days.
Dec 19 '16 #6

P: 10
Just a quick pic fo the dashboard with the kajillion objects.
Dec 19 '16 #7

PhilOfWalton
Expert 100+
P: 1,430
That looks fun.

This bit of code may give you a clue to redesigning forms/reports.
Note that it is run from an individual module.
Expand|Select|Wrap|Line Numbers
  1. Function RedesignReport(ReportName As String)
  2.  
  3.     Const TwipsPerCm = 567
  4.  
  5.     DoCmd.OpenReport ReportName, acViewDesign
  6.  
  7.     With Reports(ReportName)
  8.         .Text2.Top = 2 * TwipsPerCm
  9.         .Text2.Left = 2 * TwipsPerCm
  10.     End With
  11.  
  12.     DoCmd.Close acReport, ReportName
  13.  
  14. End Function
  15.  
I presume you are aware that if your controls have a consistent form of naming like "loom0101", "loom0102" etc., you can refer to them using indexes.

if you have integers i & j then for i = 1 and j = 2, "loom0102" can be addressed as
Expand|Select|Wrap|Line Numbers
  1.     Me.Controls("loom" & Cstr(Format (i,"00")) & Cstr(Format (j,"00"))
  2.  
Might give you the option to create a table to build your form

Phil
Dec 20 '16 #8

P: 10
Thanks a bunch. I'll have to look into it some more but just being able to move the objects for now is weight lifted off of my shoulders. When I get the 1st version out, I'll start playing around with the module and let you know how it goes. Thanks again.
Dec 20 '16 #9

Post your reply

Sign in to post your reply or Sign up for a free account.