Filling in folder numbers in an MS Excel container list.

Here at the Kheel Center we have quite a few legacy container lists in Excel spreadsheets that list the folder titles for each box, without giving the folder numbers.

A	B
Box	Folder Title
1	Folder 1
1	Folder 2
1	Folder 3
2	Folder 4
2	Folder 5
3	Folder 6
3	Folder 7

Before converting the container list to EAD I wanted to insert the folder numbers. I was able to do so using a fairly simple If-Then-Else statement. First insert a new column between the Box and Folder Title columns.

A	B	C
Box	Folder	Folder Title
1		Folder 1
1		Folder 2
1		Folder 3
2		Folder 4
2		Folder 5
3		Folder 6
3		Folder 7

In the first cell of the Folder column, cell B2, enter the following formula:

=IF(A2-A1=1,1,B1+1)

What this does is subtract A1 from B2, which will give you either a 1 or a 0. If the value is 1 then the current row is the first folder of the box. If the value is 0 then the current folder is the next folder in the box and should have a folder number that is one greater than the one above it. When you have the formula done double-click on the little handle at the lower right corner of the cell. This is the fill-down function and will run the formula down the entire column.

Note: this formula will give an error for all the folders in the first box: you are trying to subtract text (“Box”) from a number in the first cell.

A	B	C
Box	Folder	Folder Title
1	#VALUE!	Folder 1
1	#VALUE!	Folder 2
1	#VALUE!	Folder 3
2	1	Folder 4
2	2	Folder 5
3	1	Folder 6
3	2	Folder 7

To correct the error simply temporarily replace the “Box” in A1 with a zero.

A	B	C
0	Folder	Folder Title
1	1	Folder 1
1	2	Folder 2
1	3	Folder 3
2	1	Folder 4
2	2	Folder 5
3	1	Folder 6
3	2	Folder 7

Select column B and copy, then Paste Special=>Values right over it. This will convert the formulas in column B to the values shown. After that you can change cell A1 back to “Box”.

You now have a container list with both box and folder numbers.

Next and Previous Buttons for MS Access Bound Forms

Normally I prefer to use unbound controls on my forms since I feel it gives me more flexibility and control, however on a recent project I needed to use bound controls. This form has 13 subforms in a tab control for 13 different audio-visual media formats. I couldn’t allow the users to create new records using the navigation buttons on the subforms since that would create records in the table with no way of linking them back to the proper subform. I, instead, added a “New Item” button to each subform, which creates the new record and inserts the media format into it, and deleted the navigation buttons. This meant I needed Next and Previous buttons on the subforms.

Back on 07Ju11 I explained how to make Next and Previous buttons on an unbound form. It’s a little different on a bound form. I did, however, want some of the same behavior. I don’t like the Previous button to show if you are on the first record or the Next button to show if you are on the last record. This prevents the user from click a button when there is nowhere to go. The code below is how I control the display of the buttons. I ended up including Previous, Next, and Last buttons, a First button could also be included if you like. I also included a text box to let the user know that they are on “record 1 of 7”, or wherever they are.

For the sake of simplicity I used the exact same code for all three buttons:

     Private Sub cmdPrevious _Click()

     'Private Sub cmdNext _Click()

     'Private Sub cmdLast _Click()

          Dim intIndex As Integer

          Dim intCount As Integer

          'For the Previous button use:

          DoCmd.GoToRecord , , acPrevious

          'For the Next button use:

          'DoCmd.GoToRecord , , acNext

          'For the Last button use:

          'DoCmd.GoToRecord , , acLast

          'Get the count of records in the form's record set.

          intCount = Me.Recordset.RecordCount

          'Debug.Print " Count = " & intCount

          'Get the cursor position in the recordset.

          'Since the cursor position for the first record is zero

          'add 1 to the cursor position so that the last one will equal the record count.

          intIndex = Me.Recordset.AbsolutePosition + 1

          'Debug.Print "Postion = " & intIndex

          If intCount = intIndex Then

          'The last record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Next and Last buttons, one which has the focus,

          'make sure the Previous button is visible and move the focus to it.

               Me.cmdPrevious.Visible = True

               Me.cmdPrevious.SetFocus

               Me.cmdNext.Visible = False

               Me.cmdLast.Visible = False

          ElseIf intIndex = 1 Then

          'The first record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Previous button, which has the focus,

          'make sure the Next button is visible and move the focus to it.

               Me.cmdNext.Visible = True

               Me.cmdNext.SetFocus

               Me.cmdPrevious.Visible = False

               Me.cmdLast.Visible = True

          ElseIf intIndex > 1 And intIndex < intCount Then

          'The displayed record is not the first or last record, all buttons should show.

          'Since you are not changing the visible status of the button used 

          'the focus does not need to be reset.

               Me.cmdPrevious.Visible = True

               Me.cmdNext.Visible = True

               Me.cmdLast.Visible = True

          End If

         'Give the user an indication of where they are in the record set.

          Me.txtItemCount = "Item " & intIndex & " of " & intCount

     End Sub

Microsoft Access and Windows 7 Problem Solved

In my last post I noted that we were having a problem where the connection to the server was being lost, which would make Access stop working. Our IT people determined that a utility in Windows 7 was automatically refreshing the network connections and that was dropping the connections. After they turned off that utility the problem has not shown up again, so it was not the Access Based Enumeration Directory Structure Server that was causing the problem.

MS Access, Windows 7, and an Access Based Enumeration Directory Structure Server

Our IT department recently switched us to Windows 7 and an access based enumeration directory structure server. I have not really noticed any difference for most applications. Except MS Access. Since the switch the connection to the server is frequently dropped. What this means is that when you ask Access to do something that requires a form that is not currently active you will get a “network access interrupted” error and the database freezes. To unfreeze the database you have to close it and reopen it. Even with an active form, refreshing it can generate a “network access interrupted” error. Trying to open the VBA editor will get you a “File Not Found” error that you cannot get out of: clicking “Okay” just generates a new “File Not Found” error. You have to crash the database.

Continuing to try to work around these dropped server connections will corrupt your database. I had that happen yesterday. This morning I had to close and reopen my database five times in one hour. And I wasn’t even working very actively with the database.

This is very frustrating. I have no control over the problem, our IT staff needs to find a way to stop the connections from dropping. At this point out IT staff seems to think the problem is Windows 7, rather than the server. Our main database, KIDB, is used by pretty much everyone at the Kheel Center all day, every day. It is where we do our work. Constantly closing and opening it and/or risking corruption is just not tenable. I hope soon to be able to publish a post with the solution to this problem.

Line breaks in FileMaker Pro exports

We have a FileMaker Pro database as our reference database that is about to be retired. I exported the data from the database in order to see what was there and how it could be handled in order to get it into another database. What I found when I opened it up in Microsoft Excel and Microsoft Access was that some of the fields contained line breaks that show up as little boxes with arrows. I needed to either hide or get rid of these visible line breaks. Neither Excel or Access would find these using the Find function or the Find and Replace function, even using “^l” as the search term. The question was: How to get rid of those line breaks.

My solution was to open the file in Excel, then save it as a csv file. The csv file was then opened in Microsoft Word, which will find the line breaks as “^l”. I did a Find and Replace for all the line breaks, replacing them with a unique character so that I could later parse the data, putting the data from the separate lines into separate fields. In searching the data I found that any character I could both use and later parse on was already being used. I finally settle on the tilde “~” since it was used in the data to signify an approximate date. First I replaced the tilde with “ca.” then I replaced the line breaks with tildes. The file was then saved as a text file and reopened in Excel. No line breaks and ready to parse.