Not able to edit / update query

[purushotham k via AccessMonster.com]

Hi,

I am new to vb and MS access and database programming. I am doing a project
for Distributor Invoice system. I have problems as mentioned below.

I have 2 tables named "tblInvoice_BillDetails" and "tblInvoice_GoodsDetails".
To save memory I am using the above 2 tables in one form.

Table1
tblInvoice_BillDetails
Fields
txtInvoiceNo(Primary Key), cboCompanyName, txtDate, txtSalesMan
Table2
tblInvoice_GoodsDetails
Fields
txtInvoiceNo, cboItemDescription, txtAmount, txt Discount

I am extracting the fields from Table2 using the code below ( i.e, the fields
which are related to a particular invoice number)

Set rsInvoice_Details = New ADODB.Recordset
rsInvoice_Details.Open "Select * FROM tblInvoice_GoodsDetails WHERE InvoiceNo
='" & txtInvoiceNo.Text & "'", Connection1, adOpenKeyset, adLockPessimistic

I am adding the records to Table2 using the code below

If Add = True Then
With rsInvoice_GoodsDetails (note that record source name is different from
the query record source

name above)
For k = 0 To t - 1

If NewRecord = True Then .AddNew
!ItemDescription = Items(0, k)
!Quantity = Items(1, k)
!Free = Items(2, k)
!Rate = Items(3, k)
!Amount = Items(4, k)
!Discount = Items(5, k)
!InvoiceNo = txtInvoiceNo.Text

.Update
.Requery
: Next k

End With
End If

I am adding the records and working fine.

When I want to edit the fields which are dispalyed using the query mentioned
above the fields are not updating.

Below is the code i am using for updation.

If Edit = True Then
With rsInvoice_Details
For k = 0 To t - 1

!ItemDescription = Items(0, k)
!Quantity = Items(1, k)
'!Free = Items(2, k)
!Rate = Items(3, k)
!Amount = Items(4, k)
!Discount = Items(5, k)
!InvoiceNo = txtInvoiceNo.Text

.Update
.Requery

: Next k
End With
End If

Kindly help me to solve the problem.

Thanks and regards,
Purushotham Kalepalli

 

[Michel Walsh]

Hi,


It seems you update the "first" record of the recordset? The recordset
has many record, which one did REALLY get updated? or you carefully did a
RECORD MOVE to move to the desired record BEFORE you updated it through the
recordset? Since you are using 2 recordsets, be aware they can easily be
out-of-synch, ie, one may "point" to a different record than the other, so,
maybe you do an update, but of the WRONG record (not the one you WISH to
update).


You may also have to requery the "other" recordset, to make it aware
that you added a new record. The recordset you use to make the update is
aware of it, but other recordsets don't necessary known it. You are
generally not obliged to requery on "modification" (unless, but not
necessary, you modified a value implied in the WHERE clause while using a
Dynamic recordset). But after adding a record, you have to requery the
OTHERE recordsets, if you want them to be up to date.

Your problem can be something else too.



Hoping it may help,
Vanderghast, Access MVP