What I have is a form that has five combo boxes, Customer, Resource Type, Patch, StartDate, and EndDate. On the form I select a Customer, a Resource Type, a Patch, a StartDate and an EndDate. I then have a SaveRecord Command Button. The On Click function is as follows:


Private Sub cmdSaveRecord_Click()
  Dim strWhere As String
  Dim stDocName As String
  Dim stLinkCriteria As String
  Dim cmdSave As String
  Dim Patch As String
  Dim ResourceType As String
  stDocName = "frmScheduleResource"
  stLinkCriteria = "[cmdSaveRecord]=" & "" & cmdSaveRecord & ""
  Resourcetype = "qryScheduledResourceType.Resource Type"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  If IsNull(Me.Patch) Then
    MsgBox "You must specify a Patch."
    Exit Sub
  Else
    strWhere = "qryScheduledResourceType.Patch = " & Me.Patch
  End If
  If IsNull(Me.ResourceType) Then
    MsgBox "You must specify a Resource Type."
    Exit Sub
  Else
    strWhere = "qryScheduledResourceType.ResourceType = " & Me.ResourceType
  End If
  If IsNull(Me.StartDate) _
    Or (Not IsDate(Me.StartDate)) Then
    MsgBox "You must enter a start date."
    Exit Sub
  Else
    strWhere = strWhere & _
      " AND EndDate >= #" & Me.StartDate & "#"
  End If
  If IsNull(Me.EndDate) _
    Or (Not IsDate(Me.EndDate)) Then
    MsgBox "You must enter an end date."
    Exit Sub
  Else
    If Me.StartDate > Me.EndDate Then
      MsgBox "Start date cannot be greater than end date."
      Exit Sub
    End If
    strWhere = strWhere & _
      " AND EndDate <= #" & Me.EndDate & "#"
  End If
  If the new Patch and Resource Type have a entry that matches dates between an old StartDate and EndDate, Then
   MsgBox “Resources Already scheduled. Pick another resource or dates”
   Exit Sub     DoCmd.OpenForm "frmScheduledResource", WhereCondition:=strWhere

End Sub

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
End Sub

= Code That I don't know how to express.

What I want the function to do is On Click:
1. Open a query that has the five fields listed above.
2. Compare the Patch, Resource Type and StartDate, EndDate fields to see if:

a: if Patch = Null, Then an error message box appears.
b: if Resource Type = Null, Then an error message box appears.
c: if StartDate = Null, Then an error message box appears.
d: if EndDate = Null, Then an error message box appears.
e: if StartDate > EndDate, Then an error message box appears.
f: if Patch and Resource Type > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:

CustomerID Patch ResourceTypeID StartDate EndDate
Customer A (1) NO TEST TYPE 1 01-Mar-05 03-Mar-05
Customer B (2) NO TEST TYPE 2 02-Mar-05 04-Mar-05
Customer C (3) NO TEST TYPE 3 03-Mar-05 05-Mar-05
Customer D (4) NO TEST TYPE 4 04-Mar-05 06-Mar-05


In the table above, if I input a new record as follows:

Customer E (1) NO TEST TYPE 1 2-Mar-05 3-Mar-05

I should get an error message, because Patch: (1) NO TEST and ResourceTypeID, TYPE 1 is already being used by Customer A from
1 to 3 March 2005. Customer E should not be able to use this Patch and Resource Type until 4 March, 2005. So I need code that searches dates between the start and end dates)

f: if Patch and Resource Type = 0, Then perform save record function.

3. Close the query


When I run the code above, I am getting a Compile Error: method or data member not found.

If you or someone knows what I should do, Please help.