?
Solved

Database Search

Posted on 2005-05-04
40
Medium Priority
?
379 Views
Last Modified: 2010-04-23
I am creating a front end for an access database which displays data in text and combo boxes.  When you click a search button on the main form it will open another form which has a combobox on so that you can select to search by name, or box number.  Then when you have entered your data and selected what to search by I would like the application to switch back to the main form and update the text/comboboxes.  

Any help Appreciated
0
Comment
Question by:matt_swinburne
  • 23
  • 17
40 Comments
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 750 total points
ID: 13925387
try sometihng like this
     public dv as dataview

put the line below in the load event of your main form
   dv = dataset11.tables("main").defaultview
'   and bind this dataview to your textbox etc..

    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click
        dim frmSearch as new searchform
        frmSearch.owner = me
        frmsearch.showdialog
    End Sub

'in your searchform in the ok button, or in the closing event, do something like this

    Private Sub BtnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOk.Click
lets say you allow them to search on statusID

      directcast(me.owner,form1).dv.sort = "statusid"
     dim x as integer = directcast(me.owner,form1).dv.find(12)
     'use your currencymanager to jump to the correct record, your currency manager shoudl be public as well
     directcast(me.owner,form1).dv.sort = "statusid"
     directcast(me.owner,form1).cm.position = x
  end sub

