ADO .addnew Access 2K problems

[kirk Wilson]

This Access 2K code is tied to a command button that closes the form
New_Customer. The form has two unbound text boxes, (fname) and
(lname). It also has a bound TextBox named Record_id# whos data source
is the autonumber primary key for the table that contains Location
Information. The relationship is a one "Location" to many "Contacts"
in the Master_Contacts table. The Current event of New_Customer Form
uses a DoCmd to open the form & place it at a new record. The user
enters first & last name info in the approproiate text boxes and then
clicks the close command button. The click event for the close command
button is supposed to add the first name, last name, address key link,
and the byte number 1 for the field [Type] as a new record to the
table Master_Contacts. The code compiles without error and executes
without any runtime errors. I can see the correct values in debug for
[LastName],[FisrtName],[Address], & [Type] but no new record is
created. The help system seems to offer help for DAO only. What have I
done wrong here?

Sub CloseForm_Click()
On Error GoTo Err_Command60_Click

Dim rstRecordSet As ADODB.Recordset
Set rstRecordSet = New ADODB.Recordset


Dim strIndex As String
Dim ctlForm As Object
Dim strLname As String
Dim strFname As String

Set ctlForm = Forms![New_Customer]
strIndex = ctlForm.[Record_id#]
strLname = ctlForm.lname
strFname = ctlForm.fname

With rstRecordSet
Set .ActiveConnection = CurrentProject.Connection
.Source = "Master_Contacts"
.CursorLocation = adUseServer
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.Open
.AddNew
.Fields("LastName") = strLname
.Fields("FirstName") = strFname
.Fields("Address") = strIndex
.Fields("Type") = 1
.Update
End With
rstRecordSet.Close
Set rstRecordSet = Nothing

DoCmd.Close
Set ctlForm = Forms![Edit_Customer]
ctlForm.Requery
Forms![Edit_Customer]![Record_id#].SetFocus
DoCmd.FindRecord strIndex, , , , , acCurrent
Exit_Command60_Click:
Exit Sub

Err_Command60_Click:
MsgBox Err.Description
Resume Exit_Command60_Click

End Sub


Private Sub Form_Current()

Dim str_Form As String
str_Form = "new_customer"

DoCmd.GoToRecord acForm, str_Form, acNewRec

End Sub

 

[TomU]

Kirk,

Because you're using
.LockType = adLockBatchOptimistic
you need code to commit the batch of updates.

But since your code only adds a single record, using
.LockType = adLockOptimistic
is all that is needed.

Tom

 

[kirk Wilson]

I made the change & everything works now. Why did it not work before?
I can envision batch update senerios where the criteria wrturns onky 1
record for update. In other words why didn't it make a batch update of
a one record batch?

btw Thanks for the proof reading help.

Kirk,

Because you're using
.LockType = adLockBatchOptimistic
you need code to commit the batch of updates.

But since your code only adds a single record, using
.LockType = adLockOptimistic
is all that is needed.

Tom

This Access 2K code is tied to a command button that closes the form
New_Customer. The form has two unbound text boxes, (fname) and
(lname). It also has a bound TextBox named Record_id# whos data source
is the autonumber primary key for the table that contains Location
Information. The relationship is a one "Location" to many "Contacts"
in the Master_Contacts table. The Current event of New_Customer Form
uses a DoCmd to open the form & place it at a new record. The user
enters first & last name info in the approproiate text boxes and then
clicks the close command button. The click event for the close command
button is supposed to add the first name, last name, address key link,
and the byte number 1 for the field [Type] as a new record to the
table Master_Contacts. The code compiles without error and executes
without any runtime errors. I can see the correct values in debug for
[LastName],[FisrtName],[Address], & [Type] but no new record is
created. The help system seems to offer help for DAO only. What have I
done wrong here?

Sub CloseForm_Click()
On Error GoTo Err_Command60_Click

Dim rstRecordSet As ADODB.Recordset
Set rstRecordSet = New ADODB.Recordset


Dim strIndex As String
Dim ctlForm As Object
Dim strLname As String
Dim strFname As String

Set ctlForm = Forms![New_Customer]
strIndex = ctlForm.[Record_id#]
strLname = ctlForm.lname
strFname = ctlForm.fname

With rstRecordSet
Set .ActiveConnection = CurrentProject.Connection
.Source = "Master_Contacts"
.CursorLocation = adUseServer
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.Open
.AddNew
.Fields("LastName") = strLname
.Fields("FirstName") = strFname
.Fields("Address") = strIndex
.Fields("Type") = 1
.Update
End With
rstRecordSet.Close
Set rstRecordSet = Nothing

DoCmd.Close
Set ctlForm = Forms![Edit_Customer]
ctlForm.Requery
Forms![Edit_Customer]![Record_id#].SetFocus
DoCmd.FindRecord strIndex, , , , , acCurrent
Exit_Command60_Click:
Exit Sub

Err_Command60_Click:
MsgBox Err.Description
Resume Exit_Command60_Click

End Sub


Private Sub Form_Current()

Dim str_Form As String
str_Form = "new_customer"

DoCmd.GoToRecord acForm, str_Form, acNewRec

End Sub

 

[Brendan Reynolds]

Because you told it not to! I know that's not what you intended to tell
it, but in effect, that's what you did.

When you tell ADO that you're going to use batch updating, you're telling it
to cache the updates in memory and not to save them to the database until
you call the UpdateBatch method of the recordset. And you never called the
UpdateBatch method.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I made the change & everything works now. Why did it not work before?
I can envision batch update senerios where the criteria wrturns onky 1
record for update. In other words why didn't it make a batch update of
a one record batch?

btw Thanks for the proof reading help.

Kirk,

Because you're using
.LockType = adLockBatchOptimistic
you need code to commit the batch of updates.

But since your code only adds a single record, using
.LockType = adLockOptimistic
is all that is needed.

Tom

This Access 2K code is tied to a command button that closes the form
New_Customer. The form has two unbound text boxes, (fname) and
(lname). It also has a bound TextBox named Record_id# whos data source
is the autonumber primary key for the table that contains Location
Information. The relationship is a one "Location" to many "Contacts"
in the Master_Contacts table. The Current event of New_Customer Form
uses a DoCmd to open the form & place it at a new record. The user
enters first & last name info in the approproiate text boxes and then
clicks the close command button. The click event for the close command
button is supposed to add the first name, last name, address key link,
and the byte number 1 for the field [Type] as a new record to the
table Master_Contacts. The code compiles without error and executes
without any runtime errors. I can see the correct values in debug for
[LastName],[FisrtName],[Address], & [Type] but no new record is
created. The help system seems to offer help for DAO only. What have I
done wrong here?

Sub CloseForm_Click()
On Error GoTo Err_Command60_Click

Dim rstRecordSet As ADODB.Recordset
Set rstRecordSet = New ADODB.Recordset


Dim strIndex As String
Dim ctlForm As Object
Dim strLname As String
Dim strFname As String

Set ctlForm = Forms![New_Customer]
strIndex = ctlForm.[Record_id#]
strLname = ctlForm.lname
strFname = ctlForm.fname

With rstRecordSet
Set .ActiveConnection = CurrentProject.Connection
.Source = "Master_Contacts"
.CursorLocation = adUseServer
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.Open
.AddNew
.Fields("LastName") = strLname
.Fields("FirstName") = strFname
.Fields("Address") = strIndex
.Fields("Type") = 1
.Update
End With
rstRecordSet.Close
Set rstRecordSet = Nothing

DoCmd.Close
Set ctlForm = Forms![Edit_Customer]
ctlForm.Requery
Forms![Edit_Customer]![Record_id#].SetFocus
DoCmd.FindRecord strIndex, , , , , acCurrent
Exit_Command60_Click:
Exit Sub

Err_Command60_Click:
MsgBox Err.Description
Resume Exit_Command60_Click

End Sub


Private Sub Form_Current()

Dim str_Form As String
str_Form = "new_customer"

DoCmd.GoToRecord acForm, str_Form, acNewRec

End Sub