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 ETCà.. 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 Else 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 Else 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.