'Mind you there are differn way to do this this is just one, what you could also do is to add a public property to the main form which you set from form 2


 
 

 
    end sub


     


 
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13925424
You can find a lot of samples on almost every aspect of .net in the 101 code samples from MS, the are a lot off samples on databinding, navigation  etc.. (this is what I used to learn .net, although I'm still learning :-)))

http://www.microsoft.com/downloads/details.aspx?FamilyId=08E3D5F8-033D-420B-A3B1-3074505C03F3&displaylang=en
0
 

Author Comment

by:matt_swinburne
ID: 13925519
Tanks for the answer and the link
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13925563
I made a small error in my post, wrong sequence

     'use your currencymanager to jump to the correct record, your currency manager shoudl be public as well
     directcast(me.owner,form1).dv.sort = "statusid"
     dim x as integer = directcast(me.owner,form1).dv.find(12)
     directcast(me.owner,form1).cm.position = x
0
 

Author Comment

by:matt_swinburne
ID: 13927589
Will this interfere with any of my program as it stands Ronald?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13927743
Yes , it will :-(, since it will require you to bind to a dataview instead of datatable.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13927779
We could however,maybe get around this little problem, I'm going home now but I will work on it tomorrow.

to hint, you could pass a dataview to the search form locate the record in there and pass the recordnumber back to the main form. you could then use this to set the position with the currencymanager
0
 

Author Comment

by:matt_swinburne
ID: 13927823
Ignore last post

On;

 DirectCast(Me.Owner, Form1).cm.position = x (in search form on search button)

I get;

'WindowsApplication3.Form1.cm' is not accessible in this context because it is 'Private'.
0
 

Author Comment

by:matt_swinburne
ID: 13927850
(I did rename the form to public.)

Thanks for assistance speak to you tommorow
0
 

Author Comment

by:matt_swinburne
ID: 13933727
what would the code look like to jump to the correct spot using the currency manager.  I made currency manager public but i get cm is not declared when in BtnSearch_Click (on search form)
0
 

Author Comment

by:matt_swinburne
ID: 13934380
I have decided to have the search on the one main form to make things simpler.  Dont know if this makes things simpler for you?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943231
Hi Matt,

Sorry I didn't answer yesterday (I forgot it was a national holiday over here :-))

On what do you want to let the user search (on which table and which columns ?
0
 

Author Comment

by:matt_swinburne
ID: 13943319
Hi Ronald, you had a good one i hope?

To answer your question I would like to search on the table Main and I would like to search on Matter Number, Box Number and Client name.  
0
 

Author Comment

by:matt_swinburne
ID: 13943461
I now use this code:

Main Form

Imports System.Data.OleDb
Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim dv As DataView
    Dim cm As CurrencyManager

    Dim Constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\matthewm\Desktop\File Tracking.mdb;Persist Security Info=False"

 Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DA As New OleDbDataAdapter("Select * From Emp", Constr)
        Dim DT As New DataTable
>>   DA.Fill(DT)  << Error
        dv = DT.DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager)

        TxtClient.DataBindings.Add("Text", dv, "ClientName")
        TxtMatterNumber.DataBindings.Add("Text", dv, "MatterNo")
        TxtCloseDate.DataBindings.Add("Text", dv, "CloseDate")
        TxtNotes.DataBindings.Add("Text", dv, "Notes")
        TxtBox.DataBindings.Add("Text", dv, "BoxNumber")
        TxtDateIn.DataBindings.Add("Text", dv, "DateIn")
        TxtStoreDate.DataBindings.Add("Text", dv, "StoreDate")
        TxtDateRemoved.DataBindings.Add("Text", dv, "RemoveDate")
        TxtDestroyDate.DataBindings.Add("Text", dv, "DestroyDate")

        'Load Originating Location Combo
        CmbLocation.DataSource = dv
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", dv, "LocationID")


        'Load FeeEarner ComboBox
        CmbFeeEarner.DataSource = dv
        CmbFeeEarner.DisplayMember = "FeeEarnerName"
        CmbFeeEarner.ValueMember = "FeeEarnerID"
        CmbFeeEarner.DataBindings.Add("selectedvalue", dv, "FeeEarnerID")

        'Load MatterType Combobox
        CmbMatter.DataSource = dv
        CmbMatter.DisplayMember = "MatterTypeName"
        CmbMatter.ValueMember = "MatterTypeID"
        CmbMatter.DataBindings.Add("selectedvalue", dv, "MatterTypeID")

        'Load Status Combobox
        CmbStatus.DataSource = dv
        CmbStatus.DisplayMember = "StorageName"
        CmbStatus.ValueMember = "StorageID"
        CmbStatus.DataBindings.Add("selectedvalue", dv, "StatusID")

        'Load Office Combobox
        CmbOffice.DataSource = dv
        CmbOffice.DisplayMember = "OfficeName"
        CmbOffice.ValueMember = "OfficeID"
        CmbOffice.DataBindings.Add("selectedvalue", dv, "OfficeName")

    End Sub

    Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click, BtnLast.Click, BtnNext.Click, BtnPrevious.Click
        Select Case sender.Name
            Case "BtnFirst"

                cm.Position = 0

            Case "BtnPrevious"

                cm.Position -= 1

            Case "BtnNext"

                cm.Position += 1

            Case "BtnLast"

                cm.Position = DataSet11.Tables("Main").Rows.Count - 1

        End Select

    End Sub




    Private Sub BtnMainSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnMainSearch.Click
        Dim objFind As New Search
        Dim Name As String
        If objFind.ShowDialog = DialogResult.OK Then
            Name = objFind.Name
            dv.Sort = "ClientName"
            dv.RowFilter = "ClientName='" & Name & "'"
        End If
    End Sub
End Class


Search Form:

Public Class Search
    Inherits System.Windows.Forms.Form
    Dim mName As String

Public ReadOnly Property Client() As String
        Get
            Name = mName
        End Get
    End Property


    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click
        mName = TxtSearch.Text
        Me.DialogResult = DialogResult.OK

    End Sub
End Class

However I get an error on the line:

DA.Fill(DT) -- On the main Form

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
0
 

Author Comment

by:matt_swinburne
ID: 13943534
Sorry blatent error:      Dim DA As New OleDbDataAdapter("Select * From Emp", Constr)

    Dim DA As New OleDbDataAdapter("Select * From Main", Constr)

Now i get  Could not bind to the new display member.

on

  CmbStatus.ValueMember = "StorageID"
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943601
Well , you can't do what you are doing because the dv (which is table  main) doesn't have the things like storagename, FeeEarnerNam etc, (just the IDs) because the were in other tables weren't they?

Could you post the code you have now, and I'll work something out for you :-)

0
 

Author Comment

by:matt_swinburne
ID: 13943758
Hi Ronald,  This isnt my database which is why it isnt very well constructed.  Went into the database and changed the field names to match.  Now works.  

1st Problem:
Database access seems reeaally slow.

2nd Problem:
The search doesnt return any results just leaves the fields blank

3rd Problem:
I would like the user to select from a dropdown menu on the search form to pick whether they would like to search by Boxno, matter no or name

Is all this possible do you know?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943769
Yes it is all possible, but some extra questions, Do you want to allow them to search on more than 1 item, and do you want to  be able to search on a part the name
0
 

Author Comment

by:matt_swinburne
ID: 13943829
The client name might have more than one record per client so I would like it tobe able to return more than one record.  And yes I need the user to be able to search on part of the name and not just the full name.  Is that what you were asking?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943839
Yep, I'll be back
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943927
Ok consider this example (you can just cut and paste these 2 forms, they should work on their own)

This example has 2 forms (form17, FrmSearch) when you press the search button it will open form2 which has a combobox a textbox and a button, they can choose which column to search on in the combobox and in the textbox they can enter a (part) text to search on.

Public Class Form17
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents TextBox2 As System.Windows.Forms.TextBox
    Friend WithEvents BtnPrevious As System.Windows.Forms.Button
    Friend WithEvents BtnNext As System.Windows.Forms.Button
    Friend WithEvents BtnLast As System.Windows.Forms.Button
    Friend WithEvents BtnFirst As System.Windows.Forms.Button
    Friend WithEvents BtnSearch As System.Windows.Forms.Button
    Friend WithEvents Button1 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.TextBox2 = New System.Windows.Forms.TextBox
        Me.BtnPrevious = New System.Windows.Forms.Button
        Me.BtnNext = New System.Windows.Forms.Button
        Me.BtnSearch = New System.Windows.Forms.Button
        Me.BtnLast = New System.Windows.Forms.Button
        Me.BtnFirst = New System.Windows.Forms.Button
        Me.Button1 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(8, 24)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(80, 20)
        Me.TextBox1.TabIndex = 0
        Me.TextBox1.Text = ""
        '
        'TextBox2
        '
        Me.TextBox2.Location = New System.Drawing.Point(8, 48)
        Me.TextBox2.Name = "TextBox2"
        Me.TextBox2.Size = New System.Drawing.Size(80, 20)
        Me.TextBox2.TabIndex = 1
        Me.TextBox2.Text = ""
        '
        'BtnPrevious
        '
        Me.BtnPrevious.Location = New System.Drawing.Point(70, 121)
        Me.BtnPrevious.Name = "BtnPrevious"
        Me.BtnPrevious.Size = New System.Drawing.Size(40, 24)
        Me.BtnPrevious.TabIndex = 9
        Me.BtnPrevious.Text = "<"
        '
        'BtnNext
        '
        Me.BtnNext.Location = New System.Drawing.Point(182, 121)
        Me.BtnNext.Name = "BtnNext"
        Me.BtnNext.Size = New System.Drawing.Size(40, 24)
        Me.BtnNext.TabIndex = 8
        Me.BtnNext.Text = ">"
        '
        'BtnSearch
        '
        Me.BtnSearch.Location = New System.Drawing.Point(110, 121)
        Me.BtnSearch.Name = "BtnSearch"
        Me.BtnSearch.Size = New System.Drawing.Size(72, 24)
        Me.BtnSearch.TabIndex = 7
        Me.BtnSearch.Text = "Filter"
        '
        'BtnLast
        '
        Me.BtnLast.Location = New System.Drawing.Point(222, 121)
        Me.BtnLast.Name = "BtnLast"
        Me.BtnLast.Size = New System.Drawing.Size(40, 24)
        Me.BtnLast.TabIndex = 6
        Me.BtnLast.Text = ">>"
        '
        'BtnFirst
        '
        Me.BtnFirst.Location = New System.Drawing.Point(30, 121)
        Me.BtnFirst.Name = "BtnFirst"
        Me.BtnFirst.Size = New System.Drawing.Size(40, 24)
        Me.BtnFirst.TabIndex = 5
        Me.BtnFirst.Text = "<<"
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(110, 152)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(72, 24)
        Me.Button1.TabIndex = 10
        Me.Button1.Text = "Clear filter"
        '
        'Form17
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.BtnPrevious)
        Me.Controls.Add(Me.BtnNext)
        Me.Controls.Add(Me.BtnSearch)
        Me.Controls.Add(Me.BtnLast)
        Me.Controls.Add(Me.BtnFirst)
        Me.Controls.Add(Me.TextBox2)
        Me.Controls.Add(Me.TextBox1)
        Me.Name = "Form17"
        Me.Text = "Clear Filter"
        Me.ResumeLayout(False)

    End Sub

#End Region


    Dim dt As New DataTable
    Public dv As DataView
    Dim cm As CurrencyManager
    Dim CurrentRecord As Integer
    Private Sub Form17_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dt.Columns.Add("ID", GetType(System.Int32))
        dt.Columns.Add("FirstName", GetType(System.String))
        dt.Columns.Add("LastName", GetType(System.String))

        Dim dr As DataRow
        dr = dt.NewRow
        dr.Item("ID") = 1
        dr.Item("FirstName") = "John"
        dr.Item("LastName") = "Do"
        dt.Rows.Add(dr)

        Dim dr2 As DataRow
        dr2 = dt.NewRow
        dr2.Item("ID") = 2
        dr2.Item("FirstName") = "Do"
        dr2.Item("LastName") = "John"
        dt.Rows.Add(dr2)

        dv = dt.DefaultView
        TextBox1.DataBindings.Add("text", dv, "FirstName")
        TextBox2.DataBindings.Add("text", dv, "LastName")
        cm = CType(Me.BindingContext(dv), CurrencyManager)


    End Sub

    Public Sub FindRecords(ByVal SearchPattern As String)
        dv.RowFilter = SearchPattern
    End Sub


    Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click, BtnLast.Click, BtnNext.Click, BtnPrevious.Click, BtnSearch.Click
        Select Case sender.Name
            Case "BtnFirst"

                cm.Position = 0

            Case "BtnPrevious"

                cm.Position -= 1

            Case "BtnNext"

                cm.Position += 1

            Case "BtnLast"

                cm.Position = dv.Count - 1
            Case "BtnSearch"
                Dim fs As New FrmSearch
                fs.Owner = Me
                fs.ShowDialog()

        End Select

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dv.RowFilter = ""
    End Sub
End Class

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.TextBox1 = New System.Windows.Forms.TextBox
        Me.Button1 = New System.Windows.Forms.Button
        Me.ComboBox1 = New System.Windows.Forms.ComboBox
        Me.SuspendLayout()
        '
        'TextBox1
        '
        Me.TextBox1.Location = New System.Drawing.Point(48, 40)
        Me.TextBox1.Name = "TextBox1"
        Me.TextBox1.Size = New System.Drawing.Size(128, 20)
        Me.TextBox1.TabIndex = 1
        Me.TextBox1.Text = ""
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(88, 80)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(56, 32)
        Me.Button1.TabIndex = 2
        Me.Button1.Text = "OK"
        '
        'ComboBox1
        '
        Me.ComboBox1.Items.AddRange(New Object() {"FirstName", "Lastname"})
        Me.ComboBox1.Location = New System.Drawing.Point(48, 8)
        Me.ComboBox1.Name = "ComboBox1"
        Me.ComboBox1.Size = New System.Drawing.Size(128, 21)
        Me.ComboBox1.TabIndex = 3
        '
        'FrmSearch
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(240, 134)
        Me.Controls.Add(Me.ComboBox1)
        Me.Controls.Add(Me.Button1)
        Me.Controls.Add(Me.TextBox1)
        Me.Name = "FrmSearch"
        Me.Text = "FrmSearch"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sstring As String
        Select Case ComboBox1.SelectedItem.ToString
            Case Is = "LastName"
                sstring = "LastName like '" & TextBox1.Text & "*'"
            Case Is = "FirstName"
                sstring = "FirstName like '" & TextBox1.Text & "*'"
        End Select

        CType(Me.Owner, Form17).FindRecords(sstring)
        Me.Close()
    End Sub
End Class
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13943934
sorry this was missing at the top of the second form

Public Class FrmSearch
    Inherits System.Windows.Forms.Form
0
 

Author Comment

by:matt_swinburne
ID: 13944064
2 questions.

1:
    dt.Columns.Add("ID", GetType(System.Int32))
        dt.Columns.Add("FirstName", GetType(System.String))
        dt.Columns.Add("LastName", GetType(System.String))

Do I need to add all required columns from all tables?

2:
 Dim dr2 As DataRow
        dr2 = dt.NewRow
        dr2.Item("ID") = 2
        dr2.Item("FirstName") = "Do"
        dr2.Item("LastName") = "John"
        dt.Rows.Add(dr2)

I presume I don't need this coding as my database is already complete?
0
 

Author Comment

by:matt_swinburne
ID: 13944107
1:
    dt.Columns.Add("ID", GetType(System.Int32))
        dt.Columns.Add("FirstName", GetType(System.String))
        dt.Columns.Add("LastName", GetType(System.String))

Do I need to add all required columns from all tables?
Or just the columns I want to search on?
0
 

Author Comment

by:matt_swinburne
ID: 13944538
Ok i've presumed that you do need to add all columns so I have done that.  Left out the add items part, just get an error that FindRecord is not a member of windowsapplication3.Search

0
 

Author Comment

by:matt_swinburne
ID: 13944843
On open the application doesnt show any data.  No error just no results
0
 

Author Comment

by:matt_swinburne
ID: 13945470
Alright quite a few useless posts ive left here.  sorted a few of the errors,
Using;
  Dim sstring As String
        Select Case CmbSearch.SelectedItem.ToString
            Case Is = "Name"
                sstring = "ClientName like '" & TxtSearch.Text & "*'"
            Case Is = "MatterNumber"
                sstring = "MatterNo like '" & TxtSearch.Text & "*'"

            Case Is = "BoxNumber"
                sstring = "BoxNumber like '" & TxtSearch.Text & "*'"

        End Select

        CType(Me.Owner, Form1).FindRecords(sstring)

    End Sub

Im guessin on this code, now the Name search and the matter number search works!!  However when searching Boxno. get Cannot perform 'Like' operation on System.Int32 and System.String.

Please help!
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13949761
Hi Matt, no you don't need the columns I added and filled with data, I just create a table in code, so you could test how it works without a database, I couldn't use your setup because I don't have your database.

so you don't need this part at all

dt.Columns.Add("ID", GetType(System.Int32))
        dt.Columns.Add("FirstName", GetType(System.String))
        dt.Columns.Add("LastName", GetType(System.String))

        Dim dr As DataRow
        dr = dt.NewRow
        dr.Item("ID") = 1
        dr.Item("FirstName") = "John"
        dr.Item("LastName") = "Do"
        dt.Rows.Add(dr)

        Dim dr2 As DataRow
        dr2 = dt.NewRow
        dr2.Item("ID") = 2
        dr2.Item("FirstName") = "Do"
        dr2.Item("LastName") = "John"
        dt.Rows.Add(dr2)

also

'like' only works (and is only needed) for string variables, so you need to adjust it to this

 Dim sstring As String
        Select Case CmbSearch.SelectedItem.ToString
            Case Is = "Name"
                sstring = "ClientName like '" & TxtSearch.Text & "*'"
            Case Is = "MatterNumber"
                sstring = "MatterNo = " & TxtSearch.Text
            Case Is = "BoxNumber"
                sstring = "BoxNumber = " & TxtSearch.Text

        End Select

        CType(Me.Owner, Form1).FindRecords(sstring)

    End Sub

0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13949764
Hmm is just understood that your matterno is a string var. So it should be this

 Dim sstring As String
        Select Case CmbSearch.SelectedItem.ToString
            Case Is = "Name"
                sstring = "ClientName like '" & TxtSearch.Text & "*'"
            Case Is = "MatterNumber"
                sstring = "MatterNo like '" & TxtSearch.Text & "*'"
            Case Is = "BoxNumber"
                sstring = "BoxNumber = " & TxtSearch.Text

        End Select

        CType(Me.Owner, Form1).FindRecords(sstring)

    End Su
0
 

Author Comment

by:matt_swinburne
ID: 13956081
Thanks Ronald, worked a treat.
0
 

Author Comment

by:matt_swinburne
ID: 13956181
could I get your help on another thing please.  My only problem is that the combo boxes dont work.  Using this coding;

'Load Originating Location Combo
        CmbLocation.DataSource = dv
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", dv, "LocationID")

It accesses the database really slow taking around 5 mins to get to load up the data?  Do you know a way around this, or another way or just a way of getting textboxes to display the data?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13957637
Hi matt, doesn't it work or it works but just really slow ?

Could you show the code you have now (just where you load the data, I think i still have the rest :-))
0
 

