Passing arguments to procedures

There is something fundamental that I have failed to grasp about passing 
arguments to procedures.

I have a form featuring a set of unbound controls displaying dates and the 
corresponding days of the week.  (For some reason, the long-date format, on 
my computer, does not include the latter, as the documentation suggests it 
might, so it has to be separately calculated and displayed.)

A default date is displayed in each control, on Form_Open.  This can then be 
manually adjusted by a pair of ‘up’ and ‘down’ buttons associated with each 
day/date control pair.  In the case of the control ‘Start_Date’, for example, 
the first two procedures are specific to the control, whilst the third Sub 
and the following Function are common to the form.

Private Sub ComDateStartNext_Click()			‘Increment date by one day
        IncDecDateDay Me.StartDate, Me.StartDay, 1
End Sub

Private Sub ComDateStartPrevious_Click()		‘Decrement date by one day
        IncDecDateDay Me.StartDate, Me.StartDay, -1
End Sub

Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
    InDate = InDate + Increment
    InDay = Get_WeekDay(InDate)
End Sub

Private Function Get_WeekDay(InDate) As String
    Select Case Weekday(InDate)
        Case 1
            Get_WeekDay = "SUN"
        Case 2
            Get_WeekDay = "MON"
        Case 3
            Get_WeekDay = "TUE"
        Case 4
            Etc.
    End Select
End Function

Nothing happens when the ‘up’ and ‘down’ buttons are clicked but if I 
replace the implicit general-purpose Sub IncDecDateDay with the explicit test 
version:–

Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
    Me.StartDate = InDate + Increment
    Me.StartDay = Get_WeekDay(InDate)
End Sub

....it all works fine (in the case of the ‘Start_Date’ control, of course) 
but my belief that changes to the parameter InDate would be reflected in the 
displayed value of the original calling control are clearly wrong.  It is not 
that I cannot find an alternative solution – I can recast the thing using a 
function to return the required values – but what worries me is that I have 
obviously been labouring under a misapprehension.  Perhaps I spent too long 
messing about with ‘C’.  Would someone be kind enough to enlighten me?

---Peter Hallett

0
Utf
3/7/2008 1:02:00 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
704 Views

Similar Articles

[PageSpeed] 11

Hi Peter,

Because the data type of your parameters is not specified, there's some 
ambiguity about what exactly is being passed to the procedure - a reference 
to the control itself, or to the value of the data entered into the control. 
What I believe is happening is that it is a reference to the value entered 
into the control that is being passed, and this is why the control itself is 
not updated. If you change the data type of the parameter to Control or 
Textbox, I believe you will see the behavior you expected.

BTW: If the only reason for this is to display the day of the week as well 
as the date, I believe you could do that using a custom date format such as 
dddd, dd/mm/yyyy (or dddd, mm/dd/yyyy if you are in the US).

-- 
Brendan Reynolds

"Peter Hallett" <PeterHallett@discussions.microsoft.com> wrote in message 
news:4A0DEE86-CF25-499F-BEC6-E8EAB83DAB15@microsoft.com...
> There is something fundamental that I have failed to grasp about passing
> arguments to procedures.
>
> I have a form featuring a set of unbound controls displaying dates and the
> corresponding days of the week.  (For some reason, the long-date format, 
> on
> my computer, does not include the latter, as the documentation suggests it
> might, so it has to be separately calculated and displayed.)
>
> A default date is displayed in each control, on Form_Open.  This can then 
> be
> manually adjusted by a pair of ‘up’ and ‘down’ buttons associated with 
> each
> day/date control pair.  In the case of the control ‘Start_Date’, for 
> example,
> the first two procedures are specific to the control, whilst the third Sub
> and the following Function are common to the form.
>
> Private Sub ComDateStartNext_Click() ‘Increment date by one day
>        IncDecDateDay Me.StartDate, Me.StartDay, 1
> End Sub
>
> Private Sub ComDateStartPrevious_Click() ‘Decrement date by one day
>        IncDecDateDay Me.StartDate, Me.StartDay, -1
> End Sub
>
> Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
>    InDate = InDate + Increment
>    InDay = Get_WeekDay(InDate)
> End Sub
>
> Private Function Get_WeekDay(InDate) As String
>    Select Case Weekday(InDate)
>        Case 1
>            Get_WeekDay = "SUN"
>        Case 2
>            Get_WeekDay = "MON"
>        Case 3
>            Get_WeekDay = "TUE"
>        Case 4
>            Etc.
>    End Select
> End Function
>
> Nothing happens when the ‘up’ and ‘down’ buttons are clicked but if I
> replace the implicit general-purpose Sub IncDecDateDay with the explicit 
> test
> version:–
>
> Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
>    Me.StartDate = InDate + Increment
>    Me.StartDay = Get_WeekDay(InDate)
> End Sub
>
> ...it all works fine (in the case of the ‘Start_Date’ control, of course)
> but my belief that changes to the parameter InDate would be reflected in 
> the
> displayed value of the original calling control are clearly wrong.  It is 
> not
> that I cannot find an alternative solution – I can recast the thing using 
> a
> function to return the required values – but what worries me is that I 
> have
> obviously been labouring under a misapprehension.  Perhaps I spent too 
> long
> messing about with ‘C’.  Would someone be kind enough to enlighten me?
>
> ---Peter Hallett
>



