How to "flow" table data visually from column to another (1 Viewer)

redridinghood

New member
Local time
Yesterday, 21:00
Joined
Jul 13, 2018
Messages
7
Imagine a medical form or a motor vehicle dept. form -- at the bottom, there are multiple codes. I have stored these codes in a table. What I want is to display all of these codes at the bottom of my form -- top to bottom, and then left to right "flowing over" into the next column.

Ultimately, I could simply hardcode a bunch of labels and be done with it, but I am learning access, I thought I would do it "right".
So there is no concern for checkboxes or programming -- the data is static and does not vary from one motor vehicle form to another. All I need is to retrieve all records, and "flow" them across several columns.
thank you
 
Last edited:

lpapad

Registered User.
Local time
Today, 03:00
Joined
Jul 7, 2018
Messages
47
You can create multiple column reports.

But for forms, I do not know any way to accomplish multiple column flow of records.
 

redridinghood

New member
Local time
Yesterday, 21:00
Joined
Jul 13, 2018
Messages
7
Thank you for the response, but I am literally looking for how to computerize an existing business. They currently have one page paper forms with code legend at the bottom, so I am trying to mimick what they already have. Didnt realize something this conceptually simple is so complicated
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:00
Joined
Sep 21, 2011
Messages
14,048
Ok, I've not done this myself yet, but have seen examples on this site.
Basically name your controls with a common name and incremental number, txt1, txt2, txt3 etc and then loop through them and populate them with data,

Ridders has done something like this in this DB. I only point this out as it is the most recent example I have seen.

See post #14 in this thread https://www.access-programmers.co.uk/forums/showthread.php?p=1581434#post1581434

Look in frmDatePicker in Private Sub DrawDateButtons(), particularly
Code:
Set btn = Me.Controls("d" & Y & x)
He is using this to set properties of each button, but the same method can be used for assigning data.

Hopefully the experts will chip in soon, but it should get you started.

HTH
 

lpapad

Registered User.
Local time
Today, 03:00
Joined
Jul 7, 2018
Messages
47
Thank you for the response, but I am literally looking for how to computerize an existing business. They currently have one page paper forms with code legend at the bottom, so I am trying to mimick what they already have. Didnt realize something this conceptually simple is so complicated
Assuming codes at the bottom are fields, of the same record I propose to use tabs in organising them in groups.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 01:00
Joined
Jul 9, 2003
Messages
16,245
It's not clear how you are storing these Codes in the table. Are using the correct method of storing one Code in each row? Or, do you have the Codes in separate columns in the table?

If you are using the correct method and have them in individual rows in a single column in the table, then you might find my blog here on how to display vertical table data horizontally of use:-

Display Field Data Horizontally
 

isladogs

MVP / VIP
Local time
Today, 01:00
Joined
Jan 14, 2017
Messages
18,186
I think Gasman was referring to my 'Better Date Picker' which you will find here: https://www.access-programmers.co.uk/forums/showthread.php?t=298062

However that has a total of 42 controls in 6 rows of 7 to give all possible dates in a monthly calendar. So I don't think that will help with what you want

What I normally do in these cases is just to show the items in a listbox (or combo if space is an issue)

I have many reports with items flowing down & across (or vice versa) but offhand I can only think of one example where I've done that in a form.

A few years ago, I created a contacts database and wanted to emulate the Outlook layout of contacts in multiple columns - down & then across. It was a lot of work to do & I felt unhappy about its reliability so scrapped it & went back to using a single column

As an experiment, I just dragged a report with image thumbnails in multiple columns onto a form. Only one column was shown ... so that's a failure.

You could create a crosstab query with your codes & then have a subform with that as its recordsource. However you would probably just have 1 row going across - again not what you want

Suggest you just use a listbox or have a button on your form to open a report with the codes in multiple columns

HTH
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:00
Joined
May 21, 2018
Messages
8,463
the data is static and does not vary from one motor vehicle form to another
I would not over think this. Just hard code the labels on a subform and put the subform on each form. Normally this would not be something that changes often. If you feel compelled to code this then unfortunately Access does not have a native grid control. If your were doing this in .NET this would be trivial using a grid control. There is something called flex grid, but not sure if that is even still supported. And it does not port over well. Your best best is a listbox. In this example I populate a listbox by reading the code table.

More unfortunately the listbox in Access also sucks, it is again very limited. There is no way to add an item cell by cell in a multi column listbox. So if you wanted to fill down and then across you cannot do that. You can only fill an entire row by row. So in order to do what you wanted I had to determine how many codes are in the table. Then divide that by the number of columns. Then I fill a multi dimensional array because I can fill that cell by cell. I then use that to fill my listbox. FYI if you were filling across then down it would be simple. You would just build one continuous string.