Author Comment

by:matt_swinburne
ID: 13957718
Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DA As New OleDbDataAdapter("Select * From Main", Constr)

        Try
            DA.Fill(dt)
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try

        dv = dt.DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager)

        'Bind Textboxes

        TxtClient.DataBindings.Add("Text", dv, "ClientName")
        TxtMatterNumber.DataBindings.Add("Text", dv, "MatterNo")
        TxtCloseDate.DataBindings.Add("Text", dv, "CloseDate")
        TxtNotes.DataBindings.Add("Text", dv, "Notes")
        TxtBox.DataBindings.Add("Text", dv, "BoxNumber")
        TxtDateIn.DataBindings.Add("Text", dv, "DateIn")
        TxtStoreDate.DataBindings.Add("Text", dv, "StoreDate")
        TxtDateRemoved.DataBindings.Add("Text", dv, "RemoveDate")
        TxtDestroyDate.DataBindings.Add("Text", dv, "DestroyDate")


        'Load Originating Location Combo
        'CmbLocation.DataSource = DataSet11
        'CmbLocation.DisplayMember = "LocationName"
        'CmbLocation.ValueMember = "LocationID"
        'CmbLocation.DataBindings.Add("selectedvalue", DataSet11, "Main.LocationID")


        'Load FeeEarner ComboBox
        'CmbFeeEarner.DataSource = DataSet11
        'CmbFeeEarner.DisplayMember = "FeeEarnerName"
        'CmbFeeEarner.ValueMember = "FeeEarnerID"
        'CmbFeeEarner.DataBindings.Add("selectedvalue", DataSet11, "Main.FeeEarnerID")

        'Load MatterType Combobox
        'CmbMatter.DataSource = DataSet11
        'CmbMatter.DisplayMember = "MatterTypeName"
        'CmbMatter.ValueMember = "MatterTypeID"
        'CmbMatter.DataBindings.Add("selectedvalue", DataSet11, "Main.MatterTypeID")

        'Load Status Combobox
        'CmbStatus.DataSource = DataSet11
        'CmbStatus.DisplayMember = "StorageName"
        'CmbStatus.ValueMember = "StatusID"
        'CmbStatus.DataBindings.Add("selectedvalue", DataSet11, "Main.StatusID")

        'Load Office Combobox
        'CmbOffice.DataSource = DataSet11
        'CmbOffice.DisplayMember = "OfficeName"
        'CmbOffice.ValueMember = "OfficeID"
        'CmbOffice.DataBindings.Add("selectedvalue", DataSet11, "Main.OfficeID")

    End Sub