0
Brendan
3/7/2008 1:24:02 PM
Thanks Brendan,

You are absolutely right!  All it needed was to change

Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
InDate = InDate + Increment
InDay = Get_WeekDay(InDate)
End Sub

...into

Private Sub IncDecDateDay(InDate As Control, InDay As Control, ByVal 
Increment As Integer)
InDate = InDate + Increment
InDay = Get_WeekDay(InDate)
End Sub

…and it worked a treat.  I am grateful for your assistance.  It has saved me 
a fair bit of work.

As to date formatting, I am in the UK, where my regional settings appear to 
exclude the day-name from the long-date format.  I have tried using the 
formatting command in form design, but without a lot of luck.  Every cloud 
has a silver lining, though.  A late requirement was that the day-name should 
be shown in a larger font than the date and in colour, if possible.  Having 
provided a separate control by necessity, the change in job spec. was 
therefore significantly easier to meet.

Your response has also helped me to answer another of my own questions.  
Yes, I obviously did spend too long messing about with ‘C’.  Being a 
pointer-based language, it tended to instil in one’s mind that passing 
parameters by reference simply means passing the address of the control, or 
variable.  Your response implies that, “It ain’t necessarily so.”

-- 
Peter Hallett


"Brendan Reynolds" wrote:

> 
> Hi Peter,
> 
> Because the data type of your parameters is not specified, there's some 
> ambiguity about what exactly is being passed to the procedure - a reference 
> to the control itself, or to the value of the data entered into the control. 
> What I believe is happening is that it is a reference to the value entered 
> into the control that is being passed, and this is why the control itself is 
> not updated. If you change the data type of the parameter to Control or 
> Textbox, I believe you will see the behavior you expected.
> 
> BTW: If the only reason for this is to display the day of the week as well 
> as the date, I believe you could do that using a custom date format such as 
> dddd, dd/mm/yyyy (or dddd, mm/dd/yyyy if you are in the US).
> 
> -- 
> Brendan Reynolds
> 
> "Peter Hallett" <PeterHallett@discussions.microsoft.com> wrote in message 
> news:4A0DEE86-CF25-499F-BEC6-E8EAB83DAB15@microsoft.com...
> > There is something fundamental that I have failed to grasp about passing
> > arguments to procedures.
> >
> > I have a form featuring a set of unbound controls displaying dates and the
> > corresponding days of the week.  (For some reason, the long-date format, 
> > on
> > my computer, does not include the latter, as the documentation suggests it
> > might, so it has to be separately calculated and displayed.)
> >
> > A default date is displayed in each control, on Form_Open.  This can then 
> > be
> > manually adjusted by a pair of ‘up’ and ‘down’ buttons associated with 
> > each
> > day/date control pair.  In the case of the control ‘Start_Date’, for 
> > example,
> > the first two procedures are specific to the control, whilst the third Sub
> > and the following Function are common to the form.
> >
> > Private Sub ComDateStartNext_Click() ‘Increment date by one day
> >        IncDecDateDay Me.StartDate, Me.StartDay, 1
> > End Sub
> >
> > Private Sub ComDateStartPrevious_Click() ‘Decrement date by one day
> >        IncDecDateDay Me.StartDate, Me.StartDay, -1
> > End Sub
> >
> > Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
> >    InDate = InDate + Increment
> >    InDay = Get_WeekDay(InDate)
> > End Sub
> >
> > Private Function Get_WeekDay(InDate) As String
> >    Select Case Weekday(InDate)
> >        Case 1
> >            Get_WeekDay = "SUN"
> >        Case 2
> >            Get_WeekDay = "MON"
> >        Case 3
> >            Get_WeekDay = "TUE"
> >        Case 4
> >            Etc.
> >    End Select
> > End Function
> >
> > Nothing happens when the ‘up’ and ‘down’ buttons are clicked but if I
> > replace the implicit general-purpose Sub IncDecDateDay with the explicit 
> > test
> > version:–
> >
> > Private Sub IncDecDateDay(InDate, InDay, ByVal Increment As Integer)
> >    Me.StartDate = InDate + Increment
> >    Me.StartDay = Get_WeekDay(InDate)
> > End Sub
> >
> > ...it all works fine (in the case of the ‘Start_Date’ control, of course)
> > but my belief that changes to the parameter InDate would be reflected in 
> > the
> > displayed value of the original calling control are clearly wrong.  It is 
> > not
> > that I cannot find an alternative solution – I can recast the thing using 
> > a
> > function to return the required values – but what worries me is that I 
> > have
> > obviously been labouring under a misapprehension.  Perhaps I spent too 
> > long
> > messing about with ‘C’.  Would someone be kind enough to enlighten me?
> >
> > ---Peter Hallett
> >
> 
> 
> 
0
Utf
3/7/2008 2:26:00 PM
Reply:

