Scrol Bars in a continue form, or should i use a listbox?

[menstroy]

Hello,

I am creating a phone book. I set it up as a continous form, and in the detail section it displays the results (Name, phone, dept etC)

I have it pull the data based on a query, and then I have option buttons that filter the data based on the last name... So i have 27 option buttons one for each letter and an All button...

Heres my problem, everythign looks fine but the scroll bar for the results is on the far rightside of the window, this is a small problem because it isnt very clear that the scroll bar is associated with the results. I wanted to come up with a way to have the scroll bar closer to the results, but unfortunately with using continue form method, I am not able to put anythign int he detail secion, such as a scroll bar.

I was thinking about having a subfrom with the results in that window but I am having problems getting started? I want to be able to control the results displayed in that subform the same way I can with them int he detail section by means of filters etc, can anyone give me any suggestions on how to do this?

Thanks

 

[Onyxpurr]

Yes I do this often.

What you might want to is set the filter when a person presses a button.

E.G.
Sub A OnClick()
FilterSubForm("A&quot
End Sub

Sub FilterSubForm(Letter as String)
Dim SQLFind as String
SQLFind = "[LastName] Like '" & Letter & "*'"

Me.subForm.FilterOn = True
Me.subForm.Filter = SQLFind
Me.subForm.Requery
End Sub

 

[rdodge]

You can setup a scrollbar in the footer section of the form. Once you have that done, you can use the Current Event of the form to update the MaxValue and Value properties based on the RecordCount and AbsolutePosition properties respectively.

You would have

MinValue set to 1
MaxValue = rst.RecordCount
Value = rst.AbsolutePosition + 1

Note, AbsolutePosition property is only available on Dynaset and Snapshot type recordsets.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[menstroy]

Ok i'm still a bit confused.

I have 1 table that contains all the names, phone numbers etc.

I currently have a form setup as a continous form, that gets its data based off of a query I have setup that gets its data from that table. (maybe i dont need to do it this way)

I have buttons in the footer section that apply filters, everythign works fine..

How should I creat a subform so I can utilize the method you just described above? with applying the filter to the subform?

 

[rdodge]

I'm assuming your form has the continuous part in the detail section of the form. Since the scrollbar only needs to be in one place, the footer section of the form is a good location for that.

Each time your continuous form either gets refreshed or changes to a different record, the Current Event on the form is triggered. Provided that your query that the form is based off from is either a snapshot or dynaset type query, you should be able to use the following type code:

Private Sub Form_Current()
Dim CurRS as DAO.Recordset
Set CurRS = Me.RecordsetClone
scbPhoneListing.Max = CurRS.RecordCount
scbPhoneListing.Value = CurRS.AbsolutePosition + 1
End Sub

I'm not sure about the scroll bar control, but the Slider Control that I looked up on, these 3 properties are of Integer Data Type format, so if you expect your form to exceed 30,000, you may want to set the scrollbar range to between 0 and 10000, then use the following line of code:

scbPhoneListing.Value = Int((CurRS.AbsolutePosition + 1) / CurRS.RecordCount * 10000 + .5)

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Onyxpurr]

I think we have two different solutions here. RDodge is giving solution for one form set to continuous.

The solution I have provided would include the following:

1) Main Form: has command buttons for each letter of the alphabet for your users to click to filter with

2) Subform: You can have this set on continous or datasheet with all the field from your table.

The two forms would not be linked. In the OnClick event for each of your button, call the FilterSubForm procedure as above passing the letter you want to filter with. (See above example for the OnClick event and the procedure.) The FilterSubForm is just a procedure that I created, you can put in your code section.