Search Button (1 Viewer)

T

tucker61

Registered User.
Local time
Today, 01:05
Joined
Jan 13, 2008
Messages
321
I have a search button, that searches for a job number (tbjobid) yesterday i spotted a issue where that if people either left the search term null, or pressed cancel, or input text into the field i got a error message, SO i amended the code, but i am not convinced it is written in the best way.

Can someone review and advise.

thanks
G

Code: 
Private Sub btnButton_Click()
On Error GoTo handler
Dim SearchJob As String
Dim JobHold As Long
Dim LResponse As Integer
Dim deljob As Long
Dim strsql As String


If (Nz(tbJobID, 0) > 0) And ((Nz(cboxQueryType, "") = "") Or ((lblTitle.Caption <> "Rectification") And (Nz(cboxRequestArea, "") = "")) Or (Nz(cboxStockLoc, "") = "")) And cboxStatus <> 13 Then
        If (tbRectification = "Rectification") And (Nz(cboxRequestArea, "") = "") And (Nz(cboxStockLoc, "") = "") Then
            RectificationSearch = True
            GoTo PerformSearch
        Else
            MsgBox "You can't do a search while you are entering a partially created job!" & vbNewLine & "To continue, you need to have a Query Type" & IIf((lblTitle.Caption <> "Rectification"), ", Requester Area", "") & " and Stock Location!", vbOKOnly + vbExclamation, "Missing"
            ESCMessage
            Cancel = True
        End If
Else

PerformSearch:
        AttemptSave
 
        SearchJob = InputBox("Enter Job Number to Search . . . ", "Search Job")

'If (Nz(searchjob, 0) = 0) Or (Nz(DCount("*", "tblQCJobs", "Job_ID=" & searchjob), 0) = 0) Then
        If (Nz(DCount("*", "tblQCJobs", "Job_ID=" & SearchJob), 0) < 1) Then
            If Nz(SearchJob, 0) <> 0 Then
                MsgBox "Job " & Format(SearchJob, "0000000") & " does not exist!", vbExclamation + vbOKOnly, "Not Found"
            End If
            SearchJob = JobHold
        End If
        
        If SearchJob <> JobHold Then
        
strsql = "SELECT tblQCJobs.Job_ID, tblQCJobs.Query_Type, tblQCJobs.Raised_Date, tblQCJobs.Raised_By, tblQCJobs.Cat_No, tblQCJobs.Stock_Location, tblQCJobs.Request_Area, tblQCJobs.Sup_Code, TblQCJobCategories.Root_Cause, TblQCJobCategories.Reason, TblQCJobCategories.Dealt_With, TblQCJobCategories.Outcome, TblQCJobCategories.Changed_By, TblQCJobCategories.Date_Changed " & vbCrLf & _
"FROM tblQCJobs LEFT JOIN TblQCJobCategories ON tblQCJobs.Job_ID = TblQCJobCategories.Job_ID " & vbCrLf & _
"WHERE (((tblQCJobs.Job_ID)= " & SearchJob & "));"

'Debug.Print strsql
Me.RecordSource = strsql
Me.Requery
cboxStatus = Nz(DLookup("Status_ID", "tblQCJobStatus", "Job_ID=" & Nz(tbJobID, 0) & " AND Status_Change=" & SQLDate(Nz(DMax("Status_Change", "tblQCJobStatus", "Job_ID=" & Nz(tbJobID, 0)), "1/1/1"))), 0)
End If

        DoEvents
        SetFormState "Normal"
    End If

check_Not_closed

Exit Sub
handler:
If Err.Number = 13 Or Err.Number = 2471 Then
MsgBox "Invalid Job Number " & SearchJob
Err.Clear
ElseIf Err.Number = 3167 Then

Resume Next
ElseIf Err.Number = 3075 Then
Resume Next
ElseIf Err.Number = 0 Then
Debug.Print Err.Number
Err.Clear
Exit Sub
End If
btnQuit.Enabled = True
Exit Sub
        Call LogError(Err.Number, Err.Description, "11", tbJobID)
        Forms!frmMain.Visible = True
        Exit Sub
End Sub
 
T

tucker61