Code:
Private Sub Form_Load()
  Dim lst As Access.ListBox
  Dim rs As DAO.Recordset
  Dim strValues As String
  Dim N As Integer
  Dim MaxRow As Integer
  Dim TheCol As Integer
  Dim TheRow As Integer
  Dim TheData() As String
  Set rs = CurrentDb.OpenRecordset("Select Code from tblCodes ORDER BY CodeSort")
  Set lst = Me.lstCodes
   N = rs.RecordCount
  'the array and list are zero based
  MaxRow = -Int(-N / 3) - 1

  ReDim TheData(0 To MaxRow, 0 To 2)
  With lst
    .ColumnCount = 3
    .RowSource = strValues
  End With
  'Fill Array down then across
  Do While Not rs.EOF
    TheData(TheRow, TheCol) = rs!Code
    TheRow = TheRow + 1
    If TheRow > MaxRow Then
      TheRow = 0
      TheCol = TheCol + 1
    End If
    rs.MoveNext
  Loop
  'Use the array to fill the list row by row
  For TheRow = 0 To MaxRow
    strValues = ""
    For TheCol = 0 To 2
      If TheCol = 0 Then
        strValues = TheData(TheRow, TheCol)
      Else
        strValues = strValues & ";" & TheData(TheRow, TheCol)
      End If
    Next TheCol
    lst.AddItem strValues
  Next TheRow
End Sub
 

Attachments

  • Listbox.jpg
    Listbox.jpg
    19.2 KB · Views: 40

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:00
Joined
Feb 19, 2002
Messages
42,981
A legend on a paper form is there because users can't be expected to memorize the necessary codes. Why would you want to replicate that? In an Access form, you would use a combobox that has a list of all available "codes" AND an explanation in English which the paper form almost certainly does not have room for! If you need to select multiple codes for a single field, you would create a subform to support the 1-m relationship.
 

isladogs

MVP / VIP
Local time
Today, 01:00
Joined
Jan 14, 2017
Messages
18,186
Flexgrid can still be used but only in 32-bit Access.
Very powerful but learning curve perhaps a bit steep just to do this
 

redridinghood

New member
Local time
Yesterday, 21:00
Joined
Jul 13, 2018
Messages
7
This is awesome -- will give it a try. Thanks a lot



I would not over think this. Just hard code the labels on a subform and put the subform on each form. Normally this would not be something that changes often. If you feel compelled to code this then unfortunately Access does not have a native grid control. If your were doing this in .NET this would be trivial using a grid control. There is something called flex grid, but not sure if that is even still supported. And it does not port over well. Your best best is a listbox. In this example I populate a listbox by reading the code table.

More unfortunately the listbox in Access also sucks, it is again very limited. There is no way to add an item cell by cell in a multi column listbox. So if you wanted to fill down and then across you cannot do that. You can only fill an entire row by row. So in order to do what you wanted I had to determine how many codes are in the table. Then divide that by the number of columns. Then I fill a multi dimensional array because I can fill that cell by cell. I then use that to fill my listbox. FYI if you were filling across then down it would be simple. You would just build one continuous string.

Code:
Private Sub Form_Load()
  Dim lst As Access.ListBox
  Dim rs As DAO.Recordset
  Dim strValues As String
  Dim N As Integer
  Dim MaxRow As Integer
  Dim TheCol As Integer
  Dim TheRow As Integer
  Dim TheData() As String
  Set rs = CurrentDb.OpenRecordset("Select Code from tblCodes ORDER BY CodeSort")
  Set lst = Me.lstCodes
   N = rs.RecordCount
  'the array and list are zero based
  MaxRow = -Int(-N / 3) - 1

  ReDim TheData(0 To MaxRow, 0 To 2)
  With lst
    .ColumnCount = 3
    .RowSource = strValues
  End With
  'Fill Array down then across
  Do While Not rs.EOF
    TheData(TheRow, TheCol) = rs!Code
    TheRow = TheRow + 1
    If TheRow > MaxRow Then
      TheRow = 0
      TheCol = TheCol + 1
    End If
    rs.MoveNext
  Loop
  'Use the array to fill the list row by row
  For TheRow = 0 To MaxRow
    strValues = ""
    For TheCol = 0 To 2
      If TheCol = 0 Then
        strValues = TheData(TheRow, TheCol)
      Else
        strValues = strValues & ";" & TheData(TheRow, TheCol)
      End If
    Next TheCol
    lst.AddItem strValues
  Next TheRow
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:00
Joined
Aug 30, 2003
Messages
36,118
Post 11 was moderated, I'm posting to trigger email notifications.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:00
Joined
May 21, 2018
Messages
8,463
Like others, not so sure I understand the purpose of listing the codes in this manner on a form. I do see the utility of "flowing" multiple columns. Like I said this is a big deficiency in Access since it does not support a native unbound grid control. There are so many tasks like this that require a cludgy work around. So here is at least some generic code to allow the user to specify the number of columns and the data and "flow" it in a listbox.
I provided a demo to show how it is pretty generic.


