I'm developing a database for my company, and for the most part I've been successful, with some complex sub-routines already working fine. But this one has me stumped!
Some background on this database:
It has numerous and tables and queries, and a few tables have identical field names, and I've created a union query, named QryItemList, to create a list containing the unique identifier number in UniqueID, and included the field CategoryName. Then I created a form with a listbox, labeled ListBoxData, and a button that starts this sub-routine when clicked.
The intended result was open the appropriate form, determined by CategoryName, to the record filtered by UniqueID. Unfortunately the listbox only returns the value for UniqueID, and I've been trying to find a way to get the CategoryName data also. The latest attempt was to create a temporary table that I could append the recordset, and use the CategoryName from the temporary table to complete the sub-routine.
When I clicked on the button I get the rather unhelpful "Object required" error message. Can anyone help me with this code?
A successful subroutine would enable the user to open several different forms from one list and open the correct record in one step instead of two. It may not seem like much of a big deal, but when the list is long and varied it can reduce the action of opening and closing of forms by 75% or more.
Here's the code:
Expand|Select|Wrap|Line Numbers
- Private Sub ItemSelect_Click()
- On Error GoTo Err_ItemSelect_Click
- '1. Set variables for sub-routine
- Dim stDocName As String
- Dim stLinkCriteria As String
- Dim stCategory As String
- Dim strSQL As String
- Dim strTableName As String
- Dim strLinkCriteria2 As String
- '2. Set value of CategoryName according to value of UniqueID
- ' QryItemList is a union query of 3 tables with common fields
- strLinkCriteria2 = "QryItemList.UniqueID=" & "'" & Me![ListBoxData] & "'"
- strSQL = "INSERT INTO tblTemp " & _
- "SELECT QryItemList.UniqueID, QryItemList.CategoryName " & _
- "FROM QryItemList " & _
- "WHERE " & strLinkCriteria2 & ";"
- DoCmd.SetWarnings False
- DoCmd.RunSQL strSQL
- DoCmd.SetWarnings True
- '3. Match CategoryName to correspondaing form name
- stCategory = QryItemList.CategoryName
- If stCategory = "Category1" Then
- stDocName = "frmCategory1"
- ElseIf stCategory = "Category2" Then
- stDocName = "frmCategory2"
- ElseIf stCategory = "Category3" Then
- stDocName = "frmCategory3"
- End If
- '4. Open form according to CategoryName and record according to UniqueID
- stLinkCriteria = "[UniqueID]=" & "'" & Me![ListBoxData] & "'"
- DoCmd.OpenForm stDocName, , , stLinkCriteria
- '5. Delete temporary table before exiting the sub-routine
- db.TableDefs.Delete ("tblTemp")
- Exit_ItemSelect_Click:
- Exit Sub
- Err_ItemSelect_Click:
- MsgBox Err.Description
- Resume Exit_ItemSelect_Click
- End Sub