Solved

Access Search - VB Code

Posted on 2007-11-14
9
1,300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20278808
Try adding:

    Me.List.Requery
0
 
LVL 7

Author Comment

by:dann47
ID: 20278881
Where abouts would that go then please
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20278900
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 7

Author Comment

by:dann47
ID: 20278945
No joy i afraid, same result
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20279026
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
ID: 20287599
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
ID: 20288002
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
ID: 20288047
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
ID: 20288187
dann47:
<< It required
"WHERE " & sel & " ;" >>

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

;o)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 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