Recordset property

[eacollie]

I have a form in database1 that compiles and works OK. I copied this form into database2 and the form will not compile.

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblReservation", dbOpenDynaset)

The code that will not compile is:

                .Bookmark = .LastModified

The error is: Method or data member not found.

Is this a library reference issue?

Thanks!

 

[Mihail]

Yes .

 

[pr2-eugin]

Is it possible to show a little bit more code? Normally it depends how the object is referenced, are you using With...End With construct?

Also to avoid ambiguity of References, declare all objects correctly.. Like..

Dim rs As [COLOR=Red][B]DAO.[/B][/COLOR]Recordset

 

[eacollie]

Mihail:

I have the following references for Database1:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object
Microsoft Outlook 12.0 Object Library
Microsoft Calendar Control 2007

For database2 I have the above plus:

Microsoft ActiveX Data Objects 2.1 Library
Microsoft Windows Common Controls 6.0 (SP6)
Microsoft Active X Data Objects Recordset 2.8 Library
Microsoft Visual Basic for Applications Extensibility 5
Microsoft ADO Ext 2.8 for DDL and Security

Which would be the offending reference?

Thanks!

 

[pr2-eugin]

You also need Microsoft DAO X.X Object Library.. Where X.X represents the version number..

 

[Mihail]

Unlike Paul it is quite impossible for me to answer to your question so, send a big THANKS to him.
On the other hand I wish also a little one for this:

Next time is not necessary to ask.
Just open the form in Database1 and take a look to References.
The "offending one" is that that is missing in Database2

Cheers !

 

[eacollie]

Thank you Paul.

When I try to add Microsoft DAO 3.5 or 3.6 Object library it says it conflicts with existing project, module or object library.

Why would it compile in database1 and not in database2?

 

[pr2-eugin]

What version of Access are you using For Database1 and Database2?

If you have already added Microsoft Office X.X Access Database Engine Object, you do not need to add DAO..

 

[eacollie]

Thanks Peter.

I'm using Access 2007 for both databases.

Database2 is an old database that began in Access 2000 and was updated to 2007.

 

[eacollie]

Oh...I'm so sorry...I meant Paul.

 

[Mihail]

Is it possible to show a little bit more code? Normally it depends how the object is referenced, are you using With...End With construct?

Also to avoid ambiguity of References, declare all objects correctly.. Like..

Dim rs As [COLOR=Red][B]DAO.[/B][/COLOR]Recordset

Show the entire code in the subroutine.

 

[eacollie]

Private Sub cmdAddEvent_Click()
    Dim dtCriteria1 As Date
    Dim strsql As String
    Dim lngID As Long
    Dim intAnswer As Integer
    Dim rs As Recordset
    Dim dtStart As Date
    Dim dtEnd As Date
    Dim strOrganizationIndividual As String
    Dim res As Integer
    Dim lngNewID As Long
    Dim nRange As Date
 
    If Forms!frmRoomReservationRequest.Form.NewRecord Or Forms!frmRoomReservationRequest.Form.Dirty Then 'Make sure there is a record to duplicate
        MsgBox "This reservation information is incomplete.  Complete information and try again.", vbOKOnly + vbInformation, "Reservation Recurrence"
    End If
 
 
    Set rs = CurrentDb.OpenRecordset("tblReservation", dbOpenDynaset)
    dtCriteria1 = Me.Calendar0.Value
    strOrganizationIndividual = Forms!frmRoomReservationRequest!ReservingOrganization
    res = Nz(DLookup("[RoomRequestID]", "tblReservation", "[OrganizationIndividual] = '" & strOrganizationIndividual & "' AND [StartDate] = #" & dtCriteria1 & "#"))
 
    If res > 0 Then    'Check that this event has not already been duplicated to this date
        MsgBox "This reservation already exists for the date selected.", vbOKOnly + vbInformation, "Reservation Recurrence"
        Exit Sub
    Else
        'Get date range for duplicated event
        dtStart = Forms!frmRoomReservationRequest!StartDate
        dtEnd = Forms!frmRoomReservationRequest!EndDate
        nRange = DateDiff("d", dtStart, dtEnd)
        intAnswer = MsgBox("You have selected to have this event recur on " & dtCriteria1 & ".", vbYesNo + vbExclamation, "Reservation Recurrence")
 
        Select Case intAnswer
        Case vbYes:
 
            With rs
                .AddNew
                !Campus = Forms!frmRoomReservationRequest!Campus
                !OrganizationIndividual = Forms!frmRoomReservationRequest!ReservingOrganization
                !FunctionType = Forms!frmRoomReservationRequest!TypeOfFunction
                !StartDate = Me.Calendar0.Value
                !EndDate = Me.Calendar0.Value + nRange
                !StartTime = Forms!frmRoomReservationRequest!StartTime
                !EndTime = Forms!frmRoomReservationRequest!EndTime
                !AllDay = Forms!frmRoomReservationRequest!AllDayEvent
 
                .Update
                .Bookmark = .LastModified
                lngNewID = !RoomRequestID
 
            End With
 
            'Add record to tblEventReservation for reserved Rooms
            rs.Bookmark = rs.LastModified
            lngID = rs("RoomRequestID")
 
            If Forms!frmRoomReservationRequest!subfrmRoomReservation.Form.RecordsetClone.RecordCount > 0 Then
                strsql = "INSERT INTO [tblEventReservation]( RoomReservationID, Room, RoomName, StartDateTime, EndDateTime, AllDayEventRoom ) " & _
                        "SELECT " & lngID & " As RoomReservationID, Room, RoomName, StartDateTime, EndDateTime, AllDayEventRoom " & _
                        "FROM [tblEventReservation] WHERE RoomReservationID = " & Forms!frmRoomReservationRequest!RoomRequestID & ";"
                CurrentDb.Execute strsql
            End If
 
            If Forms!frmRoomReservationRequest!subfrmEventGroups.Form.RecordsetClone.RecordCount > 0 Then
                strsql = "INSERT INTO [tblEventGroups]( EventID, GroupID, NumberAttending, MealCount, ReservationFee, DepositRequired, Contact, MV_Contract ) " & _
                        "SELECT " & lngID & "  As EventID, GroupID, NumberAttending, MealCount, ReservationFee, DepositRequired, Contact, MV_Contract " & _
                        "FROM [tblEventGroups] WHERE EventID = " & Forms!frmRoomReservationRequest!RoomRequestID & ";"
                CurrentDb.Execute strsql
            End If
 
            MsgBox "This event has been successfully set to recur from " & Me.Calendar0.Value & " to " & Me.Calendar0.Value + nRange, vbOKOnly + vbInformatoin, "Reservation Recurrence"
 
        Case vbNo:
            Exit Sub
 
        End Select
 
    End If
 
    DoCmd.Close acForm, "frmSelectRecurrence"
 
    rs.Close
    Set rs = Nothing
 
 
End Sub