Create Button On Tab control with button other form

[mnaem02]

I want to create button on tab control with VBA from different form. When i click to create button than with vba other form will open in a design view and create button on specific tab page.

example i have four tab pages page1 page2 page3 page4 now i want to create button on page 2

-----------------------------------------------

Private Sub pos_Click()
Dim FormName As String
Dim tab_page As pages
Dim frm As form
Dim tab_control as Control
 
FormName = "pos"
DoCmd.OpenForm FormName, acDesign
 
Dim newBt As Control
 
Set frm = Forms!pos
Set tab_control = frm!pos_products
Set tab_page = tab_control!pages(1)
 
Set newBt = CreateControl(frm.name, acCommandButton, Left:=100 + 3000, Top:=500)
 
End Sub

[twinnyfo]

mnaem02,

Welcome to Bytes!

I'm not certain this can be done in VBA, as adding controls to a Tab Control requires that the Tab Control be the active object--And I don't know of a way to "Select" an object through VBA, like one can "select" a cell or a range of cells in Excel.

Some others might know how to do this. Sorry I couldn't be more hepp!

Any ideas, folks?

[GazMathias]

I wouldn't mind understanding the use case of this scenario.

I implemented a solution years ago where forms had several hidden buttons on them that were set visible on load when dynamically loaded configuration data demanded it. Each button was configured to call a handler and pass in it's label. The handler loaded configuration data based on that label for the event from a table and in many cases used eval to run new code stored there. Sadly the ecosystem has shifted and I work in .Net / JS land now and such things are simpler.

Gaz

[mnaem02]

i am trying to make Point of sale system where each tab will be category for example drinks ,burger etc and each category there will be products. these button i will use as products.

i can create buttons on the form and link on the category but it wont be dynamic for example if new category is created than i have to make manual form and add buttons where as if on the tab if i can make buttons than i can handle easily by adding page.

only i am stuck to create a button with vba on tab control ... in internet i have searched but no answer

[twinnyfo]

I would offer an alternative idea:

Create two tables to cover your sales items:

tblFoodCategories
fcID          Autonumber, PK
FoodCategory  Text - Drinks, Burgers, Pizza etc.

and

tblFoodItems
fiID      Autonumber, PK
fcID      FK to tblFoodCategories
FoodItem  Text - Pepsi, Diet Pepsi, Burger, Double Burger, Bacon Triple Cheeseburger, etc.

On your Tab Control, have a page set for each Food Category.

Create a Subform that is a continuous form, based upon tblFoodItems. The only control on the Form is a Command Button, with its caption set to the Food Item:

Me.cmdFoodItem.Caption = Me.FoodItem

Then, insert this subform onto each of your Tab Control Pages, including a filter for each Food Category.

Then, all you have to do is add code to your Sub form that will add food items to your sales invoice (which is beyond the scope of this thread).

If you add additional food categories, all you have to do is add another Tab Control Page, insert the Food Items subform and filter by the new category.

Hope this hepps!

[GazMathias]

Hi,

Imagine if you will a grid of 40 or so unbound buttons on a form, all of which are initially hidden, and a database structured with two levels of category with products that are tied to the outer category in a loose way.

When the form loads it displays on the top row of buttons as many of the categories as it can. Additional Next and Back buttons would be used to move between pages of these categories. Initially 0 - 7 would be loaded, then 8 - 15, etc. The categories could have a view order int column to load the most common first.

When clicking a top level category button, all sub categories will be loaded into as many of the lower buttons as needed and in turn when clicking one of those sub categories all products would be loaded.

Workflow would be:
Meal Deals -> Burgers -> Half Pounder, Chips and Drink -> Child Form appears to edit notes for kitchen, set qty, etc -> Add to order. Back.

Child form calls basket function in module. Module calls public function in form to refresh itself to update order total, qty, etc.

This is similar in theory to the solution I implemented.

Another way would be a column of buttons down one side of the form with a listview on the right. You would use a similar tier loading mechanism to the above but the same buttons would be reused each time and the products to order loaded into the listview.

Workflow is similar execept you click an item in the listview to load the child form. The benefit of this version is you can stuff hidden data into the listview and push it to the child form saving a database call and alloows for richer UI design, too, like long descriptions and back office notes (out of stock, etc).

Each method decouples the UI design from the data, like a web page would, which in all honesty would be my preferred platform for this. Day by day you only need to add data to the database as new categories and products are added and the UI will cater for them.

Each method requires a state machine so the event handlers know which tier / page the form should be rendering.

The native Access 'switchboard' mechanism functions similarly to this, or it used to in older versions. I haven't used one in a long time but when I did I abused the heck out of it. If it still does maybe you could have it build one in a test DB and you can take a look at how it works under the hood and perhaps have a crack at modifying it.

But it won't be dynamic for example if new category is created than I have to make manual form and add buttons

Do you see how these methods remove this problem? The buttons are always there but hidden and repurposed on demand, and the adding and changing of products is done in the db.

This may seem complicated but I promise you, once it clicks you'll change the way you think about project design.

Gaz