'Like' in string

G

Guest

Can someone tell me why this does not work....

------------------------------------------------------
Private Sub FindUnit_Click( )
On Error GoTo Err_FindUnit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FindUnit_Click:
Exit Sub

Err_FindUnit_Click:
Call MsgBox ("You must enter a station number or select one from the drop_
down list." & vbCrLf & "", vbOkOnly + vbExclamation + vbSystemModal + _
vbDefaultButton1, "Error....Please try again")

Resume Exit_FindUnit_Click

End Sub

------------------------------------------------------------------------------------------

I have a list box that the user can either select a station number ie: 202
from a value list, or enter the station number directly.

Everytime I select or enter a station number, I get the error message box.

Thanks in advance....
 
R

Rick Brandt

Paul B. said:
Can someone tell me why this does not work....

------------------------------------------------------
Private Sub FindUnit_Click( )
On Error GoTo Err_FindUnit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FindUnit_Click:
Exit Sub

Err_FindUnit_Click:
Call MsgBox ("You must enter a station number or select one from the drop_
down list." & vbCrLf & "", vbOkOnly + vbExclamation + vbSystemModal + _
vbDefaultButton1, "Error....Please try again")

Resume Exit_FindUnit_Click

End Sub

------------------------------------------------------------------------- -----------------

I have a list box that the user can either select a station number ie: 202
from a value list, or enter the station number directly.

Everytime I select or enter a station number, I get the error message
Click to expand...
box.

Have you tried it without your error trap. You custom handler is taking
ANY error and assuming that the problem is because an invalid entry was
made. If some other kind of error occurs you will be oblivious to what it
is.

My guess is that your Me.StationNumber needs to be outside the quotes.
 
K

Ken Snell [MVP]

As Rick indicates, change this line:
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

to this:
stLinkCriteria = "[AssignedTo] Like '" & Me.StationNumber & "*'"

I've moved the control/field name reference outside the text string, and
have delimited it and the * with ' characters.
 
G

Guest

Thanks for the replies....

I made the changes you suggested, now I get a run-time error 2001, You
canceled the previous operation.

When I debug, the DoCmd.OpenForm line is highlighted.

So, I am assuming that I have a problem with the wildcard being added to the
value selected in [StationNumber].

The field [AssignedTo] has values such as '202 Spare' or '202A1' which is
why I need the wildcard.

Ideas??

Cheers


Ken Snell said:
As Rick indicates, change this line:
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

to this:
stLinkCriteria = "[AssignedTo] Like '" & Me.StationNumber & "*'"

I've moved the control/field name reference outside the text string, and
have delimited it and the * with ' characters.
--

Ken Snell
<MS ACCESS MVP>


Paul B. said:
Can someone tell me why this does not work....

------------------------------------------------------
Private Sub FindUnit_Click( )
On Error GoTo Err_FindUnit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FindUnit_Click:
Exit Sub

Err_FindUnit_Click:
Call MsgBox ("You must enter a station number or select one from the drop_
down list." & vbCrLf & "", vbOkOnly + vbExclamation + vbSystemModal + _
vbDefaultButton1, "Error....Please try again")

Resume Exit_FindUnit_Click

End Sub

-------------------------------------------------------------------------- ----------------

I have a list box that the user can either select a station number ie: 202
from a value list, or enter the station number directly.

Everytime I select or enter a station number, I get the error message box.

Thanks in advance....
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

The error suggests that the criterion expression may not match any field in
the new form's recordset. Verify that the new form's recordset contains the
field AssignedTo.

Also, as part of the debug, when you get this error, click "Debug" option
and hover the cursor over the strLinkCriteria variable in the DoCmd. line.
What value is displayed in the popup text? Is it what you expected?

--

Ken Snell
<MS ACCESS MVP>

Paul B. said:
Thanks for the replies....

I made the changes you suggested, now I get a run-time error 2001, You
canceled the previous operation.

When I debug, the DoCmd.OpenForm line is highlighted.

So, I am assuming that I have a problem with the wildcard being added to the
value selected in [StationNumber].

The field [AssignedTo] has values such as '202 Spare' or '202A1' which is
why I need the wildcard.

Ideas??

Cheers


Ken Snell said:
As Rick indicates, change this line:
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

to this:
stLinkCriteria = "[AssignedTo] Like '" & Me.StationNumber & "*'"

I've moved the control/field name reference outside the text string, and
have delimited it and the * with ' characters.
--

Ken Snell
<MS ACCESS MVP>


Paul B. said:
Can someone tell me why this does not work....

------------------------------------------------------
Private Sub FindUnit_Click( )
On Error GoTo Err_FindUnit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FindUnit_Click:
Exit Sub

Err_FindUnit_Click:
Call MsgBox ("You must enter a station number or select one from the drop_
down list." & vbCrLf & "", vbOkOnly + vbExclamation + vbSystemModal + _
vbDefaultButton1, "Error....Please try again")

Resume Exit_FindUnit_Click

End Sub
Click to expand...
--------------------------------------------------------------------------
----------------

I have a list box that the user can either select a station number ie: 202
from a value list, or enter the station number directly.

Everytime I select or enter a station number, I get the error message box.

Thanks in advance....
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Wasn't a problem with your suggestion, it was my form....for whatever reason
it would not open, and I could not get it into design mode to edit it.
Rebuilt the form, works great.

Cheers


Ken Snell said:
The error suggests that the criterion expression may not match any field in
the new form's recordset. Verify that the new form's recordset contains the
field AssignedTo.

Also, as part of the debug, when you get this error, click "Debug" option
and hover the cursor over the strLinkCriteria variable in the DoCmd. line.
What value is displayed in the popup text? Is it what you expected?

--

Ken Snell
<MS ACCESS MVP>

Paul B. said:
Thanks for the replies....

I made the changes you suggested, now I get a run-time error 2001, You
canceled the previous operation.

When I debug, the DoCmd.OpenForm line is highlighted.

So, I am assuming that I have a problem with the wildcard being added to the
value selected in [StationNumber].

The field [AssignedTo] has values such as '202 Spare' or '202A1' which is
why I need the wildcard.

Ideas??

Cheers


Ken Snell said:
As Rick indicates, change this line:
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

to this:
stLinkCriteria = "[AssignedTo] Like '" & Me.StationNumber & "*'"

I've moved the control/field name reference outside the text string, and
have delimited it and the * with ' characters.
--

Ken Snell
<MS ACCESS MVP>


Can someone tell me why this does not work....

------------------------------------------------------
Private Sub FindUnit_Click( )
On Error GoTo Err_FindUnit_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmUnitsByStation"
stLinkCriteria = "[AssignedTo] Like Me.StationNumber & "*""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FindUnit_Click:
Exit Sub

Err_FindUnit_Click:
Call MsgBox ("You must enter a station number or select one from the
drop_
down list." & vbCrLf & "", vbOkOnly + vbExclamation + vbSystemModal + _
vbDefaultButton1, "Error....Please try again")

Resume Exit_FindUnit_Click

End Sub

--------------------------------------------------------------------------
----------------

I have a list box that the user can either select a station number ie: 202
from a value list, or enter the station number directly.

Everytime I select or enter a station number, I get the error message box.

Thanks in advance....
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Criteria when opening form 1
Run-time error '3218': could not update; currently locked 1
Error using Like* operator 1
Open form with Command button Error 3
Double Click List Box 2
msg for no record found 5
fOSUserName 17
Type Mismatch 3

Top