Format Form like Report

[mango97]

Hello,

I'm working on a form that needs to look similar if not the same as the report that contains the same information. When building the reports I was able to sort the data so a value that applies to many entries appears only once at the start of the corresponding group of entries.

How can I have this effect in a form?

Thanks,
mango97

PS. If this doesn't make sense please say so and I'll try to explain it better!

 

[vbaInet]

When building the reports I was able to sort the data so a value that applies to many entries appears only once at the start of the corresponding group of entries.

PS. If this doesn't make sense please say so and I'll try to explain it better!

You probably should have done so in the first instance

 

[plog]

sort the data so a value that applies to many entries appears only once at the start of the corresponding group of entries.

Sorting is arranging the data in a specific order--that you can do in any form using the "Order By" property. Grouping is creating an areas for related data--which you can do via sub-forms. Check out this link: http://office.microsoft.com/en-us/a...a-subform-a-one-to-many-form-HA010098674.aspx

 

[mango97]

You probably should have done so in the first instance

OK, full explanation..

I am working on a database which manages the creation of transformer clamps and cores. Each clamp/core is given a work order number which contains information about the customer, the price, the due date and such.

Each clamp/core has multiple parts which go through a set of processes to be created. This form shows a work order with each part separated into the processes done to it. I don't want to have any repeating data on the form, meaning I don't want the work order information to be displayed more than once and I don't want the process name to appear more than once for a given work order. Ideally, All the work orders would appear beneath one another in a list which could be filtered to show a single work order.

My current solution for this is to put the information for each process into a sub form that filters to show the parts that are for the specific process. Since its a sub form it also only shows the values for the work order. The problem with this approach is the maximum size for a form section is about 21" (if my memory serves correctly) which means when I have 15 sub forms, each form can only show 2-3 entries before scrolling. This proves problematic when some work orders have over 40 entries in a specific sub form. Additionally in some cases not all processes are done for the entire work order which means space is filled that doesn't have to be there.

I understand that with some VBA I can dynamically change the size of the sub forms and hide the unused forms, but ultimately the 21" section maximum results in being unable to show all the entries without scroll bars.

By formatting like a report I meant that when I create the report that corresponds with this form I was able to put the information I did not want to repeat in their own section, resulting in the information only being showed once. Is there something like this that I could use in my form to have this behavior?

I hope this was more clear. Please ask specifically what I need to clarify!

Thanks,
mango97

 

[plog]

Additionally in some cases not all processes are done for the entire work order which means space is filled that doesn't have to be there.

I had a feeling that your database tables aren't properly structured before that sentence. That sentence convinced me of it.

Can you post your table structure--perhaps a screenshot of your Relationship window? Or if you don't have that, your database itself? I think you have bigger underlying issues than this form you want to create.

 

[mango97]

It's entirely possible I've messed them up... This is my first "large" database. So... here it is! Let me know if you need anything else!

Users Enter in the Work Order information. Using that key in the WOTasks they enter the parts. Using that ID links to the Task Order table where the process (Shop) and the Task Number is assigned to the part. I've been using VBA to evaluate what the previous and next tasks are (TaskNumber + 1 with the same WOTasksID)

 

[vbaInet]

mango, which table shows just the work orders? That table should have distinct work orders alone and it's from here you'll get the distinct records.

 

[plog]

That's not as bad as I imagined, but I don't see where you have unused fields. What table is your main form based off of, and what are each of it subforms?

 

[vbaInet]

That's not as bad as I imagined,

... I know

 

[mango97]

mango, which table shows just the work orders? That table should have distinct work orders alone and it's from here you'll get the distinct records.

The table named WorkOrders shows just the work orders alone. The next table (WOTasks) contains the part names/information that relates to the all processes done on said part (essientially this is just an intermediate step for the 3rd table). All this table does is go "Hey part Top Clamp is apart of Work Order 1759. The third table (TaskOrder) uses the ID for the part and sets 2 values in it:

1) What process is done to it and 2) What number in the order of processes done to this part does this process occur at.

Finally I push this information to the TaskComplete table which receives the ID from the TaskOrder and creates a checkbox named "complete"... I have VBA code set up that evaluates if the previous task's complete field is "Yes" in which case the next process is "ready". I think I could get this to run within the task order table, just haven't had time to attempt the changes (the database structure has changed a bit since I first started on it).

 

[mango97]

That's not as bad as I imagined, but I don't see where you have unused fields. What table is your main form based off of, and what are each of it subforms?

I don't have unused fields in my tables...

My main form is based off of the WorkOrders table. I had created subforms from queries that pull the data from the the WOtasks Table and the TaskOrder table to show only the tasks that correspond with a single process. (I think that this is my mistake/where things are streamlined correctly)

Since I have a subform for each shop process there are 15 subforms within my main form which all display all the time. Some of these subforms appear empty because not every shop process is used in each work order.

 

[jdraw]

It seems you have WorkOrders and Tasks and that Workorders could have different Tasks. I'm thinking a junction table WorkOrderTasks that includes a status indicator eg Planned, Started, Completed type thing.

Perhaps there is more to the processes than has been described.

If you have some test data, and some predefined outputs, does you database design support your requirements? Better to get the tables and relationships working and meeting your needs before moving to Forms.

 

[vbaInet]

List out the fields (and their associated tables) that you want to appear on your form showing distinct records.

 

[mango97]

It seems you have WorkOrders and Tasks and that Workorders could have different Tasks. I'm thinking a junction table WorkOrderTasks that includes a status indicator eg Planned, Started, Completed type thing.

