Error 3164, Field Cannot Be Updated Error,

G

Guest

I have an input for that allows users to enter multiple dates to separate
unbound text boxes. These dates are used in applying information to a
temporary data table that is used to print invoices. All of the date fields
are unbound text boxes. The first field is current date and defaults to the
current system date in format of "08/28/ for month, day, year. Other input
dates would follow suit of mm/dd/yyyy and the user is not required to enter
the separator / character. Fields display mm/dd/yyyy when finished.

I’m getting the following error on the form and coding:

The following Error has occurred:
Error # 3164
Error Description: Field Cannot Be Updated

In debugging the code via stepping through the attached code it is failing
when it attempts to pass the data from the input form field:
unbtxtCurrentDate to a field in the table.

The unbound text field is defined on the input form as the following:

Name: unbtxtCurrentDate
Control Source: =Date()
Format: Short Date
Input Mask: 99/99/0000;0;

form when it opens displays the current system date in the unbound text box
named unbtxtCurrentDate in format mm/dd/yyyy.

The temporary table TempTrans is build with a function process of
funcCREATE_TempTrans, which follows:

------
Function funcCREATE_TempTrans():
'***************************************************************
'** This statement Defines the Table named TempTrans
** '***************************************************************
Dim NewTbl As TableDef
Set NewTbl = CurrentDb.CreateTableDef("TempTrans")
Dim fld1 As Field ' for transNo
Dim fld2 As Field ' for UnitNumb
Dim fld3 As Field ' for CuNumb
Dim fld4 As Field ' for transInvoice
Dim fld5 As Field ' for transDate
Dim fld6 As Field ' for transAmnt
Dim fld7 As Field ' for transTypeDC
Dim fld8 As Field ' for transBegDate
Dim fld9 As Field ' for transEndDate
Dim fld10 As Field ' for transDueDate
Dim fld11 As Field ' for transTypeRE
Dim fld12 As Field ' for transDesc
Dim fld13 As Field ' for transWattsUsed
Dim fld14 As Field ' for transWattsRate
Dim fld15 As Field ' for transForm
Dim fld16 As Field ' for transCheckNum
Dim fld17 As Field ' for transPaidDate
Set fld1 = NewTbl.CreateField("transNo", dbLong)
fld1.Attributes = dbAutoIncrField + dbFixedField
Set fld2 = NewTbl.CreateField("UnitNumb", dbText, 4)
fld2.AllowZeroLength = True
Set fld3 = NewTbl.CreateField("CuNumb", dbLong)
Set fld4 = NewTbl.CreateField("transInvoice", dbBoolean)
fld4.DefaultValue = YES
Set fld5 = NewTbl.CreateField("transDate", dbDate)
Set fld6 = NewTbl.CreateField("transAmnt", dbCurrency)
Set fld7 = NewTbl.CreateField("transTypeDC", dbText, 1)
fld7.AllowZeroLength = True
Set fld8 = NewTbl.CreateField("transBegDate", dbDate)
Set fld9 = NewTbl.CreateField("transEndDate", dbDate)
Set fld10 = NewTbl.CreateField("transDueDate", dbDate)
Set fld11 = NewTbl.CreateField("transTypeRE", dbText, 1)
fld11.AllowZeroLength = True
Set fld12 = NewTbl.CreateField("transDesc", dbText, 40)
fld12.AllowZeroLength = True
Set fld13 = NewTbl.CreateField("transWattsUsed", dbLong)
Set fld14 = NewTbl.CreateField("transWattsRate", dbCurrency)
Set fld15 = NewTbl.CreateField("transForm", dbText, 15)
fld15.AllowZeroLength = True
Set fld16 = NewTbl.CreateField("transCheckNum", dbText, 10)
fld16.AllowZeroLength = True
Set fld17 = NewTbl.CreateField("transPaidDate", dbDate)
NewTbl.Fields.Append fld1 ' for transNo
NewTbl.Fields.Append fld2 ' for UnitNumb
NewTbl.Fields.Append fld3 ' for CuNumb
NewTbl.Fields.Append fld4 ' for transInvoice
NewTbl.Fields.Append fld5 ' for transDate
NewTbl.Fields.Append fld6 ' for transAmnt
NewTbl.Fields.Append fld7 ' for transTypeDC
NewTbl.Fields.Append fld8 ' for transBegDate
NewTbl.Fields.Append fld9 ' for transEndDate
NewTbl.Fields.Append fld10 ' for transDueDate
NewTbl.Fields.Append fld11 ' for transTypeRE
NewTbl.Fields.Append fld12 ' for transDesc
NewTbl.Fields.Append fld13 ' for transWattsUsed
NewTbl.Fields.Append fld14 ' for transWattsRate
NewTbl.Fields.Append fld15 ' for transForm
NewTbl.Fields.Append fld16 ' for transCheckNum
NewTbl.Fields.Append fld17 ' for transPaidDate
CurrentDb.TableDefs.Append NewTbl
strSQL = "ALTER TABLE TempTrans " & _
"ADD CONSTRAINT PK_TempTrans " & _
"PRIMARY KEY(transNo)"
'CurrentDb.Execute strSQL, dbFailOnError
End Function

