MSACCESS FORM LOGIC

Question

Good day. When a form is open and there are required field entries, comments, combo box, list etc. Is it possible to make a requirement meaning if "Yes" is typed as the text for "Approval" then the next field which is AVP Name is required to be chosen from a drop down before it can be submitted? Best, CP

Answer 1

You can correlate the two combo boxes by referencing the FP Approval control as a parameter in the AVP Name controls RowSource. Requery the latter in the AfterUpdate event procedure of the former, so that it then lists the available options if FP Approval is "Yes", otherwise its list will be empty. You'll also need to Requery the AVP Name control in the Form's Current event procedure. This won't enforce a selection in the latter of course. That should be done by means of a table level ValidationRule, as I described earlier.

Answer 2

You should also be able to enforce this by means of a table level ValidationRule, which will mean that the constraint is enforced however a row is inserted into the table or edited.  In this case the rule would need to stipulate that:

([FP Approval] = "Yes" And Not IsNull([AVP Name])) Or ([FP Approval] = "No" And IsNull([AVP Name]))

If you do change [FP Approval] to a column of Boolean (Yes/No) data type the Boolean constants TRUE and FALSE would be substituted for the string expressions "Yes" and "No".

For more complex constraints which can't be enforced by a ValidationRule or other means the table can be given a CHECK CONSTRAINT.  For an illustration of this  take a look at Constraints.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

This little demo file illustrates a number of means of constraining data in Access.

Answer 3

You can find examples of data validation in the two Northwind templates. That could be adapted to your situation.

Answer 4

Good day. When a form is open and there are required field entries, comments, combo box, list etc. Is it possible to make a requirement meaning if "Yes" is typed as the text for "Approval" then the next field which is AVP Name is required to be chosen from a drop down before it can be submitted? Best, CP

In case you have more than one required fields, the best event to use for data validation is the Form's BeforeUpdate event.

Answer 5

Yes,

As an example you can use the Lost Focus event of the AVP Name control to test

Something like:

If Me.txtFPApproval = "Yes" Then

If IsNull(Me.cboAVPName) Then

    MsgBox "A selection is required"

    Me.cboAVPName.SetFocus

End If

End If

Notes,

You need to use the actual names of your controls.

its not a good idea to use spaces in Object names.

Name is a reserved word in Access and shouldn't be used as an object name.

Recommendations:

If FP Approval is a Yes/No response, then using a boolean (Yes/No) field might be more appropriate. If there are other choices than Yes/No, then using a combobox would be better

Person names should be broken down into, at least, first and last. Its easier to concatenate to display a full name then extract the parts of a name. I generally use 5 fields for person names; Salutation (Mr Ms), First, Middle, Last, Suffix (Jr,Sr)