Addnew

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
 
J

JimS

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...
 
D

Dirk Goldgar

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.
 
J

JimS

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...
 
D

Dirk Goldgar

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.
 

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

More Fun with Classes 2
A2007 ADO Update 1

Top