Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Access Display Vertical Data Horizontally

Posted on 2014-02-01
6
Medium Priority
?
1,422 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

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.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

648 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