Docmd.Movesize

Docmd.Movesize

Hello,

I have the following code that I=92ve been using for several months.
It=92s used to adjust the size of a form based on the number of records:

---------start code
Private Sub FormResize() 'code to resize both frmMainform and sfrm

Const DetailHeight =3D 240  'tried using me.detail.height but it didn't
work
'remember that the constant above will depend on how tall your textbox
(s)is
Const FormTopMargin =3D 560

Dim lngRecordCount As Long
lngRecordCount =3D Me.sfrm.Form.RecordsetClone.RecordCount '# of records

'form has Header, Detail
DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
    + (Me.FormHeader.Height) + FormTopMargin

Me.sfrm.Height =3D (DetailHeight * lngRecordCount)
'Debug.Print (lngRecordCount)
'Debug.Print (Me.Detail.Height)

End Sub
 ---------end code

All of a sudden I=92m getting this error:  An expression you entered is
the wrong data type for one of the arguments (nothing changed, not
even the number of records!).
The debugger goes to this section of code:
DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
    + (Me.FormHeader.Height) + FormTopMargin

I=92ve tried everything to fix it, even replacing all the variables with
actual numbers.  When I do that I get a stack overflow error.

In doing some research, I saw that ms had a bug when calculating lng
data types, but that was back in 2001!

What else could I be doing wrong?
Thanks,
alex
0
alex
1/12/2010 9:07:28 PM
access 16762 articles. 3 followers. Follow

6 Replies
3105 Views

Similar Articles

[PageSpeed] 0

Instead of using me.detail.height ,
use Me.Section(acDetail).Height

Instead of using me.FormHeader.height ,
use Me.Section(acHeader).Height

To use Me.sfrm.Height
there must be a subform control called sfrm on the main form.

Use the above in your code to eliminate the error message


You can get a stack overflow error when the total size of your form is too 
large.

Make sure the height measurements are all in the same units - twips or 
inches (or cemtimetres).
When telling access what height to move to in DoCmd.MoveSize, convert the 
height measurements to twips if they are not already in tiwps.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"alex" <sql_aid@yahoo.com> wrote in message 
news:1bccd1c8-f642-449d-b2fd-1572e2f5060c@e27g2000yqd.googlegroups.com...
Docmd.Movesize

Hello,

I have the following code that I�ve been using for several months.
It�s used to adjust the size of a form based on the number of records:

---------start code
Private Sub FormResize() 'code to resize both frmMainform and sfrm

Const DetailHeight = 240  'tried using me.detail.height but it didn't
work
'remember that the constant above will depend on how tall your textbox
(s)is
Const FormTopMargin = 560

Dim lngRecordCount As Long
lngRecordCount = Me.sfrm.Form.RecordsetClone.RecordCount '# of records

'form has Header, Detail
DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
    + (Me.FormHeader.Height) + FormTopMargin

Me.sfrm.Height = (DetailHeight * lngRecordCount)
'Debug.Print (lngRecordCount)
'Debug.Print (Me.Detail.Height)

End Sub
 ---------end code

All of a sudden I�m getting this error:  An expression you entered is
the wrong data type for one of the arguments (nothing changed, not
even the number of records!).
The debugger goes to this section of code:
DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
    + (Me.FormHeader.Height) + FormTopMargin

I�ve tried everything to fix it, even replacing all the variables with
actual numbers.  When I do that I get a stack overflow error.

In doing some research, I saw that ms had a bug when calculating lng
data types, but that was back in 2001!

What else could I be doing wrong?
Thanks,
alex 


0
Jeanette
1/13/2010 6:18:48 AM
Hi Alex,

If you cannot get your code to work with Gina's suggestions, you might want 
to try this subform resizing method that was posted on the Access Blog 
several months ago:

http://blogs.msdn.com/access/archive/2009/09/30/dynamically-resize-a-subform-based-on-the-record-count.aspx

 
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"alex" wrote:

