Access Search - VB Code

Dear all, i have the below code that for some reason does not work.  Can some one take a look and see where i have gone wrong please.  When i type in the text and click the box i want to search, it shows exaclty the same as what was there before.  Thanks

ption Compare Database
Dim sel, Sortby As String

Private Sub Command22_Click()

    Dim WOlist As String

    If Me.Select = 1 Then
        sel = "WorkorderNo ='" & Me.Input & "'"
    ElseIf Me.Select = 2 Then
        sel = "[Work Type].WorkTypeDescription ='" & Me.Input & "'"
    ElseIf Me.Select = 3 Then
        sel = "WorkStatus.Workstatus ='" & Me.Input & "*'"
    ElseIf Me.Select = 4 Then
        sel = "ProblemDescription like '" & Me.Input & "*'"
    ElseIf Me.Select = 5 Then
        sel = "DateReceived like '" & Me.Input & "*'"
    End If
     WOlist = "SELECT IssueData.AssetDesc,IssueData.ProblemDescription,IssueData.DateReceived,WorkStatus.WorkStatus, IssueData.WorkorderNo, [Work Type].WorkTypeDescription, IssueData.AssetNo " & _
    "FROM (IssueData LEFT JOIN [Work Type] ON IssueData.WorkType = [Work Type].WorkTypeID) LEFT JOIN WorkStatus ON IssueData.WorkStatus = WorkStatus.WorkStatusID " & Sortby & ";"
    Me.List.RowSource = WOlist
    Me.List.ColumnCount = 7          '<<< change this too
    Me.List.ColumnHeads = True
    Me.List.ColumnWidths = "3 cm; 12 cm; 3 cm; 3 cm; 3 cm ; 0 cm; 1 cm"   '<< add last column"


End Sub
LVL 7
dann47Asked:
Who is Participating?
 
Angelp1ayConnect With a Mentor Commented:
dann47:
<< It required
"WHERE " & sel & " ;" >>

angelp1ay:
<< 1) Errr... your query:
...doesn't include your variable "sel" at any point. >>

;o)
0
 
Angelp1ayCommented:
Try adding:

    Me.List.Requery
0
 
dann47Author Commented:
Where abouts would that go then please
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Angelp1ayCommented:
At the end:

        Me.List.RowSource = WOlist
        Me.List.ColumnCount = 7
        Me.List.ColumnHeads = True
        Me.List.ColumnWidths = "3 cm; 12 cm; 3 cm; 3 cm; 3 cm ; 0 cm; 1 cm"
   
        Me.List.Requery <<<<<<<<<
    End Sub
0
 
dann47Author Commented:
No joy i afraid, same result
0
 
Angelp1ayCommented:
1) Errr... your query:

    WOlist = "SELECT IssueData.AssetDesc,IssueData.ProblemDescription,IssueData.DateReceived,WorkStatus.WorkStatus, IssueData.WorkorderNo, [Work Type].WorkTypeDescription, IssueData.AssetNo " & _
    "FROM (IssueData LEFT JOIN [Work Type] ON IssueData.WorkType = [Work Type].WorkTypeID) LEFT JOIN WorkStatus ON IssueData.WorkStatus = WorkStatus.WorkStatusID " & Sortby & ";"

...doesn't include your variable "sel" at any point.

2) And your sortby variable doesn't seem to be populated with data at any point.



Suggest you add the line:

    Debug.Print(WOlist)

...just before your rowsource line and check that the sql string in the debug box is correct (i.e. it returns the data you expect).
0
 
dann47Author Commented:
The items are selected by a tick box for the item to be searched by, then the text is in a text box.

Could you please expand on the above
0
 
dann47Author Commented:
Ok i managed to debug and got the below

SELECT IssueData.AssetDesc,IssueData.ProblemDescription,IssueData.DateReceived,WorkStatus.WorkStatus, IssueData.WorkorderNo, [Work Type].WorkTypeDescription, IssueData.AssetNo FROM (IssueData LEFT JOIN [Work Type] ON IssueData.WorkType = [Work Type].WorkTypeID) LEFT JOIN WorkStatus ON IssueData.WorkStatus = WorkStatus.WorkStatusID ;
0
 
dann47Author Commented:
No problems now, i have found what i missed, thanks anyway.

It required
"WHERE " & sel & " ;"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.