Invalid object error

[beanlucy]

Hi,

I have a form for adding room bookings to event records. When I click to add the booking I get the following two messages:

Error in GetRecordsetFromSQL Funtion:
Number: -2147217865
Description: Invalid Object Name evttblBooking

Followed by:

Error in VB on evtFrmAddHosp Form: Error number 91
Description: Object variable or With block variable not set

evttblBooking does exist and I can't see any problems with it so can't work out what's going wrong. The code where this is going wrong is below - I would really appreciate any help you can give me.

Thanks,
Lucy


Dim lngThisDate As Long
Dim lngThisEndDate As Long
Dim rstAdd As ADODB.Recordset
Dim i As Integer
Dim strSQL As String

lngThisDate = CLng(Me.txtDateFrom)
lngThisEndDate = CLng(Nz(Me.txtDateTo, 0))
'MsgBox "Booking starts " & Format(CDate(lngThisDate), "dd mm yyyy")
If lngThisEndDate = 0 Then
lngThisEndDate = lngThisDate
End If

Select Case strCallingPage
Case Is = "Hospitality"
If lngThisEndDate < lngThisDate Then
WorkingMsgBox "Cannot save the booking as the End Date is before the Start Date" + vbCrLf + vbCrLf + _
"Re-enter the end Date to be either greater than the start date or blank", vbCritical + vbOKOnly, "Error"
Me.txtDateTo.SetFocus
Working False
Exit Sub
Else
i = 0
Do Until lngThisDate > lngThisEndDate
'160 is Rooms (booking type)
strSQL = "INSERT INTO evttblBooking (sumEvtID, sumRoomID, sumStartTime, sumEndTime, sumDateFrom, " & _
"sumRoomSetup, sumLayoutID, sumNotes, sumBookingStatusID, sumLastUpdated, sumFollowUpID, sumBookingTypeID) VALUES " & _
"(" & Me.txtEvtID & ", " & Me.cmbRoomID & ", '" & CStr(Me.txtStartTime) & "', '" & CStr(Me.txtEndTime) & "', '" & Format(CDate(lngThisDate), "dd mmm yyyy") & "', " & _
IIf(Nz(Me.txtRoomSetup, "") = "", "NULL", "'" & Me.txtRoomSetup & "'") & ", " & IIf(Nz(Me.cmbLayoutID, 0) = 0, "NULL", Me.cmbLayoutID) & ", " & IIf(Nz(Me.txtNotes, "") = "", "NULL", "'" & Me.txtNotes & "'") & ", " & Me.cmbBookingStatusID & ", '" & Format(CDate(Me.txtLastUpdated), "dd mmm yyyy") & "', " & IIf(Nz(Me.cmbFollowUpID, 0) = 0, "NULL", Me.cmbFollowUpID) & ", 160);"
Set rstAdd = GetRecordsetFromSQL(strSQL, adOpenForwardOnly, adLockOptimistic)
'MsgBox strSQL

ThrowawayRecordset rstAdd
lngThisDate = lngThisDate + 1
i = i + 1
Loop
MsgBox "Successfully created " & i & " room bookings for this event", vbInformation, "Success"
End If
Case Is = "Accommodation"
If lngThisEndDate < lngThisDate Then
WorkingMsgBox "Cannot save the booking as the End Date is before the Start Date" + vbCrLf + vbCrLf + _
"Re-enter the end Date to be either greater than the start date or blank", vbCritical + vbOKOnly, "Error"
Me.txtDateTo.SetFocus
Working False
Exit Sub
Else
i = 0
Do Until lngThisDate > lngThisEndDate
'162 is bedrooms (booking type)
strSQL = "INSERT INTO evttblBooking (sumEvtID, sumDateFrom, " & _
"sumBedroomTypeID, sumAccommodationHallID, sumNumBedrooms, sumNotes, sumBookingStatusID, sumLastUpdated, sumFollowUpID, sumBookingTypeID) " & _
"VALUES " & _
"(" & Me.txtEvtID & ", '" & Format(CDate(lngThisDate), "dd mmm yyyy") & "', " & _
Me.cmbBedroomTypeID & ", " & Me.cmbAccommodationHallID & ", " & _
Me.txtNumBedrooms & ", " & IIf(Nz(Me.txtNotes, "") = "", "NULL", "'" & _
Me.txtNotes & "'") & ", " & Me.cmbBookingStatusID & ", '" & _
Format(Me.txtLastUpdated, "dd mmm yyyy") & "', " & _
IIf(Nz(Me.cmbFollowUpID, 0) = 0, "NULL", Me.cmbFollowUpID) & _
", 162);"
'MsgBox strSQL

Set rstAdd = GetRecordsetFromSQL(strSQL, adOpenForwardOnly, adLockOptimistic)
ThrowawayRecordset rstAdd
lngThisDate = lngThisDate + 1
i = i + 1
Loop
MsgBox "Successfully created " & i & " accommodation bookings for this event", vbInformation, "Success"
End If

End Select

 

[gemma-the-husky]

if your dates are stored as datetime fields (which they should be) then your insert sql statement will fail, as dates need to be wrapped in # characters (in the same way that strings need to be wrapped in " characters.

this code looks very difficult to follow - so

try to break it down into checkable subroutines, and try checking individual code segments to make sure they work

also you could try setting a breakpoints to see exactly which statements are failing