how to show two rows in an unbound continuous form

[Access infant]

I have table that lists all the allowances an employee draws.It also gives
which of them an employee draws during a leave period.
EmpID Leave period Allowance status
1 01-01-2008 to28-02-2008 House rent -1
1 01-01-2008 to28-02-2008 compensatory -1
1 01-01-2008 to28-02-2008 conveyance 0
1 01-05-2009 to31-05-2009 House rent -1
1 01-05-2008 to28-02-2009 compensatory 0
1 01-05-2009 to28-02-2009 conveyance 0

These Allowances could differ from employee to employee and also from leave
period to leave period.
Now i want to display these details of the employee(in the parent form) in
the following manner in a continous subform.

leave period houserent compensatory
conveyance
01-01-2008 to28-02-2008 checked checked not
checked
01-05-2009 to31-05-2009 checked not checked not
checked

I created unbound check boxes(a maximum of ten controls is more than enough
for the purpose)and write the following code.
'I open a recordset here on the above-shown table
rst.movefirst
'I supply the no. of allowance here
AllowanceCount = 3
For x = 1 To AllowanceCount
Forms!Form1."Label"&n.Caption = rst!Allowance
Forms!Form1."Label"&n.Visible = True
Forms!Form1."check"&n.Visible = True
rst.movenext
next
it is working for one leave period(01-01-2008 to 28-02-2008).But i have no
clue how to display the second row to show another leave period(01-05-2009 to
31-05-2009). I feel that to display this way is visually pleasing. So, can
anyone help me how to display this? I will be eagerly waiting for your
replies, friends pleasse give your suggestions.

 

[Marshall Barton]

I have table that lists all the allowances an employee draws.It also gives
which of them an employee draws during a leave period.
EmpID Leave period Allowance status
1 01-01-2008 to28-02-2008 House rent -1
1 01-01-2008 to28-02-2008 compensatory -1
1 01-01-2008 to28-02-2008 conveyance 0
1 01-05-2009 to31-05-2009 House rent -1
1 01-05-2008 to28-02-2009 compensatory 0
1 01-05-2009 to28-02-2009 conveyance 0

These Allowances could differ from employee to employee and also from leave
period to leave period.
Now i want to display these details of the employee(in the parent form) in
the following manner in a continous subform.

leave period houserent compensatory
conveyance
01-01-2008 to28-02-2008 checked checked not
checked
01-05-2009 to31-05-2009 checked not checked not
checked

I created unbound check boxes(a maximum of ten controls is more than enough
for the purpose)and write the following code.
'I open a recordset here on the above-shown table
rst.movefirst
'I supply the no. of allowance here
AllowanceCount = 3
For x = 1 To AllowanceCount
Forms!Form1."Label"&n.Caption = rst!Allowance
Forms!Form1."Label"&n.Visible = True
Forms!Form1."check"&n.Visible = True
rst.movenext
next
it is working for one leave period(01-01-2008 to 28-02-2008).But i have no
clue how to display the second row to show another leave period(01-05-2009 to
31-05-2009). I feel that to display this way is visually pleasing. So, can
anyone help me how to display this?

A continuous form displays as many rows as there are records
in it record source table/query. I.e. an unbound form
displayed in continuous view can not do anything mpre than a
form in single view.

To display multiple rows, you must use a bound form with a
record set that contains a record for each row you want to
display.

It might be kind of convoluted, but I think you can get the
data arranged the way you want it by using a crosstab query
as the continuous subform's record source. The trick will
be binding the check boxes to whatever allowances the query
finds. If you are willing to display every possible
allowance, you can greatly simplify things by using the
query's Column Headings property. If that's not acceptable,
then you can use a bunch of code to loop through the form's
recorset's Fields collection and set the check boxes control
source property.

 

[Access infant]

Marsh,
Thanks a lot for your prompt response.Actually i thought of using crosstab
query but i don't know how to bind it. you showed me a way. I have tried what
you have suggested here but find some more obstacles.
First of all, I could not set all the ColumnHeadings before hand. That is
beyond a hundred. So, I cannot consider that possibility.
I have created a crosstab query on the table i gave you earlier.The first
problem is whether i should use SUM, AVG,COUNT in the TOTAL row of the query
design grid. i chose Sum of course but i need your advice here
Another problem is:
To test how it is working,I have first manually bound the checkbox
controlsource to the three allowances fields apart from the leave period(I
have bound the form's controlsource also,of course). Still it displayed only
one row i.e the first leave period 01-01-2008 to 28-02-2008 but the secondrow
for the leave period 01-05-2009 to 31-05-2009 is not displayed in the form.
But everything is perfect in the query datasheet view.could you please tell
me why it is happening so?
The third problem is with the syntax of the following code
Public Function unboundcontinuous()
Dim rst As DAO.Recordset
Dim n As Integer, i As Integer
Set rst = CurrentDb.OpenRecordset("MyCrosstabQuery")
For i = 0 To rst.Fields.Count - 1
n = i + 1
Forms![Form1].[Label & n].Caption = rst!Fields(i).Name
Forms.Form1.("Label" & n).Visible = True
Forms("Form1").Controls("check" & n).Visible = True
Forms("Form1").Controls("check" & n).Caption = rst!Fields(i).Name
rst.MoveNext
Next
Set rst = Nothing

End Function
when i am trying to change the controlsource and caption properties, it is
showing Run-time error 3265 : Item not found in this collection.Probably i
don't kknow the correct syntax to reference the properties.I have given the
different syntaxes i have tried in each line. This 'Access Infant' surely
needs your help to avoid tripping and falling. I eagerly wait your support.
Thanks in advance
--
from
chanakya
Baruva

 

[Marshall Barton]

Thanks a lot for your prompt response.Actually i thought of using crosstab
query but i don't know how to bind it. you showed me a way. I have tried what
you have suggested here but find some more obstacles.
First of all, I could not set all the ColumnHeadings before hand. That is
beyond a hundred. So, I cannot consider that possibility.
I have created a crosstab query on the table i gave you earlier.The first
problem is whether i should use SUM, AVG,COUNT in the TOTAL row of the query
design grid. i chose Sum of course but i need your advice here
Another problem is:
To test how it is working,I have first manually bound the checkbox
controlsource to the three allowances fields apart from the leave period(I
have bound the form's controlsource also,of course). Still it displayed only
one row i.e the first leave period 01-01-2008 to 28-02-2008 but the secondrow
for the leave period 01-05-2009 to 31-05-2009 is not displayed in the form.
But everything is perfect in the query datasheet view.could you please tell
me why it is happening so?
The third problem is with the syntax of the following code
Public Function unboundcontinuous()
Dim rst As DAO.Recordset
Dim n As Integer, i As Integer
Set rst = CurrentDb.OpenRecordset("MyCrosstabQuery")
For i = 0 To rst.Fields.Count - 1
n = i + 1
Forms![Form1].[Label & n].Caption = rst!Fields(i).Name
Forms.Form1.("Label" & n).Visible = True
Forms("Form1").Controls("check" & n).Visible = True
Forms("Form1").Controls("check" & n).Caption = rst!Fields(i).Name
rst.MoveNext
Next
Set rst = Nothing

End Function
when i am trying to change the controlsource and caption properties, it is
showing Run-time error 3265 : Item not found in this collection.Probably i
don't kknow the correct syntax to reference the properties.I have given the
different syntaxes i have tried in each line. This 'Access Infant' surely
needs your help to avoid tripping and falling. I eagerly wait your support.

First, that code needs to be in (or called from) the
subforms Open (or Load?) event.

The code has no reason to loop through the records in the
recordset. All you need is the field names:

Dim i As Integer
With Me.RecordsetClone
For i = 3 To .Fields.Count - 1
Me("Label" & n).Caption = .Fields(i).Name
Me("Label" & n).Visible = True
Me("Check" & n).ControlSource = .Fields(i).Name
Me("Check" & n).Visible = True
Next i
End With

The loop starts at the third field because the first two
fields (id and period) will always be there. Make sure the
label and check box controls are named to match up with the
field numbers.

I don't know why the subform only displayed one record. It
sort of sounds like the subform is not set to Continuous
view. Or maybe you forgot to set the subform's RecordSource
to the crosstab query.

 

[Mike Painter]

Access infant wrote:

First of all, I could not set all the ColumnHeadings before hand.
That is beyond a hundred.

<snip>

You have over one hundred columns (= fields) in this ?
That and using " 01-01-2008 to28-02-2008" rather than a leave ID leads me
to believe that this database needs work.

Access like "down" and does not like "across"
I had a similar problem dealing with an attendance program and the form
"had to look like" the Excel program they were using.
It ended up looking better with holidays and week ends color coded. but was
a flat file.
I used the date and field number to create relational records for reporting
purposes.
I'd suggest trying the same, loop through the relational records for the
event and create a flat file that shows what you want.
It will still cause lots of problems and need coding but you will know where
to look.

 

[Marshall Barton]

Access infant wrote:

<snip>

You have over one hundred columns (= fields) in this ?
That and using " 01-01-2008 to28-02-2008" rather than a leave ID leads me
to believe that this database needs work.

Access like "down" and does not like "across"
I had a similar problem dealing with an attendance program and the form
"had to look like" the Excel program they were using.
It ended up looking better with holidays and week ends color coded. but was
a flat file.
I used the date and field number to create relational records for reporting
purposes.
I'd suggest trying the same, loop through the relational records for the
event and create a flat file that shows what you want.
It will still cause lots of problems and need coding but you will know where
to look.

Mike, Access infant is working with a crosstab query that
denormalized the report's record source.

 

[Access infant]

There you are! The form's default view is not set to continuous view.
Everything worked perfectly well when i simply change the For Loop to i=2 to
Fields.count-1.It is simply wonderful.Thanks a lot once again. But could you
please tell me how i can dictate the order the fields names are shown instead
of simple alphabetical order.
And one more request :
please give me the syntax for setting the controlsource from the standard
modulei.e full form not with "me." shortcut
If i use me.requery will the changes be shown in the form if its
recordsource is changed or new records are added because the code is placed
in the load event.
Thanks in advance.

 

[Marshall Barton]

There you are! The form's default view is not set to continuous view.
Everything worked perfectly well when i simply change the For Loop to i=2 to
Fields.count-1.It is simply wonderful.Thanks a lot once again. But could you
please tell me how i can dictate the order the fields names are shown instead
of simple alphabetical order.

I don't think you can specify the order of the fields in a
crosstab query short of either using a fixed ColumnHeadings
OR using a bunch of code that figures out what fields will
be in the query and constructing the needed Select query.
The latter would be quite messy and duplicate what happens
behind the scenes when you use a crosstab query. Maybe you
can rename the allowances so alphabetical sorting is close
enough to what you want :-\

And one more request :
please give me the syntax for setting the controlsource from the standard
modulei.e full form not with "me." shortcut

I am having trouble finding a good reason to put that code
in a standard module. Surely it is specific to this one
form object and good programming practices strongly
recommends localizing functionality as tightly as its use
allows. If you really have a good reason, replace Me with
this kind of syntax:
Forms!mainform.subformcontrol.Form( . . .

If i use me.requery will the changes be shown in the form if its
recordsource is changed or new records are added because the code is placed
in the load event.

If you add another record to the allowances table (using
some other form), then you will need to requery the subform,
which may indeed change the crosstab query's field list.
Staying with the localization guidlines, the code in the
subform header/footer button that opens the other form
(where the new record is added) would look somethng like:

DoCmd.OpenForm "otherform", _
DataMode:= acFormAdd, _
WindowMode:= acDialog
Me.Requery
Call unboundcontinuous()