Sunday, June 2, 2019

List Table Fields

I recently needed a way to list all the table fields from an Access database project, so I could export it into Excel and format it for the user's review (just to make sure I covered all the user's requirements). Unfortunately, all I found, looking around for a utility to do it, was a way to print a report showing all the fields. Unless I simply miissed it, I couldn't find one that exports it to Excel for further consumption. So, I decided to create this demo.

This demo is similar to my other "list" demos where you get to browse for the database file and clicking a button will populate a temporary table with all the information gathered from the selected database. You can then export the table's data into Excel, as desired. The only difference with this new version is that it should be able to handle a password-protected file. If the selected file is password protected, the user is presented with a prompt to enter the password.

To download this demo, please click on the image below. As usual, if you have any comments or suggestions for improvements or found any bugs with this demo, please do not hesitate to let me know. I would really appreciate it.

(click on the above image to download the demo file)

Thank you for reading!

Saturday, May 11, 2019

Listbox Selected Property and the Form Load Event

Here's something new I just learned from a recent forum discussion: A Listbox's AfterUpdate event will fire when VBA is used to select an item on the list using the Form's OnLoad event. What? Did you all know that? I wasn't aware of it until now.

Typically, the AfterUpdate event of a control does not fire when the value of that control was updated using code. If you want the code behind the AfterUpdate event to execute when you update a control using VBA, you will have to specifically call it. For example:

Call ListboxName_AfterUpdate

However, as I was saying, there is a specific scenario (I know of only one, so far) where a Listbox's AfterUpdate event will fire, without specifically calling it, when the Listbox is manipulated using VBA code. Here's the deal...

As demonstrated in this forum discussion, the OP used the Selected property to "highlight" an item in a Listbox during the Form's Load event. Interestingly enough, this causes the form to fire the Listbox's AfterUpdate event as well. Typically, using the Selected property does not update the value of a Listbox, which means the AfterUpdate event shouldn't fire. But for some reason, this is not the case during the Form's Load event. It turns out, setting the Selected property of an item in a Listbox during the Form's Load event also updates the Listbox's Value property. However, there is a twist... If we used the "normal" way of updating the Value of the Listbox, then the AfterUpdate event does not fire. To clarify, we can use any of the following methods to change the Value of the Listbox.

Me.ListboxName = "SomeValue" 'if it's bound to a Text column
Me.ListboxName = 1 'if it's bound to an ID column
Me.ListboxName = Me.ListboxName.ItemData(2)

So, the puzzle is, what is going on here? Why does it happen when we use the Selected property? Also, why does it only happen during the Form's Load event? Once the Form is fully opened, using the Seleted property merely highlights the item. It does not update its Value, and the AfterUpdate event does not fire. Equally, using any of the methods shown above to modify the Value of the Listbox at this point also does not fire the AfterUpdate event.

If you have any ideas or logical explanation for this, I would really like to hear it. If I find out the answer somewhere else, I will definitely let you know.

Thank you for reading!




Thursday, December 27, 2018

Keyboard Key Code Values

This was an old article on my website, which I thought might be good to resurrect in case people might still find it useful.

The Form's Key events can be used to determine which key on the keyboard was pressed so that certain user actions can be intercepted. Access provides some built-in KeyCode Constants that can be used for this situation. However, I couldn't find a complete list of all the possible Key Code Values anywhere, so I decided to create this page.

Below is an image of a typical keyboard layout. Hovering your mouse over each key will display the corresponding Key Code Value.











Enjoy!

Tuesday, November 6, 2018

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with parameter queries, you can review my article on Parameter Query Basics.

The following SQL statement is an example of  a parameter query.

SELECT Title, FirstName, LastName, Position
FROM Employees
WHERE Department=[Enter Department]

The above query will prompt the user to enter a department and the result will show all the employees working for that department. So, users are free to enter any department they are interested to see, each time they run the query. But what if the user wants to see all the employees for more than one department?

Problem


Typically, in a non-parameterized query, the SQL statement might look something like this.

SELECT Department, Title, FirstName, LastName, Position
FROM Employees
INNER JOIN Detpartments
ON Employees.DeptID=Departments.DeptID
WHERE Employees.DeptID In(1,3,5)

