ADO Recordset

G

Guest

Hi
Please guide me on ADO : I have created small DB for entering Invoices
with its details, I use unbound form to save the invoices details to table,
“tInvoices†. On form I have button Save , on clicking this my details are
saving to table properly with code as :
Private Sub cmdSave_Click()
Dim conn As ADODB.Connection : Dim rst As ADODB.Recordset
Dim strConn As String
strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & _
"\ProjectTransport.mdb
Set rst = New ADODB.Recordset
With rst
.Open "tInvoices", strConn, adOpenKeyset, adLockOptimistic
.AddNew
.Fields("InvDate").Value = InvDate : .Fields("InvoiceNo").Value =
InvoiceNo
.Fields("SubInvoice").Value = SubInvoice: .Fields("CoName").Value =
CoName
And closing the records set.

I ‘ve InvoiceNo and SubInvoice as Primary Key InvoiceNo Duplicate OK. I
wrote after update of subInvoice this code to bring data on the form
control if it is there:
Private Sub SubInvoice_AfterUpdate()
Dim conn As ADODB.Connection:Dim rst As ADODB.Recordset
Dim strConn As String
strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & _
"\ProjectTransport.mdb"
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM tInvoices WHERE InvoiceNo = " & Me!InvoiceNo &
" and SubInvoice= " & Me!SubInvoice & " ", strConn, adOpenKeyset,
adLockOptimistic
InvDate = .Fields("InvDate").Value: InvoiceNo =
..Fields("InvoiceNo").Value
SubInvoice = .Fields("SubInvoice").Value: CoName =
..Fields("CoName").Value

After changing the company name, I want to save the record with same InvNo
and same subNo with save button , I m getting Run time error telling
“Duplicate value in index.. Please solve my problem and check my code, is it
correct?
Thanking you in advance,
PS I want to know any site which will help me to get idea about ADO.
Wahab
 
A

Alex Dybenko

Hi,
I think you have to check - if this is a new record entered in a form - then
you run .AddNew method (like you have), and if you this is existing record -
then no:
.Open "SELECT * FROM tInvoices WHERE InvoiceNo = " & Me!InvoiceNo &
" and SubInvoice= " & Me!SubInvoice & " ", strConn, adOpenKeyset,
adLockOptimistic

if .eof then
'new rec
.addnew
else
'existing, just update
end if
..Fields("InvDate").Value = InvDate
ect...

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 

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

Top