Addnew

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

I'm iterating through a list box, adding items to a header-detail structure.
The code is in a class module (clsPicklist). I'm getting an error saying the
maximum number of pending updates exceeded. I'm sure I'm doing something
simple wrong. Herewith the code:

Private Sub btnOneTouch_Click()
Dim lngPicklistID As Double
PickList.BoMID = lngBoMID 'Set the Bill of Materals ID
lngPicklistID = PickList.NewList 'Create a new Header Record using
BoMID
Dim i As Long
For i = 0 To Me.lbBoM.ListCount - 1
PickList.AddNewDetail CLng(Me.lbBoM.Column(0, i)),
CDbl(Me.lbBoM.Column(1, i))
Next i
End Sub

[Inside the class module...]
Private Sub Class_Initialize()
Debug.Print "clsPickList Initialized at " & Now()
rstPickListMaster.Open "tblPickListMaster", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
rstPickListDetail.Open "tblPickListDetail", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
End Sub
..
..
..
Public Function NewList()
With rstPickListMaster
' Debug.Print !picklistid
.AddNew
!PicklistBoMID = m_lngBoMID
!PicklistDropLocation = ""
!PicklistNotifyNbr = ""
!PicklistDescription = ""
!PicklistPriority = "N"
.Update
Debug.Print !picklistid
NewList = !picklistid
End With
End Function

Public Function AddNewDetail(BoMDetailID As Long, Quantity As Double) As Long
With rstPickListDetail
.AddNew
!PLPicklistid = rstPickListMaster!picklistid
!PLBoMDetailID = BoMDetailID
!PLDetailQtytoPick = Quantity
.Update
AddNewDetail = !pldetailid
Debug.Print "Added " & !pldetailid
End With
End Function
 
OK, I've figured out it has to do with the connection object. The scope of
the two recordsets are global to the class. The connection variable was not.
Still, I set the connection variable to be global to the class and now it
fails when opening the recordsets:

Option Compare Database
Option Explicit
Private m_lngBoMID As Long
Dim rstPickListMaster As New ADODB.Recordset
Dim rstPickListDetail As New ADODB.Recordset
Dim cnn As ADODB.Connection

Private Sub Class_Initialize()
cnn = CurrentProject.Connection
rstPickListMaster.Open "tblPickListMaster", cnn, adOpenDynamic,
adLockBatchOptimistic
rstPickListDetail.Open "tblPickListDetail", cnn, adOpenDynamic,
adLockBatchOptimistic
Debug.Print "clsPickList Initialized at " & Now()
End Sub
Public Function NewList()
With rstPickListMaster
.AddNew
!PicklistBoMID = m_lngBoMID
!PicklistDropLocation = ""
!PicklistNotifyNbr = ""
!PicklistDescription = ""
!PicklistPriority = "N"
.Update
Debug.Print "Added Header ID Nbr: " & !picklistid
NewList = !picklistid
End With
End Function
Public Function AddNewDetail(BoMDetailID As Long, Quantity As Double) As Long
With rstPickListDetail
.AddNew
!PLPicklistid = rstPickListMaster!picklistid
!PLBoMDetailID = BoMDetailID
!PLDetailQtytoPick = Quantity
.Update
AddNewDetail = !pldetailid
Debug.Print "Added Picklist ID" & !pldetailid
End With
End Function--
Jim


JimS said:
I'm iterating through a list box, adding items to a header-detail structure.
The code is in a class module (clsPicklist). I'm getting an error saying the
maximum number of pending updates exceeded. I'm sure I'm doing something
simple wrong. Herewith the code:

Private Sub btnOneTouch_Click()
Dim lngPicklistID As Double
PickList.BoMID = lngBoMID 'Set the Bill of Materals ID
lngPicklistID = PickList.NewList 'Create a new Header Record using
BoMID
Dim i As Long
For i = 0 To Me.lbBoM.ListCount - 1
PickList.AddNewDetail CLng(Me.lbBoM.Column(0, i)),
CDbl(Me.lbBoM.Column(1, i))
Next i
End Sub

[Inside the class module...]
Private Sub Class_Initialize()
Debug.Print "clsPickList Initialized at " & Now()
rstPickListMaster.Open "tblPickListMaster", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
rstPickListDetail.Open "tblPickListDetail", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
End Sub
.
.
.
Public Function NewList()
With rstPickListMaster
' Debug.Print !picklistid
.AddNew
!PicklistBoMID = m_lngBoMID
!PicklistDropLocation = ""
!PicklistNotifyNbr = ""
!PicklistDescription = ""
!PicklistPriority = "N"
.Update
Debug.Print !picklistid
NewList = !picklistid
End With
End Function