------

The process is failing as it steps through the following function named
funcLoadInvoices:

The failing line of code appears to be ‘ transDate = Me.unbtxtCurrentDate
‘

The function code follows:
-----
Function funcLoadInvoices():
On Error GoTo Error_Load_Invoices
Dim rsA As DAO.Recordset
Dim rsD As DAO.Recordset
Dim intTypeRun As Integer
intTypeRun = Me.Combo73
Set rsA = CurrentDb.OpenRecordset("Units")
Set rsD = CurrentDb.OpenRecordset("TempTrans", dbOpenDynaset,
dbAppendOnly)

If Not (rsA.BOF And rsA.EOF) Then
rsA.MoveFirst
Do Until rsA.EOF
With rsD
While Not rsA.EOF
Select Case intTypeRun
Case Is = 1
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 2
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 3
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 4
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 5
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 6
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 7
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
End Select
rsA.MoveNext
Wend ' ties to "While Not rsA.EOF"
End With ' ties to "With rsD"
Loop ' ties to "Do Until rsA.EOF"
End If

rsA.Close
rsD.Close
Set rsA = Nothing
Set rsD = Nothing

Exit_funcLoadInvoices:
' Clean Up Code
On Error Resume Next
Exit Function

Error_Load_Invoices:
Select Case Err.Number
Case 2501
' Action Cancelled By User, Ignore Error
Case Else
' Unexpected Error Encountered
MsgBox " The following Error # 2 Has Occurred " _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & " Error Description: " & Err.Description _
, vbExclamation, " Unexpected Error "
End Select

rsA.Close
rsD.Close
Set rsA = Nothing
Set rsD = Nothing
Resume Exit_funcLoadInvoices
End Function
 
J

Jeff L

If your function is in a module, you cannot refer to your field names
with Me.FieldName.

You either have to pass the values to the function or refer to them as
Forms!YourFormName!YourFieldName


RobertNusz@DPS said:
I have an input for that allows users to enter multiple dates to separate
unbound text boxes. These dates are used in applying information to a
temporary data table that is used to print invoices. All of the date fields
are unbound text boxes. The first field is current date and defaults to the
current system date in format of "08/28/ for month, day, year. Other input
dates would follow suit of mm/dd/yyyy and the user is not required to enter
the separator / character. Fields display mm/dd/yyyy when finished.

I'm getting the following error on the form and coding:

The following Error has occurred:
Error # 3164
Error Description: Field Cannot Be Updated

In debugging the code via stepping through the attached code it is failing
when it attempts to pass the data from the input form field:
unbtxtCurrentDate to a field in the table.

The unbound text field is defined on the input form as the following:

Name: unbtxtCurrentDate
Control Source: =Date()
Format: Short Date
Input Mask: 99/99/0000;0;

form when it opens displays the current system date in the unbound text box
named unbtxtCurrentDate in format mm/dd/yyyy.

The temporary table TempTrans is build with a function process of
funcCREATE_TempTrans, which follows:

