I'm puzzled by why you use Form_frmDoobloFileDetails to refer to the form
named frmDoobloFileDetails. Any particular reason you don't use Me. instead
(assuming that the code is running in that form's module), or
Forms("frmDoobloFileDetails"). if it's not?
I believe that the problem with #Error is because the rsNew recordset is
destroyed when the lstFilesList_DblClick subroutine finishes. So, at that
point, your form has no more data to display, and you see the #Error message
in the controls.
The instantiation of the rsNew recordset needs to be done in the
Declarations section of the form that is running the code. (And take out the
instatiation from the lstFilesList_DblClick procedure.) That means that
you'll need to handle the possibility that your subroutine procedure is run
more than once, and that you don't append the same fields to the recordset
when it already has those fields.
Also, your code is looping through the list box's selections, and apparently
is opening the "frmDoobloFileDetails" form over and over, and adding the
same five fields over and over to the rsNew recordset, and then constantly
resetting the form's recordset to the rsNew recordset, which may have
problems because you are adding the same fields to it over and over.
I think you also need to pull this code out from the loop of VarItem and run
it after the end of that loop:
DoCmd.OpenForm "frmDoobloFileDetails", acNormal
Set Form_frmDoobloFileDetails.Recordset = rsNew
If rsNew.RecordCount 0 Then
Form_frmDoobloFileDetails.txtDoobloCode.ControlSou rce =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"IsImported"
Form_frmDoobloFileDetails.lblFileName.Caption = "רשימת
הבקרות בקובץ: " & sFileName
Else
Form_frmDoobloFileDetails.lblFileName.Caption = "אין רשומות
בקובץ: " & sFileName
Form_frmDoobloFileDetails.lblFileName.ForeColor = 255
End If
This is the code that continually appends the same fields over and over to
the rsNew recordset. It too should not be running within the VarItem loop:
Do While Not rs.EOF
With rsNew
rsNew.AddNew
!SbjNum = rs!SbjNum
!vDate = rs!vDate
!Srvyr = rs!Srvyr
!SbjNam = rs!SbjNam
End With
rsVisits.Filter = "DoobloId=" & rs!SbjNum
If rsVisits.RecordCount = 0 Then
rsNew!IsImported = False
Else
rsNew!IsImported = True
End If
rsNew.Update
rs.MoveNext
Loop
--
Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/
"Orna" wrote in message
...
Hi Ken,
below is the complete code of the sub procedure.
My application is importing records from another application and I would
like to give an indication to the user whether the record was imported or
not. as you can see I have another recordset called rsVisits and if I find
the record there the field "IsImported" set to True.
I didn't have any other idea how to implement it.
When I moved the line that sets the forms' recordset to rsNew I still get
an
"#Error" in the text boxs
When I added a requery to the form after setting the recordset I get
"#Name?" in the fields.
I also tried to move the dim of rsNew to one of my modules as public
recordset, this also didn't change the error I get.
Do you know what is the meaning of the "#Error" text in a textbox, I
couldn't find any reference to it in the internet?
Thanks,
Orna.
---------------------------
Private Sub lstFilesList_DblClick(Cancel As Integer)
Dim VarItem As Variant
Dim rs As New ADODB.Recordset
Dim ConStr As String
Dim con As New ADODB.Connection
Dim rsDooblo As DAO.Recordset
Dim dbs As DAO.Database
Dim rsVisits As DAO.Recordset
Dim i As Integer
Dim sFilePath As String
Dim sFileName As String
Dim sTableName As String
Set rsNew = New ADODB.Recordset
Set dbs = CurrentDb
sFilePath = DLookup("ImportPath", "tblProjects", "ProjectId=" &
cmbSekerType.Value)
sTableName = DLookup("tblVisitsName", "tblProjects", "ProjectId=" &
cmbSekerType.Value)
For Each VarItem In lstFilesList.ItemsSelected
If VarItem 0 Then
sFileName = lstFilesList.ItemData(VarItem)
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sFilePath & "\" & sFileName & ";Persist Security Info=False"
con.Open ConStr
rs.Open "SELECT * FROM Export1", con, adOpenKeyset,
adLockReadOnly, adCmdText
Set rsVisits = dbs.OpenRecordset(sTableName, dbOpenDynaset,
dbReadOnly)
rsNew.Fields.Append "SbjNum", rs.Fields("SbjNum").Type, 50
rsNew.Fields.Append "vDate", rs.Fields("vDate").Type, 50
rsNew.Fields.Append "Srvyr", rs.Fields("Srvyr").Type, 50
rsNew.Fields.Append "SbjNam", rs.Fields("SbjNam").Type, 50
rsNew.Fields.Append "IsImported", adBoolean
rsNew.Open
If rs.RecordCount 0 Then
rs.MoveFirst
Do While Not rs.EOF
With rsNew
rsNew.AddNew
!SbjNum = rs!SbjNum
!vDate = rs!vDate
!Srvyr = rs!Srvyr
!SbjNam = rs!SbjNam
End With
rsVisits.Filter = "DoobloId=" & rs!SbjNum
If rsVisits.RecordCount = 0 Then
rsNew!IsImported = False
Else
rsNew!IsImported = True
End If
rsNew.Update
rs.MoveNext
Loop
End If
DoCmd.OpenForm "frmDoobloFileDetails", acNormal
Set Form_frmDoobloFileDetails.Recordset = rsNew
If rsNew.RecordCount 0 Then
Form_frmDoobloFileDetails.txtDoobloCode.ControlSou rce =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource =
"SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"IsImported"
Form_frmDoobloFileDetails.lblFileName.Caption = "רשימת
הבקרות בקובץ: " & sFileName
Else
Form_frmDoobloFileDetails.lblFileName.Caption = "אין רשומות
בקובץ: " & sFileName
Form_frmDoobloFileDetails.lblFileName.ForeColor = 255
End If
End If
Next VarItem
End Sub
"Ken Snell (MVP)" wrote:
I believe you've not posted the complete code? I don't see anything in
this
code that establishes the rsNew recordset object? Is it supposed to be a
subset of the rs recordset's data? If yes, why not just open it as a
filtered or unfiltered recordset from the rs recordset, instead of
writing
data into it by a loop through rs data?
Also, I would move your setting of the ControlSource properties to BEFORE
you set the form to that rsNew recordset. Or, do a requery of the form
after
you set those properties.
--
Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/
"Orna" wrote in message
...
Hi,
I have a form with a listbox control, the listbox displays access db
file
name in a specific directory, each database has only one table with the
same
name and exact schema but with different data. each time the user
double
clicks on one of the rows in the listbox, a dialog form opens and
displays
the data in the table.
when I use an ADODB recordset object to view the data in the remote
database, it works ok. but if I use a recordset I generated in my code
(with
additional fields) , I get the correct number of records but the data
displayed in each one of the text boxs is "#Error".
Below is my code, rsNew is the recordset I am trying to attach to my
dialog.
I am trying to solve this for few days and I will appreciate any help.
Thanks,
Orna.
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sFilePath & "\" & sFileName & ";Persist Security Info=False"
con.Open ConStr
rs.Open "SELECT * FROM Export1", con, adOpenKeyset,
adLockReadOnly, adCmdText
Set rsVisits = dbs.OpenRecordset(sTableName, dbOpenDynaset,
dbReadOnly)
rsNew.Fields.Append "SbjNum", rs.Fields("SbjNum").Type, 50
rsNew.Fields.Append "vDate", rs.Fields("vDate").Type, 50
rsNew.Fields.Append "Srvyr", rs.Fields("Srvyr").Type, 50
rsNew.Fields.Append "SbjNam", rs.Fields("SbjNam").Type, 50
rsNew.Fields.Append "IsImported", adBoolean
rsNew.Open
If rs.RecordCount 0 Then
rs.MoveFirst
Do While Not rs.EOF
With rsNew
rsNew.AddNew
!SbjNum = rs!SbjNum
!vDate = rs!vDate
!Srvyr = rs!Srvyr
!SbjNam = rs!SbjNam
End With
rsVisits.Filter = "DoobloId=" & rs!SbjNum
If rsVisits.RecordCount = 0 Then
rsNew!IsImported = False
Else
rsNew!IsImported = True
End If
rsNew.Update
rs.MoveNext
Loop
End If
DoCmd.OpenForm "frmDoobloFileDetails", acNormal
Set Form_frmDoobloFileDetails.Recordset = rsNew
If rsNew.RecordCount 0 Then
Form_frmDoobloFileDetails.txtDoobloCode.ControlSou rce =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e =
"Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"IsImported"
End If