Peg,
There are differrent ways to get your variable data from one Form, Function,
& or Sub Routine to another. When you declare a variable as a Global variable
this simply means that it is now available to any of your code anywhere in
your project. This is very powerful and a wonderful tool to use when you NEED
to. You do not want to make all of your variables global. You can also simply
declare a variable at the very top of your module, class module, or form
module, and then it will be available to any and all Functions / Sub Routines
that are located inside that particular module, class module, or form module.
You can also pass variable data from one Function / Sub Routine to another as
you call it like the following example:
'_______________________________________________________________
Sub DisplayMsg()
Dim varDat As Variant
varDat = InputBox("Please enter a number between 1 and 3", "Pick A
Number")
If IsNumeric(varDat) Then
MsgBox GetMsg(CLng(varDat)), vbInformation, "Your Msg:"
Else
MsgBox "You did not enter a number between 1 and 3.",
vbCritical, "Error:"
End If
varDat = Empty
End Sub
'_______________________________________________________________
Public Function GetMsg(lngMsg As Long) As String
If ((lngMsg < 1) Or (lngMsg > 3)) Then
GetMsg = "You did not pick a number between 1 and 3."
End If
Select Case lngMsg
Case 1
GetMsg = "You picked number 1."
Case 2
GetMsg = "You picked number 2."
Case 3
GetMsg = "You picked number 3."
End Select
End Function
'_______________________________________________________________
As you can see in the above example the function GetMsg is prepared to
accept a variable lngMsg declared as a long data type and then as the
function is called from the sub routine called DisplayMsg a variable is
passed to it (The number picked) and then the function itself returns a value
of string data type that is the message.
There is also an example of an IF statement and a Select Case.
And one more before I call it a night...
You can also pass variables to a form you open in the forms Opening
Arguments called OpenArgs and then the code behind that form can use whatever
you passed in the OpenArgs. You can see this when you use the DoCmd.Open like
this:
DoCmd.OpenForm "frmSomeFormName", acNormal, , , , acWindowNormal,
"MyVariableIamPassing"
Hope all of this helps!
=)
--
~ SPARKER ~
puzzled_Peg said:
Sparker,
Thank you so very much, that was exactly what I was trying to do.
I am so very new to VB programming and so if I want to keep the value of a
variable from one call of the function to the next (not sure if I am wording
this correctly) I declare the variables as global?
sparker said:
Peg,
you will need to loop through the entire recordset with a function.
I placed the following in a new module:
_______________________________________________________
_______________________________________________________
Option Compare Database
Option Explicit
Global gAcctNum As Long
Global gSeqNum As Long
Public Function GetSeq(lngAcctNum As Long) As Long
If lngAcctNum = gAcctNum Then
gSeqNum = gSeqNum + 1
Else
gSeqNum = 1
gAcctNum = lngAcctNum
End If
GetSeq = gSeqNum
End Function
_______________________________________________________
_______________________________________________________
and then I ran the following query against a
mockup table of account numbers to get your
desired result of a sequential counter per acct#:
_______________________________________________________
_______________________________________________________
SELECT tblData.AccountNumber, GetSeq([AccountNumber]) AS Seq
FROM tblData
ORDER BY tblData.AccountNumber;
_______________________________________________________
_______________________________________________________
You can then update a table or use this as a subquery for another query etc...
Hope this helps if I misunderstood please let me know. Thanks,
--
~ SPARKER ~
puzzled_Peg said:
Yes, I do want to add a sequential row count to my query results. I need it
to be a row count per account, in other words, it needs to restart at one
every time my account # changes. I could have multiple rows per account.
I will also remove the duplicate statement and see if that helps.
Any further help would be greatly appreciated.
:
Peg,
would love to help but not sure where to begin. I also get a compile error
with your code for the duplicate declaration in current scope for Function
PrevSeq() where you declare it again as Static PrevSeq. Other than that at
the beginning of your post I got the impression you were wanting to add a
sequential row count to the results of your query. Is that correct? Is that
the reason for your post? I read it through a couple of times and your
question was not jumping out at me very clear. If you have a chance and
nobody else answers it for you please re-word it and I will try again. Thanks
--
~ SPARKER ~
:
I am trying to get a seq# based off the total number of line items that I
have for a particular charge.
Example, patient has 3 charges, I ran a group query to give me a count of
the number of charges.
Next, I have a query that I need to seq a new field so that I have a 1 of 3,
2 of 3, 3 of 3.
Here is my code:
Option Compare Database
Option Explicit
Function PrevSeq()
' Only the variable PrevSeq preserves its value between calls.
Static PrevSeq
PrevSeq = PrevSeq + 1
End Function
' Static function definition.
Static Function NSeq(TtlClaim As Variant)
' All local variables preserve value between function calls.
If PrevSeq <= TtlClaim Then
varSeq = PrevSeq
Else
PrevSeq = 0
End If
NSeq = varSeq
End Function
I want to preserve the value of PrevSeq so that I will know if I am less
than or equal to my total count.
When I try to run the Query, I get a compile error.
Any help would be appreciated. I am and RPGILE programmer and this is very
foreign to me.
ne which