?
Solved

Access Search - VB Code

Posted on 2007-11-14
9
Medium Priority
?
1,332 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 1500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

809 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