Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

Form Basics

Format a continuous form (an alternative) by gol4
Posted: 28 Aug 02 (Edited 13 Sep 02)

The power of a listbox, combobox or continuous form for displaying large amounts of data is well known. The problems with these controls are their inability to apply formatting to individual list items.
Various solutions to this problem are to hide several boxes behind each other, Gurus have offered some API calls or you can purchase various custom controls from vendors.
  The alternative I offer is to simulate these controls by combining their data power with the formatting capability of the other controls, like a textbox. The example below will simulate a continuous form. You can easily make it look like a combo or list box using flat and transparent borders
This code reads the data from the rows and columns of a listbox, places it in the text boxes, where you can format it.

Lets assume you want your continuous form to look like this

Name             address              amount owed
Tom               112 main             $100.00
Fred              113main              $125.00
Bob               114main               $45.00
John              117main              $210.00
Frank             119main               $30.00
Begin by adding a listbox. Name it list0, Set its rowsource and column count property to display the data you want. Set column heads to yes. Now size this to about 1 inch square and set its visible property to no.
Now begin adding textboxes to the form place and size them in a grid the way you want the data to appear in your form. Add one row then copy and paste for as many rows you want displayed. Now you need to name them. In VB you could create an array, But in Access you will need to simulate that in how you name your fields.
Set it up in a grid like so. Remember the secret is in design
txname1       txadd1        txamt1  cdUp
txname2       txadd2        txamt2
txname3       txadd3        txamt3
txname4       txadd4        txamt4
txname5       txadd5        txamt5  cdDown

add 2 small comboboxes name them cbUp and cdDown these will allow you to scroll
now comes the coding
in the option explicit section declare a public variable

Dim intRowTracker as integer
ænow the function that does the work

Public Function FillText()
'function to load textboxes with values from a listbox
Dim IntX As Integer
Dim IntRecCnt As Integer

IntRecCnt = list0.ListCount
'Hides scroll buttons if not needed
If IntRecCnt > 5 Then ' set value to the amount of rows of text boxes you have
Me.cbDown.Visible = True
Me.cbUp.Visible = True
Me.cbDown.Visible = False
Me.cbUp.Visible = False
End If
'fill the boxes
For IntX = 1 To 5 'set value to the amount of rows of text boxes you have
Me("txname" & IntX) = list0.Column(0, IntX + intRowTracker)
Me("txadd" & IntX) = list0.Column(1, IntX + intRowTracker)
Me("txamt" & IntX) = list0.Column(0, IntX + intRowTracker)
'place code to format here unlimited possibilities
If Me("txtamt" & IntX) >= 100 Then
Me("txtamt" & IntX).BackColor = vbYellow
ElseIf Me("txtamt" & IntX) >= 200 Then
Me("txtamt" & IntX).BackColor = vbRed
Me("txtamt" & IntX).BackColor = vbWhite
End If
Next IntX
End Function
in the forms on Load event
Call FillText

Place In the onclick event of cbDown
æallows the list to scroll down
æset 5 to the amount of rows you have
If intRowTracker < list0.ListCount - 5 Then intRowTracker = intRowTracker + 1
Call FillText

Place In the onclick event of cbUp
'allows the list to scroll up
If intRowTracker > 1 Then intRowTracker = intRowTracker - 1
Call FillText

Run your form scroll thru records using the command buttons.
Incrementing intRowTracker causes a scrolling effect to your simulated grid by changing rows
Add a label for your column headers
Which controls you choose and how you use this is entirely up to your own creativity You can add buttons, number controls, Hide fields or rows.
Realize, you can also scroll horizontally by changing the first column value
You can sync to other controls like a form by setting list0 rowsource. If you do this, you will want to add
Me.list0.requery to FillText function before you load the textboxes
I realize this same thing can be accomplished by using arrays and recordsets but this offers an easy solution for Access coders.

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close