?
Solved

Access Display Vertical Data Horizontally

Posted on 2014-02-01
6
Medium Priority
?
1,381 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 2000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 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