If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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 |
#2
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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. -- More info, please ... Jeff Boyce Access MVP |
#3
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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. -- More info, please ... Jeff Boyce Access MVP |
#4
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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? -- (still) More info, please ... G Jeff Boyce Access MVP |
#5
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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? -- (still) More info, please ... G Jeff Boyce Access MVP |
#6
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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? -- Good luck Jeff Boyce Access MVP |
#7
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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 "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... 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? -- Good luck Jeff Boyce Access MVP |
#8
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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. -- Good luck Jeff Boyce Access MVP |
#9
|
|||
|
|||
Default values to load up automatically in a form based on value entered in another form
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Viewing a report from a form based on an ID# entered on form? | Richard Horne | Setting Up & Running Reports | 2 | August 3rd, 2004 05:49 PM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
auto entry into second table after update | Tony | New Users | 13 | July 9th, 2004 10:42 PM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |
How to Automatically Resize Form Controls When Resizing a Form | Kevin Myers | Using Forms | 1 | June 19th, 2004 10:30 AM |