Similar Artilces:

Best reinstall procedure for Money 2007 from CD under Win 7-64?
Cal or anyone who has successfully reinstalled from the store-bought original CD--I tried to install it, but upon attempting to open Money files .mny copied from Vista 32 OS, I got various errors like bad password or e-mail address, file being opened was not closed properly, etc. Not that we have never used LiveID in Money and our files were closed properly and Money runs fine on the other OS. I have seen reverences to running Money the first time after installation as an Admin--right click where exactly? And opening the Sample.Money.mny file--but I don't see one? Also up...

Using text as argument
Dear Gentlemen, I have the following problem. I have the following list in cell range A1 to A3 Milling Drilling Tapping ( Note :- The text strings may differ from the above. It is no possible to include any Hyphen, colan, Semicolan & Underscore etc. I is also not possible to give numbers. ) Now I want a formula in Range B1 to B3 which gives threee differen values or performs three different calculations based on the value i cell A1 to A3. ie - If cell A1 equals Milling then value in B1 is 1 ( or a formula) If cell A1 equals Drilling then value in B1 is 2 (or a formula) & I cell ...

Search for userform or procedure name?
Tried to search XL files in Windoze Explorer for userform, procedure and module names - doesn't work. Does anyone know of a search tool that can do this? Sure would be handy! Thanks in advance. Check out MZ-Tools for VBA. It's an add-in at... http://www.mztools.com/v3/mztools3.aspx -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "salgud" wrote: > Tried to search XL files in Windoze Explorer for userform, procedure and > module names - doesn't work. Does anyone know of a search too...

