How use form/subform for two purposes

We have a form that is used as a work order form.  The form also has a sub-form.  Both the form and the subform have several fields in their record sources.  Now we also want to have a quotation process.  The quotation form will look exactly like the work order form but of course will have it's own record sources.

What I'm trying to figure out is this... can I use the same form/subform for both the quotation and the work order?  What I picture is two command buttons on a form.  One of them opens the form/subform for quotes and the other one opens the form/subform for work orders.

But is there a way to dictate which record sources are to be used for the form/subform depending on which command button was clicked via VBA code?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Short answer: Yes

There are several ways of doing it. One way could be to use the OpenArgs when opening the form
docmd.OpenForm "frmTest",,,,,,"Table1"

Open in new window

docmd.OpenForm "frmTest",,,,,,"Table2"

Open in new window

And then in the forms Open event you could have:

This would essentially bind the form to either table1 or table2.

Of course this can be made more advanced if required, but thats the basic principle.

Hope that helps.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can set recordsources on the fly if you want, and use a form with different tables, but personally I'd just make a copy of it and call it something else (frmQuote).

I've found over the years that logic can get fairly convoluted between what you need for a quote and what you need for a work order despite that they have many fields in common.

 For example, for a quote, you need a salesperson, but not have a supervisor assigned to it until it becomes a work order, at which point a sup is required.

 That's a simple example, but I think you get my point...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Yes, you can do what you are asking - BUT -How a form looks is only the tip of the iceberg.  What about all the event code?  Will it be the same for both uses?  Once you start to deviate, you should probably go with separate forms.
Helen FeddemaCommented:
Another way to go is to make two subforms with different record sources, place one right on top of the other, and then make one visible and the other invisible, from some appropriate event on the main form or a control.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.