> Docmd.Movesize
> 
> Hello,
> 
> I have the following code that I’ve been using for several months.
> It’s used to adjust the size of a form based on the number of records:
> 
> ---------start code
> Private Sub FormResize() 'code to resize both frmMainform and sfrm
> 
> Const DetailHeight = 240  'tried using me.detail.height but it didn't
> work
> 'remember that the constant above will depend on how tall your textbox
> (s)is
> Const FormTopMargin = 560
> 
> Dim lngRecordCount As Long
> lngRecordCount = Me.sfrm.Form.RecordsetClone.RecordCount '# of records
> 
> 'form has Header, Detail
> DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
>     + (Me.FormHeader.Height) + FormTopMargin
> 
> Me.sfrm.Height = (DetailHeight * lngRecordCount)
> 'Debug.Print (lngRecordCount)
> 'Debug.Print (Me.Detail.Height)
> 
> End Sub
>  ---------end code
> 
> All of a sudden I’m getting this error:  An expression you entered is
> the wrong data type for one of the arguments (nothing changed, not
> even the number of records!).
> The debugger goes to this section of code:
> DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
>     + (Me.FormHeader.Height) + FormTopMargin
> 
> I’ve tried everything to fix it, even replacing all the variables with
> actual numbers.  When I do that I get a stack overflow error.
> 
> In doing some research, I saw that ms had a bug when calculating lng
> data types, but that was back in 2001!
> 
> What else could I be doing wrong?
> Thanks,
> alex
> .
> 
0
Utf
1/13/2010 8:01:01 AM
On Jan 13, 3:01=A0am, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> Hi Alex,
>
> If you cannot get your code to work with Gina's suggestions, you might wa=
nt
> to try this subform resizing method that was posted on the Access Blog
> several months ago:
>
> http://blogs.msdn.com/access/archive/2009/09/30/dynamically-resize-a-...
>
> Tom Wickerath
> Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
> __________________________________________
>
>
>
> "alex" wrote:
> > Docmd.Movesize
>
> > Hello,
>
> > I have the following code that I=92ve been using for several months.
> > It=92s used to adjust the size of a form based on the number of records=
:
>
> > ---------start code
> > Private Sub FormResize() 'code to resize both frmMainform and sfrm
>
> > Const DetailHeight =3D 240 =A0'tried using me.detail.height but it didn=
't
> > work
> > 'remember that the constant above will depend on how tall your textbox
> > (s)is
> > Const FormTopMargin =3D 560
>
> > Dim lngRecordCount As Long
> > lngRecordCount =3D Me.sfrm.Form.RecordsetClone.RecordCount '# of record=
s
>
> > 'form has Header, Detail
> > DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
> > =A0 =A0 + (Me.FormHeader.Height) + FormTopMargin
>
> > Me.sfrm.Height =3D (DetailHeight * lngRecordCount)
> > 'Debug.Print (lngRecordCount)
> > 'Debug.Print (Me.Detail.Height)
>
> > End Sub
> > =A0---------end code
>
> > All of a sudden I=92m getting this error: =A0An expression you entered =
is
> > the wrong data type for one of the arguments (nothing changed, not
> > even the number of records!).
> > The debugger goes to this section of code:
> > DoCmd.MoveSize , , , (DetailHeight * lngRecordCount) _
> > =A0 =A0 + (Me.FormHeader.Height) + FormTopMargin
>
> > I=92ve tried everything to fix it, even replacing all the variables wit=
h
> > actual numbers. =A0When I do that I get a stack overflow error.
>
> > In doing some research, I saw that ms had a bug when calculating lng
> > data types, but that was back in 2001!
>
> > What else could I be doing wrong?
> > Thanks,
> > alex
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks Jeanette,
That seemed to work, although I still need to use the constant for the
detail height...it seems to get confused when I change the record
source of the subform(like it adds and does not reset).

Tom,
I'll definitely check out the db; it's probably a better way than i'm
doing it!  If you know where I could find the code without downloading
the db, please let me know.

Thanks again,
alex
0
alex
1/13/2010 12:18:00 PM
Hi Alex,

