Amdall Gallery

As I’ve mentioned many times on this site, I am a huge fan of Microsoft’s data-crunching solutions. I’ve gained a reputation for it too; when I am about to offer a solution to something at work, co-workers usually say something like, “let me guess, it’s a pivot table.” Actually, usually it does involve pivot tables…but when I’m not preaching about a spreadsheet, the solution might be an Access database.

Last year, I shared my master list compilation of Excel functions. Although it’s less extensive, I also have a small repository of Access VBA (Visual Basic for Applications) code that I use fairly regularly. I developed this stuff over time, and the Access tricks were more difficult than Excel because I was (and still am) less familiar with how VBA works. I thought sharing some of this material could save someone time, because it was a challenge to figure out these solutions even after searching online.

I’ll add a disclaimer to this: I am by no means fluent in VBA. This code came from a ton of trial and error, and my best efforts to teach myself how it works. I can probably answer most specific questions, but I definitely won’t be able to teach another person VBA basics, best practices, or anything like that. I just don’t have a good, comprehensive knowledge base on this subject.

Anyhow, here are some useful tips:

Remove the MS Access ribbon area:

Helpful for when you have a really huge form, and need more viewing space. Or when you have other users accessing the form, and you don’t want them roaming around clicking things.

Private Sub Form_Load()

DoCmd.ShowToolbar “Ribbon”, acToolbarNo

End Sub

——————-

Search box to query a sub-form as you type:

This is a live search functionality, which allows you to have a search text box which will search a subform on your main form. This code will search and filter as you type, so there’s no need to hit enter or have a separate button. Here’s a .gif to help illustrate this:

In my example, the search text box is called “SearchBox,” the form is called “Search,” the subform is called “Subform,” and the field being queried in the subform is called “LastName.”

Private Sub SearchBox_Change()

Dim strFilter As String
Me.Refresh
strFilter = “LastName like ‘*” & Me.SearchBox & “*'”
Forms!Search!Subform.Form.Filter = strFilter
Forms!Search!Subform.Form.FilterOn = True
Me.SearchBox.SelStart = Nz(Len(Me.SearchBox), 0)

End Sub

——————-

Variant: Search box to query multiple fields in a sub-form as you type:

This is just a variant or more complex version of the above code. It does the same thing (live search box which filters as you type), but will compare multiple fields in your subform at once. This is useful if you have redundant fields that you want to search, or you think something could be in multiple.

In my example, the search text box is called “SearchBox,” the form is called “Search,” the subform is called “Subform,” and the fields being queried in the subform are called “FirstName” and “LastName.”

Private Sub SearchBox_Change()

Dim strFilter As String
Me.Refresh
strFilter = “FirstName like ‘*” & Me.SearchBox & “*’ OR LastName like ‘*” & Me.SearchBox & “*'”
Forms!Search!Subform.Form.Filter = strFilter
Forms!Search!Subform.Form.FilterOn = True
Me.SearchBox.SelStart = Nz(Len(Me.SearchBox), 0)

End Sub

——————-

Show data in the main form based on a record click in a sub-form:

This is something I use in conjunction with the above search functionality. Based on whatever record I click in my subform results, I want to show other fields related to it. For example, let’s say my searched subform is pretty narrow and only shows first and last names. But within the original table, I have a ton of extra information (DOB, favorite food, allergies, etc). As long as the additional table fields are included in the subform (even if they aren’t visible), I can display them on the main form. Here’s a .gif to illustrate this:

Just type something like this into a text box (Note: This does not go into VBA. Just type it directly into the control):

=[Subform].[Form]![Name]

Whatever data is associated with the record you clicked will appear. Clicking a different record will update this in real time.

(Tip: This works for images too! Just include a file path in an image related field, when you reference it this way, it will show the actual image)

——————-

Clicking a button on the main form opens the selected record:

Although the code is short, this one was actually pretty difficult to figure out (for me anyway). As shown above, I had the main search done, and clicking on a record to show associated data…what if I ran out of room though? In that instance, I wanted to use a button to open another window (form), but didn’t know how to make it reference the selected record (it defaults to the first record). There is actually an easier way to do this through the button control wizard, which I found out later. But here is some VBA code that does the same thing, if it’s useful for anyone. And a .gif of what I’m talking about:

In this example, the name of the button control is “Button,” the new window/form is called “NewWindow,” and I used a hidden text control called “Bridge” to link the selected subform record to my new window. This “Bridge” references the primary key ID using the previous trick (=[Subform].[Form]![ID]).

Private Sub Button_Click()

DoCmd.OpenForm “NewWindow”, , , “ID = ” & Me.Bridge

End Sub

——————-

Show a person’s age in a form field based on the DOB:

To show a person’s age within a field, you can use this code inputted directly into a text box control. This assumes the person’s date of birth is stored in a field called “DOB.”

=(DateDiff(‘d’,[DOB],Date())/365.25)

You might also want to pop this bit of VBA code in there to make sure it updates as you type. Otherwise, you might have to hit “Enter” after typing:

Private Sub DOB_Change()

Me.Refresh

End Sub

Or…

Private Sub Combo88_AfterUpdate()

DoCmd.Requery “Age”

End Sub

I have both bits of VBA code in one of my databases, but for some reason I think only one ended up being important. But it’s possible both are necessary to cover all usage circumstances.

——————-

Well, those are my favorite tricks for MS Access! There’s a wealth of additional functionality though, and this post barely scratches the surface. But these little pieces have been very useful to me, and were a bear to troubleshoot and cobble together. Maybe someone who is trying to figure out how to set up a real time/live search button will stumble onto this post and get some use out of it! If any readers have any questions, feel free to ask away in the comments section.

Also, since I’ve been adding “items used/discussed” links to posts recently, I thought I’d add something here too. I don’t actually own any of the items below, but maybe I should start advertising my love of access databases and spreadsheets more? These items aren’t directly associated with me or anything, I just thought they were funny.