DB Connection 1

[LWolf]

Hi all,
I am new to VBA and am having trouble creating a connection to the db I am working in. I found some code but is giving me a "User-Defined type not defined" on the first line. My guess is I will get it also on the second line. What am I doing wrong?

I need to loop through a table and set 4 fields equal to 4 other fields in the same table.


Private Sub Form_Load()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection
Set rstTry = New ADODB.Recordset
rstTry.Open "Assessments"

While rstTry.EOF = False
rstTry![BR Land Lot] = rstTry![SAEQ Land Lot]
rstTry.MoveNext
Wend
End Sub

Thanks in advance.

KT

 

[PHV]

You have to reference the Microsoft ActiveX Data Objects 2.x Library.
when in VBE, menu Tools -> References ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LWolf]

That seemed to work to get the connection working.

I am now getting a new error:
"The connection cannot be used to perform this operation. It is either closed or invalid in this context."

This error is happening on the rstTry.Open "Assessments

 

[PHV]

you try to open a recordset without connection ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LWolf]

I commented out the connection string and get a couple different errors: object required, the previous error...it all depends on the commented out strings.

I am an asp programmer, not used to this!!!

How would I setup the rst string?

 

[PHV]

Have a look here:

http://msdn2.microsoft.com/en-us/library/ms675544(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LWolf]

awesome....Im getting closer! LOL.

I am getting an update error on the
"rst![BR Land Lot] = rst![SAEQ Land Lot]" line:

"Update or CancelUpdate without AddNew or Edit"
What is that?

Here is what I got now...

Private Sub Form_Load()
Dim db As Object
Dim rst As Object

Set db = CurrentDb
Set rst = db.OpenRecordset("Assessments")

While rst.EOF = False
rst![BR Land Lot] = rst![SAEQ Land Lot]
rst.MoveNext
Wend
End Sub

 

[PHV]

I think you're playing with DAO now !
...
While rst.EOF = False
[!]rst.Edit[/!]
rst![BR Land Lot] = rst![SAEQ Land Lot]
[!]rst.Update[/!]
rst.MoveNext
Wend
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LWolf]

That all worked...

Thank you so much for the help.

KT