Access product catalogue website style

[Faoineag]

Hello everyone, I would need the advice of some of you experts: I run my sporting goods sales business with an Access database, I have thousands of articles, each with its own product sheet, catalogued in categories, subcategories etc. To get a better overview I'd like to create a catalogue view like a website, with the various thumbnail photos and the code underneath which when clicked opens the corresponding product sheet. Do you think this is feasible in Access, and if so could you give me some advice on how to do it? I have no idea how to do it. I thank those who can help me in advance

 

[silentwolf]

Hi,
not sure if I understand the view you like to create but in Access this could be done via Reports.
First you need the Datastructure to be in place then Queries, Forms and Reports.
Those reports can be as you wish and have buttons which open the corresponding Datasheet of that product.

Not sure what experience you have in access and what have you already created in that respect.

So I guess a bit more details on how you like to look of your application via screenshot of a website wich you like to "copy" would help to get a feel for what is required.

Hope that gives you a start for the project.

 

[tvanstiphout]

One thought is to create a Catalog report, similar to the one in Northwind 2 Developer Edition template, and run it in Report View (rather than Print Preview). That makes the report sensitive to mouse clicks on the various controls. The code-behind can then find out the PK of the record you clicked on, and open the next form.

 

[plog]

I'd like to create a catalogue view like a website,

Like a website or an actual website? If you want this available in a browser and on the internet for anyone to use, then I would not recommend Access. There are better options for that, probably some open source, plug-in-play just load data systems available for that.

If you just want this to live in Access and just be available through it, then yes you can achieve that with Access. My suggestion would not be a report though, but Forms. Since you want them linked and need some sort of interactivity on them, Forms would be a better method for achieving that.

 

[CJ_London]

Be aware the image control does not get the focus so you would need to use the click or double click event to identify which image has been clicked.

Other thing to be aware of is if your image files are large you may start getting ‘none display errors’

I recently developed a photo cataloguer app which uses a web control to display images which are automatically reduced to a ‘thumb’ size. I’m away from my computer right now by can upload here if of interest. It does a lot more than you require (such as drag and drop) but I can point you towards the relevant code. Let me know

 

[Faoineag]

Thank you all, I would like to create a catalogue view similar to a site, not an actual site, I need it to have an overview of the articles corresponding to each category because the images are more intuitive than the actual list (of names) of the articles. What I don't know how to do is to create the grid of images filtered by category. To explain myself better I enclose a diagram I drew: ‘Category Selection’, ‘Sport Selection’ and SubCategory Selection’ are 3 cascading filters, the images below are the filtered articles. I have downloaded the Northwind 2 Develope Edition, I will study it at my leisure

 

[CarlettoFed]

Attach the file with about ten articles and the related images separately.I'll show you how it can be done.

 

[Faoineag]

Sorry, I had forgotten the buttons of the corresponding article codes: clicking on ‘Product Code’ would open the detailed sheet of the corresponding product

 

[Faoineag]

Attach the file with about ten articles and the related images separately.I'll show you how it can be done.

OK, I'll prepare it. Thank you very much

 

[CarlettoFed]

Possibly with different Sports, Categories and Sub-Categories.

 

[Mark_]

Just to save you a LOT of headache, please convert all images to PNG files. If they are in a different format they may or may not load in the image control.

 

[Edgar_]

You can use the browser control to show something like this:

Doing that requires browser emulation set to IE11, which requires you to add a DWORD key with value 11001 to the path:
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION

You could also use the Edge browser control, good luck with that, though.

Attached is the sample file, but I'll explain what I'm doing.
1. On form load, I empty the browser's default content and make the browser's document respond to events

Private Sub Form_Load()
    Me.wbBrowser.ControlSource = "=""about:blank"""
    Set doc = Me.wbBrowser.Object.Document
End Sub

2. On after update of category combo box, I open a recordset to bring the products and I clear the browser for subsequent changes of the combo box. Not clearing the browser will simply append products below, we don't need that in this context.

Dim db As DAO.Database
Set db = CurrentDb
  
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM products WHERE CategoryID = " & Me.cboCategory)
  
doc.body.innerHTML = ""

