VBA Form Techniques Open a form from another

VBA Form Techniques

Open a form from another form Code in Cass module Private Sub cmd. Supplier. Form_Click() Do. Cmd. Open. Form "frm. Supplier. Details" End Sub Code in Standard module Public Function Form. Open(str. Name As String) Do. Cmd. Open. Form str. Name End Function

Open. Form Method • Method of Do. Cmd object • Syntax: Do. Cmd. Open. Form(Form. Name, View, Filter. Name, Where. Condition, Data. Mode, Window. Mode, Open. Args) • Arguments: – Form. Name Valid name of form in current database (Required – all other arguments are optional) – View ac. Form. View constant e. g. : • ac. Normal (default) • ac. Design – Filter. Name Valid name of query in current database – Where. Condition SQL WHERE clause (without the word WHERE) – Data. Mode The data entry mode for the form ac. Form. Open. Data. Mode constant e. g. : • ac. Form. Add • ac. Form. Edit • ac. Form. Property. Settings (default) – Window. Mode The window mode in which the form opens ac. Window. Mode constant e. g. : • ac. Window. Normal (default) – Open. Args use to set Open. Args property of form

Using named arguments Do. Cmd. Open. Form(Form. Name, View, Filter. Name, Where. Condition, Data. Mode, Window. Mode, Open. Args) Want to set value of Where. Condition argument Default values acceptable for all other arguments Method 1: Use commas as placemarkers for omitted arguments Do. Cmd. Open. Form “frm. Supplier. Details” , , , “Co. ID=“ & txt. Co. ID Method 2: Use named arguments Do. Cmd. Open. Form “frm. Supplier. Details” , Where. Condition : = “Co. ID=“ & txt. Co. ID

Code to list forms in database Private Sub Form_Load() Dim obj. AO As Access. Object Dim obj. CP As Object Dim str. Values As String Set obj. CP = Application. Current. Project For Each obj. AO In obj. CP. All. Forms str. Values = str. Values & obj. AO. Name & "; " Next obj. AO lst. Forms. Row. Source. Type = "Value List" lst. Forms. Row. Source = str. Values End Sub

Not. In. List 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 cbo. Box. Name_Not. In. List (New. Data As String, Response As Integer) – New. Data – the text entered by the user – Response – indicates how the event was handled • ac. Data. Err. Display (default) – displays default error message • ac. Data. Err. Continue – use to display custom message • ac. Data. Err. Added – does not display message. Enables you to add value to list.

Not. In. List Example 1 (adding new value to value list) Private Sub cbo. Emp. Title_Not. In. List(New. Data As String, Response As Integer) Dim cbo As Control Set cbo = Me. cbo. Emp. Title If Msg. Box(New. Data & " is not in list - Do you want to add this value? ", vb. OKCancel) = vb. OK Then Response = ac. Data. Err. Added cbo. Row. Source = cbo. Row. Source & "; " & New. Data Else Response = ac. Data. Err. Continue cbo. Undo End If End Sub

Not. In. List Example 2 (adding new record to lookup table) Private Sub cbo. Origin_Not. In. List(New. Data As String, Response As Integer) Dim int. New As Integer, str. Country As String, rst As Recordset int. New = Msg. Box("Add country " & New. Data & " to list? ", vb. Yes. No) If int. New = vb. Yes Then Set rst = Current. Db. Open. Recordset("tbl. Country. Lookup") rst. Add. New rst!Country = New. Data rst. Update Response = ac. Data. Err. Added Else Msg. Box ("The country you entered isn't in the list. Select a country from the list or enter a value to add") Do. Cmd. Run. Command ac. Cmd. Undo Response = ac. Data. Err. Continue End If End Sub