LISA GEORGE
asked on
sub forms grouping based on fields
Hi all, I need some help on MS access SQL query for this situation
I have main form WorkOrder with workorderID, Regionid, contractid, Typeid, Roadid etc(workorder table with workorderid as primary key and other fields are primary keys of their own tables)
I have continuous sub form with PayItems, rate, quantity etc (workorderpayitem table with workorderpayitemid as PK,workorderid as foreign key, payitemid etc)
ATM relationship is 1xworkorder will have 1xregion, 1xcontract, 1xtype, 1xroad many payitems
but now we need to modify existing form in such a way that each workorder will have many type,road and each type and road will have many payitems
say workorder --1000
will have type-1, road-1 which in turn have payitems-5
and again if user wants to enter 1xmore type and road then he will type type and road and for them he will again choose payitems
--------------------------
To implement this scenario i have mainform as it is which is having workorderid, region, contract and has removed type and road columns frm there
this main form will have subform datasheet view to select type,road from dropdowns
when user selects type and road they will again need to select payitems which are in sub-sub continuous form
----------------
what i did is i added type and roadid foreign keys to workorderpayitem table
and record source for my datasheet view subform will be sql query
SELECT WorkOrderPayItem.WorkOrder
FROM WorkOrderPayItem
GROUP BY RoadID, WorkOrderTypeID, WorkOrderID;
and record source for sub-sub continuous form is
SELECT WorkOrderPayItem.WorkOrder
FROM WorkOrderPayItem;
with these sql queries form is not grouping by type, road
say if i have earlier 1xworkorde with 1xtype,1xroad,5xpayitems then in datasheet view form needs to be displayed like
+ type1 road1
payitem1
payitem2
payitem3
payitem4
payitem5
+ here it needs to ask for new record where user will enter type2 road2
new payitems for this type2,road2
but what i am getting is
+ type1road1
payitem1
payitem2
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 2nd one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 3rd one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 4th one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 5th one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
final line is it's expecting type and road for each payitems which are there
and that is not what i want
they need to be grouped by type,road for each workorder and which in turn each type,road will have different/few same payitems
is the problem in sql query which i used for datasheet view or as i added typeid and roadid to workorderpayitems so it's not grouping as i want
please help me with this
thank you for your patience for reading long post
regards,
Lisa
I have main form WorkOrder with workorderID, Regionid, contractid, Typeid, Roadid etc(workorder table with workorderid as primary key and other fields are primary keys of their own tables)
I have continuous sub form with PayItems, rate, quantity etc (workorderpayitem table with workorderpayitemid as PK,workorderid as foreign key, payitemid etc)
ATM relationship is 1xworkorder will have 1xregion, 1xcontract, 1xtype, 1xroad many payitems
but now we need to modify existing form in such a way that each workorder will have many type,road and each type and road will have many payitems
say workorder --1000
will have type-1, road-1 which in turn have payitems-5
and again if user wants to enter 1xmore type and road then he will type type and road and for them he will again choose payitems
--------------------------
To implement this scenario i have mainform as it is which is having workorderid, region, contract and has removed type and road columns frm there
this main form will have subform datasheet view to select type,road from dropdowns
when user selects type and road they will again need to select payitems which are in sub-sub continuous form
----------------
what i did is i added type and roadid foreign keys to workorderpayitem table
and record source for my datasheet view subform will be sql query
SELECT WorkOrderPayItem.WorkOrder
FROM WorkOrderPayItem
GROUP BY RoadID, WorkOrderTypeID, WorkOrderID;
and record source for sub-sub continuous form is
SELECT WorkOrderPayItem.WorkOrder
FROM WorkOrderPayItem;
with these sql queries form is not grouping by type, road
say if i have earlier 1xworkorde with 1xtype,1xroad,5xpayitems then in datasheet view form needs to be displayed like
+ type1 road1
payitem1
payitem2
payitem3
payitem4
payitem5
+ here it needs to ask for new record where user will enter type2 road2
new payitems for this type2,road2
but what i am getting is
+ type1road1
payitem1
payitem2
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 2nd one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 3rd one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 4th one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
+ asking for newtype,newroad(for 5th one)
payitem1
payitem2 (again displaying all same payitems which are there)
payitem3
payitem4
payitem5
final line is it's expecting type and road for each payitems which are there
and that is not what i want
they need to be grouped by type,road for each workorder and which in turn each type,road will have different/few same payitems
is the problem in sql query which i used for datasheet view or as i added typeid and roadid to workorderpayitems so it's not grouping as i want
please help me with this
thank you for your patience for reading long post
regards,
Lisa
Usually for every problem there is a solution but in this case just by description and especially given the complexity ...start at least with some screenshots ..even drawing...just present what you have...what you need....and we will take it for there...
I'm not able to define your exact request but, even so, it doesn't sound like a quick fix.
Looks like a paid job to me.
Looks like a paid job to me.
Is this something like what you are looking for?
Here's a picture of a form with three connected subforms. Clicking on an item in the list on the left shows the document details on the upper right and the data fields associated with that particular endorsement. An endorsement is an attachment to an insurance policy that covers a specific risk. For example, a jewelry rider for your homeowners policy.
Here's a picture of a form with three connected subforms. Clicking on an item in the list on the left shows the document details on the upper right and the data fields associated with that particular endorsement. An endorsement is an attachment to an insurance policy that covers a specific risk. For example, a jewelry rider for your homeowners policy.
ASKER
Hi all, thank you for your reply.I find a way out of this by creating data sheet subform and which in turn has continuous form for each row in the datasheet
But i have some problem here, i want to avoid too many scroll bars on the data sheet.
Is there any VBA code that i can freeze 4 columns in the data sheet as my data sheet has only 4 columns and user don't want to see empty fields/columns
I tried to use .set focus method for all the fields(Controls) in datasheet form load event and which is freezing 4 columns
but problem is here is i can't see continuous subform records and that subform is displaying blank.
So later i have deleted that .set focus method for all controls but still i can't view continuous subfrom.
How can i again view continuous subform records?
Is there any other vba code i can use to freeze those 4 columns?
But i have some problem here, i want to avoid too many scroll bars on the data sheet.
Is there any VBA code that i can freeze 4 columns in the data sheet as my data sheet has only 4 columns and user don't want to see empty fields/columns
I tried to use .set focus method for all the fields(Controls) in datasheet form load event and which is freezing 4 columns
but problem is here is i can't see continuous subform records and that subform is displaying blank.
So later i have deleted that .set focus method for all controls but still i can't view continuous subfrom.
How can i again view continuous subform records?
Is there any other vba code i can use to freeze those 4 columns?
Freezing the four columns doesn't help. The sub data sheets are as wide as the container that holds them.
ASKER
@PatHartman---I want to avoid too many scroll bars for the form
Is there any other way i can work around to avoid scroll bars or freeze columns?
Is there any other way i can work around to avoid scroll bars or freeze columns?
Each subform needs it's own scroll bar so I'm not sure how you could accomplish this. Have you considered a design similar to what I posted where the user drills down to see the details?
The one time I used the nested data sheets technique, I left the data sheets unexpanded. When the form opened, the user saw only the highest level data with a leading + or - to indicate whether there were additional details below.
The one time I used the nested data sheets technique, I left the data sheets unexpanded. When the form opened, the user saw only the highest level data with a leading + or - to indicate whether there were additional details below.
This looks like one of those issues where you just have to get into the form and its pieces and see what's going on and what you are trying to do.
I'm not going to guess at such a complex issue in the blind.
Also, scroll bars are there for a reason, part of the data is hidden and you need the scroll bar to be able to expose it so you can see it. You only have so much screen and form real-estate to put things on. Don't be surprised if you can't see anything if you remove a scroll bar.
I'm not going to guess at such a complex issue in the blind.
Also, scroll bars are there for a reason, part of the data is hidden and you need the scroll bar to be able to expose it so you can see it. You only have so much screen and form real-estate to put things on. Don't be surprised if you can't see anything if you remove a scroll bar.
A couple of screenshots of what you have and what is exactly the issue might be helpful.
ASKER
hi all i am able to hide my empty columns in datasheet form with this code in on current event of the form
just divide the width into equal/unequal number of columns you want to replicate in the form
Dim w As Integer
Dim neww As Integer
w = Me.Width
neww = w / 5
Me.column1.ColumnWidth = neww
Me.column2.ColumnWidth = neww
Me.column3.ColumnWidth = neww
Me.column4.ColumnWidth = neww
Me.column5.ColumnWidth = neww
Me.RowHeight = 450 . 'this is to increase row height of the cells
just divide the width into equal/unequal number of columns you want to replicate in the form
Dim w As Integer
Dim neww As Integer
w = Me.Width
neww = w / 5
Me.column1.ColumnWidth = neww
Me.column2.ColumnWidth = neww
Me.column3.ColumnWidth = neww
Me.column4.ColumnWidth = neww
Me.column5.ColumnWidth = neww
Me.RowHeight = 450 . 'this is to increase row height of the cells
And how is this working for you when the subform has multiple rows some with data in the hidden columns and some without?
ASKER
i added me.controls(columnname).co
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Way too much complexity to solve by just talking about it. Be prepared to spend several weeks working on this one this way.
Now if you had some sample data and a copy of your app to post (just enough to work on this area), then we would have a chance of fixing it.