How to create Dependent Data Validation in Access form

Question

I have table "Report" contain 2 column, first "Report Name" Second "Letter Name". I have already created validation for "Report Name". Now I need validation for "Letters Name".

I believe below can be describe my requirement. If a user select Report Name "ABC" from drop down, then my requirement is, under the drop down for Lettter Name user should only be able to select lettter name associated with report "ABC" (i.e. "A", "B", "C").

Report   Name	Letter Name
ABC	A
ABC	B
ABC	C
XYZ	X
XYZ	Y
XYZ	Z

Can someone help me to design the above requirement.

Answer 1

So you have a form where you enter the report name and the associated letter. And you need make sure that the letter entered is a single character, upper case letter, correct? In that case I would use an Input Mask rather than validation. You can create an input mask that would only allow a single upper case letter.

Answer 2

I am assuming you meant A or B or C if ABC was selected.

One solution would be to offer a dropdown, from which naturally only one option can be selected.

Set the RowSourceType to "Value List" and the below air code will populate the RowSource. Of course I had to guess at some of the object names:

dim rs as dao.recordset

set rs = currentdb.openrecordset("select * from Report where [Report Name] = '" & Me.cboReportName & "'")

dim strRowSource as string

while not rs.eof

  strRowSource = strRowSource & "'" & rs![Letter Name] & "';"

  rs.MoveNext

wend

Me.cboLetter.RowSource = strRowSource

rs.close

set rs=nothing