Solved Form with tabs and getting queries to add rows of data (1 Viewer)

[miked18]

Hello again, hopefully this will make sense. first some background. I grow microgreens. What I am looking to do now is have a form that has different tabs that will be populated with data by queries. These tabs are for "tasks" that need to be completed on "this day". The tasks are "Soak" ( seeds that need to be soaked), "Sow" (what seeds need to be plated today) and a few more. One tab showing "All Tasks" (The others are Blackout, Uncover, Harvest, Package, but maybe not needed just yet)

I have tables: Orders, Products, and Sow.
What I am looking to do is for each of the tabs, when selected pull the relevant data for the date selected.

For Example: Today I need to do the planting, so I will look in the "Sow" tab and todays date. I would like to see: (The query would look up the Product from the Orders table based. It will look at the "Days to Maturity" field and work backwards from the "Harvest date" to determine that today what needs to be planted to make that Harvest Date)

Product:Broccoli SeedType:Broccoli SowRate: 25 Tray Size 10x20 Trays to Sow: 2 (1.5) Harvest date: 01/01/2023
Product:Broccoli SeedType:Broccoli SowRate: 25 Tray Size 10x20 Trays to Sow: 2 (1.5) Harvest date: 01/01/2023

So, the query would need to first determine the number of trays. It will first look up "Broccoli" in the Orders table with this "Harvest date". It will determine how many packages are ordered. So lets say 5 small packages ordered and 1 medium. Then, it will look up the "Projected Yield" in the Products table. Broccoli is say 180g (this is for a 10x20 tray, 10x10 trays would be 1/2 that.) It will then have to see how man (g) per package size are used. This is again on the Products table "Grams Per Small Pkg" and "Grams per Med Pkg". So that total would be 250g, which is the yield I would need to get to make the order. So 250/180 is 1.38. So in the "Trays to Sow" above, that is why you see 1.5 in parenthesis, and it is rounded up to 2. This would show me that I could do 1 tray at 10x20 and change the second row for broccoli to a 10x10 tray. Or I could leave it as 2 trays of 10x20.

The query would pull all of the products from the orders table that need to be planted. I would also like to be able to add a new line to be able to add a new tray to the list. Maybe I wanted to do a tray of arugula for a test even though I didn't have any ordered for this harvest date.

When all of the data is set, these would go into the Sow table.

Again, my question would be: how do I get a query to show this data in a form and also be able to change it?

 

[theDBguy]

Hmm, I wonder how much data would each tab have. It might be possible to have just one tab to display all the tasks at once. You can still filter that single tab to limit the list to one task.

 

[MarkK]

how do I get a query to show this data in a form and also be able to change it?

To bind a form to a query, set the Form.RecordSource property to the name of the query. To edit data in a query bound to a form, add a textbox, and set the TextBox.ControlSource property to the name of a field in the query. Then put your cursor in the bound textbox on your bound form, and type something.

 

[mike60smart]

Hello again, hopefully this will make sense. first some background. I grow microgreens. What I am looking to do now is have a form that has different tabs that will be populated with data by queries. These tabs are for "tasks" that need to be completed on "this day". The tasks are "Soak" ( seeds that need to be soaked), "Sow" (what seeds need to be plated today) and a few more. One tab showing "All Tasks" (The others are Blackout, Uncover, Harvest, Package, but maybe not needed just yet)

I have tables: Orders, Products, and Sow.
What I am looking to do is for each of the tabs, when selected pull the relevant data for the date selected.

For Example: Today I need to do the planting, so I will look in the "Sow" tab and todays date. I would like to see: (The query would look up the Product from the Orders table based. It will look at the "Days to Maturity" field and work backwards from the "Harvest date" to determine that today what needs to be planted to make that Harvest Date)

Product:Broccoli SeedType:Broccoli SowRate: 25 Tray Size 10x20 Trays to Sow: 2 (1.5) Harvest date: 01/01/2023
Product:Broccoli SeedType:Broccoli SowRate: 25 Tray Size 10x20 Trays to Sow: 2 (1.5) Harvest date: 01/01/2023

So, the query would need to first determine the number of trays. It will first look up "Broccoli" in the Orders table with this "Harvest date". It will determine how many packages are ordered. So lets say 5 small packages ordered and 1 medium. Then, it will look up the "Projected Yield" in the Products table. Broccoli is say 180g (this is for a 10x20 tray, 10x10 trays would be 1/2 that.) It will then have to see how man (g) per package size are used. This is again on the Products table "Grams Per Small Pkg" and "Grams per Med Pkg". So that total would be 250g, which is the yield I would need to get to make the order. So 250/180 is 1.38. So in the "Trays to Sow" above, that is why you see 1.5 in parenthesis, and it is rounded up to 2. This would show me that I could do 1 tray at 10x20 and change the second row for broccoli to a 10x10 tray. Or I could leave it as 2 trays of 10x20.

The query would pull all of the products from the orders table that need to be planted. I would also like to be able to add a new line to be able to add a new tray to the list. Maybe I wanted to do a tray of arugula for a test even though I didn't have any ordered for this harvest date.

