First paste the following module into your database. It was originally
developed by Stuart McCall and myself and allows you to pass multiple
arguments via the OpenArgs mechanism, either as a literal tokenised list
or
named arguments. In this case the last method is used.
''''module starts''''
Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="
Function Arg(buffer, idx) As Variant
If IsNumeric(idx) Then
i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
i& = InStr(i&, buffer, ASSIGNOP) + 2
Else
i& = InStr(1, buffer, idx) + Len(idx) + 2
token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
End If
Arg = Mid(buffer, i&, InStr(i&, buffer, token$) - i&)
End Function
Function Argname(buffer, idx) As String
i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
Argname = Mid(buffer, i& + 1, InStr(i&, buffer, ASSIGNOP) - (i& + 1))
End Function
Function ArgCount(buffer) As Long
ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET
End Function
Sub AddArg(buffer, Argname, argval)
If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer,
1)) + 1)
End Sub
Sub AddArgList(buffer, ParamArray Tokens())
For i& = 0 To UBound(Tokens)
AddArg buffer, i& + 1, Tokens(i&)
Next
End Sub
''''module ends''''
In your calling forms modules move the code which creates the rows for
each
of the students into a separate procedure in the form's module:
Public Sub DoStuff()
' your code goes here
End Sub
By declaring the procedure as Public this exposes it as a method of the
form, which can be called from outside the form while its open.
To open the frmStudentsPresent form you'll use this code which passes two
named arguments to the dialogue form:
Dim args As String
AddArg args, "ClassCode", is_classcode
AddArg args, "CallingForm", Me.Name
DoCmd.OpenForm "frmStudentsPresent", _
WindowMode:=acDialog, _
OpenArgs:=args
The frmStudentsPresent form's module will contain the following code along
with any other code you already have there. CmdCancel is the name of the
Cancel button.
''''module begins''''
Option Compare Database
Option Explicit
Dim blnCancelled As Boolean
Private Sub cmdCancel_Click()
blnCancelled = True
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Close()
Dim args As String
args = Me.OpenArgs
Me.Visible = False
If Not blnCancelled Then
Forms(Arg(args, "CallingForm")).DoStuff
End If
End Sub
''''module ends''''
The way it works is that if the Cancel button is clicked the module level
Boolean variable is set to True. The form's Close event procedure then
conditionally calls the DoStuff procdure in the calling form's module. It
identifies which form's module is the right one from the CallingForm named
argument passed to it.
Note that when you refer to the value of is_classcode in the
frmStudentsPresent form you now refer to it as the named ClassCode
argument
by means of the Arg function like so:
Dim strClassCode As String
strClassCode = Arg(args, "ClassCode")
In case you are interested in using the module to pass arguments as a
literal list (you can't use a list to pass variables) rather than as named
arguments here's the opening blurb from a little application which
demonstrates its use:
"This application demonstrates how lists of values can be passed to a form
by means of the OpenArgs mechanism. Values can be passed individually as
named arguments:
Dim args As String
' add some named arguments
AddArg args, "First", "Apples"
AddArg args, "Second", "Pears"
AddArg args, "Third", "Bananas"
Or as an arguments list:
AddArgList args, "Oranges", "Peaches", "Grapefruit"
The list is then passed to the form with:
DoCmd.OpenForm "frmMyForm", OpenArgs:=args
In the form's module the values can be extracted like so:
Dim args As String, i As Integer
args = Me.OpenArgs
' get some named named arguments
Debug.Print Arg(args, "First")
Debug.Print Arg(args, "Second")
Debug.Print Arg(args, "Third")
' get some arguments by ordinal position
Debug.Print Arg(args, 4)
Debug.Print Arg(args, 5)
Debug.Print Arg(args, 6)
' list all arguments
For i = 1 To ArgCount(args)
Debug.Print Argname(args, i), Arg(args, i)
Next i
' get count of arguments
Debug.Print ArgCount(args)"
Ken Sheridan
Stafford, England
Laurel said:
I have a little form that is opened by a couple of other forms, which
allows
the user to mark the students in a class present or absent and the data
is
saved in the database. This info is then used by the main form to
retrieve
students. So in the main forms I have this sort of code.
DoCmd.OpenForm "frmStudentsPresent", acNormal, , , , acDialog,
is_classcode
ls_sql = "Select * from tblStudents Where class_code = '" &
[cboClass_Code] _
& "' AND Absent = False Order by First_Name, Last_Name"
Set irst_Students = CurrentDb.OpenRecordset(ls_sql)
ETC.
And when control returns to the main form, it proceeds with its work. I
would like to be able to put a CANCEL button on the little form, so that
the
user can decide, after getting into the little window, that they don't
want
the main window to proceed with its work (which is to create rows for
each
of the present students). How can the main form know that the user
clicked
CANCEL in the little window instead of OK? In other languages, there
would
be the ablility to Close with Return, which would set a variable that the
main form could read. But I can't find such a thing in VB. Any
suggestions?
tia
las
