Using the same form with different record sources based on button clicked

[Thomasso]

Hello everybody.



I have a database where I store customer orders and supplier orders at the same time. These orders are very similar so the form can look the same. I have these forms:

• orders overview
• order card
• order details subform
• data entry forms

There are buttons for adding a new order, editing an order, navigation etc.

In the main menu, there are buttons: CUSTOMER ORDERS and SUPPLIER ORDERS. I want to set record sources for all those forms based on the button an user clicked on in the main menu. How do I approach this?

I also need specific objects to have their default value set based on the button.

One solution would be to create duplicate forms but I have a feeling that there is a more elegant way.

Thanks,
Tomas

[CJ_London]

you haven't said how your form works - so assuming they choose which type of order on the button, I assume they then select a customer or a supplier as appropriate in a combo box. you presumably have a table for customer/supplier details which will include a flag to indicate which type they are, so set the rowsource to include a criteria based on this flag.

behind the customer button, so code would be something like (change names/types to suit)

me.cboChoose.rowsource="SELECT EntityID, EntityName From tblCustSupps WHERE EntityType='customer'"

and behind the supplier

me.cboChoose.rowsource="SELECT EntityID, EntityName From tblCustSupps WHERE EntityType='Supplier'"

Or perhaps if your entity type is a number you could use an option group rather than buttons

[Thomasso]

Sorry, should have been more clear. The forms work like this:


This is the orders overview form, I have 2 queries, one shows supplier orders, the other customer orders. I want to set one of the queries as the record source for this form (based on the button).

In addition, I want to change the label in the header and a caption of the second button.


When I click the button on the left (next to an order), this form appears. It's the Order Details form. I don't think this needs to be modified. The button at the top says "edit information".


This is my data entry form, it is accessed via the orders overview form by clicking on Add a new order button or clicking on Edit information button in the details form. I need the row source of the yellow combo box to be either a list of all the customers or the suppliers, based on previously clicked button.

Tomas

[CJ_London]

same principle applies - but either to the recordsource of the form or the filter

[Thomasso]

Thank you.

Where should I put the code? What event? And when and how do I change the nested form's properties? (for example the data entry form upon clicking on a button in the main menu).

Also, is it ok to write:

Me.Recordsource = "MY QUERY"

?

Tomas

[CJ_London]

Where should I put the code? What event?

it would be the click event for each button

Also, is it ok to write:

Me.Recordsource = "MY QUERY"

yes, but if you have spaces you need to use square brackets - "[MY QUERY]"

better to not have spaces

[Thomasso]

I tried to implement it like you said, got this error:



This is the code:



What went wrong?

Thank you
Tomas

[CJ_London]

don't know without knowing more about the combo you are trying to update - perhaps the fields are the wrong way round and you are trying to apply text to a numeric bound control

[Thomasso]

I tried to play with bound controls, no result. What additional information do you need to determine the problem?

Thank you,
Tomas

[CJ_London]

a copy of the db - remove unnecessary tables, forms, queries etc. then compact and zip the file

[Thomasso]

Here it is: https://webshare.cz/file/6K15b3dX8s/db-forms-zip

Just click on "Stáhnout bez VIP". Cannot upload it here for some reason.

Tomas

[CJ_London]

regret cannot access that site - my AV software won't permit me acess. You should be able to upload to the forum - click on the 'go advanced' button

[Bulzie]

To change a caption on a button and also change what the button does based on the caption, it would be something like:

If Forms![YourForm]![YourButton].Caption = "Show Form 1" then

Docmd.OpenForm "Form1"

Forms![YourForm]![YourButton].Caption = "Show Form 2"

Else

Docmd.OpenForm "Form2"

Forms![YourForm]![YourButton].Caption = "Show Form 1"

End If