Opening a form with code and going to specific record? Or ...?

[brybril]

Hi, i've searched through the threads and faq's and have been unable to find a solution.

What i have is a form that tells general info about each project. There are also four forms each telling info about status of project, i.e., an "Initial", "Board Review," "Continuing Review," and "Closed" status forms. A project will obviously start at "Initial" and move through the process until Closed.

I want somehow, with a command button perhaps, to allow the user to click on the main form so that somehow s/he can go directly to the correct status form of the 4 above and see the correct record. DoCmd.OpenForm obviously wont take the user to the specific record on the main form.

Perhaps there's a better way than 4 forms. Maybe one "Status" form with a combo box containing the 4 status values? And if they click on, say, Closed, for a project that's not Closed, maybe a message box will display informing that the project has not reached that Status?

Hope this isnt too confusing. Any help/suggestions will be much appreciated.

 

[Maquis]

Here's what I would do. I'm assuming your main form has a place for the user to key in or select a project id. The main form then calls up one of your project forms depending on status.

Do these 4 forms look exactly the same? Differing only by the status of the project? If so, you really need to combine them into 1 form with a status field showing on it.
I'm also assuming that your projects are all in the same table, regardless of status. If not, please consider re-designing your tables so that they are.

If you can combine the forms, then your main form only has to call the 1 project form passing criteria for the project id. If you can't combine the forms then you will probably need a DLookup function to determine the project status and based on the status call the correct form. Something like this:

Dim Criteria as string
Dim StrStatus as string
Dim formname as string

Criteria = "[ProjectId] = '" & me!project & "'"
StrStatus = Dlookup("Status","ProjectTable",Criteria)
Select Case StrStatus
Case "Initial"
formname = "initial form"
Case "Board Review"
formname = "Review form"
Case "Continuing Review"
formname = "Cont form"
Case "Closed"
formname = "Closed form"
End case
docmd.openform formname,,,Criteria Maq B-)
<insert witty signature here>

 

[ChemistZ]

There ia a macro action called Open Form that allows you to specify under what conditions you want it to open the form and your conditions would be something like when the key field on the main form is equivalent to the foreign key field on one of the other tables. If it needs to know which specific table of the 4 then the criteria gets more complex. I think the combo box idea is good but you can still use the Macro action to accomplish the main task and have the command button run the Macro.

Hope this helps

 

[brybril]

Maquis:
Thanks, i'll try that. Actually, the 4 forms are different, showing different criteria, etc. And yes, all the project info comes from one table. I assume when you say combine the statuses you're suggesting using 4 tabs, which is something i considered until i realized that 2 of the status forms already have tabs, so that's definitely outta the question.

But i'll try your code and let you know how it goes!

Thanks!

 

[brybril]

ChemistZ:
Thanks for the idea but i've toyed around with the macro and yes, i think the conditions would be far too complex.