In the above example,  we switched to using the foreign key (DeptID), which would be a more likely scenario anyway and also to make demonstrating the technique being presented a bit simpler to explain. Also, please note the In() clause in the above example is a shorthand for the following criteria (meaning, it's functionally the same).

WHERE Employees.DeptID=1 OR Employees.DeptID=3 OR Employees.DeptID=5

So, our goal is to prompt the user to enter multiple DeptID using a parameter. One might be tempted to try the following as a query criteria.


WHERE DeptID In([Enter DeptID])

and expecting the user to simply enter 1,3,5 when prompted. Unfortunately, this seemingly straightforward approach will not work because Access will interpret the entire input as a String or Text. So, entering 1,3,5 does not mean 1 or 3 or 5; but rather, a string value of "1,3,5" - and we know there won't be any DeptID of "1,3,5" because DeptID is a numeric field. So, the query will result in an empty set because the intended criteria fails. Of course, the obvious solution is to use the following criteria.

WHERE DeptID=[Enter Dept1] OR DeptID=[Enter Dept2] OR DeptID=[Enter Dept3]

The above approach will work, but the user will be prompted to enter a single DeptID three times, which would be fine but what if the user wants to know more (or less) than three DeptID? As you can see, this approach is not very flexible.

Solution


Instead of using the above approach, we could try either of the following approaches.

WHERE InStr(";" & [Enter DeptID (separated by a semicolon ';')] & ";", ";" & DeptID & ";")>0

or

WHERE ";" & [Enter DeptID (separated by a semicolon ';')] & ";" Like "*;" & DeptID & ";*"

When the user runs the query and gets the prompt, we are expecting the user to enter something like 1;3;5, which in our criteria will be converted into ;1;3;5; (I prefer to use semicolons but using commas would work the same way).

The first approach uses the InStr() function to search the current record's DeptID, encapsulated within a pair of semicolons, within the user's input string from the parameter prompt. If a match is found, the result of the function should be a number greater than zero (0), which would make the criteria evaluate as True and include the current record in the query's result set.

The second approach simply uses the Like operator with a pair of wildcard characters to accomplish the same effect as the first approach. I prefer to use the second approach because it is simpler and avoids a function call, but either approach will work as intended.

Caveat


Unfortunately, this solution is not perfect. The first drawback is you'll have to "train" your users to enter their input using the exact format your query is expecting (e.g. 1,3,5 or 1;3;5 or 1 OR 3 OR 5). Otherwise, the query will not work if the user enters their input using any other format (e.g. user enters 1,3,5 but you expect 1;3;5). Adding the expected format in the parameter prompt, as in the example above, should help a little bit in reminding the user what format is expected.

The other drawback I would like to point out affects any parameter query, in general. As I mentioned in my article on Parameter Query Basics, there is really no way to validate the user's input when using a parameter query. Your query might be asking users to enter a DeptID in the parameter prompt but nothing will stop them from making a mistake and end up entering a DeptName instead. For this reason, it is considered "best practice" to use an unbound form to accept user input for your query parameters. Using a form will allow you more control over the user's input. You can easily validate their input when you use a form.

The last point I'll mention, if you do end up using parameters in your query, is to make sure you declare the data type for each parameter. This is more applicable when each parameter is a single value. To declare the parameter's data type, you can click on the Parameters button on the Design Ribbon to open the Query Parameters window.



To declare the parameter data types manually, you can add the PARAMETER clause at the beginning of your query's SQL statement. For example:

PARAMETERS [Enter DeptID] Long, [Enter Department] Text ( 255 );
SELECT ...
FROM ...
etc...

Please note, it is very important to end the PARAMETER clause with a semicolon, or you will get a syntax error. Also, the PARAMETER clause is typically required in Crosstab queries.

Conclusion


Parameter queries allow us to create semi-dynamic queries, which could help reduce the number of queries we need to create if all we need to change is the criteria condition of the query, based on user input. It is even possible to create a parameter query that accepts multiple values from the user. However, parameter queries have some limitations that it is highly recommended to use input forms whenever possible. Still, parameter queries are good as quick solutions for those times when the requirement is simple enough with very low chance of user input errors.

Tuesday, October 30, 2018

Happy Halloween to Gremlins and Zombies

About three years ago, I wrote this blog article on Combobox Gremlins. In perfect timing for the upcoming Halloween, I received a follow up comment from Colin Riddington trying to explain the reason for the issue I described in that article. He was so inspired by this phenomenon that he decided to post his own version of the same puzzle at Access World Forums.

So, in the spirit of Halloween we are about to celebrate tomorrow, I have decided to set the story straight, since I forgot to update the original blog with the solution to the puzzle. First, I must apologize to Philipp Stiefel, who helped me figure it out back then, because when I migrated my blog site to a new web host, his comment did not make it to the new site.

Philipp suggested the original table had a lookup field at one point but was then removed. However, the Row Source property for the field in the table still contains the Value List used by the original designer (it was an inherited database, I did not make it). If you download my original demo and execute the following code in the Immediate Window, you will see the Value List.

?CurrentDb.TableDefs("Searchtable").Fields("Appropiation").Properties("RowSource").Value

At the time, Philipp's explanation made sense to me and I just left it at that. It wasn't until Colin's follow up comment did I finally decide to find out the main reason why this is happening. At this point, I was lucky enough to be able to ask one of the Microsoft Access engineers (Shane Groff) for an explanation on this subject and this is what he said:

-QUOTE-

Phil is right about the original gremlin, it is easy to reproduce:

1) Create a table with a text field
2) Set the Display Control property to 'Combo Box', then set Row Source Type to Value List, and Row Source to a;b;c or whatever list items you want
3) Now change Display Control back to Textbox
4) Save and close the table
5) Make a new form, add a ComboBox, bind the form to your table, and the combo to your gremlin field
6) Switch to form view

Result: Your combobox has values, even though the don't appear anywhere in the table or form properties.

Even if you now switch the Display Control in the table back to Combo Box and the Row Source Type to Value List, you won't see the values (although if you save at that point, I think we clear the property).

This value is stored as a property on the table column, and we don't clear it when you change the Display Control property, so it still get's inherited by the form Combo Box (unless you  Inherit Value List to No).

-END QUOTE-

One other thing that Philipp told me (and reemphasized by Shane above) was setting the Inherit Value List property of the Combobox to No will remove the Gremlins (or Zombies) from the Form's Combobox; however, the Row Source property of the field in the table is still there (so, setting the property back to Yes will bring them back). To permanently remove the Row Source property from my Gremlins demo, the following code needs to be executed:

CurrentDb.TableDefs("Searchtable").Fields("Appropiation").Properties.Delete ("RowSource")

So, the above revelation seems to be one more reason to avoid using lookup fields at the table level. In fact, the owner of The Access Web is deciding to update the page on The Evils of Lookup Fields in Tables to include this information.

I hope this article helps explain what was going on with my Gremlins demo and Colin's Zombies puzzzle. I want to thank all who helped figure out this puzzle.

Happy Halloween to everyone!