Default values to load up automatically in a form based on value entered in another form

  • Thread starter Thread starter Anthony Dowd
  • Start date Start date
A

Anthony Dowd

Hi

I have two forms "Form 1" and "Form 2". "Form 2" opens by clicking on a
command button on "Form 1".

I successfully used the following code in the Load event of "Form 2" to
create default values that are automatically entered into a field on "Form
2" called "Field2" depending on the value selected in a combo box called
"Field1" on "Form 1". The value in "Field1" is itself automatically inserted
depending on the value inserted into another field on "Form 1" called
"FieldA".

My database is set up so that when the user selects a value in "FieldA" on
"Form 1", the value in "Field1" updates automatically and a message box
appears telling the user they must enter information in another form. The
user clicks on ok and the form opens. The form is closed after the data are
entered and the user is taken back to "Form1". The user then clicks on the
command button to open "Form 2".

********************start code******************
Var1 = DLookup("[Field1]", "Table1", "[ID]=" & Me!ID)
Var2 = DLookup("[Field2]", "Table2", "[ID]=" & Me!ID)

If (IsNull(Var2)) Then
If (Var1 = "Selected Value in Combo Box") Then

Forms![Form2]![Field2] = "Default value in Field2 on Form 2"
Else
etc...

End If
End If
*****************end code*************************

This code inserts the correct value into "Field2" when I open "Form 2" and I
can edit this value if required and the new value will 'stick'. However, if
I go back to "Form 1" and change the value of "FieldA" so that "Field1" also
changes, I find the old value in "Field2" when I open "Form2" again. I have
tried inserting similar code into the change event of "Form2", but this then
prevents the user from amending the data in "Field2". Any suggestions?

Thanks
Anthony
 
Anthony

You've described in detail "how" you are attempting to do something. If you
provide a description of "why" you are trying to do this, the newsgroup
readers may be able to provide an alternate approach.
 
Oops! Of Course

Thanks Jeff

What I'm trying to do is have default values load up automatically in
"Field2" of "Form2" so that, when a user selects a value for "Field1 of
"Form1" and clicks on the command button to open "Form2", s/he does not have
to enter any data in "Field2" unless the default values are to be amended or
replaced.

The problem occurs when I change the value of "Field1"/"FieldA" in "Form1"
in that the value in "Field2" does not update when I open "Form2" again.
Thanks again
Anthony
********************start code******************
Var1 = DLookup("[Field1]", "Table1", "[ID]=" & Me!ID)
Var2 = DLookup("[Field2]", "Table2", "[ID]=" & Me!ID)

If (IsNull(Var2)) Then
If (Var1 = "Selected Value in Combo Box") Then

Forms![Form2]![Field2] = "Default value in Field2 on Form 2"
Else
etc...

End If
End If
*****************end code*************************

Thanks
Anthony
 
Anthony

Thanks for further explanation. I guess I was looking for even more
understanding of an underlying business need.

Your description still seems to focus on the how -- forms, fields, and so
on.

Given that forms in Access are a way to display underlying data, it would
help me understand better if I had some idea of what kind of data you have
bound to those two forms. It would help even more if I understood what you
would want to have happening to the underlying data. It would help me the
most if I had an idea of your data structure.

For instance, from your description, it almost sounds like you will have two
copies of the data you are trying to "cross-load", in two different tables.
Are you trying to store the same fact twice?
 
Hi Jeff

I have two forms which have two underlying tables. The first form contains
data on patients. "Form 1" contains details on the operation performed on
them. When a value is selected from a combo box on "Form1" called "Field1",
a value is automatically entered in another combo box called "FieldA". Both
combo boxes are based on value lists rather than tables.

The third form (Form2) contains details about post-operative care and also
has an underlying table. It is this form/table into which I want to have
values automatically inserted depending on the value contained within
"fieldA". These values that I want inserted into the fields of the Post Op
form are contained only within the code written below, not in any table. All
values are stored in their underlying tables and then gathered into a query,
which is the data source for various Word templates which are then merged to
produce Word documents.

********************start code******************
Var1 = DLookup("[Field1]", "Table1", "[ID]=" & Me!ID)
Var2 = DLookup("[Field2]", "Table2", "[ID]=" & Me!ID)

If (IsNull(Var2)) Then
If (Var1 = "Selected Value in Combo Box") Then

Forms![Form2]![Field2] = "Default value in Field2 on Form 2"
Else
etc...

End If
End If
*****************end code*************************

I think that explains my data structure
Thanks Jeff
Anthony
 
