Error 3265 re-post

G

Guest

I need to be able to read a table, separate names (up to 3 names per record)
from selected records and build a temporary name & address table. This first
piece of code is executed if the table does not exist....

Function fncCreateName20Table()
MsgBox " Now Creating DPS_FR_RC20RW_NAMES20 Table "
' This statement Defines the Table named NAMES20
Dim NewTbl As TableDef
Set NewTbl = CurrentDb.CreateTableDef("DPS_FR_RC20RW_NAMES20")
Dim fld1 As Field ' for CASE_NUM_YR
Dim fld2 As Field ' for CASE_NUM
Dim fld3 As Field ' for SEQNO_NUM
Dim fld4 As Field ' for PRTNO_NUM
Dim fld5 As Field ' for NAME_TXT
Dim fld6 As Field ' for FIRM_TXT
Dim fld7 As Field ' for ADDR1_TXT
Dim fld8 As Field ' for ADDR2_TXT
Dim fld9 As Field ' for CITY_TXT
Dim fld10 As Field ' for STATE_CDE
Dim fld11 As Field ' for ZIPCDE_TXT

Set fld1 = NewTbl.CreateField("CASE_NUM_YR", dbLong)
fld1.Required = True
Set fld2 = NewTbl.CreateField("CASE_NUM", dbLong)
fld2.Required = True
Set fld3 = NewTbl.CreateField("SEQNO_NUM", dbLong)
fld3.Required = True
fld3.Attributes = fld3.Attributes + dbAutoIncrField
Set fld4 = NewTbl.CreateField("PRTNO_NUM", dbLong)
fld4.Required = True
Set fld5 = NewTbl.CreateField("NAME_TXT", dbText, 50)
fld5.Required = True
Set fld6 = NewTbl.CreateField("FIRM_TXT", dbText, 50)
fld6.AllowZeroLength = True
Set fld7 = NewTbl.CreateField("ADDR1_TXT", dbText, 30)
fld7.Required = True
Set fld8 = NewTbl.CreateField("ADDR2_TXT", dbText, 30)
fld8.AllowZeroLength = True
Set fld9 = NewTbl.CreateField("CITY_TXT", dbText, 30)
fld9.Required = True
Set fld10 = NewTbl.CreateField("STATE_CDE", dbText, 2)
fld10.Required = True
Set fld11 = NewTbl.CreateField("ZIPCDE_TXT", dbText, 10)
fld11.AllowZeroLength = True

NewTbl.Fields.Append fld1
NewTbl.Fields.Append fld2
NewTbl.Fields.Append fld3
NewTbl.Fields.Append fld4
NewTbl.Fields.Append fld5
NewTbl.Fields.Append fld6
NewTbl.Fields.Append fld7
NewTbl.Fields.Append fld8
NewTbl.Fields.Append fld9
NewTbl.Fields.Append fld10
NewTbl.Fields.Append fld11

CurrentDb.TableDefs.Append NewTbl

strSQL = "ALTER TABLE DPS_FR_RC20RW_NAMES20 " & _
"ADD CONSTRAINT PK_NAMES20 " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox " Successfully Created DPS_FR_RC20RW_NAMES20 Table "

End Function

----------------------------------------------------------------------------------------------------

Problem is this. I'm getting an error 3265 message and description that the
Item is not found in this collection, although I'm moving all data from one
record to another and in special instances, making 1 or 2 fields nulled value
because there would be no data to move. The table exists, the fields are
defined, but after attempt to write the records to this table, the table is
empty... gives error 3265. The following code is called after input table
has been created via previous action query. Can someone identify why this
code fails to write records to the temp table.... Thanks in advance.
---------------------------------------------------------------------------------------------------------
Public Sub subLoad_RC20RW_NAMES20()
On Error GoTo Error_Load_RC20RW_NAMES20

