Solved

Access Search - VB Code

Posted on 2007-11-14
9
1,289 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

789 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