Access and VBA Cheat Sheet

ms access header part 3

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.

Anyway, here are some tips (note: do not include the bullet numbers, those are here only for formatting purposes):

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()

  1. 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:

amdall gallery search example

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()

  1. Dim strFilter As String
  2. Me.Refresh
  3. strFilter = “LastName like ‘*” & Me.SearchBox & “*'”
  4. Forms!Search!Subform.Form.Filter = strFilter
  5. Forms!Search!Subform.Form.FilterOn = True
  6. 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()

  1. Dim strFilter As String
  2. Me.Refresh
  3. strFilter = “FirstName like ‘*” & Me.SearchBox & “*’ OR LastName like ‘*” & Me.SearchBox & “*'”
  4. Forms!Search!Subform.Form.Filter = strFilter
  5. Forms!Search!Subform.Form.FilterOn = True
  6. 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:

amdall gallery search and click example

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:

amdall gallery button example

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()

  1. 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)

——————-

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.

Advertisements

2 comments

Leave a Reply