Relationship not working properly

[msday]

I have a simple problem that I cannot seem solve for the life of me. (I've spent hours on this.)

I created a Table and Form called eSPR. The form has a button (called Open Project Form) on it to open up a more detailed form called Projects. (Projects has its own table.)

There are seven common fields that should populate the Projects form. The problem is that when I click the Open Project Form button on the eSPR form, a blank Project form opens with no pre-populated fields.

About the relationships I created: The eSPR table has a primary key called PolicyFormNumber. It has a one-to-many relationship with the PolicyFormNumber field in the Project table.

Can someone help me? Why isn't this relationship working? Thanks very much in advance,

Kathy

 

[JimHorton]

Kathy,
Are you opening the projects for for data-entry? Or to browse through existing? When you call the OpenForm method from the button, are you passing a Where clause? This would bring up an existing record(s) based on the PolicyFormNumber (assuming that the eSPR form was showing a list of the Policyformnumbers and you had one selected, and of course this # was in the Where clause)

If you're opening up a Project form for a new record, you have to either manually pre-populate (meaning, in code) or use Default value's in the form or table property. Please elaborate some...
--Jim

 

[msday]

Jim,

Thank you - you helped me identify the key problem.

I believe the problem is the OpenForm method for the buttom. When I created the button, I went through the following procedures:
Selected Open Form
Selected "Projects" form
Selected Open the form and find specific data to display
Answered &quot;Which fields contain matching data the button can use to look up information?&quot; by selecting eSPRolicyFormNumber <-> ProjectsolicyFormNumber

Obviously, without the exact PolicyFormNumber existing in the Projects table, pre-population cannot occur. It does work if I manually enter the PolicyFormNumber into the Projects table. How can have the PolicyFormNumber automatically pre-populate a specific Project Form (i.e., one that matches the eSPR's PolicyFormNumber?)

I should mention that when I created the Projects form, I combined fields from both the eSPR and Projects tables. On the Projects form, the PolciyFormNumber field is &quot;Primary eSPR.PolicyFormNumber.&quot; Is this the correct way to relate the data between tables?

Thank you for your patience and advice,

Kathy

 

[JimHorton]

Kathy,
I'm not sure if you're form is setup as Form/Subform. With Form/Subform, the prepopulating isn't a problem because if you add a new subform record, that is automatic.

But if your Form eSPR is the 'header' or 'parent' form, and the Projects form is Single form, then you need to assign the value, and you don't necessarily need the espr table in the base query for the projects form. Either way, you need to create the eSPR record before you create the Project record if this will be a new Policyformnumber. But it sounds like you'll always be choosing from an existing policyformnumber from the eSPR form.

That said, when you call the OpenForm method, you should know whether you'll be adding a new detail record or editing existing. Then either use the acFormAdd or acFormEdit constant in the DataMode argument of docmd.OpenForm

Then, if it's Edit, then the project form opens with (assuming you've used the Where clause for PolicyFormNumber) all the records that match. Here you also have the option to Add, by calling docmd.gotorecord acNewrec. If you call OpenForm with acAdd, then you're automatically at the new record.

Now, to prepopulate. Sevaral ways to do this. (again, with form/subform, this isn't needed). You could just refer to the eSPR form and use the DefaultValue property of the PolicyFormNumber (of the detail, or Project table if you have both tables on the form). If you have both policyformnumbers on the form, it will auto-populate the 'parent' (again, if the parent exists). Or you could refer again to the eSPR form in the Open event and say
If me.DataEntry and IsNull(me!txtPolicyFormNumber) then
me!txtPolicyFormNumber = Forms!eSPR!Policyformnumber
end if

You could also use OpenArgs, which is basically just another way to pass a value to a form that's being opened, and pass the policyformnumber.
I hope this helps,
--Jim