3. Before traversing the recordset, I initialize the products page, it's just a container div with display flex and set to wrap. We do that to ensure the position of the products changes according to the screen size.

Set productPage = doc.createElement("div")
productPage.Style.display = "flex"
productPage.Style.flexWrap = "wrap"

4. While traversing the recordset, I initialize the product card, which is just a div, and I give it an id from the recordset. We will use the id to open the form on click later. This div is for containing a single product, the previous div is for containing all of the products.

Do While Not rs.EOF
    Set product = doc.createElement("div")
    product.id = rs!ProductID

5. Now I initialize the image and assign the URL from the recordset to it. You can use a local file too. Then, I append the image to the product card.

Set image = doc.createElement("img")
image.src = rs!ImagePath
product.appendChild image

6. Now I initialize the title of the product, which is just a div as well, and I assign the title from the recordset to it. Then, I append the title to the product card and now it is complete.

Set title = doc.createElement("div")
title.innerText = rs!productName
product.appendChild title

7. Since the product card has been built in memory, I just append it to the container of products and move on with the next product.

productPage.appendChild product
rs.MoveNext
Loop

8. Now that I have appended all of the products to the products page, we append the result to the body of the browser's document.

doc.body.appendChild productPage

9. Now, to respond to clicks, we use the click event of the document and check what we're clicking, if it has an id, we use that to open the form. Not doing that check would make the click respond to any click on the document, which is essentially any click. We just want to respond to clicks on the product cards.

Private Function doc_onclick() As Boolean

    Dim id As Variant
    id = doc.activeElement.id
  
    If Len(id) > 0 Then
        DoCmd.OpenForm "product", acNormal, , "ProductID = " & id, , acDialog
        cboCategory_AfterUpdate
    End If
  
End Function

And this is how the variables were declared, so the HTML library must be referenced.

Option Compare Database
Option Explicit

Private WithEvents doc As HTMLDocument
Private productPage As HTMLDivElement
Private product As HTMLDivElement
Private image As HTMLImg
Private title As HTMLDivElement

Spoiler: Here's the full code

Option Compare Database
Option Explicit

Private WithEvents doc As HTMLDocument
Private productPage As HTMLDivElement
Private product As HTMLDivElement
Private image As HTMLImg
Private title As HTMLDivElement

Private Sub cboCategory_AfterUpdate()
    Dim db As DAO.Database
    Set db = CurrentDb
  
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("SELECT * FROM products WHERE CategoryID = " & Me.cboCategory)
  
    doc.body.innerHTML = ""
  
    Set productPage = doc.createElement("div")
    productPage.Style.display = "flex"
    productPage.Style.flexWrap = "wrap"
  
    Do While Not rs.EOF
        Set product = doc.createElement("div")
        product.id = rs!ProductID
      
        Set image = doc.createElement("img")
        image.src = rs!ImagePath
        product.appendChild image
      
        Set title = doc.createElement("div")
        title.innerText = rs!productName
        product.appendChild title
      
        productPage.appendChild product

        rs.MoveNext
    Loop
  
    doc.body.appendChild productPage
End Sub

Private Function doc_onclick() As Boolean

    Dim id As Variant
    id = doc.activeElement.id
  
    If Len(id) > 0 Then
        DoCmd.OpenForm "product", acNormal, , "ProductID = " & id, , acDialog
        cboCategory_AfterUpdate
    End If
  
End Function

Private Sub Form_Load()
    Me.wbBrowser.ControlSource = "=""about:blank"""
    Set doc = Me.wbBrowser.Object.Document
End Sub

So, as you can see, the approach is the standard used in web development but with VBA. You basically create an element to be able to customize it, once it's done you append it to another element.

Remember: you need browser emulation for this to work. If you don't want to emulate IE11 because you're afraid or unable to change registry keys, you'll have to deal with string manipulation, which is really tedious and the code looks abysmal, but you're free to choose.

I added a "fancy" version with hover effects to illustrate the huge possibilities. Styles can be added in many ways in HTML, that's why I'm using two approaches here. Check the example. I hope this helps you.

 

[Faoineag]

You can use the browser control to show something like this:
View attachment 117953

Doing that requires browser emulation set to IE11, which requires you to add a DWORD key with value 11001 to the path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION
View attachment 117954

