Solved

Access Search - VB Code

Posted on 2007-11-14
9
1,263 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:dann47
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
Try adding:

    Me.List.Requery
0
 
LVL 7

Author Comment

by:dann47
Comment Utility
Where abouts would that go then please
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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
 
LVL 7

Author Comment

by:dann47
Comment Utility
No joy i afraid, same result
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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
 
LVL 7

Author Comment

by:dann47
Comment Utility
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
 
LVL 7

Author Comment

by:dann47
Comment Utility
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
 
LVL 7

Author Comment

by:dann47
Comment Utility
No problems now, i have found what i missed, thanks anyway.

It required
"WHERE " & sel & " ;"
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
Comment Utility
dann47:
<< It required
"WHERE " & sel & " ;" >>

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

;o)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now