Public Function AddNewDetail(BoMDetailID As Long, Quantity As Double) As Long
With rstPickListDetail
.AddNew
!PLPicklistid = rstPickListMaster!picklistid
!PLBoMDetailID = BoMDetailID
!PLDetailQtytoPick = Quantity
.Update
AddNewDetail = !pldetailid
Debug.Print "Added " & !pldetailid
End With
End Function
Click to expand...
 
JimS said:
I'm iterating through a list box, adding items to a header-detail
structure.
The code is in a class module (clsPicklist). I'm getting an error saying
the
maximum number of pending updates exceeded. I'm sure I'm doing something
simple wrong. Herewith the code:

Private Sub btnOneTouch_Click()
Dim lngPicklistID As Double
PickList.BoMID = lngBoMID 'Set the Bill of Materals ID
lngPicklistID = PickList.NewList 'Create a new Header Record using
BoMID
Dim i As Long
For i = 0 To Me.lbBoM.ListCount - 1
PickList.AddNewDetail CLng(Me.lbBoM.Column(0, i)),
CDbl(Me.lbBoM.Column(1, i))
Next i
End Sub

[Inside the class module...]
Private Sub Class_Initialize()
Debug.Print "clsPickList Initialized at " & Now()
rstPickListMaster.Open "tblPickListMaster", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
rstPickListDetail.Open "tblPickListDetail", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
End Sub
.
.
.
Public Function NewList()
With rstPickListMaster
' Debug.Print !picklistid
.AddNew
!PicklistBoMID = m_lngBoMID
!PicklistDropLocation = ""
!PicklistNotifyNbr = ""
!PicklistDescription = ""
!PicklistPriority = "N"
.Update
Debug.Print !picklistid
NewList = !picklistid
End With
End Function

Public Function AddNewDetail(BoMDetailID As Long, Quantity As Double) As
Long
With rstPickListDetail
.AddNew
!PLPicklistid = rstPickListMaster!picklistid
!PLBoMDetailID = BoMDetailID
!PLDetailQtytoPick = Quantity
.Update
AddNewDetail = !pldetailid
Debug.Print "Added " & !pldetailid
End With
End Function
Click to expand...


I haven't really explored the more sophisticated ADO methods, but I see that
you opened your recordset in Batch Mode (using adBatchOptimistic). As I
understand that, in batch mode your updates are cached in a buffer, and only
written out when you execute the recordset's UpdateBatch method. Are you
calling that method anywhere? If not, I would presume that at some point
you would fill the buffer.
 
See, I knew I did something stupid. Thanks!
--
Jim


Dirk Goldgar said:
JimS said:
I'm iterating through a list box, adding items to a header-detail
structure.
The code is in a class module (clsPicklist). I'm getting an error saying
the
maximum number of pending updates exceeded. I'm sure I'm doing something
simple wrong. Herewith the code:

Private Sub btnOneTouch_Click()
Dim lngPicklistID As Double
PickList.BoMID = lngBoMID 'Set the Bill of Materals ID
lngPicklistID = PickList.NewList 'Create a new Header Record using
BoMID
Dim i As Long
For i = 0 To Me.lbBoM.ListCount - 1
PickList.AddNewDetail CLng(Me.lbBoM.Column(0, i)),
CDbl(Me.lbBoM.Column(1, i))
Next i
End Sub

[Inside the class module...]
Private Sub Class_Initialize()
Debug.Print "clsPickList Initialized at " & Now()
rstPickListMaster.Open "tblPickListMaster", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
rstPickListDetail.Open "tblPickListDetail", CurrentProject.Connection,
adOpenDynamic, adLockBatchOptimistic
End Sub
.
.
.
Public Function NewList()
With rstPickListMaster
' Debug.Print !picklistid
.AddNew
!PicklistBoMID = m_lngBoMID
!PicklistDropLocation = ""
!PicklistNotifyNbr = ""
!PicklistDescription = ""
!PicklistPriority = "N"
.Update
Debug.Print !picklistid
NewList = !picklistid
End With
End Function

Public Function AddNewDetail(BoMDetailID As Long, Quantity As Double) As
Long
With rstPickListDetail
.AddNew
!PLPicklistid = rstPickListMaster!picklistid
!PLBoMDetailID = BoMDetailID
!PLDetailQtytoPick = Quantity
.Update
AddNewDetail = !pldetailid
Debug.Print "Added " & !pldetailid
End With
End Function
Click to expand...


I haven't really explored the more sophisticated ADO methods, but I see that
you opened your recordset in Batch Mode (using adBatchOptimistic). As I
understand that, in batch mode your updates are cached in a buffer, and only
written out when you execute the recordset's UpdateBatch method. Are you
calling that method anywhere? If not, I would presume that at some point
you would fill the buffer.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
JimS said:
Private Sub Class_Initialize()
cnn = CurrentProject.Connection
Click to expand...

This line ought to use the Set keyword:

Set cnn = CurrentProject.Connection

Otherwise you are assigning the connection string from
CurrentProject.Connection to an object variable that has not been
instantiated.
 
Back
Top