Solved

Access Display Vertical Data Horizontally

Posted on 2014-02-01
6
1,340 Views
Last Modified: 2014-02-09
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
...
0
Comment
Question by:rogerdjr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 39827327
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
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 39827522
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
 

Author Comment

by:rogerdjr
ID: 39840288
Looks like a workable solution - need some time to put it together - I'll update when I have it working
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:rogerdjr
ID: 39844783
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
 
LVL 16

Expert Comment

by:Sheils
ID: 39844870
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
 

Author Comment

by:rogerdjr
ID: 39845444
thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question