' Insert code to build new names20 records here...
Dim rsA As Recordset
Dim rsB As Recordset
Dim rsD As DAO.Recordset
Dim intSelectAtty As Integer
Dim intHoldCaseNumYr As Integer
Dim intHoldCaseNum As Integer
Dim intHoldPrtNoNum As Integer
Dim intSeqNo As Integer
intSeqNo = 0

Set rsA = CurrentDb.OpenRecordset("DPS_FRQ_RC20RW")
Set rsB = CurrentDb.OpenRecordset("DPS_FR_ATTORNEY")
Set rsD = CurrentDb.OpenRecordset("DPS_FR_RC20RW_NAMES20")

If Not (rsA.BOF And rsA.EOF) Then
rsA.MoveFirst
Do Until rsA.EOF
With rsD
While Not rsA.EOF
'*********************************************************************
'* If Licensee not null, add Licensee Name, Address, City, State to Table D
*
'*********************************************************************
If Not IsNull(rsA![LIC_FIRST_NME]) Then
.AddNew
intHoldCaseNumYr = rsA![CASE_NUM_YR]
intHoldCaseNum = rsA![CASE_NUM]
intHoldPrtNoNum = rsA![PRTNO_NUM]
![CASE_NUM_YR] = intHoldCaseNumYr ' build primary
key pt-1
![CASE_NUM] = intHoldCaseNum ' build primary
key pt-2
![SEQNO_NUM] = intSeqNo + 1
![PRTNO_NUM] = intHoldPrtNoNum ' build PRTNO_NUM
![NAME_TXT] = rsA![LIC_FIRST_NME] & " " & _
rsA![LIC_MIDDLE_NME] & " " & _
rsA![LIC_LAST_NME] & " " & _
rsA![LIC_SUBT_TXT]
![FIRM_TXT] = rsA![LIC_LAST_NME]
![ADDR1_TXT] = rsA![LIC_ADDR_TXT]
![ADDR2_TXT] = rsA![LIC_ADDR_TXT]
![CITY_TXT] = rsA![LIC_CITY_NME]
![STATE_CDE] = rsA![LIC_STATE_CDE]
![ZIPCDE_TXT] = rsA![LIC_ZIP_CDE] & " " &
rsA![LIC_ZIP4_CDE]
.Update
End If
'***********************************************************************
'* If DOA_NME not null, add DOA Name, Address, City, State to Table D
*
'***********************************************************************
If Not IsNull(rsA![DOA_NME]) Then
.AddNew
![CASE_NUM_YR] = intHoldCaseNumYr ' build primary
key pt-1
![CASE_NUM] = intHoldCaseNum ' build primary
key pt-2
![SEQNO_NUM] = intSeqNo + 1
![PRTNO_NUM] = intHoldPrtNoNum ' build PRTNO_NUM
![NAME_TXT] = rsA![DOA_NME]
![FIRM_TXT] = Null
![ADDR1_TXT] = rsA![DOA_ADDR_TXT]
![ADDR2_TXT] = Null
![CITY_TXT] = rsA![DOA_CITY_NME]
![STATE_CDE] = rsA![DOA_STATE_CDE]
![ZIP_CDE] = rsA![LIC_ZIP_CDE] & " " & rsA![LIC_ZIP4_CDE]
.Update
End If
'**********************************************************************
'* If Attorney <> 0, add Attorney Name, Address, City, State to Table D
*
'**********************************************************************
If rsA![ATTY_NUM] <> 0 Then
'select attorney record via sql statement
intSelectAtty = rsA![ATTY_NUM]
DoCmd.runMacro "FRM-Atty-FOF-Select"
If Not (rsB.BOF And rsB.EOF) Then
rsB.MoveFirst
If Not rsB.EOF Then
.AddNew
![CASE_NUM_YR] = intHoldCaseNumYr ' build
pkey pt-1
![CASE_NUM] = intHoldCaseNum ' build
pkey pt-2
![SEQNO_NUM] = intSeqNo + 1
![PRTNO_NUM] = intHoldPrtNoNum ' build
PRTNO_NUM
![NAME_TXT] = rsB![FIRST_NME] & " " & _
rsB![MIDDLE_NME] & " " & _
rsB![LAST_NME] & " " & _
rsB![SUBTITLE_TXT]
![FIRM_NME] = rsB![FIRM_NME]
![ADDR1_TXT] = rsB![ADDR1_TXT]
![ADDR2_TXT] = rsB![ADDR2_TXT]
![CITY_TXT] = rsB![CITY_NME]
![STATE_CDE] = rsB![STATE_CDE]
![ZIP_CDE] = rsB![LIC_ZIP_CDE] & " " &
rsB![LIC_ZIP4_CDE]
.Update
End If
End If
End If
rsA.MoveNext

