Full Version: I am posting my question again
UtterAccess Forums > Microsoft® Access > Access Forms
Good Morning to everybody! Last time I posed this problem, and I did not get any solution. I would very much appreciate any help/clue in this regard.
I have two tables. Table 1 has following fields: Test (primary key), TestRequirement, TestFrequency, TestOrganization. There is a associated form, Form 1
Table 2, specific record for particular test, has following fields, Test, Test Requirement, DateOfTest, PersonDone, Results, Comments. There is a associated form, Form 2
Table 1 has 1 to many relationship with Table 2. I have added a command button in form 1. I first open form 1 to see a particular test type, say Voltage test. When I click the command button, I navigate to form 2, which is specific about a particular test, say Voltage test, and the common field such as Test Type, here Voltage, Test Requirement should automatically be filled up from Table 1. But this is not happening. What I did in Form 2, for example, in 'Test' text box I have added, =[Test]![Table1]. However, when I open the Form 2 from Form 1, the 'Test' field shows #NAME. I do not know what to do. I would appreciate you help very much to sort out the issue.
Thanks in advance!
Can you make Form2 a subform? If so, you'd just need to link the subform to the main form by Child and Master Fields.
Is Form2 supposed to show existing records, or are you adding new records in Form2?
If you're adding new records, and need to open Form2 separately, put the following code in Form2's OnOpen event:
Me.txtTest = Forms!Form1.txtTest
This assumes that your text box on Form2 is named txtTest, and your control that has your test value in Form1 is called txtTest.
I added the code, as you suggested. However, I am getting run time error on syntax. I do not know why.
Please attach a copy of the database so I can look at how you have it set up and what's going wrong.
I have tried to attach the file. But it is not allowing with .mdb extensio. I am typing my codes, you can look and if possible, please comment
e.SubDiscipline = Forms!PT Summary Form.SubDiscipline
I have used the exact field and form name
Spaces in Field and Form names always cause problems.
You should also change the name your form controls to be different than the field name.
would suggest the following changes:
SubDiscipline text box to txtSubDiscipline (on both forms)
PT Summary Form to frmPtSummary
Now your code would look like this:
Me.txtSubDiscipline = Forms!frmPtSummary.txtSubDiscipline
If you choose not to make these changes, you'll just need to change your code to:
Me.SubDiscipline = Forms![PT Summary Form].SubDiscipline
If you want to post files to this forum, you'll have to ZIP them first.
Thanks for your help. I will do what you suggested and come back to you.
nce again for your help.
Why store the same data again? That's violating the rules of normalization and a big reason you're having this problem. You can always get the Test Requirement via a query. It's what a relational database is supposed to do for you.
You can check out the link in my signature for more on normalization.
I think I am not storing the data again. I am entering the data in two places (Form 1 and Form 2) and storing in two places (Table1 and Table 2). The Table 1/Form 1 is about a specific test type, such as Voltage, Current etc. The Form 2/Table 2 is used to enter/store a record for a particular test for example Voltage, when the testing has been done, who has done, any comments etc. The two tables have 1 to many relationship. I feel this kind of database is very common. You open one form, Form 2, from Form 1, to enter specific record. My desire is that the fields, which are common in both the forms and for which data have already been entered through Form1, should automatically appears in Form 2. But I am unable to do so. I have done what DoubleD suggested. But I am getting syntax error. Please suggest/give some clues.
Displaying on the second form is fine. I'm referring to storing the Test Requirement again in the 2nd table. That you do not want to do, since, if I understand correctly, the requirement describes the test itself, and not the results. You would have to store the PK from the 1st table, but not both.
The easiest way to do what you want is to have a form/subform arrangement instead of a two form arrangement. You could link the form/subform on a parent/child basis on the TestID field. You would not have to display the test information again in the subform, but could if you wanted to. Even if using a 2nd form, I'd still use a form/subform arrangement, with the parent form being unbound with only a couple of unbound text boxes on the parent form, with the default values of these set to:
Then link the subform to the main form on the Test ID.
That is exactly what Noah is talking about. The ONLY value from Table 1 that should be in Table 2 is the TestID. This is your Foreign key and allows the relation of test results (table2) to test type (Table1).
There is NO coding necessary with this if you use a mainform/subform. The mainform is bound to table 1 and the subform to table2. When you display a particular test type in the mainform you will see all the test rusults inthe subform.
If you post a zipped version of your DB, I'll take a look at it and see if your design does violate the rules of normalization. I'll also look at what's happening with your attempt to enter Test results.
Then How do you enter the data for a particular test? Through a subform?
Yep, you sure do. See the attachment.
Hello All, I have got the clue from other MS Access discussion forum, and solved my problem. The suggestion is so simple. In my Form 2, the fields, which are common to Form 1, I have added the followings =[Forms]![Form1]![field Name from Form1]
really want to thank all of you for your help. At the end all have helped me. What a relief!!!
Where did you add that? That works if Form1 ia open. If you added it as the data source of the control, that's OK, but if you did something different you still have a normalization issue.
eally, this is just a mainform/subform things that requires NO coding.
I have added it as the data source of control. It is working.
Thanks ScottGem
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.