ADODB Recordset

[Guest]

I have 2 questions regarding recordset. Please help. Thanks in advance.

1. I would like to run a query and then store the results of the query in
the textboxs in a form. here is the code that I write. A error message
occured. There is more than one record in the result of the query.

Dim con As Object
Dim rs As Object

tempstr = “SELECT [table1].[app_no] AS [MI No], [table2].[status]†& _
“FROM table1 INNER JOIN table2 ON [table1].[app_no]=[table2].[app_no]†& -
“WHERE (((Mid([table1].[id],2,7)) Like (Left([forms]![control]![o1],7)))) “
& _
“OR (((Left([table1].[id],7)) Like (Left([forms]![control]![o1],7))));â€

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
rs.Open tempstr, con

record_count = recordcount (rs)

for i = 1 to record_count
ref_temp(i)=rs![mi no]
Me.txtbox.record(i) = ref_temp(i)
next i

2.There is more than 1 record in a form. How could I refer to differnt
records in vb? this is what I have tried.

for i = 1 to 100
temp(i) = me.txtbox1.record(1)
msgbox temp(i)
next i

 

[Guest]

What do you want to accomplish?

Basically a form is linked to 1 record only at the time (Form View)

However when you are in datasheet view you will see a recordset containing 1
or more(or all) records according your Selection (Query).

In either views only 1 Record can be the current.

- Raoul

 

[Guest]

The form is in design view. I want to update more than 1 record according to
the results of a query (with more than 1 record). For example,

Form in design view with Query Result
Txtbox1 Txtbox2 Column1 Column2
Record1 ABC DEF ABC DEF
Record2 XYZ UVW XYZ UVW

I want to update the data from query (record1 in query column1 and column2
to record1 in form txtbox1 and txtbox 2, respectively; the same for record2)

What do you want to accomplish?

Basically a form is linked to 1 record only at the time (Form View)

However when you are in datasheet view you will see a recordset containing 1
or more(or all) records according your Selection (Query).

In either views only 1 Record can be the current.

- Raoul

I have 2 questions regarding recordset. Please help. Thanks in advance.

1. I would like to run a query and then store the results of the query in
the textboxs in a form. here is the code that I write. A error message
occured. There is more than one record in the result of the query.

Dim con As Object
Dim rs As Object

tempstr = “SELECT [table1].[app_no] AS [MI No], [table2].[status]†& _
“FROM table1 INNER JOIN table2 ON [table1].[app_no]=[table2].[app_no]†& -
“WHERE (((Mid([table1].[id],2,7)) Like (Left([forms]![control]![o1],7)))) “
& _
“OR (((Left([table1].[id],7)) Like (Left([forms]![control]![o1],7))));â€

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
rs.Open tempstr, con

record_count = recordcount (rs)

for i = 1 to record_count
ref_temp(i)=rs![mi no]
Me.txtbox.record(i) = ref_temp(i)
next i

2.There is more than 1 record in a form. How could I refer to differnt
records in vb? this is what I have tried.

for i = 1 to 100
temp(i) = me.txtbox1.record(1)
msgbox temp(i)
next i

 

[Guest]

Well Basically you have to assign this query to the recordsource of your form.

Then if the query is right, you will be able to see the available fields
which can be bound to individual controls or even you can drag and drop these
fields onto the form.


for i = 1 to record_count

ref_temp(i)=rs![mi no]
Me.txtbox.record(i) = ref_temp(i)
next i

2.There is more than 1 record in a form. How could I refer to differnt
records in vb? this is what I have tried.

for i = 1 to 100
temp(i) = me.txtbox1.record(1)
msgbox temp(i)
next i

This syntax is not valid and so not available.

The current form is refered as me in vba.
THere you can access the recordset through

me.recordset (DAO recordset)
With this recordset you can basically perform all the basic DAO recordset
tasks.
So if you do me.recordset.movenext then the form will go to the next record
and all the control which are bound will change according to what is in the
current record.

Changing values in the bound controls can just be done by assigning a value
to them
e.g. me.txtTest="hallo"
if you move to another record or close the form then the changes are saved.

- Raoul

The form is in design view. I want to update more than 1 record according to
the results of a query (with more than 1 record). For example,

Form in design view with Query Result
Txtbox1 Txtbox2 Column1 Column2
Record1 ABC DEF ABC DEF
Record2 XYZ UVW XYZ UVW

I want to update the data from query (record1 in query column1 and column2
to record1 in form txtbox1 and txtbox 2, respectively; the same for record2)

What do you want to accomplish?

Basically a form is linked to 1 record only at the time (Form View)

However when you are in datasheet view you will see a recordset containing 1
or more(or all) records according your Selection (Query).

In either views only 1 Record can be the current.

- Raoul

I have 2 questions regarding recordset. Please help. Thanks in advance.

1. I would like to run a query and then store the results of the query in
the textboxs in a form. here is the code that I write. A error message
occured. There is more than one record in the result of the query.

Dim con As Object
Dim rs As Object

tempstr = “SELECT [table1].[app_no] AS [MI No], [table2].[status]†& _
“FROM table1 INNER JOIN table2 ON [table1].[app_no]=[table2].[app_no]†& -
“WHERE (((Mid([table1].[id],2,7)) Like (Left([forms]![control]![o1],7)))) “
& _
“OR (((Left([table1].[id],7)) Like (Left([forms]![control]![o1],7))));â€

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
rs.Open tempstr, con

record_count = recordcount (rs)

for i = 1 to record_count
ref_temp(i)=rs![mi no]
Me.txtbox.record(i) = ref_temp(i)
next i

2.There is more than 1 record in a form. How could I refer to differnt
records in vb? this is what I have tried.

for i = 1 to 100
temp(i) = me.txtbox1.record(1)
msgbox temp(i)
next i