Solved

Access Display Vertical Data Horizontally

Posted on 2014-02-01
6
1,228 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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