Access Display Vertical Data Horizontally

I'm trying to create a sub form that lists data horizontally

At first I tried a pivot table but creating a form from a pivot table doesn't work because the columns have different names each time

What I would like to show is:

Photo#1  Photo#2 Photo#3 Photo#4
Photo# 5 Photo#6  Photo#7 Photo#8
...
rogerdjrAsked:
Who is Participating?
 
SheilsConnect With a Mentor Commented:
just looking back at this I realised that show photos not phone numbers. I must be going blind. Anyway you can use a similar approach.

First create a string and make it the row source for a listbox. Create the listbox somewhere on your form but make it invisible. Let's call is lst0

On your form make a number of image box and name each one in sequence eg Img1, Img2,Img3,Img4 etc...

Then you can use vba to change the background of each textbox.

Dim strFileName As String
Dim strSourceFolder As String
Dim strRowSource As String 
 
strSourceFolder = FolderPath & "\"  'Enter the path of the folder that has the photos
 
strFileName = Dir(strSourceFolder)
    
While strFileName <> ""
 
    strRowSource = strRowSource & strFileName & ";"
 
    strFileName = Dir
     
Wend
 
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
 
Me.lst0.RowSourceType = "Value List"
Me.lst0.RowSource = strRowSource  'This gives you a listbox with the path of all the photos

'Now we will display these photos

Dim i as Integer

For i = 0 to Me.lst0.ListCount -1

  Me("Img" & i+1).Picture="'" & Me.lst0.Column(0, i)

Next i

Open in new window

0
 
SheilsCommented:
One option is to create a string and then make that string the data source. Should work fine with phone numbers because they are all equal length.

To do this run a select query in vba then extra the string from the recordset. Let me know if you need help with that
0
 
rogerdjrAuthor Commented:
Looks like a workable solution - need some time to put it together - I'll update when I have it working
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rogerdjrAuthor Commented:
ended up putting a series of labels and unbound photo objects and and update them from a query

Not the most elegant solution but it works for now

Code looks like:

    StrSql = "SELECT DISTINCT CASp_WorkingChecklistPhotoIndexlDataTbl.ReportId, CASp_WorkingChecklistPhotoIndexlDataTbl.PhotoID, "
    StrSql = StrSql & "CASp_WorkingChecklistPhotoIndexlDataTbl.RefFilePhoto, CASp_WorkingChecklistTbl.ElementListRecId FROM CASp_WorkingChecklistTbl "
    StrSql = StrSql & "INNER JOIN CASp_WorkingChecklistPhotoIndexlDataTbl ON CASp_WorkingChecklistTbl.WkgCeckListRecId = "
    StrSql = StrSql & "CASp_WorkingChecklistPhotoIndexlDataTbl.WkgCeckListRecId WHERE CASp_WorkingChecklistPhotoIndexlDataTbl.ReportId = "
    StrSql = StrSql & """" & Me![ReportId] & """"
    StrSql = StrSql & " And CASp_WorkingChecklistTbl.ElementListRecId = "
    StrSql = StrSql & Me![ElementListRecId]
    StrSql = StrSql & " ORDER BY CASp_WorkingChecklistPhotoIndexlDataTbl.PhotoID;"

    Set Rst1 = dbs.OpenRecordset(StrSql)
   
    PhotCount = 0
    Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![PhotCount].Caption = PhotCount
    Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![PhotCount].Visible = -1
   
    If Not Rst1.EOF Then
        Rst1.MoveFirst
           While Not Rst1.EOF
           
            PhotCount = PhotCount + 1
   
            If PhotCount = 1 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoa].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagea].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoa].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagea].Visible = -1
            End If
           
            If PhotCount = 2 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photob].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imageb].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photob].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imageb].Visible = -1
            End If
           
            If PhotCount = 3 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoc].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagec].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoc].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagec].Visible = -1
            End If
           
            If PhotCount = 4 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photod].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imaged].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photod].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imaged].Visible = -1
            End If
           
            If PhotCount = 5 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoe].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagee].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoe].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagee].Visible = -1
            End If
           
            If PhotCount = 6 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photof].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagef].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photof].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagef].Visible = -1
            End If
           
            If PhotCount = 7 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photog].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imageg].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photog].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imageg].Visible = -1
            End If

            If PhotCount = 8 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoh].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imageh].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoh].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imageh].Visible = -1
            End If
           
            If PhotCount = 9 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoi].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagei].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoi].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagei].Visible = -1
            End If
           
            If PhotCount = 10 Then
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoj].Caption = Rst1![PhotoID]
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagej].Picture = Rst1![RefFilePhoto]
               
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Photoj].Visible = -1
                Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![Imagej].Visible = -1
            End If
           
            Rst1.MoveNext
           
            Forms![0_masterdatafrm]![CASp_Input04_WorkingChecklistDetailSubFrm]![PhotCount].Caption = PhotCount
   
        Wend
    End If
0
 
SheilsCommented:
Happy to see that you've worked it out but you could reduce a lot of coding by changing the letters frogramatically. EG

	 If Not Rst1.EOF Then
		Rst1.MoveFirst
		Dim strLetter As String
		strLetter="a"

		While Not Rst1.EOF
		   
			PhotCount = PhotCount + 1
			
			With Forms!0_masterdatafrm!CASp_Input04_WorkingChecklistDetailSubFrm

				!("Photo" & strLetter).Caption = Rst1![PhotoID]
				!("Image" & strLetter).Picture = Rst1![RefFilePhoto]
				!(Photo & strLetter).Visible = -1
				!(Image & strLetter).Visible = -1

			End With
			
			strLetter = Chr(Asc(Letter) + 1)

		WEND

	End If

Open in new window

0
 
rogerdjrAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.