you cancelled prev. operation error

[kaosyeti]

i have a form that is used to enter 4 values in a tblmonthlyunits. they are:
salesperson (text), monthandyear (date in the format mmm-yyyy), department
(text) and units (gen. number). i have a search button on this form that
opens a 2nd form, formsearchmontlyunits, where a user would enter 1 or more
values to do a search with, then those values are formed into an sql that is
used to filter the first form. my problem is that i didn't write this code (i
do understand it, but i seemingly can't debug it). now that i've change a
few things in the forms, the code doesn't work.

it gets hung up at the dlookup function where it is supposed to verify that
there are records present that meet the criteria. my thought is the problem
is the date format. i did a msgbox for the where on the sql and got (date) =
10/01/2005 even though my entry on the search form was oct-2005 (this is how
the records are stored in the table). here's the code:

Private Sub cmdEdit_Click()
Dim strWhere As String
Dim strF As String

strWhere = BuildWhere

' the form is already open...so we set the filter..not the "where"

' however, BEFORE we do this, lets make sure some reocrds match!!

If DCount("*", "tblsoldunits", strWhere) = 0 Then

MsgBox "no records found", vbExclamation, "no match"

Else

' got some rocords...lets filter

strF = "formmonthlyunits"
Forms(strF).Filter = strWhere
Forms(strF).FilterOn = True

DoCmd.Close acForm, Me.Name

End If
End Sub

Function CheckFields()

' check for required fields
' returns false if required fields missing
If IsNull(Me.tboxmonthandyear) = True Then

MsgBox "Month and Year are required", vbExclamation, "Date required"
Me.tboxmonthandyear.SetFocus
Exit Function

End If

End Function

Function BuildWhere() As String
Dim strTemp As String
Dim strWhere As String
Dim strField As String
Dim strControl As String

If (IsNull(Me.tboxmonthandyear) = False) Then
strWhere = "([Date] = " & "#" & (Me.tboxmonthandyear) & "#)"
End If

Call AddWhere("cboxSalesperson", "Salesperson", strWhere)

Call AddWhere("cboxdepartment", "Department", strWhere)

BuildWhere = strWhere
End Function

Sub AddWhere(strControl As String, strField As String, strWhere As String)
If IsNull(Me(strControl)) = False Then

If strWhere <> "" Then
strWhere = strWhere & " and "
End If

strWhere = strWhere & "(" & strField & " = " & """ & "(Me(strControl).
Value) & ")" & """

End If

End Sub

thanks for looking.

--
Message posted via http://www.accessmonster.com

[Wayne Morgan]

The textbox for the date is called txtMonthAndYear. Is this a full date or
just the month and year? What value is in the field? What is the data type
of the field?

Also, have you placed a Debug.Print line in the code to have the strWhere
clause print to the immediate window to make sure that your where strings
looks the way you want it to? Before the line

BuildWhere = strWhere

add the line

Debug.Print strWhere

--
Wayne Morgan
MS Access MVP
"ka******@comcast.net via AccessMonster.com" <u15580@uwe> wrote in message
news:580c069c08285@uwe...