I suppose anyone could download the sample, and then copy the code and paste 
it into a newsgroup reply. However, I think it would be better for you to 
download the sample database to see it in action. If your concern is 
downloading from a company-owned PC, then can't you download it using a PC 
that you or a friend owns instead?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"alex" wrote:

> Thanks Jeanette,
> That seemed to work, although I still need to use the constant for the
> detail height...it seems to get confused when I change the record
> source of the subform(like it adds and does not reset).
> 
> Tom,
> I'll definitely check out the db; it's probably a better way than i'm
> doing it!  If you know where I could find the code without downloading
> the db, please let me know.
> 
> Thanks again,
> alex
0
Utf
1/14/2010 7:33:01 AM
On Jan 14, 2:33=A0am, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> Hi Alex,
>
> I suppose anyone could download the sample, and then copy the code and pa=
ste
> it into a newsgroup reply. However, I think it would be better for you to
> download the sample database to see it in action. If your concern is
> downloading from a company-owned PC, then can't you download it using a P=
C
> that you or a friend owns instead?
>
> Tom Wickerath
> Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
> __________________________________________
>
>
>
> "alex" wrote:
> > Thanks Jeanette,
> > That seemed to work, although I still need to use the constant for the
> > detail height...it seems to get confused when I change the record
> > source of the subform(like it adds and does not reset).
>
> > Tom,
> > I'll definitely check out the db; it's probably a better way than i'm
> > doing it! =A0If you know where I could find the code without downloadin=
g
> > the db, please let me know.
>
> > Thanks again,
> > alex- Hide quoted text -
>
> - Show quoted text -

Hi Tom,
I checked out the site from home last night...
The db seems to be '07 (which I don't have).  Someone else I see asked
for it to be posted in '03 too.  I plan on getting '07 in a few days,
however; we'll see.
Thanks for the response.
alex
0
alex
1/14/2010 1:20:23 PM
Hi Alex,

If you send me a private e-mail message, with a valid reply address, I can 
send you a zipped copy of this database converted to the Access 2000 file 
format.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"alex" wrote:

> Hi Tom,
> I checked out the site from home last night...
> The db seems to be '07 (which I don't have).  Someone else I see asked
> for it to be posted in '03 too.  I plan on getting '07 in a few days,
> however; we'll see.
> Thanks for the response.
> alex
0
Utf
1/15/2010 7:44:02 AM
Reply:

Similar Artilces:

How convert Docmd.Domenuitem
I'm converting a 97 db to 2000. A form has a button that executes a Docmd.Domenuitem command. This is supposed to invoke Find and Replace. It does, but the subject is grayed out. I need the find to be based on the control where the cursor was when the button was pressed. Any ideas? Access help in 2000 does not even mention this command neither does my Access book. Maybe you need to do Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind or DoCmd.FindRecord On Thu, 29 Sep 2005 13:43:03 -0700, "mscertified" <rupert@tigerlily.com> wrote: >I'm converti...

Is it true about DoCmd.SendObject
That DoCmd.SendObject will find your default Mail client? -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 "Bob Vance" <rjvance@ihug.co.nz> wrote in message news:u91TwlG3KHA.4964@TK2MSFTNGP05.phx.gbl... > That DoCmd.SendObject will find your default Mail client? > > -- > Thanks in advance for any help with this......Bob > MS Access 2007 accdb > Windows XP Home Edition Ver 5.1 Service Pack 3 > > It's true if your default Mail client is Outlook :~) ...

When I close a form with DoCmd.Close I get a parameter popup
This is a repost. I will try to explain the problem a little better than I did the last time. When I execute DoCmd.Close on one of my forms I get one of those little unknown parameter popups, the kind you get when there's something wrong with your query. But all I'm trying to do is close the form, not read any data. This popup appears to be gernerated by the row source query in a list box on the form. This row source query references another text box on the form and this appears to be what the popup is asking for. But there's a value in this text box. And why should th...

