Adodb Problem

I keep on getting the following error-

sub or function not defined.

Here is my code:

Code:

---

Public ss, sc

Private Sub Form_Load()
s = "provider=microsoft.jet.oledb.4.0;data source=.\Invoice2.mdb;"
Set c = New ADODB.Connection
c.Open s
Set r = New ADODB.Recordset
'Set rs = New ADODB.Recordset
r.Open "Select * from Invoice", c, adOpenDynamic, adLockOptimistic
Call display
End Sub

Private Sub display()
  txtInvoiceNo.Text = str(r(0)) & " "
  txtDate.Text = r(1) & " "
  txtcustomer = r(2) & " "
  dbNamesl.Text = str(r(3)) & " "
  txtnettMainsl.Text = str(r(4)) & " "
  txtVatMainsl.Text = str(r(5)) & " "
  txttotalMainsl.Text = str(r(6)) & " "
End Sub

Private Sub assign()
  r(0) = txtInvoiceNo.Text
  r(1) = txtDate.Text
  r(2) = txtcustomer.Text
  r(3) = dbNamesl.Text
  r(4) = txtnettMainsl.Text
  r(5) = txtVatMainsl.Text
  r(6) = txttotalMainsl.Text
End Sub

---

I get the error at the "r" on the first line of display:

txtInvoiceNo.Text = str(r(0)) & " " the r is highlighted and the error comes up.

Does anybody know what the problem is and how i can add information from another table within this code?

just a small example would do.

Where are r and c declared? they should be at the form level (in "General" - "Declarations" of the form).

If your declaration is ok there are two other possibilities, the first being that txtInvoiceNo.Text is not right (eg: txtInvoiceNo is not a text box). The second is that the (built in) Str function has an issue.

Str is part of the standard VBA library, which tends to get errors when some other reference is not working. If you go to "Project" -"References" you will probably see a ticked item which says "MISSING: ....", which you will need to replace with a valid reference to that item (if it is not listed again without 'missing', let us know what it is and we can probably help).

r is defined in a private sub. You are trying to access r from another private sub. Private Sub Form_Load() is the only thing that has any idea what r is. Either define r as public(not recommended) or create a Public Function and pass your RecordSet back to the calling procedure

Code:

---

Public Sub GetData(ByRef rstRemote as ADODB.RecordSet)

s = "provider=microsoft.jet.oledb.4.0;data source=.\Invoice2.mdb;"
Set c = New ADODB.Connection
c.Open s
Set r = New ADODB.Recordset
'Set rs = New ADODB.Recordset
r.Open "Select * from Invoice", c, adOpenDynamic, adLockOptimistic

Set rstRemote= r.Clone

end sub

---

Make sure you close your connections.

What type of public function do you mean? I am new to adodb. I've never used it before but i have no choice as this is the only way to link vb5 with access 2000.

So could you explain what you mean by a public function?

Also how is it possible to add another table to the recordset.

Should i just use select * from invoice,description? where description is the second table.

But my main priority is to load the database into vb5.

Use either Public Sub or just plain Sub for the calls.

Use the following code along with the GetData Sub

Code:

---

Private Sub display()
  dim r as ADODB.RecordSet
  GetData(r)
  txtInvoiceNo.Text = str(r(0)) & " "
  txtDate.Text = r(1) & " "
  txtcustomer = r(2) & " "
  dbNamesl.Text = str(r(3)) & " "
  txtnettMainsl.Text = str(r(4)) & " "
  txtVatMainsl.Text = str(r(5)) & " "
  txttotalMainsl.Text = str(r(6)) & " "
End Sub

---

It stops at this line

VB Code:

Public Sub GetData(ByRef rstRemote As ADODB.Recordset)

It comes up with user-defined type not found.

I call that procedure in the form load by saying "Call GetData"

Is this a problem with the getdata code or the way i'm calling it?

Is there a reference to ADODB in your project?

I think not, i went to project references, but it's not there. so i found Microsoft access and added that, still same error.

How can i reference it?

Alright give this a try, I did not realize that the Clone Method did not work with Access, I normally use SQL Server. As far as making sure there is a reference to ADODB, go to the menu and select

Project
References
Microsort ActiveX DataObjects 2.7 Library

This must be selected for this to work.

Code:

---


Private Sub Command1_Click()
Dim r As New ADODB.Recordset
Dim i As Integer

  Set r = GetData

  txtInvoiceNo.Text = Str(r(0)) & " "
  txtDate.Text = r(1) & " "
  txtcustomer = r(2) & " "
  dbNamesl.Text = Str(r(3)) & " "
    txtnettMainsl.Text = Str(r(4)) & " "
  txtVatMainsl.Text = Str(r(5)) & " "
  txttotalMainsl.Text = Str(r(6)) & " "
End Sub



Public Function GetData() As ADODB.Recordset
Dim strSQL As String
Dim r As ADODB.Recordset
Dim c As ADODB.Connection

Set c = New ADODB.Connection
c.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\Invoice2.mdb;"
c.Open

strSQL = "Select * from Invoice;"
Set r = New ADODB.Recordset
r.Open strSQL, c, adOpenDynamic, adLockOptimistic


Set GetData = r

End Function

---

Thanks alot for your help. It's comming up with an error but thats due to the database set up, i know how to resolve that.

if i wanted to get data from more than one table within the database, from a table thats linked to invoice, would i go about saying

select * from Invoice,Description (description being other table)
i realise i dont have to use star, i understand sql.

But would i add it to the current dataset or create a new one?

would i say

VB Code:

txtdescriptionno.text = str(r(7)) & " "

See i need to keep the relationship stable, desciption is linked to invoice via a invoice no common to both tables.

Is that the best way to do it or am i way off?

I would use different recordsets for each call and always close and clean up a connection as soon as you no longer need it. As far as your last question, if I am understanding it correctly I would use a JOIN statement in your query to pull back the info you need.

Select * from Invoice INNER JOIN Description On Invoice.ID = Description.ID....

Well you get the idea.

Makes sense when put like that.

Thanks alot for your help, very much appreciated. Was struggling to work it out for 4 days.