Amdall Gallery

I’ve posted quite a bit regarding my love for Excel and Pivot Tables, but I haven’t actually mentioned my other source for data-crunching fun – Microsoft Access. Occasionally, I’ll come across some data structure or method of viewing that can’t be accomplished using Excel (or can’t be accomplished well). That’s when I’ll jump into database design.

One example of this has to do with art archiving and searching. I wanted to see if there was a better way to make my drawings easier to browse and index, with image previews and other information. Here’s the end result:

Image 1. Basic browsing with the Art Database, Normal Zoom.

The primary search form works exactly as I intended; you can use the Theme Search box at the top to type something, or the Inspiration dropdown to select where the art came from. Clicking on choices in the results box on the left changes the image and details in the viewer on the right. Also, a side-note on the “price”; that is a totally arbitrary number range I assigned based on how satisfied I was with the drawing.

Image 2. Searching the Themes and Notes Fields, 125% Zoom.

The only thing I’m disappointed about is that I originally made this using MS Office 2013, and when I upgraded to Office 2016, it broke my entire statistics page. Apparently, Microsoft removed graph functionality from Access, assuming everyone who make a graph should do so in Excel. Oh well.

I will say creating a database like this is a bit higher on the difficulty scale than processing data via a spreadsheet. You have to create the data tables, then build the user-visible form (which is what you see above). This database also utilizes a subform on the main form. The functionality that allows you to click an item and view its image and details requires a bit of Visual Basic code via the built-in VB Editor that comes with office (it’s only six lines of code). The most time-consuming part to arranging and formatting the form, though.

Although I have no idea if anyone who reads this page is interested in building something like this, I’m going to paste my code below. I couldn’t even tell you how many times I’ve found and used some obscure Excel function via Google. So, maybe someone seeking a solution will find this useful. Here’s some Visual Basic, to affect clicking on a subform record to change what appears in controls on a form:

Private Sub InspirationSearch_Change()

‘The code in this database was written by Jon Amdall. For questions or comments, contact Jon via his website https://jonamdall.com/

Dim strFilter As String

Me.Refresh

strFilter = “Inspiration like ‘*” & Me.InspirationSearch & “*'”

Forms!Search!Subform_Table.Form.Filter = strFilter
Forms!Search!Subform_Table.Form.FilterOn = True

Me.InspirationSearch.SelStart = Nz(Len(Me.InspirationSearch), 0)

End Sub

This code is for the text box called “InspirationSearch.” To make the actual controls on your form change, you have to change the Control Source field on every control to say something like =[Subform_Table].[Form]![Inspiration]. The first part reference the data table, the last part references the column from that data table. If you’ve got the VB code attached to your search box, the form controls all worked up as I’ve indicated, it should work perfectly! If anyone is trying to make something like this and is having trouble, feel free to comment below; I’ll try to help if I can.