Wend

End With
Loop

End If

rsA.Close
rsB.Close
rsD.Close

Set rsA = Nothing
Set rsB = Nothing
Set rsD = Nothing

Exit_Load_RC20RW_NAMES20:
' Cleanup Code
On Error Resume Next
Exit Sub

Error_Load_RC20RW_NAMES20:
Select Case Err.Number
Case 2501
' Action Cancelled by User, Ignore Error
Case Else
' Unexpected Error Encountered
MsgBox " The following error has occurred: " _
& vbNewLine & " Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, " Unexpected Error "
End Select
Resume Exit_Load_RC20RW_NAMES20

End Sub
 
G

Guest

Error resolved:

Resolution: Modified the open statement of output file to following format:

Set rsD = CurrentDb.OpenRecordset("DPS_FR_RC20RW_NAMES20",
dbOpenDynaset, dbAppendOnly)

By opening the recordset rsD with the dbAppendOnly mode, it allowed me to
add records properly.

Resolved the true 3265 error in moving the correct field name to the output
file. (finger check spelling course and new glasses now on order.)

Hope my mistakes and corrections can help others.

Thanks...


--
Robert Nusz
Sr. Programmer Analyst II


RNUSZ@OKDPS said:
I need to be able to read a table, separate names (up to 3 names per record)
from selected records and build a temporary name & address table. This first
piece of code is executed if the table does not exist....

Function fncCreateName20Table()
MsgBox " Now Creating DPS_FR_RC20RW_NAMES20 Table "
' This statement Defines the Table named NAMES20
Dim NewTbl As TableDef
Set NewTbl = CurrentDb.CreateTableDef("DPS_FR_RC20RW_NAMES20")
Dim fld1 As Field ' for CASE_NUM_YR
Dim fld2 As Field ' for CASE_NUM
Dim fld3 As Field ' for SEQNO_NUM
Dim fld4 As Field ' for PRTNO_NUM
Dim fld5 As Field ' for NAME_TXT
Dim fld6 As Field ' for FIRM_TXT
Dim fld7 As Field ' for ADDR1_TXT
Dim fld8 As Field ' for ADDR2_TXT
Dim fld9 As Field ' for CITY_TXT
Dim fld10 As Field ' for STATE_CDE
Dim fld11 As Field ' for ZIPCDE_TXT

Set fld1 = NewTbl.CreateField("CASE_NUM_YR", dbLong)
fld1.Required = True
Set fld2 = NewTbl.CreateField("CASE_NUM", dbLong)
fld2.Required = True
Set fld3 = NewTbl.CreateField("SEQNO_NUM", dbLong)
fld3.Required = True
fld3.Attributes = fld3.Attributes + dbAutoIncrField
Set fld4 = NewTbl.CreateField("PRTNO_NUM", dbLong)
fld4.Required = True
Set fld5 = NewTbl.CreateField("NAME_TXT", dbText, 50)
fld5.Required = True
Set fld6 = NewTbl.CreateField("FIRM_TXT", dbText, 50)
fld6.AllowZeroLength = True
Set fld7 = NewTbl.CreateField("ADDR1_TXT", dbText, 30)
fld7.Required = True
Set fld8 = NewTbl.CreateField("ADDR2_TXT", dbText, 30)
fld8.AllowZeroLength = True
Set fld9 = NewTbl.CreateField("CITY_TXT", dbText, 30)
fld9.Required = True
Set fld10 = NewTbl.CreateField("STATE_CDE", dbText, 2)
fld10.Required = True
Set fld11 = NewTbl.CreateField("ZIPCDE_TXT", dbText, 10)
fld11.AllowZeroLength = True