I have comented out the combo boxes as the access is so slow.

It does work eventually just acceses the data really slow
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13957752
Well, you connect the combobox directly to the complete dataset, try it to the individual table instead, like

        'Load Originating Location Combo
        CmbLocation.DataSource = DataSet11.tables("locations")
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", DataSet11, "Main.LocationID")

(also for the rest of the comboboxes ofcourse :-))
0
 

Author Comment

by:matt_swinburne
ID: 13958822
When I use that coding the comboboxes arent populated by anything.

Any ideas\?
0
 

Author Comment

by:matt_swinburne
ID: 13958844
Failing using comboboxes is it possible to achieve the same outcome using textboxes as the user has no need to select from the comboboxes
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13958850
Are you actually filling the locations table, In your other post I don't see you doing that
0
 

Author Comment

by:matt_swinburne
ID: 13958895
I dont think so (help?!)
0
 

Author Comment

by:matt_swinburne
ID: 13958902
just to help;

Dim dt As New DataTable
    Public dv As DataView
    Dim cm As CurrencyManager
    Dim CurrentRecord As Integer
    Dim Constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Matt\Desktop\File Tracker.mdb;Persist Security Info=False"

 Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DA As New OleDbDataAdapter("Select * From Main", Constr)

        Try
            DA.Fill(dt)
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try


        dv = dt.DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager)

        'Bind Textboxes

        TxtClient.DataBindings.Add("Text", dv, "ClientName")
        TxtMatterNumber.DataBindings.Add("Text", dv, "MatterNo")
        TxtCloseDate.DataBindings.Add("Text", dv, "CloseDate")
        TxtNotes.DataBindings.Add("Text", dv, "Notes")
        TxtBox.DataBindings.Add("Text", dv, "BoxNumber")
        TxtDateIn.DataBindings.Add("Text", dv, "DateIn")
        TxtStoreDate.DataBindings.Add("Text", dv, "StoreDate")
        TxtDateRemoved.DataBindings.Add("Text", dv, "RemoveDate")
        TxtDestroyDate.DataBindings.Add("Text", dv, "DestroyDate")


        'Load Originating Location Combo
        CmbLocation.DataSource = DataSet11.Tables("Locations")
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", DataSet11, "Main.LocationID")


        'Load FeeEarner ComboBox
        CmbFeeEarner.DataSource = DataSet11.Tables("FeeEarner")
        CmbFeeEarner.DisplayMember = "FeeEarnerName"
        CmbFeeEarner.ValueMember = "FeeEarnerID"
        CmbFeeEarner.DataBindings.Add("selectedvalue", DataSet11, "Main.FeeEarnerID")

        'Load MatterType Combobox
        CmbMatter.DataSource = DataSet11.Tables("Matter")
        CmbMatter.DisplayMember = "MatterTypeName"
        CmbMatter.ValueMember = "MatterTypeID"
        CmbMatter.DataBindings.Add("selectedvalue", DataSet11, "Main.MatterTypeID")

        'Load Status Combobox
        CmbStatus.DataSource = DataSet11.Tables("Status")
        CmbStatus.DisplayMember = "StorageName"
        CmbStatus.ValueMember = "StatusID"
        CmbStatus.DataBindings.Add("selectedvalue", DataSet11, "Main.StatusID")

        'Load Office Combobox
        CmbOffice.DataSource = DataSet11.Tables("Offices")
        CmbOffice.DisplayMember = "OfficeName"
        CmbOffice.ValueMember = "OfficeID"
        CmbOffice.DataBindings.Add("selectedvalue", DataSet11, "Main.OfficeID")

    End Sub

    Public Sub FindRecords(ByVal SearchPattern As String)
        Try
            dv.RowFilter = SearchPattern
        Catch
            MsgBox("You have entered an incorrect search.  Please try again.")
            dv.RowFilter = ""
        End Try
    End Sub
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 13958921
I've tried to answer it in your other post :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

864 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