------
Function funcCREATE_TempTrans():
'***************************************************************
'** This statement Defines the Table named TempTrans
** '***************************************************************
Dim NewTbl As TableDef
Set NewTbl = CurrentDb.CreateTableDef("TempTrans")
Dim fld1 As Field ' for transNo
Dim fld2 As Field ' for UnitNumb
Dim fld3 As Field ' for CuNumb
Dim fld4 As Field ' for transInvoice
Dim fld5 As Field ' for transDate
Dim fld6 As Field ' for transAmnt
Dim fld7 As Field ' for transTypeDC
Dim fld8 As Field ' for transBegDate
Dim fld9 As Field ' for transEndDate
Dim fld10 As Field ' for transDueDate
Dim fld11 As Field ' for transTypeRE
Dim fld12 As Field ' for transDesc
Dim fld13 As Field ' for transWattsUsed
Dim fld14 As Field ' for transWattsRate
Dim fld15 As Field ' for transForm
Dim fld16 As Field ' for transCheckNum
Dim fld17 As Field ' for transPaidDate
Set fld1 = NewTbl.CreateField("transNo", dbLong)
fld1.Attributes = dbAutoIncrField + dbFixedField
Set fld2 = NewTbl.CreateField("UnitNumb", dbText, 4)
fld2.AllowZeroLength = True
Set fld3 = NewTbl.CreateField("CuNumb", dbLong)
Set fld4 = NewTbl.CreateField("transInvoice", dbBoolean)
fld4.DefaultValue = YES
Set fld5 = NewTbl.CreateField("transDate", dbDate)
Set fld6 = NewTbl.CreateField("transAmnt", dbCurrency)
Set fld7 = NewTbl.CreateField("transTypeDC", dbText, 1)
fld7.AllowZeroLength = True
Set fld8 = NewTbl.CreateField("transBegDate", dbDate)
Set fld9 = NewTbl.CreateField("transEndDate", dbDate)
Set fld10 = NewTbl.CreateField("transDueDate", dbDate)
Set fld11 = NewTbl.CreateField("transTypeRE", dbText, 1)
fld11.AllowZeroLength = True
Set fld12 = NewTbl.CreateField("transDesc", dbText, 40)
fld12.AllowZeroLength = True
Set fld13 = NewTbl.CreateField("transWattsUsed", dbLong)
Set fld14 = NewTbl.CreateField("transWattsRate", dbCurrency)
Set fld15 = NewTbl.CreateField("transForm", dbText, 15)
fld15.AllowZeroLength = True
Set fld16 = NewTbl.CreateField("transCheckNum", dbText, 10)
fld16.AllowZeroLength = True
Set fld17 = NewTbl.CreateField("transPaidDate", dbDate)
NewTbl.Fields.Append fld1 ' for transNo
NewTbl.Fields.Append fld2 ' for UnitNumb
NewTbl.Fields.Append fld3 ' for CuNumb
NewTbl.Fields.Append fld4 ' for transInvoice
NewTbl.Fields.Append fld5 ' for transDate
NewTbl.Fields.Append fld6 ' for transAmnt
NewTbl.Fields.Append fld7 ' for transTypeDC
NewTbl.Fields.Append fld8 ' for transBegDate
NewTbl.Fields.Append fld9 ' for transEndDate
NewTbl.Fields.Append fld10 ' for transDueDate
NewTbl.Fields.Append fld11 ' for transTypeRE
NewTbl.Fields.Append fld12 ' for transDesc
NewTbl.Fields.Append fld13 ' for transWattsUsed
NewTbl.Fields.Append fld14 ' for transWattsRate
NewTbl.Fields.Append fld15 ' for transForm
NewTbl.Fields.Append fld16 ' for transCheckNum
NewTbl.Fields.Append fld17 ' for transPaidDate
CurrentDb.TableDefs.Append NewTbl
strSQL = "ALTER TABLE TempTrans " & _
"ADD CONSTRAINT PK_TempTrans " & _
"PRIMARY KEY(transNo)"
'CurrentDb.Execute strSQL, dbFailOnError
End Function

------

The process is failing as it steps through the following function named
funcLoadInvoices:

The failing line of code appears to be ' transDate = Me.unbtxtCurrentDate
'

The function code follows:
-----
Function funcLoadInvoices():
On Error GoTo Error_Load_Invoices
Dim rsA As DAO.Recordset
Dim rsD As DAO.Recordset
Dim intTypeRun As Integer
intTypeRun = Me.Combo73
Set rsA = CurrentDb.OpenRecordset("Units")
Set rsD = CurrentDb.OpenRecordset("TempTrans", dbOpenDynaset,
dbAppendOnly)

If Not (rsA.BOF And rsA.EOF) Then
rsA.MoveFirst
Do Until rsA.EOF
With rsD
While Not rsA.EOF
Select Case intTypeRun
Case Is = 1
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 2
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 3
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 4
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 5
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 6
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 7
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
End Select
rsA.MoveNext
Wend ' ties to "While Not rsA.EOF"
End With ' ties to "With rsD"
Loop ' ties to "Do Until rsA.EOF"
End If

