Passing Parameters between Subroutines

[John Quinn]

Sorry Guys, I rarely use Sub-Routines.

I want to continue where I left off.

Do not know what is wrong with the code below.



Private Sub WeekNo_GotFocus()

Dim W As Byte

W = 18
HoldWeek (W)
Me!WeekNo = W

End Sub
Public Sub HoldWeek(W As Byte) ' Comes in Correctly

Dim db As DAO.database
Dim rst As DAO.Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("Select * From WeeklySchedule")

rst.MoveLast
W = rst!WeekNo ' This is the number 9

End Sub

The correct Week # is 9 and it is stored correctly. When I return to the
calling Sub-Routine it stay at 18. I cannot get it to go to 9.

Is it because I am using Got Focus?

Any Help Appreciated

Thanks in Advance

JQ

 

[John Quinn]

Pieter;

I changed the sub-routine to a function and no change. It does the same
thing.

Public Function HoldWeek(W As Byte)

Dim db As DAO.database
Dim rst As DAO.Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("Select * From WeeklySchedule")

rst.MoveLast
W = rst!WeekNo ' This is a 9, but when it returns to the call
it is 18.

End Function

 

[Tom van Stiphout]

On Thu, 22 Apr 2010 17:28:01 -0700, John Quinn

That seems impossible.
Replace the code with:
'W = rst!WeekNo
W = 9

Btw, you should also close a recordset after use:
rst.Close

Also, in the calling routine the parentheses are redundant:
'HoldWeek (W)
HoldWeek W

Also, if your plan is to return a value from HoldWeek, I would suggest
you do it through the function return value:
Me!WeekNo = HoldWeek()
Public Function HoldWeek() as Byte
HoldWeek = 9
End Function

-Tom.
Microsoft Access MVP

 

[Paul Shapiro]

I think you have to explicitly specify passing a parameter by reference if
you want to return a value in the parameter.

Public Sub HoldWeek(ByRef W As Byte). There's no point to making HoldWeek a
function unless the function returns the value.

Or, you can use a function as:
Public Function HoldWeek() As Byte
...
HoldWeek = rst.Fields("WeekNo") 'Return the value as the function
return value.
End Function