Is It Possible To Pass Parameters to A Pass Through Query
My assumption is that's it's not. At work I use ODBC to connect to our oracle database with Access 2003. There are cases where using a pass through query runs much much faster and I then use it in a make table query to make a local table. In access you can use brackets [] to have it ask for input. May I assume there is no way to do anything like that in a pass through query? Create a PassThrough query and use code to assign the SQL to this query filtering it with a parameter, and then run it e.g Dim MyVariable As Integer MyVariable = InputBox("Please select a Number&quo...

Argument ()
thank you =================================== Private Sub Form_BeforeInsert(Cancel As Integer) =================================== help me to know any information about the ( ) beside the name of the procedure how can I understand the benefit of using this ( ) and when should I put entry in () Any help or information thank you Then parentheses () are always required in the definition of a sub or function. Those items in the parentheses are arguments the sub or function needs to complete its task. -- Dave Hargis, Microsoft Access MVP "a" wrote: > thank you > >...

public procedures
how can i create a public procedure, which can be used by different macros in the same workbooks? thanks Hi access within ONE workbook: Just put your procedure in a standard module of this workbook: public sub foo() '... end sub -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > how can i create a public procedure, which can be used by > different macros in the same workbooks? > thanks ...

Pass multiple selected values from list box to table
How do I pass multiple selected values from a list box to a table? I've found code to use the values as criteria in a query but would now like to store the values in a table (at least temporarily) thanks in advance. jack Pass selected values from a list box to a table? I don't really think you want to do this. Can you describe more about what you are trying to accomplish? You just store raw data in a table. You don't store manipulated data in a table. Maybe you need a query to do what you want to do... -- Ryan--- If this information was helpful, please indi...

executing a stored procedure on MS SQL Server 2000 within MS Excel 2000
Hi everybody I prepared a pivot table based on an external database source (a table on MS SQL Server 2000). Now I would like to add a button which will execute a stored procedure on the server (procedure recalculating data in above table) and than will refresh the pivot table. I appreciate any help. Cheers ...

How to pass an object from C++
Hello! We have a C# asp.net web application that is using a COM dll We use the tlbimp to be able to use the COM dll from C# asp.net web application The method InitRules shown below is located in the COM dll. It can be seen below the text Original. Method InitRules below is called from C# but we want to pass an object of type Handle_DS in addition to all the other parameters. You can see the modified InitRules below marked Modified Note the object of type Handle_DS is a C++ object that is created from C# asp.net application and pass into the InitRules I have also copied the whole idl fil...

100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itreal
100% Passing Guaranteed in All I.T. Exams/Certifications at 1st Attempt Hi Friends, To pass all types of I.T Exams/Certifications with mind blowing results in 1st attempt just visit http://www.itrealexams.com/ and get real time Q&As, Brain Dumps, Real Exams, Study material, E-Books, Video Exams and Labs for your all type of I.T Certification Exams. ...

IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!!
IT CertificationzzzZ.....100% GUARANTEED PASS.......!!!@!!! Hi guys, If you are serious about your professional career and pass IT Certification exam in first attempt then Visit http://www.FreeExamKing.com and get latest Questions And Answers Dumps for Microsoft, Oracle, Cisco, CompTIA and many more, i have used and found it very helpful. ...

Passing data between forms
Hi I am working on a project someone else started, and he has this code: If STR_FormName = "FMS_ToDo" Then Page47.SetFocus He obviously expects the name of the last form to be here, but it is not. In fact, when I put Option Explicit at the top - this field was undefined. How is this supposed to work? Stapes ...

Passing arguments to procedures
There is something fundamental that I have failed to grasp about passing arguments to procedures. I have a form featuring a set of unbound controls displaying dates and the corresponding days of the week. (For some reason, the long-date format, on my computer, does not include the latter, as the documentation suggests it might, so it has to be separately calculated and displayed.) A default date is displayed in each control, on Form_Open. This can then be manually adjusted by a pair of ‘up’ and ‘down’ buttons associated with each day/date control pair. In the case of the control ‘St...

Excel VB triggering Stored Procedures/Queries
I want to use Excel to operate SQL Server 2000 Stored Procedures. Doe anyone know of anyway I can use Excel VB to trigger a sequence o these? I will also need to be able to pass a 'keyfield' (i ParticiapantID)parameter from Excel to the Stored Procedures, is thi possible to achieve? Alternatively, if I cannot get to trigger Stored Procedures, I may b forced to export the data to Access first then trigger a sequence of M Access Queries. Again, can a query be triggered from Excel? I guess could somehow open the .mdb and have an autoexec in Access trigger th queries. Ideally Access wo...

Pass Parameter to stored procedure in pass thru query
How do I pass a parameter to a stored procedure in a pass thru query? I'd like to enter the parameter in a control on a form and then run the pass thru query and pass the parameter to it. I've tried this, but haven't been successful. Any ideas? Thanks. On Sun, 8 Jul 2007 18:04:26 -0500, "SAC" <sac@somewhere.com> wrote: >How do I pass a parameter to a stored procedure in a pass thru query? > >I'd like to enter the parameter in a control on a form and then run the pass >thru query and pass the parameter to it. > >I've tried this, b...

You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com
You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com ...

CLR stored procedure fails to rollback the transaction?
I created the following stored procedure. It inserts a row into the Headlines table. If it fails, it returns -1 with the error that sql server returned. So far, everything seems to be working right except the rollback part. Is there any reason why the rollback fails? [code] using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Transactions; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void InsertHeadline(string Title, string Des...

OnInitDialog and passing argument
Hello, I want to use a dialog for three different purpose so every time dialog should come up with a different situation. How can I pass argunment(s) to the OnInitDialog window message of the dialog ? Is it possible? Thanks Smith "JSmith" <jsmithmitra@yahoo.com> wrote in message news:u4ajmu$nEHA.3900@TK2MSFTNGP10.phx.gbl... > Hello, > > I want to use a dialog for three different purpose so every time dialog > should come up with a different situation. How can I pass argunment(s) to > the OnInitDialog window message of the dialog ? > Is it possible? The...

How to pass a parameter to a userform
Hi, I need to both open a useform and pass a parameter to it. How do I do this? I'd like to do the following but the Show method doesn't take this kind of parameter. Userform1.Show(param) Cheers Gromit -- Gromit ------------------------------------------------------------------------ Gromit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=928 View this thread: http://www.excelforum.com/showthread.php?threadid=320229 Create a public variable in the userform, say called myVar, and then Load Userform1 Userform1.myVar = "SomeValue" ...

part of procedure not executed by button
Hi all, In the NG I saw earlier questions about residual "down arrows", I mean that the filter arrows in the top row will not (all) disappear after finishing the filtering. Saw no usefull answer in the NG. Maybe it occurs when Excel (I use Excel 2k with XP) gets "overloaded" because the spreadsheet contains very much formulas, connections to other complex sheets or workbooks with graphs (or should I say charts?) etc. Perhaps an area of memory gets clotted with all kinds of scribblings, like with exhausting the memory space for system resources under Windows 3.11. When reac...

Pass/Name not recognized
When I try to get my e-mail through outlook express, I enter my name an my password and both are correct because they work online but i outlook it keeps asking me for my Pass/Name. I currently have sb yahoo dsl. Please respond if you know how to fix this ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

by passing SMTP
Hello, On my e-mail system here we use an external company called " postini " which filters our spam. We keep getting spam. My partner said its because it is comming in VIA smtp or other ways by passing postini. Is there a way to accept e-mail that comes only from the Postini IP Address? Please let me know ASAP. Thanks On your firewall, modify the rules to only allow SMTP connections from Postini. You can work with Postini to obtain a list of the IP's they send from. When using Postini, it's also a common practice to remove all MX records except those entries for...

Passing the id to a function
Hi, I have a CStatic instance as follows: CStatic label1; label1.Create("", WS_CHILD | WS_VISIBLE| SS_BITMAP | SS_NOTIFY, CRect(100+i*(15+32), 100, 60, 60), this, static_id); And I have a corresponding ON_STN_CLICKED(static_id,ThisFunctionClicked); But I am not able to do this for dynamic values for the static_id. For example if I have two CStatic instances, each with a different static_id. But I want to call the same function ThisFunctionClicked for both. But the ThisFunctionClicked needs to know which static_id was invoked in order to make decisions. Can I pa...

Pass variables to Worksheet_SelectionChange
I have the following code which works well as it stands. My problem is that I need to vary the range depending on conditions set in other code. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit If Not Intersect(Target, Worksheets("Room").Range("P11:T39")) Is Nothing Then With Target If .Value = Chr(252) Then .Value = "" Else .Value = Chr(252) .Font.Nam...

How to execute Stored Procedure from QSRules.Session object in .NE
Last time I accidently posted question under wrong subject.. My question is: I am building COM add-ins to POS with VB.NET I'd like to use QSRules.Session.Database to run stored procedure But I don't know how. Procedure has one input parameter and the other is output parameter. Can anyone know how I should do that? Thanks, Ene ...