Popup subform - linking fields (programatically)

[PrimaryKey]

Hello All,



First off let me say these forums have helped me tremendously over the last month. I'm relatively new to Access, most of the previous databases I've designed were pretty simple and required very little VBA (a weakness for me). I have strong programming skills in other areas such as PHP, so I'm hoping that will help me somewhat here.

Anyway - here's what I need to do. I'm building an order system. When a user goes to add a Product to the OrderItems subform on the Orders main form, a button will appear depending on the type of Product that is selected because some Products require a configuration. The configuration that is needed is very long. I built it using a tabbed form and it includes 6 tabs, each with numerous questions. Because the Order form is simple and smaller than the Config form, I wanted to display the Config form as a popup subform that links the OrderItems table's ID field to the OrderConfigurations table's OrderItemID.

So, basically, when a user selects a Product requiring a configuration the "Configure" button is shown, and when clicked launches the modal/popup Configuration form. When the user saves the Configuration form I want the Configuration form to store the OrderItemID for the OrderItem that they just configured.

I have read a few threads on here that have informed me that there is no simple way to do this similar to linked the master/child fields of a main form/subform, but I'm wondering if this can be done programmatically when the user clicks the "Configure" button (which only appears when Products of a particular type are selected from Products dropdown).

I did think of one way of doing this, by including a textbox on the Configurations form called "OrderItemID" with a default value of =[Forms]![OrderItems]![ID] - but I'm not sure if this is a good way of accomplishing this.

Any help/advice is much appreciated.

[Minty]

You can pass a value to the form being opened using the OpenArgs() property of the docmd.openform statement.
It is then available on the opened form using Me.Openargs .
More here http://www.baldyweb.com/OpenArgs.htm

[PrimaryKey]

You can pass a value to the form being opened using the OpenArgs() property of the docmd.openform statement.
It is then available on the opened form using Me.Openargs .
More here http://www.baldyweb.com/OpenArgs.htm

Wow - thank you! That's EXACTLY what I was looking for. I saw that same suggestion on another thread I found here by searching, but was under the impression this was set using a macro command (and I just couldn't find it). Thank you!

One followup question/ I want to create an order viewer form that allows users to edit the status of orders (Confirmed/Paid/Shipped, etc.). I want it to look like a datasheet but was thinking of building it as a continuous form, but I wanted to include a button for each orderItem that has a configuration set. I know I cannot do this using a continuous form, even programmatically, since there's only one instance of the button, thus I was showing/hiding it for all orderItems, not just the orderItem that had the config set.

In essence, when a user is viewing the order queue page and is looking at an order record, each orderItem will be listed along with Qty/Price. But then items that also have a config set will have an "Edit Config" button.

Is this possible?

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

Is this what you're trying to do?

http://www.baldyweb.com/wherecondition.htm

[PrimaryKey]

You can pass a value to the form being opened using the OpenArgs() property of the docmd.openform statement.
It is then available on the opened form using Me.Openargs .
More here http://www.baldyweb.com/OpenArgs.htm

Thank you Minty!

[PrimaryKey]

Post 3 was moderated, I'm posting to trigger email notifications.

Is this what you're trying to do?

http://www.baldyweb.com/wherecondition.htm

@pbaldy - Thank you for the explanation. May I ask what you mean when you say my post was moderated? Does that mean I did something incorrectly/wrong?

And yes, sort of. With one catch. Is it possible to display the link or button that opens the second form for only some items on the form?

Basically I want users to be able to go back into orders after they've been saved to see what items were added, etc. So they'll be viewing an order, which will include a list of OrderItems. Some OrderItems may have a Configuration as well, whereas others won't. I wanted to create a way for users to access the Configuration settings for OrderItems that have a config. I was hoping to create a datasheet-style view that listed all of an Order's OrderItems, and for items that have a Configuration I'd include a button or link that users can click to open the Configuration form for that item, and for OrderItems that do not have a Configuration, no button or link would be shown.

I tried building it using a continuous form and used some VBA to toggle the visibility of the button based on the type of Product selected for each OrderItem, but after finishing it I realized there's only one instance of the button on the form, it's either hidden or shown for ALL OrderItems, rather than just for OrderItems of a particular type that have a configuration.

Is this something that's possible?

[pbaldy]

No, you didn't do anything wrong. Because you're a new member and there was a link in your second post (even though it was in a quote), the forum software moderated it. It won't happen after you have 10 posts or so.

Code wouldn't work for that on a form, as you found out (it can on a report). One option is Conditional Formatting, if there's a test that would work for it.

[PrimaryKey]

Thank you for explaining that. I really appreciate it.

Does the conditional formatting you mentioned apply to forms, or reports? Forgive me if that's a stupid question. All of my database design thus far has been using MySQL or Oracle, with a PHP front end. I've been working on this database for 4 weeks. I did some work with Access in college, but nothing like this. I watched about 6 different Lynda video courses, but am still pretty new to all of this.

Just trying to figure out how users can go back and review an OrderItems Configuration after creating an order, without showing the "Edit Configuraiton" button or link for OrderItems that don't have any configuration.

[PrimaryKey]

No, you didn't do anything wrong. Because you're a new member and there was a link in your second post (even though it was in a quote), the forum software moderated it. It won't happen after you have 10 posts or so.

Code wouldn't work for that on a form, as you found out (it can on a report). One option is Conditional Formatting, if there's a test that would work for it.

I just had an idea. What if the form just listed all of the OrderItems like a datasheet and there was a column called ConfigID that showed the ID# of the Configuration for each OrderItem (for some items this would be blank). The ID# could be a link that when clicked would open the Configuration form for that ConfigID.

I have no idea how to build that yet - but is something like that even possible?

[Minty]

Yes that would work. If the ID is null then simply don't open the form.
You could format it to be blue underlined so it "looks" like a hyperlink and that something would happen if you double clicked it.

I tend to use the double click event rather than click, in case someone just clicks into a field by mistake. Just a personal preference.

[PrimaryKey]

Thank you for getting back to me Minty. Would it be appropriate here to use VBA to establish the action that occurs when a user clicks on a ConfigID #? I was thinking of using the DoCmd OpenForm method you had shown me where I use OpenArgs to pass the ID of the ConfigID record to the Configuration form so that it opens with that record pre-selected. I'm just wondering if I do it that way and use VBA, will it set that value for every record in the continuous form individually (like I want), or will it apply a single action to every record on that form, kind of like what happened when I tried to include a button where the visibility was toggled based on a record parameter.

Or were you thinking I should use a datasheet instead of a continuous form?

[Minty]

Yes a simple docmd.openform with openargs will do exactly what you want here.
The Id of the current record would be available with Me.TheControlName of the thing you want to reference.

[PrimaryKey]

I really can't thank you enough. I've spent days searching through my Access 2016 Bible looking for the answers to these questions - I just wish I would have registered here sooner and posted my question - you guys REALLY know Access.

Thank you again!