i have a form that is used to enter 4 values in a tblmonthlyunits. they
are:
salesperson (text), monthandyear (date in the format mmm-yyyy), department
(text) and units (gen. number). i have a search button on this form that
opens a 2nd form, formsearchmontlyunits, where a user would enter 1 or
more
values to do a search with, then those values are formed into an sql that
is
used to filter the first form. my problem is that i didn't write this code
(i
do understand it, but i seemingly can't debug it). now that i've change a
few things in the forms, the code doesn't work.

it gets hung up at the dlookup function where it is supposed to verify
that
there are records present that meet the criteria. my thought is the
problem
is the date format. i did a msgbox for the where on the sql and got
(date) =
10/01/2005 even though my entry on the search form was oct-2005 (this is
how
the records are stored in the table). here's the code:

Private Sub cmdEdit_Click()
Dim strWhere As String
Dim strF As String

strWhere = BuildWhere

' the form is already open...so we set the filter..not the "where"

' however, BEFORE we do this, lets make sure some reocrds match!!

If DCount("*", "tblsoldunits", strWhere) = 0 Then

MsgBox "no records found", vbExclamation, "no match"

Else

' got some rocords...lets filter

strF = "formmonthlyunits"
Forms(strF).Filter = strWhere
Forms(strF).FilterOn = True

DoCmd.Close acForm, Me.Name

End If
End Sub

Function CheckFields()

' check for required fields
' returns false if required fields missing
If IsNull(Me.tboxmonthandyear) = True Then

MsgBox "Month and Year are required", vbExclamation, "Date required"
Me.tboxmonthandyear.SetFocus
Exit Function

End If

End Function

Function BuildWhere() As String
Dim strTemp As String
Dim strWhere As String
Dim strField As String
Dim strControl As String

If (IsNull(Me.tboxmonthandyear) = False) Then
strWhere = "([Date] = " & "#" & (Me.tboxmonthandyear) & "#)"
End If

Call AddWhere("cboxSalesperson", "Salesperson", strWhere)

Call AddWhere("cboxdepartment", "Department", strWhere)

BuildWhere = strWhere
End Function

Sub AddWhere(strControl As String, strField As String, strWhere As String)
If IsNull(Me(strControl)) = False Then

If strWhere <> "" Then
strWhere = strWhere & " and "
End If

strWhere = strWhere & "(" & strField & " = " & """ &
"(Me(strControl).
Value) & ")" & """

End If

End Sub

thanks for looking.

--
Message posted via http://www.accessmonster.com

[kaosyeti]

tboxmonthandyear is just the month and the year. format is mmm-yyyy. if i
type in 10/2005 in this field it will automatically change it to oct-2005.
this is as desired. the data type of the field is date/time.

the debug.print line came up with this:

([Date] = #10/1/2005#)

now, when i build a query from tblsoldunits in design view, using 10-01-05 as
a criteria, it WILL filter the rest of the records out. the sql looks like
this:

SELECT tblsoldunits.salesperson, tblsoldunits.monthandyear, tblsoldunits.
department, tblsoldunits.units
FROM tblsoldunits
WHERE (((tblsoldunits.monthandyear)=#10/1/2005#));

(btw... i want to be clear and say that the actual name of this table is
tblsoldunits. if i refer to it as tblmonthlyunits it's because it has
changed over the last few weeks as a result of being re-done so many times.
this is just my foggy brain trying to cope with the fact that most of my
tables/forms/queries have been renamed recently and i cling to the old ones.)

Wayne Morgan wrote:

The textbox for the date is called txtMonthAndYear. Is this a full date or
just the month and year? What value is in the field? What is the data type
of the field?

Also, have you placed a Debug.Print line in the code to have the strWhere
clause print to the immediate window to make sure that your where strings
looks the way you want it to? Before the line

BuildWhere = strWhere

add the line

Debug.Print strWhere

i have a form that is used to enter 4 values in a tblmonthlyunits. they
are:

[quoted text clipped - 97 lines]

thanks for looking.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200511/1

[kaosyeti]

sigh.. nevermind. i found the problem. i had this line:

strWhere = "([Date] = " & "#" & (Me.tboxmonthandyear) & "#)"

when i should have had this line:

strWhere = "([monthandyear] = " & "#" & (Me.tboxmonthandyear) & "#)"

i didn't realize that [date] referred to a field NAMED date, so much as it
referred to the date field, because it was formatted that way. it didn't
jump out at me that it was wrong because it didn't look out of place. ah,
well. thanks for your time.
ka******@comcast.net wrote:

tboxmonthandyear is just the month and the year. format is mmm-yyyy. if i
type in 10/2005 in this field it will automatically change it to oct-2005.
this is as desired. the data type of the field is date/time.

the debug.print line came up with this:

([Date] = #10/1/2005#)

now, when i build a query from tblsoldunits in design view, using 10-01-05 as
a criteria, it WILL filter the rest of the records out. the sql looks like
this:

SELECT tblsoldunits.salesperson, tblsoldunits.monthandyear, tblsoldunits.
department, tblsoldunits.units
FROM tblsoldunits
WHERE (((tblsoldunits.monthandyear)=#10/1/2005#));

(btw... i want to be clear and say that the actual name of this table is
tblsoldunits. if i refer to it as tblmonthlyunits it's because it has
changed over the last few weeks as a result of being re-done so many times.
this is just my foggy brain trying to cope with the fact that most of my
tables/forms/queries have been renamed recently and i cling to the old ones.)

The textbox for the date is called txtMonthAndYear. Is this a full date or
just the month and year? What value is in the field? What is the data type

[quoted text clipped - 15 lines]

thanks for looking.

--
Message posted via http://www.accessmonster.com