Need help with recordset code...

[dmbfan715]

Hi,

I'm having trouble with the following code. The error message is, "User defined type is not defined." I don't know what's wrong with my code...Any help is appreciated!


Private Sub Command0_Click()

Dim Conn2 As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Dim SQLCode As String
Dim RptStartDate As Date

Set Conn2 = CurrentProject.Connection
Set Rs1 = New ADODB.Recordset



RptStartDate = InputBox("Enter report start date&quot

SQLCode = "SELECT EntitlementID, AuctionID, ZoneID, StripID, ProductID, OriginalBuyerID, CurrentHolderID, OriginalPrice, OriginalPriceUnit, BeginDate, EndDate FROM tblEntitlement WHERE BeginDate = Format(DatePart("m", RptStartDate)-1 & "/" & DatePart("d", RptStartDate) & "/" & DatePart("yyyy", RptStartDate), "Short Date""


Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
' Rs1 is going to have the results of this query or
Rs1 is the recordset.

' this little trick forces the recordset pointer to
the end
' which gets a correct count of the number of records.

Rs1.MoveLast
Rs1.MoveFirst

' so to loop through the recordset one at a time we use
a for next loop

For A = 1 To Rs1.RecordCount
'Rs1!yourfield = somevalue
'Rs1!yourfield2 = someOtherValue
''etc

Rs1.Update
Rs1.MoveNext
Next

' close it this way
Set Rs1 = Nothing
Set Conn2 = Nothing


End Sub

 

[Custom24]

This error is normally caused by not setting a reference to the type library in your project.

If you've not already done this, then it's (Menu) Project, References, Microsoft ActiveX Data Objects (whichever version you are using)

Hope this helps

Mark

 

[dmbfan715]

Thanks Mark. I tried that and it worked. BUT, now I'm getting another error message that says, "Variable not defined" and it highlights "CurrentProject". How do I define this? I'm new to this...

 

[Custom24]

OK, so it's this line?

Set Conn2 = CurrentProject.Connection

I don't know where you got the code but it looks like this is referencing an ADO Connection object.

I'm not sure where this was intended to be defined, but the way it might have been done

(In a module or maybe a class called CurrentProject)

Dim Connection as ADODB.Connection

(In the class Initialize or your form load event or maybe Sub Main)

Set Connection = new ADODB.Connection

Connection.ConnectionString = "The connection string - this is specific to your database and the provider used to talk to it and other stuff like what user is being logged on"

Connection.Open

You need to basically get the rest of the code

Mail me if you need any more help

Mark