vb6 DAO database...linking textbox with recordset?

Question

Dim db As Database
Dim rs As Recordset

Sub opendata()
Set db = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\tyu.mdb")
Set rs = db.OpenRecordset("Select*from Table1", dbOpenDynaset)

End Sub

Sub showfields()
Form1.Text1.Text= rs.Supplier_id
Form1.Text1.Text=rs.Supplier_City
End Sub


Supplier_id and Supplier_City are the fields in Table1
in the click event of a command button ....Call opendata
Call showfield
i am getting an error ...method not found or data member not found in showfield...plz help me rectify this

Answer 1

'at first from project menu,click References,from References check mark on "Microsoft Activex Data Object 2.6 Library or 2.7 library

Create access data base in the same folder where you save the project and name it "db"

In general event

Dim C As New ADODB.Connection

Dim R As New ADODB.Recordset



In form load() event:

With C

.Provider = "MICROSOFT.ACE.OLEDB.12.0"

.ConnectionString = App.Path & "\db.ACCDB"

.Open

End With

R.Open "SELECT * FROM TABLE1", C, adOpenDynamic, adLockOptimistic

' table1 is your record source

DISP

'From tools menu Click on add procedure and write DISP ,click on oK button

under DISP Event()

If R.EOF <> True And R.BOF <> True Then

Text1.Text = R.Fields(0)

Text2.Text = R.Fields(1)

'in place of field name you can write R.fields(0) because fields starts from index value 0

Answer 2

Vb6 Recordset

Answer 3

I know this may sound too simple but make sure that you have proper spacing about the equal signs.



Form1.Text1.Text=rs.Supplier_City looks like one big long string of text without spaces. Add spaces on either side of the ensure that the VB parser can distinguish the parts.



Also if these routines are contained within form1 then you don't have to declare form1 in the commands. Simplify the code to look like:



Text1.Text = rs.Supplier_id

Text2.Text = rs.Supplier_City 'NOTICE I changed the text box name to another one that should be on the form



I notice that you have called Text1 in both lines of code. This is an error as you need to identify unique text boxes for each field to be displayed. Otherwise you are just over writing data in one text box called Text1.

Answer 4

A recordset is one of the two critical ADO objects anyone doing database work is going to use. You use an ADODB.Connection object to gain access to the database and execute SQL commands against it, and you use the ADODB.Recordset to look at the return values of your SQL statement. The use of a recordset in code would look like this: Function GetName(CustID as Integer) as String 'get the name of the customer Dim SQL as string SQL = "SELECT Name from Foo WHERE CustID = " _ & CustID Dim Conn as new ADODB.Connection Dim Rec as new ADODB.Recordset Conn.Open "MyOleDbConnectionString" Set Rec = Conn.Execute(SQL) if Rec.EOF = False then GetName = Rec("Name") End if Rec.Close Conn.Close End Function