Registered User.
Local time
Today, 01:05
Joined
Jan 13, 2008
Messages
321
Ok, had a bit of a google and found that this code works just as well. i get error 13 if i enter text into the search, but i am capturing that using the same error trap

Code: 
Dim rs As Object
Dim lngID As Long
lngID = InputBox("Please enter the Job Number.")
If lngID > 0 Then
   Set rs = Me.RecordsetClone
   With rs
      .FindFirst "[Job_id]=" & lngID
      If .NoMatch Then
         MsgBox "Sorry, that Job Number does not exists.", vbExclamation, "ID Not Found"
      Else
         Me.Bookmark = .Bookmark
      End If
   End With
   Set rs = Nothing
End If

I this works great for the job_id field which is unique, but i need to adapt this for a "Sup_Code" search, which would not be unique field and could contain multiple entries of the same supplier code.
 
Minty

Minty

AWF VIP
Local time
Today, 09:05
Joined
Jul 26, 2013
Messages
10,371
If you are checking for a number only you can simply use

If Not IsNumeric(Me.YourSearchControl) Then ...

Also simply disable the search button until the record is saved.
 
T

tucker61

Registered User.
Local time
Today, 01:05
Joined
Jan 13, 2008
Messages
321
I have spotted another issue with the above, I have 3 search buttons on my form, 1 using the code above, and this works ok, 2nd using this code.
Code: 
Private Sub BtnSupplierSearch_Click()
    Dim Supp As String
    
    Supp = InputBox("Enter Supplier Code . . . ", "Search Supplier")
                If (Nz(Supp, 0) = 0) Or (Nz(DCount("*", "tblQCJobs", "Sup_Code='" & Supp & "'"), 0) = 0) Then
                            If Nz(Supp, 0) <> 0 Then
                                MsgBox "Supplier Code :- " & UCase(Supp) & " has never been Audited!", vbExclamation + vbOKOnly, "Not Found"
                             End If
                Exit Sub
                End If
' End If

If Len(Supp) > 0 Then
strsql = "SELECT tblQCJobs.Job_ID, tblQCJobs.Query_Type, tblQCJobs.Raised_Date, tblQCJobs.Raised_By, tblQCJobs.Cat_No, tblQCJobs.Stock_Location, tblQCJobs.Request_Area, tblQCJobs.Sup_Code, TblQCJobCategories.Root_Cause, TblQCJobCategories.Reason, TblQCJobCategories.Dealt_With, TblQCJobCategories.Outcome, TblQCJobCategories.Changed_By, TblQCJobCategories.Date_Changed " & vbCrLf & _
"FROM tblQCJobs LEFT JOIN TblQCJobCategories ON tblQCJobs.Job_ID = TblQCJobCategories.Job_ID " & vbCrLf & _
"WHERE tblqcjobs.Sup_Code='" & Trim(UCase(Nz(Supp, ""))) & "' ORDER BY raised_Date DESC;"

'Debug.Print strsql
Me.RecordSource = strsql
Forms!frmMain.Requery
Me.Visible = True
End If
    
End Sub

and the third using the following code.

Code: 
Private Sub Search_By_Line_Click()
'On Error GoTo Handler
Dim SearchJob As String
Dim deljob As Long

SearchJob = InputBox("Enter Line Number . . . ", "Search Line")
    
                If (Nz(DCount("*", "tblQCJobs", "Cat_No='" & SearchJob & "'"), 0) = 0) Then
                    MsgBox "Line Number :- " & UCase(SearchJob) & " has never been audited!", vbExclamation + vbOKOnly, "Not Found"
                Else
                    If Nz(SearchJob, 0) <> 0 And Len(SearchJob) > 0 Then
                    MsgBox "Line Number :- " & UCase(SearchJob) & " has never been audited!", vbExclamation + vbOKOnly, "Not Found"
                    Exit Sub
                    End If
                End If

