VBA Code Problem

S

Shel

Scenario: The code below allows me to use an unbound form (frmLocateTaxPayer)
with unbound text boxes to open and filter another form (frmTaxPayerInfo)
based on the search criteria entered by the user. The problem I am having is
that I do not want to search the txtTxPrID field with the wildcard parameter
of “likeâ€, instead I want the txtTxPrID field to be = to the criteria
entered. For example: If the user enters 2 in the ID field I want that exact
record to appear, not records including the number 2 (like 12, 20, 32, etc…)

Problem: I get an error when I change the word Like to an = in my code. I am
pretty sure this is because my ID field is a number field and my code is
trying to read it as a string (or something of the like). Unfortunately I
don’t know enough about VBA to even begin to create a solution for this.
Please HELP ïŠ

******************CODE***********************

Private Sub btnOK_Click()

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation, "No
Search Criteria"
DoCmd.CancelEvent

Else

Dim strSQL As String
Dim strWhere As String
Dim ctl As Control

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'" & txtSSN & "*'"
End If

If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'" & txtLName & "*'"
End If


'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If


'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtLName = Null
End If

End Sub
 
K

Ken Snell [MVP]

I'll show you how to change one of the code steps, and leave the rest as an
exercise for the reader < g >.

Change this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If


to this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If


Your code thought the parameter was a string because you still had the
delmiting " characters around the value after you concatenated it, and you
had the * character at the end of the string.
 
P

Paul Overway

You are including a wild card with the Like criteria, so, the records
returned will not be exact matches. You can't use a wildcard with =...which
is probably why you're getting an error.
 
S

Shel

THANK YOU! THANK YOU! THANK YOU!
That is EXACTLY what I needed.

Ken Snell said:
I'll show you how to change one of the code steps, and leave the rest as an
exercise for the reader < g >.

Change this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If


to this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If


Your code thought the parameter was a string because you still had the
delmiting " characters around the value after you concatenated it, and you
had the * character at the end of the string.

--

Ken Snell
<MS ACCESS MVP>


Shel said:
Scenario: The code below allows me to use an unbound form
(frmLocateTaxPayer)
with unbound text boxes to open and filter another form (frmTaxPayerInfo)
based on the search criteria entered by the user. The problem I am having
is
that I do not want to search the txtTxPrID field with the wildcard
parameter
of "like", instead I want the txtTxPrID field to be = to the criteria
entered. For example: If the user enters 2 in the ID field I want that
exact
record to appear, not records including the number 2 (like 12, 20, 32,
etc.)

Problem: I get an error when I change the word Like to an = in my code. I
am
pretty sure this is because my ID field is a number field and my code is
trying to read it as a string (or something of the like). Unfortunately I
don't know enough about VBA to even begin to create a solution for this.
Please HELP ?

******************CODE***********************

Private Sub btnOK_Click()

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation, "No
Search Criteria"
DoCmd.CancelEvent

Else

Dim strSQL As String
Dim strWhere As String
Dim ctl As Control

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'" & txtSSN & "*'"
End If

If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'" & txtLName & "*'"
End If


'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If


'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtLName = Null
End If

End Sub
Click to expand...
Click to expand...
 
P

Paul Overway

Omitting the quotes might be appropriate for some of the criteria, but
probably not all....i.e., the SSN and LN are probably text fields.
Nevertheless, including the wildcard is what is tripping him up.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Ken Snell said:
I'll show you how to change one of the code steps, and leave the rest as
an exercise for the reader < g >.

Change this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If


to this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If


Your code thought the parameter was a string because you still had the
delmiting " characters around the value after you concatenated it, and you
had the * character at the end of the string.

--

Ken Snell
<MS ACCESS MVP>


Shel said:
Scenario: The code below allows me to use an unbound form
(frmLocateTaxPayer)
with unbound text boxes to open and filter another form (frmTaxPayerInfo)
based on the search criteria entered by the user. The problem I am having
is
that I do not want to search the txtTxPrID field with the wildcard
parameter
of "like", instead I want the txtTxPrID field to be = to the criteria
entered. For example: If the user enters 2 in the ID field I want that
exact
record to appear, not records including the number 2 (like 12, 20, 32,
etc.)

Problem: I get an error when I change the word Like to an = in my code. I
am
pretty sure this is because my ID field is a number field and my code is
trying to read it as a string (or something of the like). Unfortunately I
don't know enough about VBA to even begin to create a solution for this.
Please HELP ?

******************CODE***********************

Private Sub btnOK_Click()

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation, "No
Search Criteria"
DoCmd.CancelEvent

Else

Dim strSQL As String
Dim strWhere As String
Dim ctl As Control

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'" & txtSSN & "*'"
End If

If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'" & txtLName & "*'"
End If


'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If


'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtLName = Null
End If

