Subform datasheet view/Clickable

[umair434]

Hi,


I have a combo box on a form which displays records in a subform (datasheet view). the combo box is a list of containers - And the subform shows the different products numbers that are in that containers..

Now, sometimes the user is required to know on which container was a particular product carried before.


Is there a way to click on the Product# field in the datasheet view which triggers another subform/subreport that shows this information?


any other suggestions are highly appreciated. Thanks!

 

[Pat Hartman]

In the double-click event of the product # control, open a form that shows the current record.

DoCmd.OpenForm "detailform", acNormal,, "ProductID = " & Me.ProductID

I prefer the double-click event because it is less likely the user will invoke it by accident. Use italics and the underline to identify this as a "clickable" option.

 

[umair434]

Thank You! it works really well. Just wondering, is there a way to open the form as a subform underneath? like it becomes visible only if there is there is a matching product?

 

[Pat Hartman]

Forms don't automatically resize so this is more of a problem. You would need a blank area on the form where the subform would appear or you could overlay other controls with the subform control. I like opening a separate form. You can set its property to popup so it opens as a separate window rather than a tab if you are using A2007 + in tab view.

 

[umair434]

I just revised the requirements of this application and realized that the second subform does not have to be invisible. How would I approach the issue that:

the user clicks on the productID field in the 1st subform, and this populates the second subform with the productID selected?


thanks for your time Pat

 

[Pat Hartman]

Why are you duplicating the data? Subforms are linked to mainforms. As long as the master/child links fields are correctly defined, Access automagically populates the FK in the subform when you add a new row.

 

[umair434]

I am not duplicating the data.


Would you mind to have a look at the database if I posted it here?

Here is how application should work:


1) User selects a container # from the combo box on the main form.

2) this popuates the first subform with the list of products (datasheet view)

3) When the user clicks/double clicks on a product #, the second subform (which is located on main form as well) gets populated as well, with the matching product # and other relevent details.


Do I make sense?

 

[Pat Hartman]

To control the subforms, change their RecordSource queries so they reference some control on the main form. This may be visible or hidden depending on your preference. In the click event of the combo or the current event of the subform, "copy" the Key value (if necessary) to the form field and requery the subform.

So the RecordSource query would be something like:
Select .. From .. Where somefield = forms!frmmain!somefield;

then in the click event where you are "selecting" something to display, put something like:

Me.sfrmMySubform.Form.Requery

You may also need the requery in the mainform's Current event depending on what you want to initially show in the subforms.

 

[umair434]

Thanks! that guided me in a right direction and I almost have what I am looking for.


Just one thing: When the user selects a container number in the combo box, the first subform is populated with multiple product numbers. Each product number has to have a comment (Comment is a field in this subform). Now how can I make sure that users can only save the record if they enter comments for all the product in the first subform?


I tried:

If IsNull(Me!frmsubform.Form![Receiving Comment]) Then

MsgBox "Please comment on the Product", vbOKOnly
Cancel = True

but that only works if no comment has been added for any of the product#.


In other words, code is only run if all comments are filled for every product. Otherwise we get a msg box.


Thanks for being patient!

 

[Pat Hartman]

I presume the reason the comments are in a separate table is because you want to allow more than one. The way relational databases work when you enforce RI (which you should) is the parent row must be saved to the database before a child row can be saved because the child row (a comment) requires the value of the parent's PK to use as a FK. Given that, there really is no way to ensure that a comment ever gets added without going to a lot of trouble - shadow forms and tables and batch updates or unbound forms, we can go there if this is critical but for most cases, it is simply more work than it is worth.

There isn't even an event you can use that would reliablely be triggered where you could verify that a comment was saved.

If you are working with a form in single record view, you could use the form's Unload event to run a query to determine if a comment record existed for the current record and if one didn't, raise an error message and cancel the unload event so the user can't close the form. Of course, the user actually has the ultimate say since there is always ctl-alt-del and if absolutely necessary, the power switch so I don't like to do this.

I might try nagging warnings. Every time the user opens the main form for the first time, I run a query that looks for records without comments and tell him to add the comments.

What I would probably do is "denormalize" the schema and add a checkbox to the parent record that indicates the presence of a comment. I would then modify all the queries so that the record is only available for use if it has comments. To manage this checkbox, I would add code in the AfterInsert event of the comments form and also to the AfterDeleteConfirm event to run a query to count the comments and as long as there was more there was more than 1, I would update the box to true, otherwise I would update it to false.