NewTbl.Fields.Append fld1
NewTbl.Fields.Append fld2
NewTbl.Fields.Append fld3
NewTbl.Fields.Append fld4
NewTbl.Fields.Append fld5
NewTbl.Fields.Append fld6
NewTbl.Fields.Append fld7
NewTbl.Fields.Append fld8
NewTbl.Fields.Append fld9
NewTbl.Fields.Append fld10
NewTbl.Fields.Append fld11

CurrentDb.TableDefs.Append NewTbl

strSQL = "ALTER TABLE DPS_FR_RC20RW_NAMES20 " & _
"ADD CONSTRAINT PK_NAMES20 " & _
"PRIMARY KEY(Case_Num_Yr,Case_Num)"
CurrentDb.Execute strSQL, dbFailOnError

MsgBox " Successfully Created DPS_FR_RC20RW_NAMES20 Table "

End Function

----------------------------------------------------------------------------------------------------

Problem is this. I'm getting an error 3265 message and description that the
Item is not found in this collection, although I'm moving all data from one
record to another and in special instances, making 1 or 2 fields nulled value
because there would be no data to move. The table exists, the fields are
defined, but after attempt to write the records to this table, the table is
empty... gives error 3265. The following code is called after input table
has been created via previous action query. Can someone identify why this
code fails to write records to the temp table.... Thanks in advance.
---------------------------------------------------------------------------------------------------------
Public Sub subLoad_RC20RW_NAMES20()
On Error GoTo Error_Load_RC20RW_NAMES20

' Insert code to build new names20 records here...
Dim rsA As Recordset
Dim rsB As Recordset
Dim rsD As DAO.Recordset
Dim intSelectAtty As Integer
Dim intHoldCaseNumYr As Integer
Dim intHoldCaseNum As Integer
Dim intHoldPrtNoNum As Integer
Dim intSeqNo As Integer
intSeqNo = 0

Set rsA = CurrentDb.OpenRecordset("DPS_FRQ_RC20RW")
Set rsB = CurrentDb.OpenRecordset("DPS_FR_ATTORNEY")
Set rsD = CurrentDb.OpenRecordset("DPS_FR_RC20RW_NAMES20")

