OpenForm problem

  • Thread starter Thread starter Pete Provencher
  • Start date Start date
P

Pete Provencher

Using Access 2000:

I have a form that has two unbound combo boxes that allow me to select a
parameter for a query by form.
I use a command button for each to start the process.

This first one works:

Option Compare Database

Private Sub cbOrderNumber_Click()

Dim strWhereON As String

stFormNameON = "frmDisplaySearchOrderNumber"

If Not IsNull(Me.CmbOrderNumber) Then
strWhereON = "[OrderNumber] =""" & CmbOrderNumber & """"

DoCmd.OpenForm stFormNameON, WhereCondition:=strWhereON
DoCmd.Close acForm, "frmSearch"

Else
MsgBox "You must select an Order Number."
End If

End Sub


I copied this code to (which I learned how to do from a answer given by Jim
Viescas in an earlier question) the next command button and made the
applicable changes:

Option Compare Database


Private Sub cbDateTaken_Click()

Dim strWhereD As String

stFormNameD = "frmDisplaySearchDateTaken"

If Not IsNull(Me.CmbDateTaken) Then
strWhereD = "[DateTaken] =""" & CmbDateTaken & """"

DoCmd.OpenForm stFormNameD, WhereCondition:=strWhereD
DoCmd.Close acForm, "frmSearch"

Else
MsgBox "You must select a Date."
End If

End Sub


When I click on the command button for this one I can an error on the line

DoCmd.OpenForm stFormNameD, WhereCondition:=strWhereD

Run-time error 2501
OpenForm action was canceled.

I looked at the value for stFormNameD and it was correct
I looked at the value for strWhereD and it set the correct parameter

I just can't figure this out and hope someone can see the error of my ways.

Pete Provencher
 
Pete Provencher said:
Using Access 2000:

I have a form that has two unbound combo boxes that allow me to
select a parameter for a query by form.
I use a command button for each to start the process.

This first one works:

Option Compare Database

Private Sub cbOrderNumber_Click()

Dim strWhereON As String

stFormNameON = "frmDisplaySearchOrderNumber"

If Not IsNull(Me.CmbOrderNumber) Then
strWhereON = "[OrderNumber] =""" & CmbOrderNumber & """"

DoCmd.OpenForm stFormNameON, WhereCondition:=strWhereON
DoCmd.Close acForm, "frmSearch"

Else
MsgBox "You must select an Order Number."
End If

End Sub


I copied this code to (which I learned how to do from a answer given
by Jim Viescas in an earlier question) the next command button and
made the applicable changes:

Option Compare Database


Private Sub cbDateTaken_Click()

Dim strWhereD As String

stFormNameD = "frmDisplaySearchDateTaken"

If Not IsNull(Me.CmbDateTaken) Then
strWhereD = "[DateTaken] =""" & CmbDateTaken & """"

DoCmd.OpenForm stFormNameD, WhereCondition:=strWhereD
DoCmd.Close acForm, "frmSearch"

Else
MsgBox "You must select a Date."
End If

End Sub


When I click on the command button for this one I can an error on the
line

DoCmd.OpenForm stFormNameD, WhereCondition:=strWhereD

Run-time error 2501
OpenForm action was canceled.

I looked at the value for stFormNameD and it was correct
I looked at the value for strWhereD and it set the correct parameter

I just can't figure this out and hope someone can see the error of my
ways.
Click to expand...

From the looks of it, the field you're applying the criterion to is a
date field, so possibly it doesn't like that you've specified a string
value as the criterion. Try this variation:

strWhereD = "[DateTaken] =#" & CmbDateTaken & "#"

If CmbDateTaken is not already formatted in m/d/y order, you should
avoid any ambiguity by forcing it that way:

strWhereD = "[DateTaken] =#" & _
Format(CmbDateTaken, "mm/dd/yyyy") & "#"
 
Worked like a charm. Can't believe that I didn't see that. Guess the longer
you work at it less you see.

Thanks again.

Pete Provencher
Dirk Goldgar said:
Pete Provencher said:
Using Access 2000:

I have a form that has two unbound combo boxes that allow me to
select a parameter for a query by form.
I use a command button for each to start the process.

This first one works:

Option Compare Database

Private Sub cbOrderNumber_Click()

Dim strWhereON As String

stFormNameON = "frmDisplaySearchOrderNumber"

If Not IsNull(Me.CmbOrderNumber) Then
strWhereON = "[OrderNumber] =""" & CmbOrderNumber & """"

DoCmd.OpenForm stFormNameON, WhereCondition:=strWhereON
DoCmd.Close acForm, "frmSearch"

Else
MsgBox "You must select an Order Number."
End If

End Sub


I copied this code to (which I learned how to do from a answer given
by Jim Viescas in an earlier question) the next command button and
made the applicable changes:

Option Compare Database


Private Sub cbDateTaken_Click()

Dim strWhereD As String

stFormNameD = "frmDisplaySearchDateTaken"

If Not IsNull(Me.CmbDateTaken) Then
strWhereD = "[DateTaken] =""" & CmbDateTaken & """"

DoCmd.OpenForm stFormNameD, WhereCondition:=strWhereD
DoCmd.Close acForm, "frmSearch"

Else
MsgBox "You must select a Date."
End If

End Sub


When I click on the command button for this one I can an error on the
line

DoCmd.OpenForm stFormNameD, WhereCondition:=strWhereD

Run-time error 2501
OpenForm action was canceled.

I looked at the value for stFormNameD and it was correct
I looked at the value for strWhereD and it set the correct parameter

I just can't figure this out and hope someone can see the error of my
ways.
Click to expand...

From the looks of it, the field you're applying the criterion to is a
date field, so possibly it doesn't like that you've specified a string
value as the criterion. Try this variation:

strWhereD = "[DateTaken] =#" & CmbDateTaken & "#"

If CmbDateTaken is not already formatted in m/d/y order, you should
avoid any ambiguity by forcing it that way:

strWhereD = "[DateTaken] =#" & _
Format(CmbDateTaken, "mm/dd/yyyy") & "#"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top