Lock in value in header field in continuous form

[shira47]

I'm a total access newbie, it seems like this issue should be easy to solve, but I've been googling all day and can't
figure it out. Please tell me if the question here isn't clear...

I have a continuous form with one field located in the header called PN (Part Number) and a few fields in the detail section of the form that are related to processes/parameters. The form is based on a parameter query and opens to one part number at a time (and there is no reason an employee would be working on two part numbers at a time)

The problem I'm having is every time a new entry is made in the detail section of the form, the PN disappears from the header and the PN is not recorded in the related table...is there any way to lock this information in so it always shows in the form and always gets recorded
in the related table. I thought putting it in the header and not in the detail section would make it consistent...

Thanks in advance

[John_G]

What's happening is that the PN in the header, and the fields on the detail section are all bound to fields in the same underlying table, and more importantly, to the same record in that table. So, when you add a new record in the detail section, the header control moves to the new record as well, and is of course blank. You can do what you want to do, but it will require a bit of VBA code.

Are you familiar with VBA at all?

John

[Missinglinq]

What you're describing here sounds like the classic one-to-many scenario, with the 'one' side being the PartNumber and the 'many' side being the Detail Fields. This is usually handled with a Main Form/Subform set up.

In general terms the Main Form would be a Single View Form and based on a PartNumber Table. The Subform would be, as you’re currently doing, displayed as a Continuous View Form (to better display the 'Detail Fields') based on a Detail Fields Table.

The Fields would typically be something like this:

PartNumberTable
PartNumberID 'Primary Key
PartNumberName
...and so forth

Detail Fields Table
PartNumberID 'Foreign Key
Detail FieldID 'Primary Key
DetailField1
DetailField2
..and so forth

Once you've designed your Main Form and Detail Fields Form, based on PartNumberTable and Detail FieldsTable, respectively, open the Main Form in Form Design View and add a Subform Control to it. The Subform Wizard will walk you through the process. When asked, base your Subform on the DetailFields Form. Seeing that both Tables the Forms are based on have a Field named PartNumberID, the Wizard should ask if you want to link the Main Form /Subform using these Fields. Answer OK and Bob's your uncle!

As you move through the PartNumber Records in the Main Form, Access will automatically update the Detail Fields Records in the Subform and only show those Detail Fields related to that Area.

And when you add a New Detail Record, the PartNumberID from the Main Form will automatically be entered in the related Detail Record.

None of this requires VBA coding, as all the 'heavy lifting' is done by the Access Gnomes by way of the Subform Wizard.

Good luck with your project!

Linq ;0)>