0

My Microsoft Access 2013 DB contains three main tables: A primary Suppliers table, a Products table, and a slave table to the products if a product is a matrix (multiple variations, like colours) instead of a single standalone product.

I have already created a rather nice interface with Access 2013 using this tutorial: https://www.youtube.com/watch?v=4ei0PFrDUa0 to create a drop-down populated from the Suppliers to limit the products displayed as being from that supplier. I have also been able to create a link within the embedded products datasheet (https://www.youtube.com/watch?v=GopSdt4QNck) to launch for any one product a form to edit that product (a link instead of a button because datasheets cannot contain buttons).

My difficulty now is that I want to put beneath the embedded products form a button that not only launches a blank form for adding a new product, but also to pre-populate the foreign key of the product with the ID of the supplier from the drop-down menu.

So keep in mind I now have three forms, and I want to add a fourth. One form is the overall “Suppliers” form which contains a drop-down. That drop-down modifies an embedded form within it (“Products Display”) to show only those products that are for that supplier selected from the drop-down. The embedded form has got rows that show only the two most important pieces of information about the product (the code we use in-house to identify it, and its name) and a link. This link is tied to that product and launches a “Product Edit” form that pre-populates the form with that specific product for editing (and has all the details from that table).

So those are the three forms. I want to now add a button to the first form (the one with the drop-down) that launches a “Product Create” form similar to the “Product Edit” form, but only with the essential fields, and with the foreign key (the Supplier ID) pre-filled to the last supplier selected in the drop-down before the button was clicked. This means that the button must dynamically change every time the drop-down is used.

Problem is, I haven’t the foggiest notion how to do this within Access 2013. Using a standard button with the wizard does allow me to spawn a new “Product Create” form, but there is no way to pre-populate the foreign key using just the wizard itself. Plus, I do not know how to have the button auto-modify each and every time the drop-down is used.

René Kåbis
  • 3
  • 1
  • 3

1 Answers1

1

Lets see if I can explain this clearly.

Suppose you have two forms, with attached tables:

  • Parent Form
  • Child Form

Each attached table is defined as:

Parent:

ParentID (AutoNumber)
ParentName Text

Child:

ChildID (AutoNumber)
ParentID Long Integer
ParentName Text

Each form has all of its fields displayed. The parent Form has a button to add a child.

VBA Code on Parent Form is:

Private Sub AddButton_Click()
  DoCmd.OpenForm FormName:="Children", view:=acNormal, OpenArgs:=Me.ParentID
End Sub

VBA Code on Child Form is:

Private Sub Form_Dirty(Cancel As Integer)
  Me.ParentID = Me.OpenArgs
End Sub

So how does this work? We are passing the ParentID to the Child form via the OpenArgs parameter. (http://msdn.microsoft.com/en-us/library/office/ff836583.aspx)

Before any record is modified, the Dirty event is triggered (when the user types the first character into the new record). We defined the Dirty event to set the ParentID.

Theoretically, you can populate more than one foreign key or value. You would need to pack them into a delimited string, then unpack them on the child's onDirty event.

Hope that helps.

DHW
  • 1,176
  • 1
  • 15
  • 30
  • I have selected your answer as being correct, because the logic is sound. I have found another method of doing this action which is just as convenient and doesn’t open a new window. – René Kåbis Oct 18 '13 at 18:12
  • I tore out the second form, put in a tabbed interface, put the second form into the tabbed interface (re-creating the master and child settings) and put the pure entry form into the second tab and set the same master and child fields. The only additional thing I had to do was change the Data Entry attribute to Yes, so that the form itself never brought up pre-existing content -- only a blank form for entering data. That way, even if I was on that tab, switching Suppliers from the drop-down menu always gave me a blank entry form but with different foreign keys (for the appropriate supplier). – René Kåbis Oct 18 '13 at 18:19
  • I had assumed by "launches a 'Product Create' form" you meant a new form in its own window - that's why I outlined that approach. Glad you got it figured out though. – DHW Oct 18 '13 at 18:51
  • Actually, I had originally wanted the Product Create form in its own window, but discovered that duplicating the load functionality of the Product Display form (by adding it to the same Suppliers form as the Product Display form) ended up being simpler. It might not end up being as efficient as a separate window, but for now it works. I had flagged your answer so that others with the same objective would benefit. I still may have need of something like that in the future of this project, too, so thanks. – René Kåbis Oct 21 '13 at 15:25