If Not (rsA.BOF And rsA.EOF) Then
rsA.MoveFirst
Do Until rsA.EOF
With rsD
While Not rsA.EOF
'*********************************************************************
'* If Licensee not null, add Licensee Name, Address, City, State to Table D
*
'*********************************************************************
If Not IsNull(rsA![LIC_FIRST_NME]) Then
.AddNew
intHoldCaseNumYr = rsA![CASE_NUM_YR]
intHoldCaseNum = rsA![CASE_NUM]
intHoldPrtNoNum = rsA![PRTNO_NUM]
![CASE_NUM_YR] = intHoldCaseNumYr ' build primary
key pt-1
![CASE_NUM] = intHoldCaseNum ' build primary
key pt-2
![SEQNO_NUM] = intSeqNo + 1
![PRTNO_NUM] = intHoldPrtNoNum ' build PRTNO_NUM
![NAME_TXT] = rsA![LIC_FIRST_NME] & " " & _
rsA![LIC_MIDDLE_NME] & " " & _
rsA![LIC_LAST_NME] & " " & _
rsA![LIC_SUBT_TXT]
![FIRM_TXT] = rsA![LIC_LAST_NME]
![ADDR1_TXT] = rsA![LIC_ADDR_TXT]
![ADDR2_TXT] = rsA![LIC_ADDR_TXT]
![CITY_TXT] = rsA![LIC_CITY_NME]
![STATE_CDE] = rsA![LIC_STATE_CDE]
![ZIPCDE_TXT] = rsA![LIC_ZIP_CDE] & " " &
rsA![LIC_ZIP4_CDE]
.Update
End If
'***********************************************************************
'* If DOA_NME not null, add DOA Name, Address, City, State to Table D
*
'***********************************************************************
If Not IsNull(rsA![DOA_NME]) Then
.AddNew
![CASE_NUM_YR] = intHoldCaseNumYr ' build primary
key pt-1
![CASE_NUM] = intHoldCaseNum ' build primary
key pt-2
![SEQNO_NUM] = intSeqNo + 1
![PRTNO_NUM] = intHoldPrtNoNum ' build PRTNO_NUM
![NAME_TXT] = rsA![DOA_NME]
![FIRM_TXT] = Null
![ADDR1_TXT] = rsA![DOA_ADDR_TXT]
![ADDR2_TXT] = Null
![CITY_TXT] = rsA![DOA_CITY_NME]
![STATE_CDE] = rsA![DOA_STATE_CDE]
![ZIP_CDE] = rsA![LIC_ZIP_CDE] & " " & rsA![LIC_ZIP4_CDE]
.Update
End If
'**********************************************************************
'* If Attorney <> 0, add Attorney Name, Address, City, State to Table D
*
'**********************************************************************
If rsA![ATTY_NUM] <> 0 Then
'select attorney record via sql statement
intSelectAtty = rsA![ATTY_NUM]
DoCmd.runMacro "FRM-Atty-FOF-Select"
If Not (rsB.BOF And rsB.EOF) Then
rsB.MoveFirst
If Not rsB.EOF Then
.AddNew
![CASE_NUM_YR] = intHoldCaseNumYr ' build
pkey pt-1
![CASE_NUM] = intHoldCaseNum ' build
pkey pt-2
![SEQNO_NUM] = intSeqNo + 1
![PRTNO_NUM] = intHoldPrtNoNum ' build
PRTNO_NUM
![NAME_TXT] = rsB![FIRST_NME] & " " & _
rsB![MIDDLE_NME] & " " & _
rsB![LAST_NME] & " " & _
rsB![SUBTITLE_TXT]
![FIRM_NME] = rsB![FIRM_NME]
![ADDR1_TXT] = rsB![ADDR1_TXT]
![ADDR2_TXT] = rsB![ADDR2_TXT]
![CITY_TXT] = rsB![CITY_NME]
![STATE_CDE] = rsB![STATE_CDE]
![ZIP_CDE] = rsB![LIC_ZIP_CDE] & " " &
rsB![LIC_ZIP4_CDE]
.Update
End If
End If
End If
rsA.MoveNext

Wend

End With
Loop

End If

rsA.Close
rsB.Close
rsD.Close

Set rsA = Nothing
Set rsB = Nothing
Set rsD = Nothing

Exit_Load_RC20RW_NAMES20:
' Cleanup Code
On Error Resume Next
Exit Sub

Error_Load_RC20RW_NAMES20:
Select Case Err.Number
Case 2501
' Action Cancelled by User, Ignore Error
Case Else
' Unexpected Error Encountered
MsgBox " The following error has occurred: " _
& vbNewLine & " Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, " Unexpected Error "
End Select
Resume Exit_Load_RC20RW_NAMES20

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error # 3265 - Desc. Item Not Found in this collection ERROR 1
Write New table record failing with error 424 1
Error 3164, Field Cannot Be Updated Error, 2
Set fld1 = NewTbl.CreateField (set attribute Question) 5

Top