Unanswered: Forms and subforms: how do I automatically update autonumber fields?
Hi folks, hope someone out there can give me a hand with this problem!
I'm writing a database in Microsoft Access 2000 for a hospital Emergency Room, to collect information about cardiac arrest patients. The main data entry interface is set up as a form called [Add New Record] with three subforms: [Pre-hospital variables], [Emergency Department Variables] and [Outcome Variables], linked by an autonumbered field in the [Add New Records] form called [Arrest ID]. Each form draws its data from a similarly named table. [Arrest ID] is a primary key in the main table and also a primary key in each of the the related tables, so a 1-to-1 relationship exists between the [Add New Records] table and the [Pre-hospital variables], [Emergency departmnt variables] and [Outcome variables] tables. The reason I'm using 1-to-1 relationships rather than a single table is that I'm gathering several hundred discrete data points, and a single table would be unmanageably large.
Okay, so when the user goes to add a new record, the word "Autonumber" appears in the "Arrest ID" field in the [Add New Records] form. If I start by filling in the fields in this form, the record is assigned a number and I can subsequently go to the subforms and fill in details no problem. The problem arises if I go straight to one of the subforms without initially filling in values in the main form. The [Arrest ID] remains at "autonumber" and the table underlying the subform has a null value recorded as its [arrest id]. The unfortunate and inexpert end-user is faced with confusing "Table cannot have null value as its primary key" messages. I've tried making one of the fields in the main form a required field, so as to force the user to enter some value before proceeding to the subforms, but this doesn't seem to work.
In a nutshell, I want a way to ensure that some data is entered in the main form before proceeding to a subform, in order to ensure the integrity of the linked fields.
Any ideas anyone?
In a situation like this the solution maybe to make the subforms not enabled, until the required fields on the main form are complete. This will force the user to enter required information first as otherwise the subform cannot be completed.
Real quick...Maybe you could try this (not tested). The idea here is that when a subform trys to recieve focus, the AreFieldsFilled function is fired which in turn checks to see if ALL TextBoxes within the main form contain something (not Null). If any one field within the main form is Null then focus is reverted back to the main form to the control that actually had last focus. The subform can't gain focus unless all fields within the main form are filled in. All you need to do is ensure that when your DB is started or when changing to a different record via navigation, that a field (control) within your main form gets focus. This can be easily accomplished by placing something like:
within the On Current event code window of your main form (or any other way you might want to do it).
Copy and paste this Function into the code module of your main form:
Private Function AreFieldsFilled()
Dim Ctrl as Control
Dim Frm As Form
Dim Fcs As integer
Set Frm = Forms![myMainFormName]
For Each Ctrl In Frm.Controls
If Ctrl.ControlType = acTextBox Then
If IsNull(Ctrl) Then Fcs = 1: Exit For
If Fcs = 1 Then Screen.PreviousControl.SetFocus
Copy and paste this line into the On Enter event property box for each SubForm Control on the Main Form (not the subform itself):
Thanks a lot guys, I actually figured it out yesterday on my own. Wrote a bit of code and set it to the "On enter" event of each of the subform controls, checking to see if the [Arrest ID] in the main form was still NULL (i.e, no data entered) and if so providing an error message and returning focus to main form.
CyberLynx, I appreciate the code, u went to a lot of trouble!