Code:
Private Sub LoadFlowList(lst As Access.ListBox, NumberColumns As Integer, Exp As String, Domain As String)
  'lst is a reference to a listbox
  'NumberColumns: how many columns for your listbox
  'Exp: Name of the field to show in the listbox
  'Domain: Needs to be a sorted query, or sorted SQL string
  Dim rs As DAO.Recordset
  Dim strValues As String
  Dim N As Integer
  Dim MaxRow As Integer
  Dim TheCol As Integer
  Dim TheRow As Integer
  Dim TheData() As String
   
  Set rs = CurrentDb.OpenRecordset(Domain)
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveLast
    rs.MoveFirst
  End If
  N = rs.RecordCount
  Debug.Print "n " & N
  'the array and list are zero based
  MaxRow = -Int(-N / NumberColumns) - 1

  ReDim TheData(0 To MaxRow, 0 To NumberColumns - 1)
  With lst
    'modify other properties if desired
    .ColumnCount = NumberColumns
    .Locked = True
  End With
  'Fill Array down then across
  Do While Not rs.EOF
    TheData(TheRow, TheCol) = rs.Fields(Exp)
    TheRow = TheRow + 1
    If TheRow > MaxRow Then
      TheRow = 0
      TheCol = TheCol + 1
    End If
    rs.MoveNext
  Loop
  'Use the array to fill the list row by row
  For TheRow = 0 To MaxRow
    strValues = ""
    For TheCol = 0 To NumberColumns - 1
      If TheCol = 0 Then
        strValues = TheData(TheRow, TheCol)
      Else
        strValues = strValues & ";" & TheData(TheRow, TheCol)
      End If
    Next TheCol
    lst.AddItem strValues
  Next TheRow
End Sub

And calling the code

Code:
Private Sub demo1_Click()
 MsgBox "3 columns, SQL String with a concatenated field"
 Me.lstCodes.RowSource = ""
 LoadFlowList Me.lstCodes, 3, "NameUnit", "Select ProductName & ': ' & QuantityPerUnit As NameUnit from Products ORDER BY ProductName"
End Sub
Private Sub demo2_Click()
 MsgBox "4 columns, SQL String"
 Me.lstCodes.RowSource = ""
 LoadFlowList Me.lstCodes, 4, "ProductName", "Select * from Products ORDER BY ProductName"
End Sub
Private Sub Demo3_Click()
 MsgBox "2 columns, Stored Query"
 Me.lstCodes.RowSource = ""
 LoadFlowList Me.lstCodes, 2, "Info", "qryEmpAddress"
End Sub
Private Sub Demo4_Click()
 MsgBox "2 columns, Stored Query"
 Me.lstCodes.RowSource = ""
 LoadFlowList Me.lstCodes, 2, "Info", "qryCustomers"
End Sub

May be useful somewhere, but a listbox is limited to a single line of text per row.
 

Attachments

  • FlowListBox.accdb
    816 KB · Views: 38

isladogs

MVP / VIP
Local time
Today, 01:00
Joined
Jan 14, 2017
Messages
18,186
MajP
Thanks for this example.
Filed away for possible future use
 

redridinghood

New member
Local time
Yesterday, 21:00
Joined
Jul 13, 2018
Messages
7
See attached image. This is what I wanted to accomplish.
So when a list of activities gets filled out per person, there is a description for each code, so its clear that "D3" is "main engine driver", but once the form is filled out, all thats left per row are codes like D3 and M77 -- and who remembers what that means. So I wanted to have a legend at the bottom so its easier to view these forms.
Thanks again everyone for the provided edumacation.
 

Attachments

  • Capture.PNG
    Capture.PNG
    47.3 KB · Views: 45

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:00
Joined
May 21, 2018
Messages
8,463
So Billy Bob is qualified AW, E, P3. Are you using a multivalue field for that? MV fields work well if you understand them. They do a lot of stuff behind the scenes that make it hard to know what is going on. This may be an example where they work well. If that is not a multivalue field but just a single text field, you will run into problems. Also why does Jim James have two records?
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:00
Joined
Sep 21, 2011
Messages
14,048
Surely if one is are using a computer system, then one would only show what is applicable?
 

redridinghood

New member
Local time
Yesterday, 21:00
Joined
Jul 13, 2018
Messages
7
Correct -- those are multivalued fields.





So Billy Bob is qualified AW, E, P3. Are you using a multivalue field for that? MV fields work well if you understand them. They do a lot of stuff behind the scenes that make it hard to know what is going on. This may be an example where they work well. If that is not a multivalue field but just a single text field, you will run into problems. Also why does Jim James have two records?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:00
Joined
May 21, 2018
Messages
8,463
Surely if one is are using a computer system, then one would only show what is applicable?
No idea what that means.
 

Users who are viewing this thread

Top Bottom