1 / 8

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.

leane
Download Presentation

VBA Form Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBA Form Techniques

  2. 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

  3. 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) • ViewacFormView constante.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 opensacWindowModeconstant e.g.: • acWindowNormal (default) • OpenArgs use to set OpenArgs property of form

  4. 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

  5. Code to list forms in database Private Sub Form_Load() Dim objAO AsAccessObject Dim objCP AsObject Dim strValues As String Set objCP = Application.CurrentProject ForEach objAO In objCP.AllForms strValues = strValues & objAO.Name & ";" Next objAO lstForms.RowSourceType = "Value List" lstForms.RowSource = strValues End Sub

  6. 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.

  7. NotInList Example 1 (adding new value to value list) PrivateSub cboEmpTitle_NotInList(NewData AsString, Response AsInteger) 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 EndIf EndSub

  8. 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

More Related