Automatically Update Form Field with Parent Value?

[jnoonan22]

I need to accomplish something i figured would be rather easy in Access, but i can't figure out how to do it and haven't had any luck googling it. In putting together a tooling projects database for my company to use, i am trying to make it easy to navigate and enter data using forms, now that others will be inputting data (I have been only user of DB up to now).



I know how to create a form and nest subforms into it, and establish the master/child relationship that connects them via relationships, but my forms get way too busy doing that. Instead, I've learned via YouTube how to create datasheet forms with hyperlink fields that, when clicked, open up a more detailed input form using either the openForm w/ filter macro or a BrowseTo macro, and then the form can be set to open up into a dialogue mode. Unfortunately, i can't seem to figure out how to maintain the parent field data when trying to establish this method of data input. A simple example probably explains my problem better...

Tooling Projects is the master table, and each project number is unique. I accumulate costs incurred related to each project via Purchase Orders, so purchase order table is connected to projects via a one-to-many relationship. In my current design, when i view my open projects, i can see all the purchase orders currently accumulated within that project (via OpenForm and filtering cmds). However, if i click on the blank/new line to create a new PO under that project (in a datasheet view), a blank PO input form opens up, but without any data in the project field. I'd like to somehow have that value pre-populate when the new PO form input dialogue opens, so the user doesn't have to look back at the unique project number in the parent projects table and re-enter it manually for each new Purchase Order. Obviously, under nested subforms, this happens automatically with master/child relationship, but i can't figure out how to accomplish this using hyperlinks that open new forms.

Thanks in advance for any help!

[June7]

What do you mean by the forms 'get way too busy'? Are there multiple subforms? Did you consider placing subforms on pages of a tab control?

If you want to open independent detail forms, will need code to pass the ID and populate the new record. There are several ways to accomplish this. I use only VBA. Try code in the detail form Current event:

If Me.NewRecord Then Me!Project = Forms!Form1name!ID

I've never used hyperlink to open form. I use command button and DoCmd.OpenForm which has an OpenArgs argument that can be used to pass info to the opening form.

DoCmd.OpenForm "formname", , , , , , Me.ID

Then code in the detail form Current event:

If Me.NewRecord And Not IsNull(Me.OpenArgs) Then
Me!Project = Me.OpenArgs
End If

[Missinglinq]

What he said!!!

Especially the part

"What do you mean by the forms 'get way too busy'?"

Just because they seem 'way too busy' (whatever that means) doesn't mean that this isn't the appropriate approach! If I found myself in a situation that required, say, four or five, or more subforms, and using a Tabbed Control, placing each Subform on a different page, simply wasn't a doable thing, I might consider opening a separate form, passing the ID as June7 suggested. But in a situation requiring only one or two Subforms, that's the way I'd do this! Given the info you've provided (which I realize may not be complete) it sounds like you're over complicating things! Whenever possible, leave the driving to the Access Gnomes!

Linq ;0)>

[jnoonan22]

Wow thanks for such a quick reply! "Way too busy" does indeed mean multiple subforms, and numerous fields for each subform that quickly clutter the screen. I guess i was sort of inspired or turned on to the hyperlink method when viewing how-to videos on youtube, because i like being able to automatically open a Project form in datasheet format and have it automatically filtered to show the 10 or 15 tooling projects currently ongoing (filtered by plant location of course, of which there are 5 with tooling). You quickly see the list of projects and click on the one you currently are looking into and from there "dive into" the details with more dialogue-driven input.

Maybe I'm using incorrect terminology as well; I'm setting a field to appear LIKE a hyperlink, with on-click event procedures that open/filter/browseto forms. I also use object box that filters for open projects, closed projects, or all projects and a list selector that allows to filter by location. I'm currently using a navigation form to help navigate around my database...

I guess i'm not quite understanding your code references; my VBA experience is limited to Excel macros and functions. The macros in Access i've used were using that macro-interface writer thing...

[jnoonan22]

Okay thanks for the input! You all are probably right. I'll see if i can re-design my form with a few tabs for each project where i can accumulate costs (Purchase Orders > Purchase Order Lines), Project Notes, and Project Revenues. I just want the other eventual users whom I'm about to grant access to (no pun intended) to be able to navigate, input, and reference as easily as possible. Thanks again for the input!

[June7]

Excel macros are really VBA procedures. Macros in Access are very different.

I did not mean to imply that opening an independent detail form is not appropriate, but as Linq said, let Access drive whenever possible.

I do have one situation where I open a detail form for easier data entry/edit. I did not want to use a tab control on the detail form for organizing other controls and therefore the detail form is actually bigger than the main form. Then I have a situation where a main form has 5 subforms organized on a tab control.

What exactly do you not understand about the suggested code?

[jnoonan22]

Well, for starters, are you referring to code in the script editor (Visual Basic) or as an embedded macro under the event tab in the properties window to enter this code...

If Me.NewRecord... Is me the name of the database, form, table... (I'm sure having to ask this basically means I'm way in over my head here). I know what you are implying semantically (somehow saying that the new Project Field value is equal to the project field value of the form that was currently open, but I guess i don't follow the VBA syntax you are using). LOL, ya I'm in over my head. I'll try the tab approach as a form within my navigation form, and maybe use a split form to get the data sheet view at the top from which to navigate currently open projects. I do appreciate your input though!!

[June7]

I use only VBA and that is what I provided.

Me is alias for the form or report the code is behind.

The code is saying: If this is a new record then populate the ProjectID field with the ID from main form.

I also don't like and don't use the Navigation form/control object.