You could also use the Edge browser control, good luck with that, though.

Attached is the sample file, but I'll explain what I'm doing.
1. On form load, I empty the browser's default content and make the browser's document respond to events

Private Sub Form_Load()
    Me.wbBrowser.ControlSource = "=""about:blank"""
    Set doc = Me.wbBrowser.Object.Document
End Sub

2. On after update of category combo box, I open a recordset to bring the products and I clear the browser for subsequent changes of the combo box. Not clearing the browser will simply append products below, we don't need that in this context.

Dim db As DAO.Database
Set db = CurrentDb
  
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM products WHERE CategoryID = " & Me.cboCategory)
  
doc.body.innerHTML = ""

3. Before traversing the recordset, I initialize the products page, it's just a container div with display flex and set to wrap. We do that to ensure the position of the products changes according to the screen size.

Set productPage = doc.createElement("div")
productPage.Style.display = "flex"
productPage.Style.flexWrap = "wrap"

4. While traversing the recordset, I initialize the product card, which is just a div, and I give it an id from the recordset. We will use the id to open the form on click later. This div is for containing a single product, the previous div is for containing all of the products.

Do While Not rs.EOF
    Set product = doc.createElement("div")
    product.id = rs!ProductID

5. Now I initialize the image and assign the URL from the recordset to it. You can use a local file too. Then, I append the image to the product card.

Set image = doc.createElement("img")
image.src = rs!ImagePath
product.appendChild image

6. Now I initialize the title of the product, which is just a div as well, and I assign the title from the recordset to it. Then, I append the title to the product card and now it is complete.

Set title = doc.createElement("div")
title.innerText = rs!productName
product.appendChild title

7. Since the product card has been built in memory, I just append it to the container of products and move on with the next product.

productPage.appendChild product
rs.MoveNext
Loop

8. Now that I have appended all of the products to the products page, we append the result to the body of the browser's document.

doc.body.appendChild productPage

9. Now, to respond to clicks, we use the click event of the document and check what we're clicking, if it has an id, we use that to open the form. Not doing that check would make the click respond to any click on the document, which is essentially any click. We just want to respond to clicks on the product cards.

Private Function doc_onclick() As Boolean

    Dim id As Variant
    id = doc.activeElement.id
  
    If Len(id) > 0 Then
        DoCmd.OpenForm "product", acNormal, , "ProductID = " & id, , acDialog
        cboCategory_AfterUpdate
    End If
  
End Function

And this is how the variables were declared, so the HTML library must be referenced.

Option Compare Database
Option Explicit

Private WithEvents doc As HTMLDocument
Private productPage As HTMLDivElement
Private product As HTMLDivElement
Private image As HTMLImg
Private title As HTMLDivElement

Spoiler: Here's the full code

Option Compare Database
Option Explicit

Private WithEvents doc As HTMLDocument
Private productPage As HTMLDivElement
Private product As HTMLDivElement
Private image As HTMLImg
Private title As HTMLDivElement

Private Sub cboCategory_AfterUpdate()
    Dim db As DAO.Database
    Set db = CurrentDb
  
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("SELECT * FROM products WHERE CategoryID = " & Me.cboCategory)
  
    doc.body.innerHTML = ""
  
    Set productPage = doc.createElement("div")
    productPage.Style.display = "flex"
    productPage.Style.flexWrap = "wrap"
  
    Do While Not rs.EOF
        Set product = doc.createElement("div")
        product.id = rs!ProductID
      
        Set image = doc.createElement("img")
        image.src = rs!ImagePath
        product.appendChild image
      
        Set title = doc.createElement("div")
        title.innerText = rs!productName
        product.appendChild title
      
        productPage.appendChild product

        rs.MoveNext
    Loop
  
    doc.body.appendChild productPage
End Sub

Private Function doc_onclick() As Boolean

    Dim id As Variant
    id = doc.activeElement.id
  
    If Len(id) > 0 Then
        DoCmd.OpenForm "product", acNormal, , "ProductID = " & id, , acDialog
        cboCategory_AfterUpdate
    End If
  
End Function

Private Sub Form_Load()
    Me.wbBrowser.ControlSource = "=""about:blank"""
    Set doc = Me.wbBrowser.Object.Document
