What I have is a form that has four combo boxes, Customer, Resourcetype, StartDate, and EndDate. On the form I select a Customer, a Resource Type, a StartDate and an EndDate. I then have a SaveRecord Command Button. The On Click function is as follows:
Private Sub cmdSave_Click()
Dim strWhere As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim cmdSave As String
Dim Resourcetype As String
stDocName = "frmScheduleReosurce"
stLinkCriteria = "[cmdSave]=" & "" & cmdSave & ""
Resourcetype = "qryScheduledResourcetype.Resourcetype"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If IsNull(Me.Resourcetype) Then
MsgBox "You must specify a Resource Type."
strWhere = "qryScheduledResourcetype.Resourcetype = " & Me.Resourcetype
If IsNull(Me.StartDate) _
Or (Not IsDate(Me.StartDate)) Then
MsgBox "You must enter a start date."
strWhere = strWhere & _
" AND EndDate >= #" & Me.StartDate & "#"
If IsNull(Me.EndDate) _
Or (Not IsDate(Me.EndDate)) Then
MsgBox "You must enter an end date."
If Me.StartDate > Me.EndDate Then
MsgBox "Start date cannot be greater than end date."
strWhere = strWhere & _
" AND EndDate <= #" & Me.EndDate & "#"
DoCmd.OpenForm "frmScheduledReosurce", WhereCondition:=strWhere
What I want the function to do is On Click:
1. Open a query that has the four fields listed above.
2. Compare the Resourcetype and StartDate, EndDate fields to see if:
a: if Resourcetype = Null, Then an error message box appears.
b: if StartDate = Null, Then an error message box appears.
c: if EndDate = Null, Then an error message box appears.
d: if StartDate > EndDate, Then an error message box appears.
e: if Resourcetype > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:
Customer Resourcetype StartDate EndDate
Jim Item 1 1-Mar-05 4-Mar-05
In the table above, if I input a new record as follows:
Mike Item 1 2-Mar-05 3-Mar-05
I should get an error message, because Item 1 is already being used by Jim from
1 to 4 March 2005. Mike should not be able to use this Resourcetype until 5 March, 2005. So I need code that searches dates between the start and end dates)
f: if Resourcetype = 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.