Control data input

[Rick]

I have the following forms set up based on five tables:
Main form (Contacts)
ContactID PK (autonumber)
ContactName Text
ContactCredential Text
.....etc.

AddressTypes (subform1) linked to Contacts (main form) via
AddrTypeID PK (autonumber)
AddressType Text (ie. home, cottage, Corporate)
CompanyId FK (long integer lookup on Co. table)
ContactId FK (long integer)
.....etc.

AddressDetails (subform2) linked to AddressTypes (subform1)
linked via AddrTypeID
AddrDetID PK (autonumber)
CivicAddressLookup FK (long integer lookup on addresses)
SpecialLabel1 Text
SpecialLabel2 Text
DateChanged Date
AddrTypeID FK (long interger)
....etc

The form is thus:

Contacts
AddressTypes (a subform of Contacts)
AddressDetails (a subform of AddressTypes)

I am attempting to prevent users from entering an address
type on subform AddressTypes and then failing to add any
details in the AddressDetails form.

Presently they can add an address type and advance to the
next record without adding details in the AddressDetails
table/form.

I have attempted to use OnFocus, BeforeUpdate, Current
events, etc. to force the user to provide important info
in the details form before saving the AddressType record.
It appears just moving into the AddressDetails form fires
a save of the AddressType record. If after moving the
focus to the Address Details form the user can press
escape a few times and exit.

I have tried putting IsRequired properties on fields in
both tables and this does not prevent the user from
neglecting to enter data in the AddressDetails table.

I do not have a lot of experience with Access but is there
any suggestion on how to force complete data input?

TIA

 

[Arvin Meyer]

Try this (untested):

Using the AfterUpdate event of Subform1, set the focus on subform2 and wite
theAddrTypeID in:

Me.Parent.NameOfSubform2.Form.txtAddrTypeID =
Me.NameOfSubform1.txtAddrTypeID

Me.Parent.NameOfSubform2.Form.txtAddrTypeID.SetFocus

If it doesn't quite work that was, save the AddrTypeID from subform1 to a
variable, move the focus to subform2, then write it from the variable.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Rick]

Hello Arvin:

I finally got the following code to produce the required
result.

Private Sub Form_AfterUpdate()

If [Forms]![Contacts]![AddressTypes]!AddrTypeID > 3 And
[Forms]![Contacts]![AddressTypes]!CompanyID < 1 Then
MsgBox "You must enter a Company for this Address
Type", , "WBLI user input required"
Me.CompanyID.Visible = True
Me.CompanyID.SetFocus
End If
Forms!Contacts!AddressTypes!AddressDetails.SetFocus
Forms!Contacts!AddressTypes!AddressDetails!AddrTypeID
= HereSerial
Forms!Contacts!AddressTypes!AddressDetails!
CivicAddressLookup.SetFocus
End Sub


Function HereSerial()
Forms!Contacts!AddressTypes.SetFocus
HereSerial = Me.AddrID
End Function


However, I am still having difficulty in maintaining the
validation of data in the AddressDetails(subform2)

1. Once the user has entered an address type and a
company name (if applicable) in the AddressTypes
(subform1) the AfterUpdate event above correctly saves the
record; writes the AddrTypeID in the detailed record from
the HereSerial variable; and moves the focus to the
CivicAddressLookup field. So far so good.

2. I have set the validation rule on the
CivicAddressLookup field to > 0 which prevents the user
from saving the detailed record without entering a valid
Civic address.

3. However; if the user presses escape they can move the
focus back to the AddressType field in the AddressTypes
(subform1) and move to the next record, effectively saving
a record in the AddressTypes table without a corresponding
record in the AddressDetails table.

What should happen when the user "opts out" of saving a
detailed record in subform2 with a valid civic address the
focus should be set back on AddressTypes (subform1) and
the record automatically deleted. The user should not be
able to abort the deletion of the record but forced to
start the entire entry over from scratch.

Rick