sorting and grouping in forms - not in reports, but in same way

[broj1]

Hey;

i got that, the reports are more flexible with this and that there is no function for sorting and grouping like there is one in reports. But is there a way around? To somehow simulate this in forms?
Like, i have continious subform in which i would like to show products by category - like:

cars:
car1
car2
car3
.....
motorbikes:
motor1
motor2
......
Trucks:
...
...
and so on!

 

[Remou]

The usual way to do this kind of thing with forms is to use a main form and subform. This will show each group on a form page, would this not suit?

 

[broj1]

thanks for fast reply Remou!
Yes, you are correct, but the subform itself shows you records only for induvidual group.

I would like to how all groups and all products for each group in one subeform for one Order (linked by master and child): so,

Mainform
OrderNo: 55 Costumer: SomeGuy
--------------------------------------
bound Subform:

cars:
car1
car2
car3
.....
motorbikes:
motor1
motor2
......
Trucks:
...
...
and so on!

 

[Remou]

You cannot have a form quite like a report, you can have a second subform:

[tt]Mainform
OrderNo: 55 Costumer: SomeGuy
--------------------------------------
bound Subform:

Vehicle type: cars

Bound subform2:
car1
car2
car3
<-0->[/tt]

Ot you can set up a query to show:

[tt]Mainform
OrderNo: 55 Costumer: SomeGuy
--------------------------------------
bound Subform:

car car1
car car2
car car3
motorbikes motor1
motorbikes motor2[/tt]

With some fancy conditional formatting, it should be possible to achieve:

[tt]car car1
car2
car3
motorbikes motor1
motor2[/tt]

 

[MichaelRed]

Actually, you can have many subforms. You cannot nest them more than 4 deep, but from the description that should not be a problem.



MichaelRed

 

[broj1]

@Remou
the second suggestion is exectly what i am looking for
Mainform
OrderNo: 55 Costumer: SomeGuy
--------------------------------------
bound Subform:
car car1
car2
car3
motorbikes motor1
motor2

what kind of a "fancy conditional formatting" would that be and how to achive this?
I have found this though from someguy named SunWuKung:
(how to change this to achive my goal?)
1. Create a textbox in the detail section above your data
Name=Separator
ControlSource:=SeparatorVisible([TargetOrder])
2. Create a textbox in the header
Name=PreviousTargetOrder
Visible=False
3. Put the following code in the form
Function SeparatorVisible(TargetOrder As Integer)
If Me.PreviousTargetOrder <> TargetOrder Then SeparatorVisible = 1 Else SeparatorVisible = 0
Me.PreviousTargetOrder = TargetOrder
End Function
4. Set conditional formatting for the Separator - black if 1 the color of the form othervise

 

[Remou]

Presumably there is a unique ID associated with these items? If so, here is my suggestion. Create a control in the detail section called, say txtTest, having the visible property set to No and the control source set to:

[tt]=SeparatorVisible([ID])[/tt]

Where ID is the unique id.

Set the conditional format of the group field to:

[tt]Expression Is: [txtTest]>0[/tt]

Set the format of the group field to something on these lines:

[tt]Back style transparent
Back color=transparent
Fore color=same color as form background (-2147483633)[/tt]

Add some code:

Option Compare Database
Option Explicit

Dim strIDs

Function SeparatorVisible(intID)
Debug.Print intID
SeparatorVisible = InStr(strIDs, "," & intID)
End Function

Private Sub Form_Load()
Set rs = Me.RecordsetClone

Do While Not rs.EOF
'GroupField is text
    strGroup = rs!GroupField
    strIDs = strIDs & "," & rs!ID
    rs.FindNext "GroupField>'" & strGroup & "'"
    If rs.NoMatch Then Exit Do
Loop

End Sub

 

[IceRuam]

This is very confusing, what is the ID field? and how does the strIDs relate to it?

I am assuming groupfield is the car, motorbikes...etc
but where is this getting compared?

Thanks

 

[IceRuam]

when I use the below, it almost works, except for the first record, shows a 0 even though looking at the immediates window it show they are not the same....

----------------------------------------------------------
1. Create a textbox in the detail section above your data
Name=Separator
ControlSource:=SeparatorVisible([TargetOrder])
2. Create a textbox in the header
Name=PreviousTargetOrder
Visible=False
3. Put the following code in the form
Function SeparatorVisible(TargetOrder As Integer)
If Me.PreviousTargetOrder <> TargetOrder Then SeparatorVisible = 1 Else SeparatorVisible = 0
Me.PreviousTargetOrder = TargetOrder
End Function
4. Set conditional formatting for the Separator - black if 1 the color of the form othervise
----------------------------------------------------------

 

[MajP]

Take a look at thread702-1491694. I explain how to do this, plus another discussion on alternating colors. At the end of the thread there is a demo you can download.

 

[MajP]

The idea is basically the same as Remou's.

 

[IceRuam]

Thanks, downloaded the demo, it looks like what I am looking for, thanks again very much.