VBA code to display multiple images in sinfgle Form 1

[VBA2007]

Hello Everyone,
Can anyone please help me with the problem of displaying multiple images in a single form??

I want to display all the images in a "grid" or something like that. But unfortunately within a form its only displaying one record at a time. The image control is bounded to the image table.

If I make the form "continous form" then its showing one image in one row, where as I want to display images side-by-side.

Its really urgent, but I could not find any solution.

Any kind of help is really appreciated.

Thanks

 

[MajP]

Are they stored images, attachements, or paths? Is this 2007 or later? If it is 2007 or later you have many more options.

If it is 2007 or later and they are stored paths, my solution would be without code. I would make a query that put all the paths into the desired number of columns.

PathOne PathTwo PathTree

Once you do that then use bound image controls (access 2007 or later).

The query would be a "de-normalizing" query, which would be a little tricky but should be doable. I am not great at Sql so I would probably build the table with the desired columns, then use dao/ado to read the original table and push the paths into my "grid Table". Both achieving the same end result.

The other option is to do this unbounded. But then your grid would be a set number of images per page. Which is probably better anyways. More like a webpage with "show next 20" at the bottom.

 

[VBA2007]

Thanks a lot MajP for replying. I am using Access 2007 and storing the path. The coding for displaying the images in the form by extracting the path and the image names from the table is not that difficult.

For the 1st option, I can definitely make a query but I don't know how it will work for me as in my case one table only contains image names and the other table contains path. The images are not embedded within the database. Could you please clarify more how your 1st option might work.

For the 2nd option, I am not aware of the grid control. I can't see it readily in the toolbar. Even if I find the grid and manually place 20 imagebox in 20 different cell how will each record (the images) be displayed in proper image control??

Please help me.

I think if I store the images as bitmap in the table may be the query can work. But that only takes .bmp .

I am so confused and don't know what to do.

Please see the attached file. The form is displaying individual image. I want all the images right next to each other. In the zip I placed a word file that shows how I want the screen to be.

I don't think it is impossible for experts like you but I am having tough time figuring this out.

Thanks once again

 

[MajP]

I will take a look and get back. For the second option, there is no grid control to do this, but you would make a "grid" by putting N x N image controls. I have not looked at your database yet, but there is one thing that many people are not aware of starting in 2007. The control is a "bound image control". There is not code required for this. If the control source is a path it displays the image. Prior to that this required a lot of code.

 

[VBA2007]

I am aware that image control could be bounded to the table but I don't know how "image control1" will map with record 1st "image1" and "image control2" will map to record 2nd "image2" from the same field and so on...

I can't figure out how to connect individual control to an individual record from the same field or column of the table.

Thanks

 

[MajP]

In this demo I use dao to "denormalize" my images. If you can do this in SQL then no code is required. If all your images are png then you will not have to store the extension if not you need to store it.

http://www.4shared.com/zip/WxEG1rak/file.html

 

[MajP]

However, if you cannot figure out the sql then you can simply modify the "tblGrid" and the code to fit the amount of desired columns and the order of the images in the columns.

 

[VBA2007]

Wow, you are great. Thanks a lot for your help. This is really good.

One more thing, actually this project is meant to calculate the nutritional value of fast food items. So I wanted to put checkboxes with each image. When anyone clicks on number of checkboxes I am planning to calculate the total calories and display.

I added a field Check (Yes/No Data Type) in table "tblGrid" and even added 2 checkbox control each under each image control in the Form. If I bound checkbox controls with that of table then at runtime horizontal (2 checkboxes) at a time get selected and de-selected. If I keep unbounded checkboxes then during runtime vertically means all checkboxes in one column get selected and de-selected all at the same time. I think to make it work I have to create the checkboxes through coding just to keep individual checkbox independent of each other. Could you please help me with the code to generate this please?

I hope this is not too much to ask for. I really appreciate your time and help.

Thanks

 

[MajP]

If I was going to do this staying with the same paradigm, I would modify my grid table. Lets assume it would be three columns.

tblGrid
img1ID (foriegn key to the image table)
img1Name (the image name)
img1Selected (boolean field yes/no)
img2ID
img2Name
img2Selected
img3ID
img3Name
img3Selected


This gives me a bound field to store the selection for each image and the field to relate it back to the "item" table.

Now you should be able to do a union query to get this back to normal

select
img1ID as imgName,
img1Selected as Selected
from
tblGrid
UNION
Select
img2ID,
img2Selected
from
tblGrid
....
That will give you a query of selected items. (I am assuming that the image ID somehow relates to the actual item)

 

[MajP]

This
img1ID as imgName
should read
img1ID as ImageID

 

[VBA2007]

Thanks I will try to do this. Hopefully it will work for me.

Thanks once again

 

[VBA2007]

Hello MajP,

I did make changes in the tblGrid and its working but when should I call the "query"?? Moreover even when I made the two statements "makegrid" (calling function) and "me.requery" as comment still its showing the images.

The only problem is that the same grid is repeated 10 times. Don't know where I am going wrong.

Yesterday, I thought I was done with this but again I am stuck .

I am sure you can solve this problem in no time. Please please help me. This is really important.

Thanks in advance

 

[VBA2007]

Sorry, its working I just saw that in the delete statement I did not mention the name of proper table. aahhh At last its working.

Thanks a ton for your help.

I still have a quick question, at the end of the form appears to blank checkboxes with no images. How to get rid of them??

