Please Help,

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."
    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
  DoCmd.OpenForm "frmScheduledReosurce", WhereCondition:=strWhere

End Sub

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.

Thanks
James Jackson
[email protected]
[email protected]
813-828-4528

James,
Where is the data for each combo box on your form coming from?



dartcoach

That is a lot of issues:

I think you might mean you have four text boxes, or two combo boxes and two text boxes, or two combo boxes and two date pickers. In any event, you shouldn't have combo boxes for dates, unless they are specific dates that are predetermined and must be used.

The problem with passing combo box values is that you are passing the PK, and not the meaningful data. If you have a Customer whose name is Smith, John, and they have a PK of 1, then when you select Smith, John, you are passing 1. So you have to account for that in your SQL statement.

Also, why are you checking for Null values on your form AFTER you open the frmScheduleResource? You should check for Null values before you open that form, it seems.

If this is working otherwise and you are getting this error message, perhaps you need to pass your PKs like this:

    stLinkCriteria = "[cmdSave]=" & cmdSave

This allows you to pass integers, which don't need the single quotes.

I also noted in pasting your code that you write it like this:

    stLinkCriteria = "[cmdSave]=" & "" & cmdSave & ""

It should be written like this:

    stLinkCriteria = "[cmdSave]= " & "'" & cmdSave & "'"

Note the single quotes withtin the double double quotes.

If you get past your errors, we can discuss fees for further consultation :D

(Okay, that was a joke, but there are a lot of issues here that will require many more posts to resolve.)

Hope this gets you started.

mmcdonal