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

--
Jim

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" wrote:

> 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
>
> --
> Jim

"JimS" <(E-Mail Removed)> wrote in message
news:B5632550-10AC-4F29-8B1A-(E-Mail Removed)...
> 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


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)

See, I knew I did something stupid. Thanks!
--
Jim


"Dirk Goldgar" wrote:

> "JimS" <(E-Mail Removed)> wrote in message
> news:B5632550-10AC-4F29-8B1A-(E-Mail Removed)...
> > 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
>
>
> 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)
>

"JimS" <(E-Mail Removed)> wrote in message
news2BE4CCD-7972-42B1-9AB6-(E-Mail Removed)...
>
> Private Sub Class_Initialize()
> cnn = CurrentProject.Connection

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.

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

(please reply to the newsgroup)