I guess I should use a command button to go to the next screen. Before that internally I would calculate how many checkboxes were selected. For this if I put a button at the bottom of image control its appearing again and again. So may be I should make this image display in a subform and in Main form with the command button.

How can I invoke the query from the subform??

 

[MajP]

If you set the allow additions property of the form to "false" that should eliminate the blank record.

I could not decipher your last question.

 

[VBA2007]

Elimination of blank record worked perfectly.

My other question is regarding how to track the checkboxes if they are selected or not. In your earlier post you wrote that I need to use a query to do that.

I made the query but don't know from where I should invoke the query and pass the values from the current form into the query.

Based on the food items I have to find the nutritional values.

Thanks a lot for your prompt answers

 

[VBA2007]

Good Morning,
I am back with more problems.

The query seems to be working fine but whenever I am selecting a checkbox and then de-selecting it and again re-selecting it, in the query view the "Selected Column"(yes/No Data Type) is displaying -1 instead of 1. How to fix this problem??

Another problem the images in the form are appearing randomly, not sorted according to the image ID.

The function code is :

Public Sub makeGrid()
  Dim rsImages As DAO.Recordset
  Dim rsGrid As DAO.Recordset
  Dim strSql As String
  Dim strPath As String
  Dim img1 As String
  Dim img2 As String
  Dim img3 As String
  Dim img4 As String
  Dim img1ID As Integer
  Dim img2ID As Integer
  Dim img3ID As Integer
  Dim img4ID As Integer
  
  strSql = "delete * from tblGrid1"
  'clear out grid
  CurrentDb.Execute strSql
  'loop images and put into grid
   strSql = "Select * from tblImages"
  Set rsImages = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  strSql = "Select * from tblGrid1"
  Set rsGrid = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  strPath = CurrentProject.Path
  'The following can be modified for any amount of columns
  Do While Not rsImages.EOF
    img1 = strPath & "\" & rsImages!image & ".png"
    img1ID = rsImages!imageID
    rsImages.MoveNext
    If Not rsImages.EOF Then
         img2 = strPath & "\" & rsImages!image & ".png"
         img2ID = rsImages!imageID
    End If
    If Not rsImages.EOF Then
        rsImages.MoveNext
    End If
        If Not rsImages.EOF Then
            img3 = strPath & "\" & rsImages!image & ".png"
            img3ID = rsImages!imageID
        End If
        If Not rsImages.EOF Then
             rsImages.MoveNext
         End If

        If Not rsImages.EOF Then
            img4 = strPath & "\" & rsImages!image & ".png"
            img4ID = rsImages!imageID
        End If
        If Not rsImages.EOF Then rsImages.MoveNext
        rsGrid.AddNew
        rsGrid!img1 = img1
        rsGrid!img1ID = img1ID
      'Forms!frmGrid1.Image0.ControlTipText = img1
        If Not img2 = "" Then
            rsGrid!img2 = img2
            rsGrid!img2ID = img2ID
      'Form.frmGrid1.Image1.ControlTipText = "Food 2"
        End If
        If Not img2 = "" Then
            rsGrid!img3 = img3
            rsGrid!img3ID = img3ID
        End If
        If Not img4 = "" Then
            rsGrid!img4 = img4
            rsGrid!img4ID = img4ID
        End If
        
    
    rsGrid.Update
    img2 = ""
    img3 = ""
    img4 = ""
 Loop
End Sub

THE QUERY IS:

SELECT img1ID as imageID, img1Selected as Selected
FROM tblGrid1 
UNION 
Select img2ID, img2Selected from tblGrid1 
UNION 
Select img3ID, img3Selected from tblGrid1 
UNION 
Select img4ID, img4Selected from tblGrid1;

Thanks

 

[MajP]

The query seems to be working fine but whenever I am selecting a checkbox and then de-selecting it and again re-selecting it, in the query view the "Selected Column"(yes/No Data Type) is displaying -1 instead of 1. How to fix this problem??

Not sure how you are using the query. However, true is stored as -1 and false as 0. This is the correct behavior. If you are viewing this in a table or query view you can right click on a field and get the properties. You should be able to display as true/false, yes/no, and I think on/off.

The pictures are read from the images table but there is no Order by clause so they are random.

  'loop images and put into grid
   strSql = "Select * from tblImages"

So make that a proper sql string
strSql = "Select * from tblImages Order By YourfieldName

 

[VBA2007]

By using order by clause, all the items are now arranged according to the ID. I don't know what I was doing.

Sorry, I wasn't aware of the fact how yes/no values are stored internally. In that case my query is working properly.

Right now, I am just checking if the query is functioning properly or not. So viewing the result in "query view".I did not use the values from the query for further calculation.

I just put a command button in the header section of the form and on click am executing the query.

Somehow, whatever items I am selecting for the first time in the form, for example: 1, 2, 3, 4 in the "query view" its only showing 1, 2, 3 as selected. Then for the next time if I select 1,3,4 at that time its showing 1, 2, 3, 4 as selected, and for the next time if I select 5,6,7 its showing 1,3,4 and so on.

That means in the query the previous selection set is displayed. Its updating according to the previous selections and not according to the current selection.

Am I doing anything wrong or do I have to add something else??

Thanks once again.

 

[MajP]

Without seeing how you have this all coming together it is difficult. But it sounds like you need to requery your form to update the values.

 

[VBA2007]

Please see the attached file.

Thanks