Search Button Problems

[Steve1234]

Hi,

I've got a peice of code I'm using to search for records in a table and display them on a form. I'm using MS Access 2000, the code is run on the click of a button on the form and references a textbox as the search criteria. The control table has Fault_ID(PK, Autonumber), Call_Flag(0 or 1) and Fault_Closed(0 or 1) fields.

If the number in the text box matches a Fault_ID and the Call_Flag value is 0 and the Fault_Closed is 0 then search displays "Match Found For: X".

If the number in the text box matches a Fault_ID and the Call_Flag value is 1 and the Fault_Closed is 0 then search displays "This Call Already has Fault Details".

If the number in the text box matches a Fault_ID and the Call_Flag value is 1 and the Fault_Closed is 1 then search displays "This Call is Closed".

If the number in the text box does not match a Fault_ID then search displays "Match Not Found For: X"

The code seems to work fine for single digi numbers, however, if I enter '10' into the search box and press the button I get "Match Not Found For: X" but the Call_Flag and Fault_Closed values are both 0 and there is a Fault_ID of 10 in the table.

If I enter '12' into the search box and press the button I get "Match Not Found For: X" but the Call_Flag and Fault_Closed values are both 1.

I'm not a programmer so I'm not sure I've approached this corectly, I've tried moving the code around. Any advise would be appriated.

Thanks in advance

Steve

CODE:

Private Sub Command36_Click()

Dim strFaultRef As String
Dim strSearch As String
Dim strFaultStat As String
Dim strCallFlag As String

'Check txtSearch for Null value or Nill Entry first.

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in fault_id

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Fault_ID")
DoCmd.FindRecord Me!txtSearch

Fault_IDFOCUS.SetFocus
strFaultRef = Fault_ID.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

FAULT_CLOSED.SetFocus
strFaultStat = FAULT_CLOSED.Text

CALL_Flag.SetFocus
strCallFlag = CALL_Flag.Text

'If matching record found sets focus in strFaultID and shows msgbox
'and clears search control

If strFaultRef = strSearch And strFaultStat = "1" Then
MsgBox "This Call is Closed: " & strSearch, , "Please Try Again"
Fault_ID.SetFocus
txtSearch = ""

End If

If strFaultRef = strSearch And strCallFlag = "1" Then
MsgBox "This Call Already has Fault Details: " & strSearch, , "Please Open via XXX Search"
Fault_ID.SetFocus
txtSearch = ""

End If

If strFaultRef = strSearch And strFaultStat = "0" And strCallFlag = "0" Then MsgBox "Match Found For: " & strSearch, , "Congratulations!"
Fault_ID.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
If strFaultRef <> strSearch Then
MsgBox "Match Not Found For: " & strSearch & _
" - Please Try Again.", , "Invalid Search Criterion!"
txtSearch.SetFocus

End If
End Sub

[M Owen]

On your find method cast the TxtSearch contents to an int: CInt(Me!TxtSearch) ...

[Steve1234]

Thanks for your advise, I'm not a programmer by any stretch of the imagination..... How would I fit this into the code? and what does it do?

Thanks again

Steve