Help with "Form DoCmd.Open and DoCmd.Close
The following two lines of code is not operating correctly. Private Sub cmdChangeVendor1_Click() DoCmd.Close acForm, "Invoice by Market Reviewer" DoCmd.OpenForm "Selections Vendor and Reviewer", acNormal End Sub When I click on the cmdChangeVendor1 button, I expect the form "Invoice by Market Reviewer" to close and the form "Selections Vendor and Reviewer" to open. But that is not the result I get. The form "Invoice by Market Reviewer" closes but that is about it. Nothing else opens. I have two forms that behaves this way. I have ...

docmd.runcmd SQL functions
I have a table with fields a,b,c,d. If I have a row (x) where x.d is blank, I want to search the table and see if there is a row (y) such that x.c=y.c and x.b=y.b and if y.d is not blank, then I want x.d to be filled in with y.d. I can't seem to figure out how to start this. Thanks Carl Frankly, you don't want to do this. You have an un-normalized data model and you are storing redundant data. If b and c determine d, then that should be stored *once* a record in its own table. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want ans...

DoCmd.FindRecord Error
My code has a function to find a record. The function is called by 2 separate search forms, each of which generates SQL for a query that builds a table of record keys of all qualifying recs. When there is only 1 qualifying rec, the function is called to open the primary form (Member Data) to the qualifying rec. The function uses the rec in the new results table ( a long int ) as the argument for the FindRecord. When called by 1 of the forms, it works properly. When called by the second form it fails on the DoCms.FindRecord line with Runtime error 2162: "A macro set to 1 of th...