End Sub

So, as you can see, the approach is the standard used in web development but with VBA. You basically create an element to be able to customize it, once it's done you append it to another element.

Remember: you need browser emulation for this to work. If you don't want to emulate IE11 because you're afraid or unable to change registry keys, you'll have to deal with string manipulation, which is really tedious and the code looks abysmal, but you're free to choose.

I added a "fancy" version with hover effects to illustrate the huge possibilities. Styles can be added in many ways in HTML, that's why I'm using two approaches here. Check the example. I hope this helps you.

Wow, that sounds great! the image represents exactly what I was looking for. Thank you. Unfortunately I have no idea what browser emulation means. Also I have the problem with the combo boxes filtering the articles. I attach a very restricted version of the db to explain better. Is it really not possible to have a simpler solution that can be managed within the db, like a form in which the thumbnails of the filtered articles are aligned? Thanks anyway

 

[Faoineag]

I could not insert the db, it is 31 mb, what can I do? Thank you

 

[Pat Hartman]

This is the error I get when I open either form

 

[DHookom]

I could not insert the db, it is 31 mb, what can I do? Thank you

You have limited functionality in this forum until you have 100 posts. You are currently at 68.

 

[CarlettoFed]

You have to compact it first and then compress it.
If it remains too large you have to post the compressed file on a data sharing site, such as FileTransfer.

 

[Faoineag]

I created a LimeWire link but when I insert it in the response I get the error message that the content cannot be sent because it is spam

 

[Edgar_]

This is the error I get when I open either form

Thanks. It seems the document wasn't set up correctly in your environment. This might be due to the Access version you're using, but I'm really curious to find out the exact cause. Could you download the attached file and see if you get the same behavior? If you still get the same behavior, please post the contents of the immediate window here.

By the way, I updated the previous post to correct an important detail: the necessary registry key is located under HKEY_CURRENT_USER, not HKEY_LOCAL_MACHINE. The full path where the MSAccess.exe key with the value 11001 needs to be added is:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION

I discovered this while testing on another machine where HKEY_LOCAL_MACHINE didn't even have the key, yet it still worked because the key was present in HKEY_CURRENT_USER.

When the key is missing, it fails to handle flexWrap properly, as that property is only supported in IE11 and it's necessary for the items to wrap onto the next line when no more space is available, which is what makes the form responsive to any screen size. That also means a workaround can be used to avoid setting the registry key, which I'll try to find out.

 

[Edgar_]

I created a LimeWire link but when I insert it in the response I get the error message that the content cannot be sent because it is spam

Just edit the link, for example, instead of google.com, you can write google dot com

Unfortunately I have no idea what browser emulation means.

Internet browsers are really complex programs that must support old and new things and that is really hard to accomplish. Therefore, as most things, they have been evolving consistently and some things have been left behind. Browser controls have experienced something similar, so we have a few versions. Here's an overview:

MS Access for M365 has 3 browser controls:
1. ActiveX browser control (IE)
2. Legacy browser control (IE)
3. Edge browser control (Edge, which is Chrome under the hood)

MS Access 2024 and older versions have 2 browser controls:
1. ActiveX browser control (IE)
2. Legacy browser control (IE)

The browser controls:
I won’t talk much about the ActiveX browser control since it’s not even available in the toolbox.

The Edge browser control does work with modern frameworks, but I can’t say much about it because I don’t have access to M365. That said, I’ve seen people mention its bugs and issues, so I can’t fully recommend it.

On the other hand, the Legacy browser control is easy to find in the toolbox in many versions of Access and is generally stable. It doesn’t support websites built with modern frameworks since it struggles with syntax elements like arrow functions and other features commonly used today, but it works well if you design your websites to be compatible for IE11, which is a browser we were using not too long ago. Within those limitations, it’s still possible to create some powerful solutions.

Browser emulation:
The issue with the Legacy browser control is that it doesn’t support IE11 by default; it supports older versions. To enable support for IE11, which was the last version released, you need to modify the registry key to make it emulate IE11. This change will only impact Access. Other applications using the browser control, such as antivirus programs and others with rich UIs, will not be affected.