AddNew method problem. pls help.

[Guest]

Hello everyone. I have the following code. I am using only one table
(tempTable) and a form (tempForm) to add record to the table using AddNew
method. I have placed this code on ‘on click’ event of a command button
(cmdSave_Click())
Private Sub cmdSave_Click()
Dim rs As ADODB.Recordset

On Error GoTo HandleError

Set rs = New ADODB.Recordset

rs.Open “tempTableâ€, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

With rs

.AddNew

![Qty] = Qty

.Update
End With

rs.Close

Set rs = Nothing

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub
Now the problem is:
1.When I enter value of Qty on the form and click the button to save it on
the table, it creates duplicate values in a strange way. After clicking the
button, if I open the table to see if data is posted keeping the form open,
it shows single record as it should be. But when I close the form and refresh
the table clicking refresh button or reopen the table it duplicates the value.
2.If I hard code the value of Qty like Qty = “100â€, the code works just
fine. But when you replace the value with ![Qty] = Qty, then is the problem.
For testing this further, I added a line Qty = Ҡafter .Update to see what
happens. In this case, I found the same duplicate effect but first record is
blank, only Id has the autonumber value.
How can I use the .AddNew method properly so that I can get the desired
result?
Another question, if I want to use .bookmark on record, how can I use it? As
if I use it like .BookMark = .LastModified after .upadate, it generates an
error message saying “Method or Data Member not foundâ€.
Can anyone please help me on this?
Thanks in advance. 

 

[Dirk Goldgar]

In

Hello everyone. I have the following code. I am using only one table
(tempTable) and a form (tempForm) to add record to the table using
AddNew method. I have placed this code on 'on click' event of a
command button (cmdSave_Click())
Private Sub cmdSave_Click()
Dim rs As ADODB.Recordset

On Error GoTo HandleError

Set rs = New ADODB.Recordset

rs.Open "tempTable", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

With rs

.AddNew

![Qty] = Qty

.Update
End With

rs.Close

Set rs = Nothing

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub
Now the problem is:
1.When I enter value of Qty on the form and click the button to save
it on the table, it creates duplicate values in a strange way. After
clicking the button, if I open the table to see if data is posted
keeping the form open, it shows single record as it should be. But
when I close the form and refresh the table clicking refresh button
or reopen the table it duplicates the value.
2.If I hard code the value of Qty like Qty = "100", the code works
just
fine. But when you replace the value with ![Qty] = Qty, then is the
problem. For testing this further, I added a line Qty = "" after
.Update to see what happens. In this case, I found the same duplicate
effect but first record is blank, only Id has the autonumber value.
How can I use the .AddNew method properly so that I can get the
desired result?
Another question, if I want to use .bookmark on record, how can I use
it? As if I use it like .BookMark = .LastModified after .upadate, it
generates an error message saying "Method or Data Member not found".
Can anyone please help me on this?
Thanks in advance.

It sounds to me as if your form is bound to the table, and Qty is a
bound control on the form. If that's the case, the form will add a
record to the table *and* your code will also add a record to the table.
The form won't add its record, though, until you close the form, or move
to a new record on the form, or take one of several other possible
actions that force the record to be saved. That's why you don't see the
duplicate record until the form is closed.

The normal way to use Access is with bound forms, which make it
unnecessary for you to use the sort of code you posted to add or update
records. The form takes care of all that. If you want to manually save
the record using your own code, then you use unbound forms so that the
form itself has no "record" to save. Those two approaches are mutually
exclusive, though.

 

[Guest]

thanks dirk, it worked. can you please explain how can I use .bookmark in
this code so that i can bookmark the last entered record?
thanks very much again

In

Hello everyone. I have the following code. I am using only one table
(tempTable) and a form (tempForm) to add record to the table using
AddNew method. I have placed this code on 'on click' event of a
command button (cmdSave_Click())
Private Sub cmdSave_Click()
Dim rs As ADODB.Recordset

On Error GoTo HandleError

Set rs = New ADODB.Recordset

rs.Open "tempTable", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

With rs

.AddNew

![Qty] = Qty

.Update
End With

rs.Close

Set rs = Nothing

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub
Now the problem is:
1.When I enter value of Qty on the form and click the button to save
it on the table, it creates duplicate values in a strange way. After
clicking the button, if I open the table to see if data is posted
keeping the form open, it shows single record as it should be. But
when I close the form and refresh the table clicking refresh button
or reopen the table it duplicates the value.
2.If I hard code the value of Qty like Qty = "100", the code works
just
fine. But when you replace the value with ![Qty] = Qty, then is the
problem. For testing this further, I added a line Qty = "" after
.Update to see what happens. In this case, I found the same duplicate
effect but first record is blank, only Id has the autonumber value.
How can I use the .AddNew method properly so that I can get the
desired result?
Another question, if I want to use .bookmark on record, how can I use
it? As if I use it like .BookMark = .LastModified after .upadate, it
generates an error message saying "Method or Data Member not found".
Can anyone please help me on this?
Thanks in advance.

It sounds to me as if your form is bound to the table, and Qty is a
bound control on the form. If that's the case, the form will add a
record to the table *and* your code will also add a record to the table.
The form won't add its record, though, until you close the form, or move
to a new record on the form, or take one of several other possible
actions that force the record to be saved. That's why you don't see the
duplicate record until the form is closed.

The normal way to use Access is with bound forms, which make it
unnecessary for you to use the sort of code you posted to add or update
records. The form takes care of all that. If you want to manually save
the record using your own code, then you use unbound forms so that the
form itself has no "record" to save. Those two approaches are mutually
exclusive, though.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

In

thanks dirk, it worked. can you please explain how can I use
.bookmark in this code so that i can bookmark the last entered record?
thanks very much again

It's not clear to me what you want to do. How did you solve the
original problem, by unbinding the form or by dropping the recordset
update? What is it you really want to accomplish?

Note, by the way, that bookmarks are only sharable between a recordset
and a clone of that recordset. You can't use the bookmark from one
recordset and apply it to another, independent recordset (unless the
second recordset is a clone of the first).