DoCmd.OpenReport
Can someone please help me get the " and ' right for this? (PARTNUMBER is a text field). I just can't get it...argh! DoCmd.OpenReport stDocName, acPreview, , "[PARTNUMBER] =" ' "& Me.PARTNUMBER" ' " DoCmd.OpenReport stDocName, acPreview, , "[PARTNUMBER] =""" & Me.PARTNUMBER & """" -- Duane Hookom Microsoft Access MVP "Gina K" wrote: > Can someone please help me get the " and ' right for this? (PARTNUMBER is a > text field). I just can't get it...argh! >...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8
Hello, I have vba that will import the data from Excel file. Is it possible to append the data from excel file to a table in Access in such a way that excel file does not have a field's name? When I run this transferspreadsheet command, it comes in as F1, F2,..... thanks, On Mar 3, 3:08=A0pm, "Boon" <boonyawat.la-ongth...@cnh.com> wrote: > Hello, > > I have vba that will import the data from Excel file. Is it possible to > append the data from excel file to a table in Access in such a way that > excel file does not have a field's n...

Comact & Repair using DoCmd
I would like to set somw databases to compact & repair when a form is opened (using a macro that opens the form from the task scheduler). I tried the following code on form load but it wants to create a new table. instead of running compact & repair: Private Sub Form_Load() DoCmd.DoMenuItem acForm, acTools, acDatabaseUtilities, acCompactAndRepairDatabase End Sub Any Ideas? Thanks, Alan hello you! "Alan" <Alan@discussions.microsoft.com> skrev i melding news:A470028B-2D43-49B2-B4C4-CA80C70C8B7A@microsoft.com... >I would like to set somw databases to compact &...

docmd.sendobject
Outlook 2003 does not accept message text from DoCmd.SendObject command in access database. All other fields pass to the message fine. Is there a setting in outlook 2003 that causes this? It works fine if I use Outlook 97 as my default mail program. DoCmd.SendObject , , "MSTXT", "recipient", "cc", "bcc", "subject", "Message text" ...

DoCmd.SendObject
The behavior of the DoCmd.SendObject method seems to be arbitrary in regards to which eMail client it selects to do the sending. Sometimes it selects Outlook Express, sometimes it selects Outlook. We only use the scheduling functions of Outlook through our Access app. No one here uses Outlook for eMail - we all use Outlook Express. I don't see any way in the SendObject method to specify which eMail client I want to use. Is anyone aware of a way I can force it to always select Outlook Express? Not strictly an Access Problem but, Both Outlook & Express wants to be your default ...

Filter expression passed with DoCmd
It appears that the filter expression passed to a report as a part of the DoCmd expression is not available in the Open code. I.e., Me.Filter is null. When? OnLoad? Oops, wrong group...........sorry "Bill" <billstanton@psln.com> wrote in message news:TO-dnbGyUMhI2y_anZ2dnUVZ_jydnZ2d@trueband.net... > It appears that the filter expression passed to > a report as a part of the DoCmd expression > is not available in the Open code. > > I.e., Me.Filter is null. > > When? OnLoad? > > > "Bill" <billstanton@psln.com> wrote in me...

docmd.close
I have an autoExec to call my function and verify user log in information to let user ue or close the application. I use docmd.close which only support database object like tables, forms, reports..etc. The MS Access stil open and run the main form. Is it possible to close the MS Access before open and load the main form use any other function? Your information is great appreciated, Souris, > Is it possible to close the MS Access before open and load the main form > use > any other function? Sorry, but I don't understand what you mean by that statement... but... ...

docmd.openform
Is there a way to open a form without making it visible from the start? Here's sample code to help explain: DoCmd.OpenForm "form1", acNormal Me.Visible = False Me.Combo1.RowSource = "" DoCmd.Close acForm, "Country_BT_Land_APAC", acSaveYes The minor problem with this is that the form is visible for a split second, and then becomes invisible - which looks like a flicker. Since I have to do this with 4 forms at one time, there will be 4 flickers. I'm trying to elimate the flicker problem. Thanks! -- Message posted via AccessMonster.com http://www.acces...

Docmd.Save
I'm using the following code to save my record before printing it If Me.Dirty Then Docmd.Save I'm getting an error saying I have not specified the object Do I have to specify 'table' and the table name each time I use this statement, I thought it defaulted to the current object? "mscertified" <rupert@tigerlily.com> wrote in message news:CE644884-31D0-4428-B1B0-D0E792B1F87A@microsoft.com... > I'm using the following code to save my record before printing it > > If Me.Dirty Then Docmd.Save > > I'm getting an error saying I have not spec...

Using DoCmd in an IF/Then statment
I am using the below coding, debug says there are no problems, but the Else statement DoCmd will not run. Can anyone tell me what is wrong here? Function Another_Record() MSGBOX "Do you wish to do another LRU update?", vbYesNo, "LRU UPDATE" If vbYes Then DoCmd.GoToControl "Serial Number" Else DoCmd.Close acForm, "EDIT CDS LRU ASSET" End If End Function This has been answered already about 5 posts below this. "Curtis" wrote: > I am using the below coding, debug says there are no problems, b...

DoCmd.SearchForRecord
Can anyone tell me what is wrong with the following statement? DoCmd.SearchForRecord acDataForm, DetailForm, acFirst, WhereCond DetailForm = "frmDynDfltsDetail" frmDynDfltsDetail is a form that is currently open. WhereCond = "kcoSinkTable = "PsgrService" AND kcoSinkField = "LoadFactor" AND kcoSourceTable = "Options" AND kcoSourceField = "PsgrLoadFactor" " kcoSinkTable, kcoSinkField, kcoSourceTable, and kcoSourceField are Combo Boxes in frmDynDfltsDetail. I get no error message when the DoCmd.SearchForRecord runs...

DoCmd to delete records
Hi I have a command button that when clicked deletes certain records from a subform based on an application number being equal. It deletes all records with the same applciation number as currently shown on the main form, see code below: DoCmd.RunSQL ("DELETE FROM tblAdultDetails WHERE [frmAdultDetails2].Form![ApplicationNum]=" + Str(Me.ApplicationNumber)) However I would like it to only delete records if the fields [MainApplicantFlag] =1 or [PartnerFlag] =1 as well as ApplicationNum being equal. These are both number fields btw. Can someone tell me what I should add to my code in...

DoCmd.OpenQuery
How can I use DoCmd.OpenQuery and not have it prompt that it is performing a append or delete. I would like it to perform the operation without giving me the warnings and just do it. Example in use within Access VB: DoCmd.OpenQuery "qry-append-BuildMonthlyUsage" DoCmd.OpenReport "rpt-ShowMonthlyUsage", acViewPreview Or is there another method that I should be using? Thank you in advance for your help, Michael Kintner On Wed, 7 Nov 2007 15:23:25 -0500, Michael Kintner wrote: > How can I use DoCmd.OpenQuery and not have it prompt that it is performing a >...

DoCmd.OpenReport Error
Hi , Please kindly assist. Every time I try to click the OK butto to open the report that I have created It passes an error of Run time error 2585, action cannot be carried out while processing a form or report or event. Below is the coding. in the Ok event procedure. Private Sub cmdOK_Click() Me.Visible = False 'DoCmd.OpenQuery "LicenseQueryB", acViewNormal, acEdit DoCmd.OpenReport "Torrens Valley Firearms Report B", acViewNormal, acEdit 'DoCmd.Close acForm, "License DialogB" End Sub Below is the coding for opening the Report which is call...

Docmd.Movesize
Docmd.Movesize Hello, I have the following code that I=92ve been using for several months. It=92s used to adjust the size of a form based on the number of records: ---------start code Private Sub FormResize() 'code to resize both frmMainform and sfrm Const DetailHeight =3D 240 'tried using me.detail.height but it didn't work 'remember that the constant above will depend on how tall your textbox (s)is Const FormTopMargin =3D 560 Dim lngRecordCount As Long lngRecordCount =3D Me.sfrm.Form.RecordsetClone.RecordCount '# of records 'form has Header, ...

DoCmd Code
I'm wanting to insert data, in this case and employee's name from query1, into the text of an email. Can anyone tell me how this is done. I've provide some sample code below. Many thanks! Function SendEmail() DoCmd.SendObject acSendQuery, "Query1", "HTML", "John Doe", , , "Adherence", _ "The following agent (Query1.[Employee Name]) is out of adherence" End Function you can use DLookup() but there may be a better way depending on how you are running this code. For example, how are you deriving the "John Doe" nam...

DoCmd.HourGlass not working
I've got a form with a treeview object. When I double click on a particular node in the treeview I open a second form. The second form takes awhile to populate so I want to display an hourglass cursor. I can't get the hourglass to display. What's odd is that it works from a button click event but not the treeview double click event. This code doesn't work: Private Sub TreeView1_DblClick() Call DoCmd.Hourglass(True) DoCmd.OpenForm "SecondForm" End Sub This code works: Private Sub Button_Click() Call DoCmd.Hourglass(True) DoCmd.O...

DoCmd.RunSQL
How can I get the Zero to be Null or Empty or Text? CompletedDate is a Text Field I thought "" or "Not updated" would get it but error's out DoCmd.RunSQL "update tblPayrollCompletion set CompletedDate = 0" Thanks for any help Hi Mike, I won't ask why you have a completed date as a text datatype, rather than a datetime datatype. But given that it IS a text datatype, the following will work: DoCmd.RunSQL "UPDATE tblPayrollCompletion SET CompletedDate = '0'" will set CompletedDate to the text string '0' DoCmd.RunSQL "...

DoCmd.OpenReport
Dim ReportName If Me.Action = 1 Then ReportName = "Rpt_ActivationBounty_Detail" If Me.Action = 2 Then ReportName = "Rpt_RevenueShare_Detail" If Me.Action = 3 Then ReportName = "Rpt_ClientSummary" If Me.Action = 4 Then ReportName = "Rpt_CheckRequisition" If Me.Action = 5 Then ReportName = "Rpt_ItemCodes_NONVAR_AUDIT" If Me.Action = 6 Then ReportName = "Rpt_ItemCodes_VAR_AUDIT" DoCmd.OpenReport ReportName, acViewPreview, , "Partner_ID=" & Me.Lkp The client selects a Partner from a drop-down list... Then selects which ...