Getting information from a table onto a form

[Guest]

I have a small database with two main tables called Exams and Action Steps.
The relationship between the two tables is that a Exam can have 0 to many
Action Step records, and a Action Step can have 0 or 1 exam. I've created
two forms for entry one for exams and one for action steps. I'm having a
problem on the action step form. The action step table holds the link
between the two tables (exam id which is a unique identifier of exams). What
I'm trying to do is when a user enters that exam id onto the action step
form, I need it to pull information from the exam table on to the action step
form. This is not redundant saving, these values would be more like defaults
for the action step, but not necessarily what the action step needs. I've
been able to fill in those values when there is a match in the exam table.
However, when there isn't a match, I get a error stating "the value you
entered isn't valid for this field." I've setup a macro on the action steps
table in the afterupdate event. The macro reads from a subform I've created
and hidden on the action steps form. The subform comes from the exam table
and has the values I need on the main action steps form. The macro goes from
setting focus on the field it needs to get the value of to using a SetValue
to get the value from the subform. I'm guessing there's an easier way to do
this, but it works when there is a exam id match. Is there any way to ignore
the error that comes up when there isn't a match?

 

[Guest]

I don't think you can do this with a macro. You'll need to use code in the
ExamID control's AfterUpdate event procedure. This would also obviate the
need for the hidden subform as you can simply look up the values from the
Exams table, e.g.

Dim strCriteria As String

' build criterion for finding row in Exams table,
' using NZ function to take account of ExamID
' being Null, e.g. if user deletes existing value
strCriteria = "ExamID = " & Nz(Me.ExamID,0)

' first check that a matching record exists in Exams
If Not IsNull("ExamID", "Exams", strCriteria) Then
' if record exists look up values and assign to fields in current record
Me.ExamName = DLookup("ExamName", "Exams", strCriteria)
Me.ExamDate = DLookup("ExamDate", "Exams", strCriteria)
' and so on for other fields
Else
' if record doesn't exist set values of other fields to Null
Me.ExamName = Null
Me.ExamDate = Null
' and so on for other fields
End If

If any of your object names contain spaces or other special characters
remember to wrap them in square brackets, e.g. [Exam Name]. The above could
be done more efficiently by creating a recordset object which returns a
reference to the relevant row in the Exams table rather than calling the
DLookup function repeatedly, but the code for that is rather more complex, so
I've kept it simple here so as not to cause unnecessary confusion. I've
assumed in the above that ExamID is a number data type, not text. If it were
the latter the value would need to be delimited with quotes characters:

strCriteria = "ExamID = """ & Me.ExamID & """"

Ken Sheridan
Stafford, England