C
Connie via AccessMonster.com
Help! I am suddenly getting the error 'Item not found in this collection'
on a form that worked previously which uses DAO.recordset. This form is in
a database that I just converted from a replicated d.b. to a regular d.b.
by importing all of the objects into a new d.b. except the tables, which I
brought in using the 'make table query' method suggested in other postings.
This error has only occurred since I converted the replicated d.b. to a
regular d.b.
The scenario is that a user will enter a name into a search form and double
click on the correct record in a list box of matching records. The double
click event calls a function with a case statement that references the form
the user was on when the search was initiated, and in turn, the case
statement calls a function to fill existing data into a form. (This error
does not occur when the case statement fills the form directly without
calling the FillData function, so I know it is the culpret.) I am concerned
that there is a problem with the DAO.recordset, even though I have the MS
DAO 3.6 Object Library checked in the references. By stepping through the
code, I think that it is crashing when a variable, rstTemp (the
DAO.Recordset) is set to currentdb.openrecordset = xxx
Private Sub FillData(intID As Integer, frmIn As Form, strTable As String)
Dim strSQL As String
Dim rstTemp As DAO.Recordset
Dim ctrlTemp As Control
strSQL = "Select * from [" & strTable & "] where EntityNo = " & intID
Set rstTemp = CurrentDb.OpenRecordset(strSQL)
With rstTemp
For Each ctrlTemp In frmIn.Controls
If TypeOf ctrlTemp Is TextBox Then
ctrlTemp.SetFocus
Debug.Print ctrlTemp.Name
ctrlTemp.Text = Nz(.Fields(ctrlTemp.Name).Value, "")
ElseIf TypeOf ctrlTemp Is ComboBox Or TypeOf ctrlTemp Is
CheckBox Then
ctrlTemp.SetFocus
ctrlTemp.Value = .Fields(ctrlTemp.Name).Value
End If
Next
End With
End Sub
First, I am not sure that I have correctly identified the cause of the
problem.
Second, if I have, I am not sure what else I need to do besides including
the reference for the DAO 3.6. When I converted the d.b. I changed it from
one file that was replicated to a fe/be, but I was getting this error and
thought that currentDB might be the problem, even though I am not using
seek anywhere, so for testing purposes, I brought in all my other objects
into the backend file, but I am still getting this error. Once it is fixed,
I will delete the non-table objects and return to a fe/be setup, after
applying the fix to the front-end.
on a form that worked previously which uses DAO.recordset. This form is in
a database that I just converted from a replicated d.b. to a regular d.b.
by importing all of the objects into a new d.b. except the tables, which I
brought in using the 'make table query' method suggested in other postings.
This error has only occurred since I converted the replicated d.b. to a
regular d.b.
The scenario is that a user will enter a name into a search form and double
click on the correct record in a list box of matching records. The double
click event calls a function with a case statement that references the form
the user was on when the search was initiated, and in turn, the case
statement calls a function to fill existing data into a form. (This error
does not occur when the case statement fills the form directly without
calling the FillData function, so I know it is the culpret.) I am concerned
that there is a problem with the DAO.recordset, even though I have the MS
DAO 3.6 Object Library checked in the references. By stepping through the
code, I think that it is crashing when a variable, rstTemp (the
DAO.Recordset) is set to currentdb.openrecordset = xxx
Private Sub FillData(intID As Integer, frmIn As Form, strTable As String)
Dim strSQL As String
Dim rstTemp As DAO.Recordset
Dim ctrlTemp As Control
strSQL = "Select * from [" & strTable & "] where EntityNo = " & intID
Set rstTemp = CurrentDb.OpenRecordset(strSQL)
With rstTemp
For Each ctrlTemp In frmIn.Controls
If TypeOf ctrlTemp Is TextBox Then
ctrlTemp.SetFocus
Debug.Print ctrlTemp.Name
ctrlTemp.Text = Nz(.Fields(ctrlTemp.Name).Value, "")
ElseIf TypeOf ctrlTemp Is ComboBox Or TypeOf ctrlTemp Is
CheckBox Then
ctrlTemp.SetFocus
ctrlTemp.Value = .Fields(ctrlTemp.Name).Value
End If
Next
End With
End Sub
First, I am not sure that I have correctly identified the cause of the
problem.
Second, if I have, I am not sure what else I need to do besides including
the reference for the DAO 3.6. When I converted the d.b. I changed it from
one file that was replicated to a fe/be, but I was getting this error and
thought that currentDB might be the problem, even though I am not using
seek anywhere, so for testing purposes, I brought in all my other objects
into the backend file, but I am still getting this error. Once it is fixed,
I will delete the non-table objects and return to a fe/be setup, after
applying the fix to the front-end.