Link versus Query Criterion (A97 / 2K)

Topic

New Reply

I have one form with a couple of subforms on it. Until now I have been using the link master / link child relationship to present only the appropriate records on the subforms.

I have just started to implement a scheme where new records for the table that feeds the subform can be entered by clicking on a command button to open a modified version of the subform (a little larger, for one thing, with a couple of extra data fields). The VBA routine needs to determine how many records are displayed on the (other) subform in order to know whether a particular control is active and can supply information to the data entry form. I also wanted to have an indicator of how many records are included in the subform since only a couple are visible at any time.

I was unable to do either of those things (any pointers will be appreciated ), but when I wrote a query to extract all of the data fields from the tables that feeds the subforms and set the criteria in the query to the control on the main form I was able to count count the number of records returned by the query.

Since the query returns only the records that would be displayed with the Link Master and Link Child fields set in any event, I was able to use the query results and delete the Link fields in the subform details. So far everything still works , but I wonder which of the two approaches (criteria in query versus link Master / Child) is preferred? Is there a difference in speed or likelihood of failure down the road?

Reply | Quote

Replies

Why would you use a second form to enter records when the subform is already open? It looks like a lot of unnecessary work to me, especially since you then have to requery your subform to display any new records after you close the second form.

Reply | Quote

Boy – are you trying to get LOGICAL on me?

It’s a good question, though. The subform reports insurance policies in force for projects we have dealing with, but only displays some of the information – the carrier and the expiry date as a quick “heads up” that there is a policy in force.

We actually want to track a lot more – the policy limits, date issued, date in force, broker, etc. The idea is that people will be looking at the main form with the subform in it and that will trigger them to enter any new policy documents received, or they will see that the last policy has expired and chase it with the appropriate owner – and then enter the details when available. When opening the data-entry form it defaults to in-force and expiry dates being one year after the most recent policy, coverage amount being the same, and so on….

Reply | Quote

OK, that makes sense. Now, what exactly do you need help doing? If you want to pop up another form for data entry, that is different from popping up another for to display more details. It isn’t clear to me what rules you want to use to limit the records in the popup form or what capabilities you want to enable there. What isn’t working for you now?

Reply | Quote

Charlotte – I think I raised some red herrings in my previous messages. I am able to pop up the forms I want with some default values set. The subforms are displaying the right data. My question was more theoretical.

I can write a query that returns a number of records and use that query (or a base table) to supply data to a subform. Since I want the subform to only display records related to the currently active record on the main form, I would normally impose a Parent / Child link relationship so the subform is appropriately restricted.

Alternatively, I can write the query with a criterion set to match the current value of a control on the main form. In this case the query results are already restricted to those related to the active record on the main form, and I can feed results of the query to the subform without establishing the Parent / Child link. I am not sure if doing that will be faster or slower (or no different) than having an unrestrained query with the P/C link on the form. I am also not sure if one approach is inherently more robust than the other.

If the first approach is better, I would like to count (and display) the number of records in the query (or table) with a value in a particular field that match the value of the control in the main form, because the subform only displays a couple of records out of what may be a large number and my users have complained that they don’t know (in this case) how many projects a particular developer has submitted for consideration. I have patiently explained to them that this is a feature not a bug – but they complain anyhow.

I was wrestling with

Dcount(“*”, “qrydevelopProposal” , “[qryDevelopProposal].[company] = ” & forms!frmDeveloper!CompanyName)

and various iterations, but got nowhere.

Reply | Quote