rsA.Close
rsD.Close
Set rsA = Nothing
Set rsD = Nothing

Exit_funcLoadInvoices:
' Clean Up Code
On Error Resume Next
Exit Function

Error_Load_Invoices:
Select Case Err.Number
Case 2501
' Action Cancelled By User, Ignore Error
Case Else
' Unexpected Error Encountered
MsgBox " The following Error # 2 Has Occurred " _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & " Error Description: " & Err.Description _
, vbExclamation, " Unexpected Error "
End Select

rsA.Close
rsD.Close
Set rsA = Nothing
Set rsD = Nothing
Resume Exit_funcLoadInvoices
End Function
Click to expand...
 
G

Guest

Jeff,

Thanks for the response but my function is part of the same form coding, not
external.

That's why I used the Me.unbtxtCurrentDate indicator. When you code
msgbox " value for unbtxtCurrentDate is " & Me.unbtxtCurrentDate
during execution of the form, it reflects what was entered and currently in
the field
as 10/01/2006 when what was entered was the same date, without having to
enter the separators '/' character.

thanks

--
Robert Nusz @ DPS


Jeff L said:
If your function is in a module, you cannot refer to your field names
with Me.FieldName.

You either have to pass the values to the function or refer to them as
Forms!YourFormName!YourFieldName


RobertNusz@DPS said:
I have an input for that allows users to enter multiple dates to separate
unbound text boxes. These dates are used in applying information to a
temporary data table that is used to print invoices. All of the date fields
are unbound text boxes. The first field is current date and defaults to the
current system date in format of "08/28/ for month, day, year. Other input
dates would follow suit of mm/dd/yyyy and the user is not required to enter
the separator / character. Fields display mm/dd/yyyy when finished.

I'm getting the following error on the form and coding:

The following Error has occurred:
Error # 3164
Error Description: Field Cannot Be Updated

In debugging the code via stepping through the attached code it is failing
when it attempts to pass the data from the input form field:
unbtxtCurrentDate to a field in the table.

The unbound text field is defined on the input form as the following:

Name: unbtxtCurrentDate
Control Source: =Date()
Format: Short Date
Input Mask: 99/99/0000;0;

form when it opens displays the current system date in the unbound text box
named unbtxtCurrentDate in format mm/dd/yyyy.

The temporary table TempTrans is build with a function process of
funcCREATE_TempTrans, which follows:

