conditional data entry access to fields in forms

[Glenn Blank]

In Access 2003, is there a way to make access to certain fields on a form
conditional? I only want data entry to be possible in certain fields if
another field contains a particular value. In fact, I would like for those
fields to not show up on the form at all unless the condition is true. For
example, in a db for a bibliography, certain information is required if the
source is a website which would not be relevant if the source were a book. I
want to include a field in which the user enters what type of medium the
bibliographic source is, and then have the fields specifically relevant to
that type of source show up on the form.

Can this be done in the design of the form? Or alternatively, is there a
way to make the input mask conditional in table design?

 

[Allen Browne]

This can be done in a form, provided it is in Form view (not Continuous or
Datasheet.)

There are several pieces to the puzzle:
a) Use the AfterUpdate event procedure of the ResourceTypeID field (the one
where you choose it's a website or whatever) to set the Visible or Enabled
property of the other controls.

b) Use the Current event of the form to run the same code (so the fields
show/hide as you move record.)

c) Use the Undo event of the form to reset the form (based on the OldValue
of ResourceTypeID.)

d) Include error handling to cope with the case where you are trying to
hide/disable the control that has the focus (typically moving focus to
something safe, and trying again.)

Since you want to call this code from 3 places, it makes sense to write it
as a separate function. Then in ResourceTypeID_AfterUpdate and Form_Current:
Call ShowHide()
and in Form_Undo:
Call ShowHide(True)

As it is going to run each time you change records, and setting a property
is much slower than reading it, the example below shows how to only change
the onese that are really needed:

Private Function ShowHide(Optional bUseOldValue As Boolean)
On Error Goto Err_Handler
Dim lngTypeID As Long
Dim bShow As Boolean
Dim iErrCount As Integer

With Me.ResourceID
If bUseOldValue Then
lngTypeID = Nz(.OldValue, 0)
Else
lngTypeID = Nz(.Value, 0)
End If
End With

'Show AccessedDate for websites only.
bShow = (lngTypeID = 7) 'say 7 = website
With Me.AccessedDate
if .Visible <> bShow Then
.Visible = bShow
End if
End With

'Show PublicationDate for books or journals.
bShow = ((lngTypeID = 4) OR (lngTypeID = 5))
With Me.PublicationDate
if .Visible <> bShow Then
.Visible = bShow
End if
End With

'and so on.

Exit_Handler:
Exit Function

Err_Handler:
Select Case Err.Number
Case 2164&, 2165& 'Can't disable/hide the control with focus.
Me.ResourceTypeID.SetFocus
iErrCount = iErrCount + 1
If iErrCount < 3 Then
Resume
Else
Resume Exit_Handler
End If
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Handler
End Select
End Function

 

[Glenn Blank]

Thanks so much. I am actually in over my head, as I have not yet how to
write macros in ACCESS. But I understand the general concept of what you are
describing, so I am sure I will be able to follow it once I learn VBA syntax.

I see your name quite a bit on these discussion boards. I appreciate the
direction you have given.

Thanks again.