strsql = "SELECT tblQCJobs.Job_ID, tblQCJobs.Query_Type, tblQCJobs.Raised_Date, tblQCJobs.Raised_By, tblQCJobs.Cat_No, tblQCJobs.Stock_Location, tblQCJobs.Request_Area, tblQCJobs.Sup_Code, TblQCJobCategories.Root_Cause, TblQCJobCategories.Reason, TblQCJobCategories.Dealt_With, TblQCJobCategories.Outcome, TblQCJobCategories.Changed_By, TblQCJobCategories.Date_Changed " & vbCrLf & _
"FROM tblQCJobs LEFT JOIN TblQCJobCategories ON tblQCJobs.Job_ID = TblQCJobCategories.Job_ID " & vbCrLf & _
"WHERE tblQCjobs.cat_no='" & Trim(UCase(Nz(SearchJob, ""))) & "' ORDER BY raised_Date DESC;"
            
                Me.RecordSource = strsql
                Me.Requery
                Exit Sub
                Else
                Cancel = True
                End If
    End If

        
If (Nz(tbCatNo, "") = "") Then
        Cancel = True
        WarningsOff
        If (Nz(tbJobID, "") = "") Then
        Else
        deljob = tbJobID
        tbCatNo = "LNull"
        CurrentDb.Execute "INSERT INTO tblQCDeletedJobs ( Job_ID, User_Name, [Time], Raised_By, Line_Number) " & _
                          "SELECT " & deljob & " AS Expr1, '" & Environ("UserName") & "' AS Expr2, Now() AS Expr3, '" & Raised_by & "' As Expr4, '" & tbCatNo & "' as Expr5;"
            If cboxStatus <> 13 Then
            cboxStatus = 14
            InsertStatus
            End If
        End If
        WarningsOn
End If


    SearchJob = InputBox("Enter Line Number . . . ", "Search Line")
    If (Nz(DCount("*", "tblQCJobs", "Cat_No='" & SearchJob & "'"), 0) = 0) Then
            If Nz(SearchJob, 0) <> 0 And Len(SearchJob) > 0 Then
                MsgBox "Line Number :- " & UCase(SearchJob) & " has never been audited!", vbExclamation + vbOKOnly, "Not Found"
            Exit Sub
            End If
    End If
    
        If Len(SearchJob) > 0 Then
        strsql = "SELECT tblQCJobs.Job_ID, tblQCJobs.AuditTrail, tblQCJobs.Query_Type, tblQCJobs.Raised_Date, tblQCJobs.Raised_By, tblQCJobs.Cat_No, tblQCJobs.Stock_Location, tblQCJobs.Request_Area, tblQCJobs.Sup_Code, tblCatalog.Division, tblCatalog.Department, tblCatalog.Category, tblCatalog.Sub_Category, TblQCJobCategories.Root_Cause, TblQCJobCategories.Reason, TblQCJobCategories.Dealt_With, TblQCJobCategories.Outcome, TblQCJobCategories.Changed_By, TblQCJobCategories.Date_Changed " & vbCrLf & _
        "FROM (tblQCJobs LEFT JOIN tblCatalog ON tblQCJobs.Cat_No = tblCatalog.Cat_No) LEFT JOIN TblQCJobCategories ON tblQCJobs.Job_ID = TblQCJobCategories.Job_ID " & vbCrLf & _
        "WHERE tblQCjobs.cat_no='" & Trim(UCase(Nz(SearchJob, ""))) & "' ORDER BY raised_Date DESC;"
        Me.RecordSource = strsql
        
        End If

Exit Sub
handler:
        Call LogError(Err.Number, Err.Description, "formmainsearchbyline", tbJobID)
        Forms!frmMain.Visible = True
        Exit Sub
    End Sub

so my 2nd and 3rd search change the recordsource of the form.

So if someone uses the 2nd search, and then tries to us the first search, then they get the message to say that the job cannot be found.

What is the easiest way to overcome this ? Do i need to reset the recordsource ?
 
Last edited:
You must log in or register to reply here.

Similar threads

T
Struggling with Error 3022 in SQL Server Table
Replies
8
Views
642
Pat Hartman
P
N
Dlookup Vs Record set query
Replies
1
Views
333
gemma-the-husky
gemma-the-husky
M
Solved Search and find records on a continuous subform
2
Replies
33
Views
1,190
Gasman
Gasman
C
Solved Update a record with a recordset
Replies
10
Views
265
cheekybuddha
cheekybuddha
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
436
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom