Scrollbars on subforms 
Author Message
 Scrollbars on subforms

I've got a form with two subforms; one based on a crosstab query, the other
on a select query.  They both show the same key data and have the same
number of records, but display different information for the style numbers.

What I'd like to do is when the user uses the scroll bar to move down the
records in one subform, I'd like it to move down the other subform also, so
that the records appear synchronized.  That way,  I'll be able to hide the
key fields in one subform and have it appear as a single row across the
screen.

Can anyone tell me how to synchronize the two scrollbars?  I've tried
synchronizing the forms using criterias and bookmarks, but that only works
if the user is actively in one of the records, not when they are browsing
the records.

Thanks,
    George Arnett-Hutto



Fri, 15 Jun 2001 03:00:00 GMT  
 Scrollbars on subforms
Hi George,

The only possible I can think of would probably require trapping the
scrolldown message.  But Access controls are not standard VB controls, so I
don't think the solution would work.

You might want to try checking out the various Mouse events, but I wouldn't
bet that this would work.

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

:I've got a form with two subforms; one based on a crosstab query, the other
:on a select query.  They both show the same key data and have the same
:number of records, but display different information for the style numbers.
:
:What I'd like to do is when the user uses the scroll bar to move down the
:records in one subform, I'd like it to move down the other subform also, so
:that the records appear synchronized.  That way,  I'll be able to hide the
:key fields in one subform and have it appear as a single row across the
:screen.
:
:Can anyone tell me how to synchronize the two scrollbars?  I've tried
:synchronizing the forms using criterias and bookmarks, but that only works
:if the user is actively in one of the records, not when they are browsing
:the records.
:
:Thanks,
:    George Arnett-Hutto

:
:
:



Fri, 15 Jun 2001 03:00:00 GMT  
 Scrollbars on subforms
Hello George

I don't know anyway to control scroll bars in Access.  You could use an
ActiveX scroll bar that might give you some programatic control or you could
just put to command buttons at the edge with up and down arrows on them.
When the user clicks the up button (you could trap and synchronize the up
arrow key too) then you can move up a record on both forms with run command
method.  If the user went into the datasheet and scrolled i am not sure how
you would get lined back up again.

Matthew MacFarland

Quote:

>I've got a form with two subforms; one based on a crosstab query, the other
>on a select query.  They both show the same key data and have the same
>number of records, but display different information for the style numbers.

>What I'd like to do is when the user uses the scroll bar to move down the
>records in one subform, I'd like it to move down the other subform also, so
>that the records appear synchronized.  That way,  I'll be able to hide the
>key fields in one subform and have it appear as a single row across the
>screen.

>Can anyone tell me how to synchronize the two scrollbars?  I've tried
>synchronizing the forms using criterias and bookmarks, but that only works
>if the user is actively in one of the records, not when they are browsing
>the records.



Fri, 15 Jun 2001 03:00:00 GMT  
 Scrollbars on subforms
Hi George

Have you considered the possibility of using just one subform, based on the
two existing queries joined by the primary key field?

Good Luck!
Graham Mandeno [Access MVP]
Alpha Solutions Limited
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions to newsgroups.

Quote:

>I've got a form with two subforms; one based on a crosstab query, the other
>on a select query.  They both show the same key data and have the same
>number of records, but display different information for the style numbers.

>What I'd like to do is when the user uses the scroll bar to move down the
>records in one subform, I'd like it to move down the other subform also, so
>that the records appear synchronized.  That way,  I'll be able to hide the
>key fields in one subform and have it appear as a single row across the
>screen.

>Can anyone tell me how to synchronize the two scrollbars?  I've tried
>synchronizing the forms using criterias and bookmarks, but that only works
>if the user is actively in one of the records, not when they are browsing
>the records.

>Thanks,
>    George Arnett-Hutto




Sat, 16 Jun 2001 03:00:00 GMT  
 Scrollbars on subforms
Hi George

I thought that might be the case - I should have mentioned the resulting
query would not be updatable.

I had this problem recently with an inventory control database.  The client
wanted the previous few months' stock counts displayed on the form in which
he was entering his current stocktake results.  I got around it by
simulating a crosstab query using a function.
It performs surprisingly well, provided the key field and the field used for
the 'crosstab column heading' constitute a unique (preferably primary) key
in your table.  In my case, the primary key in the 'StocktakeNumbers' table
was the StockID coupled with the StocktakeMonth.

You need to create a standard module for your code - it can't be in the form
module as the function needs to be called from SQL.  The Form_Load event can
call a procedure which opens the database and table, assigning them to
module-level database and recordset object variables, and sets the
recordset's index appropriately.  The Form_Unload event should call a
cleanup procedure to close everything.

The actual lookup function simply performs a Seek on the recordset, given
the key value and the heading value, and returns the 'value field' for the
crosstab.  Something like this:

Public Function GetCrosstab( KeyValue, ColumnValue)
With rsCrosstab
  .Seek "=", KeyValue, ColumnValue
  If .NoMatch Then
    GetCrosstab = Null
  Else
    GetCrosstab = !ValueField
  End If
End With
End Function

The 'crosstab' columns in the select query then look like this:
    Select KeyField, Field2, Field3, ...
        GetCrosstab([KeyField],"Value1") as Value1,
        GetCrosstab([KeyField],"Value2") as Value2
        GetCrosstab([KeyField],"Value3") as Value3
        from SomeTable ...

I know it looks cumbersome, but it works well, and because the calculations
are on the query rather than in the form you can filter and sort on the
resulting fields.

Let me know how you get on...

Good Luck!
Graham Mandeno [Access MVP]
Alpha Solutions Limited
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions to newsgroups.

George replied via email...

Quote:
> Graham,
>  I had tried that, but one of the subforms is coming from a
> crosstab query, and the other is a select query.  And I need to be able
> to change some values in the subform based on the select query.  Now I
> had tried using yet another query that pulls data from both queries, but
> it takes the form/subform entirely too long to load.  I didn't go much
> further with it since I also got a message that I couldn't update the
> underlying recordset since it was based (partially) on the crosstab
> query.

> Thanks,
>  George



Sun, 17 Jun 2001 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Subforms with scrollbars

2. forms and subforms hwo can I link two subforms with two different forms ?

3. Requery a subforms recordsource

4. snycing subforms

5. use of Subforms

6. No Subforms

7. Option Group & SubForms

8. iterate through all subforms?

9. unloading/loading subforms

10. dynamically creating subforms

11. Synchronizing "AllowEdits" on subforms

12. Subforms not in the Forms collection?

 

 
Powered by phpBB® Forum Software