End Sub
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Perhaps... but the original post said that the values are numeric, not text.

"...my ID field is a number field ..."

< g >

--

Ken Snell
<MS ACCESS MVP>


Paul Overway said:
Omitting the quotes might be appropriate for some of the criteria, but
probably not all....i.e., the SSN and LN are probably text fields.
Nevertheless, including the wildcard is what is tripping him up.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Ken Snell said:
I'll show you how to change one of the code steps, and leave the rest as
an exercise for the reader < g >.

Change this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If


to this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If


Your code thought the parameter was a string because you still had the
delmiting " characters around the value after you concatenated it, and
you had the * character at the end of the string.

--

Ken Snell
<MS ACCESS MVP>


Shel said:
Scenario: The code below allows me to use an unbound form
(frmLocateTaxPayer)
with unbound text boxes to open and filter another form
(frmTaxPayerInfo)
based on the search criteria entered by the user. The problem I am
having is
that I do not want to search the txtTxPrID field with the wildcard
parameter
of "like", instead I want the txtTxPrID field to be = to the criteria
entered. For example: If the user enters 2 in the ID field I want that
exact
record to appear, not records including the number 2 (like 12, 20, 32,
etc.)

Problem: I get an error when I change the word Like to an = in my code.
I am
pretty sure this is because my ID field is a number field and my code is
trying to read it as a string (or something of the like). Unfortunately
I
don't know enough about VBA to even begin to create a solution for this.
Please HELP ?

******************CODE***********************

Private Sub btnOK_Click()

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation,
"No
Search Criteria"
DoCmd.CancelEvent

Else

Dim strSQL As String
Dim strWhere As String
Dim ctl As Control

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'" & txtSSN & "*'"
End If

If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'" & txtLName & "*'"
End If


'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If


'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtLName = Null
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

You're very welcome.

--

Ken Snell
<MS ACCESS MVP>

Shel said:
THANK YOU! THANK YOU! THANK YOU!
That is EXACTLY what I needed.

Ken Snell said:
I'll show you how to change one of the code steps, and leave the rest as
an
exercise for the reader < g >.

Change this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If


to this:

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID = " & txtTxPrID
End If


Your code thought the parameter was a string because you still had the
delmiting " characters around the value after you concatenated it, and
you
had the * character at the end of the string.

--

Ken Snell
<MS ACCESS MVP>


Shel said:
Scenario: The code below allows me to use an unbound form
(frmLocateTaxPayer)
with unbound text boxes to open and filter another form
(frmTaxPayerInfo)
based on the search criteria entered by the user. The problem I am
having
is
that I do not want to search the txtTxPrID field with the wildcard
parameter
of "like", instead I want the txtTxPrID field to be = to the criteria
entered. For example: If the user enters 2 in the ID field I want that
exact
record to appear, not records including the number 2 (like 12, 20, 32,
etc.)

Problem: I get an error when I change the word Like to an = in my code.
I
am
pretty sure this is because my ID field is a number field and my code
is
trying to read it as a string (or something of the like). Unfortunately
I
don't know enough about VBA to even begin to create a solution for
this.
Please HELP ?

******************CODE***********************

Private Sub btnOK_Click()

If (Eval("[Forms]![empfrmLocateTaxPayer]![txtTxPrID] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtSSN] Is Null And
[Forms]![empfrmLocateTaxPayer]![txtLName] Is Null")) Then
Beep
MsgBox "Please enter search criteria or click Cancel", vbInformation,
"No
Search Criteria"
DoCmd.CancelEvent

Else

Dim strSQL As String
Dim strWhere As String
Dim ctl As Control

'Build the wherecondition to use in the OpenObject method.
If Not IsNull(txtTxPrID) Then
strWhere = strWhere & " AND TxPrID Like " & "'" & txtTxPrID & "*'"
End If

If Not IsNull(txtSSN) Then
strWhere = strWhere & " AND SSN Like " & "'" & txtSSN & "*'"
End If

If Not IsNull(txtLName) Then
strWhere = strWhere & " AND TxPrLName Like " & "'" & txtLName & "*'"
End If


'Trim the leading "AND" strWhere and store the new string in strSQL.
If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If


'Open Form based on the contents of strSQL.
stDocName = "empfrmTaxPayerInfo"
DoCmd.OpenForm stDocName, , , strSQL

'Set the text boxes back to null.
Clear_Controls:
txtTxPrID = Null
txtSSN = Null
txtLName = Null
End If

End Sub
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

Search using alternative multiple fields 0
Check ALL fields for null 2
Search Form with a SubForm 3
Create Report On the Fly 1
Filtering a multiple item form 1
Error on modified Allenbrowne SearchForm 6
Simplify Code-Delete Query 3
Modified Allenbrowne Search Form displays error 2

Top