------
Function funcCREATE_TempTrans():
'***************************************************************
'** This statement Defines the Table named TempTrans
** '***************************************************************
Dim NewTbl As TableDef
Set NewTbl = CurrentDb.CreateTableDef("TempTrans")
Dim fld1 As Field ' for transNo
Dim fld2 As Field ' for UnitNumb
Dim fld3 As Field ' for CuNumb
Dim fld4 As Field ' for transInvoice
Dim fld5 As Field ' for transDate
Dim fld6 As Field ' for transAmnt
Dim fld7 As Field ' for transTypeDC
Dim fld8 As Field ' for transBegDate
Dim fld9 As Field ' for transEndDate
Dim fld10 As Field ' for transDueDate
Dim fld11 As Field ' for transTypeRE
Dim fld12 As Field ' for transDesc
Dim fld13 As Field ' for transWattsUsed
Dim fld14 As Field ' for transWattsRate
Dim fld15 As Field ' for transForm
Dim fld16 As Field ' for transCheckNum
Dim fld17 As Field ' for transPaidDate
Set fld1 = NewTbl.CreateField("transNo", dbLong)
fld1.Attributes = dbAutoIncrField + dbFixedField
Set fld2 = NewTbl.CreateField("UnitNumb", dbText, 4)
fld2.AllowZeroLength = True
Set fld3 = NewTbl.CreateField("CuNumb", dbLong)
Set fld4 = NewTbl.CreateField("transInvoice", dbBoolean)
fld4.DefaultValue = YES
Set fld5 = NewTbl.CreateField("transDate", dbDate)
Set fld6 = NewTbl.CreateField("transAmnt", dbCurrency)
Set fld7 = NewTbl.CreateField("transTypeDC", dbText, 1)
fld7.AllowZeroLength = True
Set fld8 = NewTbl.CreateField("transBegDate", dbDate)
Set fld9 = NewTbl.CreateField("transEndDate", dbDate)
Set fld10 = NewTbl.CreateField("transDueDate", dbDate)
Set fld11 = NewTbl.CreateField("transTypeRE", dbText, 1)
fld11.AllowZeroLength = True
Set fld12 = NewTbl.CreateField("transDesc", dbText, 40)
fld12.AllowZeroLength = True
Set fld13 = NewTbl.CreateField("transWattsUsed", dbLong)
Set fld14 = NewTbl.CreateField("transWattsRate", dbCurrency)
Set fld15 = NewTbl.CreateField("transForm", dbText, 15)
fld15.AllowZeroLength = True
Set fld16 = NewTbl.CreateField("transCheckNum", dbText, 10)
fld16.AllowZeroLength = True
Set fld17 = NewTbl.CreateField("transPaidDate", dbDate)
NewTbl.Fields.Append fld1 ' for transNo
NewTbl.Fields.Append fld2 ' for UnitNumb
NewTbl.Fields.Append fld3 ' for CuNumb
NewTbl.Fields.Append fld4 ' for transInvoice
NewTbl.Fields.Append fld5 ' for transDate
NewTbl.Fields.Append fld6 ' for transAmnt
NewTbl.Fields.Append fld7 ' for transTypeDC
NewTbl.Fields.Append fld8 ' for transBegDate
NewTbl.Fields.Append fld9 ' for transEndDate
NewTbl.Fields.Append fld10 ' for transDueDate
NewTbl.Fields.Append fld11 ' for transTypeRE
NewTbl.Fields.Append fld12 ' for transDesc
NewTbl.Fields.Append fld13 ' for transWattsUsed
NewTbl.Fields.Append fld14 ' for transWattsRate
NewTbl.Fields.Append fld15 ' for transForm
NewTbl.Fields.Append fld16 ' for transCheckNum
NewTbl.Fields.Append fld17 ' for transPaidDate
CurrentDb.TableDefs.Append NewTbl
strSQL = "ALTER TABLE TempTrans " & _
"ADD CONSTRAINT PK_TempTrans " & _
"PRIMARY KEY(transNo)"
'CurrentDb.Execute strSQL, dbFailOnError
End Function

------

The process is failing as it steps through the following function named
funcLoadInvoices:

The failing line of code appears to be ' transDate = Me.unbtxtCurrentDate
'

The function code follows:
-----
Function funcLoadInvoices():
On Error GoTo Error_Load_Invoices
Dim rsA As DAO.Recordset
Dim rsD As DAO.Recordset
Dim intTypeRun As Integer
intTypeRun = Me.Combo73
Set rsA = CurrentDb.OpenRecordset("Units")
Set rsD = CurrentDb.OpenRecordset("TempTrans", dbOpenDynaset,
dbAppendOnly)

If Not (rsA.BOF And rsA.EOF) Then
rsA.MoveFirst
Do Until rsA.EOF
With rsD
While Not rsA.EOF
Select Case intTypeRun
Case Is = 1
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 2
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 3
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 4
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 5
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 6
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
Case Is = 7
rsD.AddNew
UnitNumb = rsA![UnitNumb]
CuNumb = rsA![CuNumb]
transDate = Me.unbtxtCurrentDate
'failing line
transAmnt = rsA![UnRate]
transTypeDC = "D"
transBegDate = Me.unbtxtBegDate
transEndDate = Me.unbtxtEndDate
transDueDate = Me.unbtxtDueDate
transTypeRE = "R"
transDesc = "Invoice For Month Of"
transWattsUsed = 0
transWattsRate = 0
transForm = "Invoice"
rsD.Update
End Select
rsA.MoveNext
Wend ' ties to "While Not rsA.EOF"
End With ' ties to "With rsD"
Loop ' ties to "Do Until rsA.EOF"
End If

rsA.Close
rsD.Close
Set rsA = Nothing
Set rsD = Nothing

Exit_funcLoadInvoices:
' Clean Up Code
On Error Resume Next
Exit Function

Error_Load_Invoices:
Select Case Err.Number
Case 2501
' Action Cancelled By User, Ignore Error
Case Else
' Unexpected Error Encountered
MsgBox " The following Error # 2 Has Occurred " _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & " Error Description: " & Err.Description _
, vbExclamation, " Unexpected Error "
End Select

rsA.Close
rsD.Close
Set rsA = Nothing
Set rsD = Nothing
Resume Exit_funcLoadInvoices
End Function
Click to expand...
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

Set fld1 = NewTbl.CreateField (set attribute Question) 5

Top