Anthony

Here's my paraphrase, to see if I "get it"...

You have patients.
You have operations.
You have post-op care.

You want to generate "reports" (whether via Access reports or via Word
documents).

There's really no need to copy information from one table to another. I
know you described copying from one form to another, but forms are just
windows to the underlying data.

If your data is structured in a well-normalized design, you can use a query
to pull the pieces together from the tables, for use in "reports".

Could you now provide a brief description of the tables involved, including
what kinds of data elements are in each?

For example, perhaps something like:

tblPerson
PersonID
FName
LName
DOB
... (other "person" facts)

tlkpOperation (the "types" of Operations possible)
OperationID
OperationTitle
OperationDescription

trelPerformedOperation
PerformedOperationID
PatientID (the PersonID of the patient)
OperationID
PhysicianID (the PersonID of the physician)
DatePerformed
... (other facts about the operation)

trelPostOp (assumes Post Op relates to a specific operation)
PostOpID
PerformedOperationID (the ID of the performed operation)
?Fact1
?Fact2
... (you didn't indicate what kinds of post-op conditions you needed to
record)

Please note that this is just a hunch, based on what you've described so
far. Also note that you can determine, via joins in a query, which person
was under which operation, was under which post-op. No need to record the
same fact in multiple tables.

Or have I completely misunderstood?
 
Hi Jeff

I'm not recording the same info in two separate locations. For a given
operation, there are post op instructions that are the same for that
operation in most instances. Therefore, it would reduce data input by having
the post op instructions for a particular operation load up automatically
when the post-op form is opened. So the user selects the type of operation
from a combo box on the Operation form and when the post-op form is opened,
the post-op instructions for that operation are displayed automatically. So
it is not the data in the operation form that is copied to the post-op form.
The value in the post-op form is merely displayed automatically depending on
the value selected in the operation form. For example, a patient has a heart
transplant. My database is set up so that the postop instructions are
displayed automatically. For example, let's just say that in 95% of cases,
those patients who have had a heart transplant will need to have their feet
tickled three times a day for two weeks after the operation. Well, I want
this info to be displayed automatically when the operation, heart transplant
is selected in the operation form. This part I was able to achieve. It is
when I change the operation type from say a heart transplant to a
colonoscopy that the post-op instructions on the post-op form do not change
to the "default values" unless I manually delete the old entries, go back to
the operation form and then reopen the post-op form. The change event does
not seem to work, because this prevents me from editing the entries in the
5% of cases in which such editing is required.

I really would like to fix this problem but I can live with it if I have to
because most operations, once selected on the operation form, will not be
changed.

Thanks Jeff
Anthony
 
Anthony

Now we're getting closer to a description of the entities and relationships.
This may seem like a move backwards, since you already have your forms
determined.

I think you'll find a general consensus in these newsgroups that you need to
get the data right before you determine how you'll use forms to
display/enter it. It sounds like you're working this problem from the other
end "I have forms, tell me how to make them work!".

If the relationship is 1:1 between operation and post-op instructions (and I
doubt this -- I don't think you do only ONE thing for post-op for any given
operation), you could keep that info in one table.

If one operation can have many post-op instructions (I suspect this is
true), you have a 1:M relationship. After creating the appropriate table
structure, you can use main form/subform construction to display and capture
this.
 
Hi Jeff

I did as you suggested and placed the fields that I want updated
automatically on the same form as the "conditional" field (ie the one that
controls which value should appear in the other fields). It works fine!
Thanks.

I have been working backwards as you suggested, at least to some degree. The
basic structure of my database is two "node" forms (with underlying tables)
containing several combo boxes. The first "node" form/table contains combo
boxes with their row sources as separate tables. The second "node"
form/table tends to rely more heavily on combo boxes which have value lists
as their row sources. The use of value lists as row sources in the second
"node" was necessary (at my level of knowledge when creating these
forms/tables) because I had difficulty navigating to/from the "branch"
forms/tables on the second "node" form/table at a given value of the related
field. I think another major factor in my decision to use value lists in the
combo boxes on the second "node" form/table was because my database was by
now becoming very complicated. The details required for each operation
differs, therefore it was necessary to set up the second node so that a
different form opens when different values in the OperationType combo box
are selected. I thought (at the time) it would have been too complicated to
have tables/forms for each of the combo boxes and also for each of the
operations. Anyhow, I will give some thought to restructuring my data in the
near future. I wonder if this paragraph makes any sense at all.

Thanks again.
Anthony
 
Back
Top