Query, Form, Code help

[empyrean90]

I am creating a project database and to help reduce the potential for user input errors I want any forms opened off the main form to display the record(s) that match the project currently pulled up and if there is no records to put the project number in the appropriate field on the new form that is opened.

One instance of this is to display the costs for a project. I have a button on the main form that will open the form that displays the results from the query that gathers and calculates the total. I have the following code for the button:

Private Sub Command131_Click()
DoCmd.OpenForm "CalculatedCost"
Forms!CalculatedCost.ProjectID = Me.ProjectID
End Sub

The parameter for the query is set to get the value placed in the ProjectID field on the CalculatedCost form. It works great when I am opening a record that has costs already associated with it but when there is no cost data associated with the open project the form opens as a blank form.

Is there a way to have it display the value that was entered as the parameter into the ProjectID field on the cost form if the query returns no results?

Any help on this would be greatly appreciated.

Ryan

[June7]

Have you considered a form/subform arrangement?

Otherwise, the code would have to create a record for the form to open to or have code that will cancel the form open if no data exists.

[empyrean90]

I did think of that but the problem is I would have 17 subforms on the page which would get really confusing. Also, not every project needs to have a record in each of the subforms and even with the subforms they do not carry the value in the related field over from the main to the subform.

I was able to find a way to do this by using a query, coded button, an expression and some manipulation of the tab order but it seems to me like there is a better route to use than that