Perhaps there is more to the processes than has been described.

If you have some test data, and some predefined outputs, does you database design support your requirements? Better to get the tables and relationships working and meeting your needs before moving to Forms.

I have already set up the junction table (TaskComplete) When the task is entered the field "ready" is given a value of yes or no (its a checkbox). If its a yes the data is highlighted to show that this step is ready to be created. The table "TaskComplete" has a record that corresponds to each task. There is a checkbox that says "Complete", when checked it marks the next task as ready. Because the data gets printed and employees check off when they finish a task we ended up with this solution instead of a single field with text input. This way employees wouldn't be writing over data.

And yes, ive entered in 3 work orders worth of test data in which the logic & relationships work to create the desired outputs. The problems all rely within the forms now

 

[mango97]

List out the fields (and their associated tables) that you want to appear on your form showing distinct records.

From Table "WorkOrder":
PR#
WO#
Rev#
Customer
TaskName
RequiredDeliveryDate
Complete

From Table "WOTasks":
Description
Quantity
Material

From Table "TaskOrder":
Shop
Previous
Next
CalcTaskNum
Notes
Ready (Hidden, used for conditional formatting)

From Table "Task Complete":
Complete
Comp Date
Employee

The information from the work Order table should only appear once at the start of each work order (with each work order being sorted by the required the delivery date and then if Complete = No). Then the remaining fields should be sorted into their processes (Shop field in TaskOrder) followed by the CalcTaskNum (also in TaskOrder). Ideally, the value in the Shop field would only appear once (similar to the work order information only showing once).

This report (which shows the same information) is what I'm hoping to create (I havn't finished the final formatting of the text box's and spacing yet):

Link to larger image

Thanks,
mango97

PS. Earlier the use of "As Bad" has me suspecting that there is something that could be done to be better?

 

[vbaInet]

You've to remember that in a form there's no such thing as Grouping as you have in a report. So whilst you can bundle tables together and group by certain fields in a report, you can't do that in a form.

For this reason, you need to use subforms for repeating records and a main form for unique records. In other words, the WorkOrders table which returns unique records should be the main form and everything else should be in subforms.

You can setup the main form to be a Continuous Form to mimick a report and put the subform within.

 

[plog]

When I said "as bad", I expected, based on your description, maybe 2 total tables, one of which would have ton of fields that didn't belong in the table they were in (Task1, Task2, Task3). But you actually seperated them out and it seems correct in that manner (although I still can't wrap my head around your data--projects, work orders, tasks, processes).

With that said, I do see these issues (which are issues, but not the cardinal sins I expected).

1. TaskComplete doesn't need a [Complete] field and a [Comp Date] field. If [Comp Date] is null, its not complete. No need to store it redundantly.

2. TaskComplete has a [WOTasksID] field. TaskComplete is connected to TaskOrder so it doesn't need a direct link to WOTasks.

3. TaskOrder doesn't need a [Previous] nor [Next] field. If you need to keep them in order, I believe TaskNumber does that.

Actually, that's a big reason for my confusion. What does TaskNumber and WOTaskNumber do?

Based on your relationship, your data doesn't really avail itself to using just one form. It seems you would need a few forms with subforms.

Can you post your database with sample data in all your tables. Maybe that would help me understand more.

 

[mango97]

You can setup the main form to be a Continuous Form to mimick a report and put the subform within.

I was under the impression that subforms could not be used in continuous forms?

1. TaskComplete doesn't need a [Complete] field and a [Comp Date] field. If [Comp Date] is null, its not complete. No need to store it redundantly.

Good idea, i'll be sure to implement this sometime.

3. TaskOrder doesn't need a [Previous] nor [Next] field. If you need to keep them in order, I believe TaskNumber does that.

Those values are calculated via a module and not directly edited by the user. You are correct that TaskNumber keeps the fields in order. Those fields were created because when the final forms/reports are created the [previous] and [Next] fields are needed for the employees to see where to get the part or where to take it next. I thought that it would be more efficient to calculate and store these values as the data is entered and edited, instead of calculating all of them when the form/report is generated. Is this correct?

Actually, that's a big reason for my confusion. What does TaskNumber and WOTaskNumber do?

TaskNumber is used to set the order the processes are done on the part. So it is used to set the ready/complete fields and set the value in "previous/next". Its also used to calculate the [CalcTaskNum] which i'll explain later. WOTaskNumber is a bit obscure and should be calculated instead of entered (Currently only 1 user on the database and he is ok with some extra manual work at first because at the moment he is typing everything out many times in exel). It's used for [CalcTaskNum]... For each part in a work order it gets a WOTaskNumber (value starts at 1 for each work order).

These values are both used to create the [CalcTaskNum] which gives a numerical reference to a part and task number. This is done by multiplying the TaskNumber by 0.01 and adding the corresponding WOTaskNumber. This gives a unique value for each task in a work order and allows for easy reference.

Based on your relationship, your data doesn't really avail itself to using just one form. It seems you would need a few forms with subforms.

Can you post your database with sample data in all your tables. Maybe that would help me understand more.

There is some information in the database that I wouldn't want to post online for everyone to see. Is it possible for me to private message you a link to the public folder of my OneDrive?

 

[vbaInet]

I forgot about that!

 

[mango97]

any others ideas then? I'm ok with changing the design of the form, the only thing I want is to have the form require a bunch of scroll bars to see the information!