Transcript VBA Form Techniques

VBA Form Techniques
Open a form from another form
Code in Cass module
Private Sub cmdSupplierForm_Click()
DoCmd.OpenForm "frmSupplierDetails"
End Sub
Code in Standard module
Public Function FormOpen(strName As String)
DoCmd.OpenForm strName
End Function
OpenForm Method
• Method of DoCmd object
• Syntax:
DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
• Arguments:
– FormName Valid name of form in current database (Required – all other arguments are
optional)
– View acFormView constant e.g.:
• acNormal (default)
• acDesign
– FilterName Valid name of query in current database
– WhereCondition SQL WHERE clause (without the word WHERE)
– DataMode The data entry mode for the form acFormOpenDataMode constant e.g.:
• acFormAdd
• acFormEdit
• acFormPropertySettings (default)
– WindowMode The window mode in which the form opens acWindowMode constant e.g.:
• acWindowNormal (default)
– OpenArgs use to set OpenArgs property of form
Using named arguments
DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
Want to set value of WhereCondition argument
Default values acceptable for all other arguments
Method 1: Use commas as placemarkers for omitted arguments
DoCmd.OpenForm “frmSupplierDetails” , , , “CoID=“ & txtCoID
Method 2: Use named arguments
DoCmd.OpenForm “frmSupplierDetails” , WhereCondition := “CoID=“ & txtCoID
Code to list forms in database
Private Sub Form_Load()
Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String
Set objCP = Application.CurrentProject
For Each objAO In objCP.AllForms
strValues = strValues & objAO.Name & ";"
Next objAO
lstForms.RowSourceType = "Value List"
lstForms.RowSource = strValues
End Sub
NotInList Event
• Occurs when user enters value in Combo box that is not in
Combo box list
• Limit to list property must be set to “Yes” for this event to
occur
•
Private Sub cboBoxName_NotInList (NewData As String, Response As Integer)
– NewData – the text entered by the user
– Response – indicates how the event was handled
• acDataErrDisplay (default) – displays default error message
• acDataErrContinue – use to display custom message
• acDataErrAdded – does not display message. Enables you to add value to list.
NotInList Example 1
(adding new value to value list)
Private Sub cboEmpTitle_NotInList(NewData As String, Response As Integer)
Dim cbo As Control
Set cbo = Me.cboEmpTitle
If MsgBox(NewData & " is not in list - Do you want to add this value?",
vbOKCancel) = vbOK Then
Response = acDataErrAdded
cbo.RowSource = cbo.RowSource & ";" & NewData
Else
Response = acDataErrContinue
cbo.Undo
End If
End Sub
NotInList Example 2
(adding new record to lookup table)
Private Sub cboOrigin_NotInList(NewData As String, Response As Integer)
Dim intNew As Integer, strCountry As String, rst As Recordset
intNew = MsgBox("Add country " & NewData & " to list?", vbYesNo)
If intNew = vbYes Then
Set rst = CurrentDb.OpenRecordset("tblCountryLookup")
rst.AddNew
rst!Country = NewData
rst.Update
Response = acDataErrAdded
Else
MsgBox ("The country you entered isn't in the list. Select a country from the list or
enter a value to add")
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If
End Sub