Getting Data From Another Form in MS Access

Sometimes in order to run the VBA code I need data from a form other than the one that contains the code. For example, I have a form that shows all the data for an folder in a collection, frmFolderData. In order to pull the data to populate that form I need to know the folder’s index number. If frmFolderData is being opened from the list of folders in frmSeriesFolderList it is pretty simple:

     lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)

Where lngFolderID is a long variable for the folder index number, which is in the first column of the list box.

However if that is the code I use and then try to open frmFolderData from frmEADFolders I will get an error because frmSeriesFolderList is not open. I need to test for whether or not the form I want is open, or in this case, which of the two forms I could use is open. To do that I use the IsLoaded property.

Here is the code used in frmFolderData to decide whether to pull the needed data from frmSeriesFolderData or frmEADFolders:

     If CurrentProject.AllForms("frmSeriesFolderList").IsLoaded Then

          lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)

     ElseIf CurrentProject.AllForms("frmEADFolders").IsLoaded Then

          lngFolderID = Forms!frmEADFolders.lstFolders.Column(0)

     End If

Capturing a list box error in MS Access.

There are several places in KIDB where the user can select from a list box and then click a button to perform an action on the selection. A good example of this is you can select a box from the list box on the Box Location form and click a button to move it, that is to assign it a new address in the stacks. A potential problem with this is if someone clicks the button without first selecting a row in the list box, which chokes the code. Here is how I capture that error in the On Click event of the Move Box button:

Private Sub cmdMoveBox_Click()

On Error GoTo Err_cmdMoveBox_Click

'Sub allows user to move a box from one address to another.

'Limits ability to move boxes to users with a permission level of 3 or higher.

     Dim strDocName As String

     Dim strLinkCriteria As String

     Dim lst As Access.ListBox

     Set lst = Me.BoxList

     Dim intUser As Integer

     strDocName = "frmBoxMove"

     'Check for user authorization to delete boxes.

     intUser = Nz(DLookup("[Level]", "tluUsers", "[UserID]= '" & Environ("username") & "'"))

     If intUser > 2 Then

          'Check to see if any boxes have been selected.

          If lst.ItemsSelected.Count = 0 Then

               'Let the user know what is wrong.

               MsgBox "No box has been selected to move."

               'Exit the sub.

               Exit Sub

          'Check that no more than 1 box has been selected.

          ElseIf lst.ItemsSelected.Count > 1 Then

               'Let the user know what is wrong.

               MsgBox "Please select only one box to move."

               'Clear the listbox selections.

               lst.SetFocus

               lst.ListIndex = -1

               'Exit the sub.

               Exit Sub

          'If only one box is selected open the move box form.

          Else:

               strLinkCriteria = "[BoxID]=" & Me!BoxList.Column(11)

               DoCmd.OpenForm strDocName, , , strLinkCriteria

          End If

     Else:

          MsgBox "Sorry, you are not authorized to move boxes."

     End If

Exit_cmdMoveBox_Click:

Exit Sub

Err_cmdMoveBox_Click:

MsgBox Err.Description

Resume Exit_cmdMoveBox_Click

End Sub

Changing List Box Results in MS Access

Our publications database currently has about 81,000 items listed in it, mostly union and company grey literature. The database opens to a search form which contains a list box. This used to load with all the items when opened, but once we got past 60,000 it was slow to load. Since there is no need for all items to load I looked for ways to limit the list box results. There is probably a way to load 100 random records, but I wasn’t able to figure one out so instead it started with the first 100 records and each time the database is opened or the form refreshed it opens the next 100 in sequence.

To control where the record set begins I added a new table to the database, tblLoad. There are only two fields and one record in the table. The ID field has a value of 1 and never changes, while the Start field contains the lower limit for the list box record set and is initially had a value of 1 but the value is increased by 99 whenever the search form opens or is refreshed.

Here is the VBA code for the search form’s On Open event:

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False

     Dim strSQL As String           'This is the SQL query that provides the record set for the list box.

     Dim lngStart As Long           'This establishes the lower limit of the record set.

     Dim lngEnd As Long            'This establishes the upper limit of the record set.

     Dim lngMax As Long           'This is the ID for the last record in tblPams.

     'Pick up the lower limit from tblLoad.

     lngStart = DLookup("[Start]", "tblLoad", "[ID] = 1")

     'Debug.Print "lngStart = " & lngStart

     'Create the upper limit by adding 99 to the lower limit.

     lngEnd = lngStart + 99

     'Debug.Print "lngEnd = " & lngEnd

     'Pick up the ID for the last record in tblPams.

     lngMax = DMax("[PamID]", "tblPams")

     'Debug.Print "lngMax = " & lngMax

     'To avoid generating a error when the SQL query is run

     'test to make sure that lngEnd is not greater than lngMax.

     If lngEnd > lngMax Then

     'If lngEnd is greater than lngMax reset lngEnd to lngMax.

          lngEnd = lngMax

     Else:

     'Otherwise, keep lngEnd the same.

          lngEnd = lngEnd

     End If

     strSQL = "SELECT tblPams.PamID, tblPams.Title, tblPams.Date FROM tblPams "

     strSQL = strSQL & "WHERE (tblPams.PamID BETWEEN " & lngStart & " AND " & lngEnd & ") "

     strSQL = strSQL & "ORDER BY Article(tblPams.Title), tblPams.Date;"

     'Debug.Print strSQL

     Me.lstPamList.RowSource = strSQL

     'If the last record in the table has been reached we need to reset the lower limit to 1.

     If lngEnd = lngMax Then

          DoCmd.RunSQL "Update tblLoad SET tblLoad.Start = 1 Where tblLoad.ID = 1"

     Else:

     'Otherwise, the current upper limit becomes the next lower limit.

          DoCmd.RunSQL "UPDATE tblLoad SET tblLoad.Start = " & lngEnd & " Where tblLoad.ID = 1"

     End If

DoCmd.SetWarnings True

End Sub