Dynamically selecting subform by query result?

[hewligan]

Hi,

I am reasonably new to Access (coming from a Unix background) and I have been having many problems that do not seem to be covered in the massive manual I bought or on the interactive MS help. So, here is a problem I came up against today:

I am creating a simple stock order system. An ordered item can be given many different statuses to indicate where in the process of delivery it is (ordered, shipping, in stock, etc.) Well, I have a form to display the order data on a user query (search by surname, etc.), but depending upon what status the order is in at that point in time there are extra fields I need to display. I have thought of two ways to do this:
1) Create a form with a dynamic subform. The subform would be generate based on what status the order is in.
2) Create one form for each status and call up the relevant form based upon what status the order is in.

The problem is that neither of these seem to be simple to create in Access. Obviously both boil down to the same issue: Picking a form/subform to display depending upon the data returned from a query. I have tried to find a way to do this, but to no avail. Surely this sort of thing is pretty common? I would really appreciate if someone could tell me how to open a form based upon the values returned from a query.

Thanks for reading my long and rambling post.

Hewligan

 

[JoeMiller]

Number two is the easier way to go. I've done this on many occasions. Make your separate subforms the way you need them for each status. And in the event that the user searches for the status/order/surname whatever, change the form that the subform displays. Here's how you would change the subform form in code.

Me!SubFormObjectName.SourceObject = "SubFormName"

Change the blue terms to match your environment.

HTH Joe Miller
joe.miller@flotech.net

 

[hewligan]

Thanks Joe. I think I know what you mean. I will try and actually get it working tonight.

Hewligan