When all of the data is set, these would go into the Sow table.

Again, my question would be: how do I get a query to show this data in a form and also be able to change it?

Hi
Welcome to the Forum.
Are you able to upload a zipped copy of the database with no confidential data?

 

[CJ_London]

my question would be: how do I get a query to show this data in a form and also be able to change it?

On the assumption that the data required for sowing is different to soaking have a separate form for each activity which can then be displayed on your main form as a subform.

As already advised, each of these forms would be based on the relevant query

with regards changing the data, this can be done in each form providing the underlying query is updateable - so not too complex an not an aggregate or crosstab query

 

[miked18]

Ah see, maybe I made it sound so easy. In my mind, it doesn't seem so simple. #1 there would be multiple queries, so I don't think I could set the form record source to a query. 1 query would be to show what needs to be Sown, 1 to show what needs to be harvested, etc... #2 I don't want to edit the record source, I want to pull the data, edit as needed, then store in a new table.

there are multiple fields being pulled and from different locations, while also doing so calculations. Maybe it is an easy task for most, but I don't do this often so I can tend to over complicate it sometimes..

@theDBguy - yeh, maybe tabs are not the way to go. I see your point on filters, or even a combobox or buttons to change the query and show only the data required.

So the query for any task could return any number of records from the Orders table. it could be 10 one day and 20 the next time. I don't want to have to code a bunch of textboxes for each query. I would like for the query to add text/comboboxes per record retrieved as needed if that is possible? Sow Rate and tray size would need to be the only 2 that would need to be edited.

Then there is the ability to add a new line for another product not pulled from the query?

 

[miked18]

On the assumption that the data required for sowing is different to soaking have a separate form for each activity which can then be displayed on your main form as a subform.

As already advised, each of these forms would be based on the relevant query

with regards changing the data, this can be done in each form providing the underlying query is updateable - so not too complex an not an aggregate or crosstab query

Ok, so multiple subforms. probably nicer that doing a bunch of tabs... still, my question is how to have the query add only the rows it pulls and make them editable without coding a bunch of text boxes? would it need to be more of a table view than a form or report type format?

 

[CJ_London]

Ok, so multiple subforms. probably nicer that doing a bunch of tabs

you would have one subform on each tab. More efficient would be one subform and instead of a tab you would have a number of buttons, or a listbox or combo which would change the sourceobject of a single subform control

You could have one form as a subform and your buttons/listbox/combo would either change its recordsource or applies filters so you can see the data required. The requirement for this is that each 'view' uses the same fields

without coding a bunch of text boxes?

please clarify - I don't see any need to code textboxes, you would simply bind them to the relevant field in your query

would it need to be more of a table view than a form or report type format?

table view are a big nono. reports aren't editable

Suspect you need to spend a bit of time looking at example apps (see templates for examples) as to what Access is all about and how it works

 

[Pat Hartman]

Ah see, maybe I made it sound so easy. In my mind, it doesn't seem so simple. #1 there would be multiple queries, so I don't think I could set the form record source to a query. 1 query would be to show what needs to be Sown, 1 to show what needs to be harvested, etc... #2 I don't want to edit the record source, I want to pull the data, edit as needed, then store in a new table.

It is pretty easy when you divide and conquer.
If the data you need for each task is different, then there is no advantage to trying to reuse form/query objects. All that would do is to add complicated code. Simply make a separate query and a separate form to display/update that query for each task. You do this by binding queries to forms because queries do not support events and therefore, there would be no place for you to hang validation code or other processing code.

For display efficiency, you can use a tab control. On each page of the tab control,you would place a subform control and each subform control would display a separate task. To view a different task list, you would click on the tab control and that would make a different subform visible.

A slightly more complicated solution but one that is commonly used when you have more than a couple of subforms to display is to create the tab control but shrink it down vertically so that you are only using the tab part. Then put a subform control directly on the main form instead of on a page of the tab control. In this scenario, when you click on the tab, instead of Access doing its magic automatically, you need a line of code to load the desired subform:

Me.sfrmControl.ObjectSource = "name of some subform"

So the query for any task could return any number of records from the Orders table. it could be 10 one day and 20 the next time. I don't want to have to code a bunch of textboxes for each query. I would like for the query to add text/comboboxes per record retrieved as needed if that is possible?

There is no need to ever generate controls to support this process. A subform in continuous or DS view will display a separate row for each record in the query. So, if 10 records were selected, the subform would show 10 rows. If the query returned 500 rows, the subform would show 500 rows - you would of course have to scroll since the size of the subform control is fixed and it will only show a specific maximum number of rows at one time.

Code/object reuse can make your procedures more efficient but ONLY if what you are doing is actually reusable. If the formats and the fields and the calculations are different, you don't gain any advantage by trying to mush them all into a single procedure.

 

[miked18]

Thanks for everyone's input. It has been a long time since I've used access so I am having to relearn it as I go. @Pat Hartman The continuous form was what I needed, thank you.