+ Post New Thread
Results 1 to 14 of 14
Coding Thread, VB10 - Search a Access DB with input in Coding and Web Development; It's been a good few years since I dabbled in VB and I'm very very rusty. What I'm looking to ...
  1. #1
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0

    VB10 - Search a Access DB with input

    It's been a good few years since I dabbled in VB and I'm very very rusty. What I'm looking to do is search an access database with user input.

    I have a single table set up in an access database called school_info with the following fields and data:

    Tres________DfE________Name_________Type
    001________1234________School 1________Primary
    002________4321________School 2________Secondary
    etc
    etc

    The Tres and DfE fields are show in text boxes on the VB form, and the school and Type are show in lables. What I want to do is be able to type in either a Tres or DfE number and have the other lables auto populate with the relevant data thats stored in the database table.

    Any pointers in a way to do this would be greatly appreciated!

  2. #2

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,338
    Thank Post
    367
    Thanked 622 Times in 508 Posts
    Rep Power
    155
    Funny, I've looked at doing something like this, but I've not got very far. I would recommend using a MSSQL database rather then access for starts.

  3. #3

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,986
    Thank Post
    850
    Thanked 2,653 Times in 2,253 Posts
    Blog Entries
    9
    Rep Power
    764
    You could do it with a LIKE query

    Select * from school_info WHERE DfE LIKE "your search" or Tres LIKE "your search"

    I think this should work but you may need to do it in two querys thanks to Access numerous limitations. I would also recommend MSSQL and there is even an easy upgrade wizard in Access to dump your current design and data on a SQL instance.

  4. #4
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    I went with Access purely because I could import all the info straight off of a Excel Spreadsheet, and as it's only every going to be used to look up info, I though it would be simple enough. Seems VB has changed a lot since I last used it. All other aspects of the app are working apart from the ability to search for the info (which is the key feature!)

  5. #5

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,677
    Thank Post
    3,209
    Thanked 1,030 Times in 955 Posts
    Rep Power
    361
    Is this a form within access that you are linking to the database records / table using vba or is this literally a seperate VB .NET project that you are linking to an access database ?

  6. #6
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    It's a septate VB .NET application that's linked to an Access DB.

  7. #7

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Use the "on change" event (I think it is still called this!).

    Create the VB code in a module and then use the "on change" for each text box to trigger the updating of the labels.

    I would need more detail about your project to help much more...

  8. #8
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Right, I'm using Visual Basic 2010 Express and I've created this form:

    pwdgen.JPG

    I've linked in an Access DB using the "Add new data source" feature:

    datasource.JPG

    I've added each field onto the form and the first record automatically populates. All the other areas (copy, close etc) work a treat but I just cannot seem to put in a Treasury or DfE number and click go to populate the other fields automatically. Someone on the MSDN forums posted this code as a suggestion but it's a bit beyond me:

    Code:
      Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SQLs As String
        Dim conditionNum As Integer = 0
        SQLs = "Treasury where 1=1 "
        ' add Tres condition
        If Trim(TreasuryText.Text) <> "" Then
          SQLs = SQLs & " and tres = '" & Trim(TreasuryTxt.Text) & "'"
          conditionNum = conditionNum + 1
        End If
      
        'handle condition
        If conditionNum = 0 Then
          ' do something
          MsgBox("Invalid Treasury Number!")
        Else
          ' DO DB query and populate feild
          Dim command1 As SqlCommand = New SqlCommand(SQLs, conn)
          Dim dt1 As New DataTable()
          Dim da1 As New SqlDataAdapter()
    
          da1.SelectCommand = command1
          da1.Fill(dt1)
          TreasuryTxt.Text = dt1.Rows(0)(0)
          DfETxt.Text = dt1.Rows(0)(1)
          School.Text = dt1.Rows(0)(2)
          SA_PWD.Text = dt1.Rows(0)(3)
          S2S_PWD.txt = dt1Rows(0)(4)
         End If
      End Sub
    I've played around but cannot get it to work.
    Last edited by Rawns; 22nd February 2011 at 04:53 PM.

  9. #9
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Never mind, got it working! Success!

  10. #10

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,677
    Thank Post
    3,209
    Thanked 1,030 Times in 955 Posts
    Rep Power
    361
    any chance you can post back what you did to get it to work.

  11. #11
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Here we go. This is the code linked to the button. (I did not have an open connection to the database!):

    Code:
    Private Sub TresSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TresSearch.Click
    
            Dim SQL As String
            Dim conditionNum As Integer = 0
            Dim conn As New OleDb.OleDbConnection
            'Connect to Access 2007/2010 Database
            conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;data source=data.accdb;"
    
            ' SQL Command
            SQL = "select * from data where 1=1 "
            ' add Tres condition
            If Trim(TreasuryTxt.Text) <> "" Then
                SQL = SQL & " and Treasury = '" & Trim(TreasuryTxt.Text) & "'"
                conditionNum = conditionNum + 1
            End If
    
            'If
            If conditionNum = 0 Then
                ' do something
                SchoolTxt.Text = "Invalid Treasury Number!"
            Else
    
                ' DO DB query and populate feild
                Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand(SQL, conn)
                Dim dt1 As New DataTable()
                Dim da1 As New OleDb.OleDbDataAdapter()
    
    
                da1.SelectCommand = command1
                da1.Fill(dt1)
                DfETxt.Text = dt1.Rows(0)(1)
                SchoolTxt.Text = dt1.Rows(0)(2)
                SA_Pwd.Text = dt1.Rows(0)(3)
                S2S_Pwd.Text = dt1.Rows(0)(4)
            End If
        End Sub
    One issue I am having is that if you search for a treasury number that does not exist, it errors out!

    Any suggestions on trapping that error would be appreciated!

  12. #12

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,677
    Thank Post
    3,209
    Thanked 1,030 Times in 955 Posts
    Rep Power
    361
    what error do you get because in the code at least in one section it has the error message stating that the number is not valid :

    Code:
     If conditionNum = 0 Then
                ' do something
                SchoolTxt.Text = "Invalid Treasury Number!"
            Else
    I will install and play about with vb .net 2010 later ( this evening or this weekend ). Any chance of a dummy database so I have the correct fields / records etc , also any chance of doing this in access 2003 as I do not have 2007 or 2010

    I remember doing something like this at uni and the code I used literally looped through all the ID records to check if the treasury number entered matched any of the ones it looped through, if it did then it would pull out the rest of the information and if not then I could make a label in bold red come up with an error message ie

    Treasury number does not exist, please re enter the correct number and try again

    something to that effect anyway - you will have to use the trim function to ensure there are no blank spaces before or after the number entered ie

    Dim intNumb As Integer

    intNumb = Trim(txtNumber.Text)
    Last edited by mac_shinobi; 23rd February 2011 at 02:51 PM.

  13. Thanks to mac_shinobi from:

    Rawns (23rd February 2011)

  14. #13
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    The message "Invalid Treasury Number!" Populates if the Field is blank when you click on "Go"

    If an unknown treasury number is entered, the app crashes and VB reports "There is no row at position 0".

    Here is a dummy Access 2003 Database: data.zip
    Last edited by Rawns; 23rd February 2011 at 03:01 PM. Reason: Added attachment.

  15. #14
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Never mind! Fixed it again! After some research, I found out about the "Try" function to catch errors. Working code:

    Code:
                Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand(SQL, conn)
                Dim dt1 As New DataTable()
                Dim da1 As New OleDb.OleDbDataAdapter()
    
                da1.SelectCommand = command1
    
                Try
                    da1.Fill(dt1)
                    DfETxt.Text = dt1.Rows(0)(1)
                    SchoolTxt.Text = dt1.Rows(0)(2)
                    SA_Pwd.Text = dt1.Rows(0)(3)
                    S2S_Pwd.Text = dt1.Rows(0)(4)
                Catch ex As IndexOutOfRangeException
                    TreasuryTxt.Text = ""
                    DfETxt.Text = ""
                    SchoolTxt.Text = "Invalid Treasury Number!"
                    SA_Pwd.Text = ""
                    S2S_Pwd.Text = ""
                End Try
    So when the SQL command is ran and data is populated, if a treasury number is not there, the error gets caught!

SHARE:
+ Post New Thread

Similar Threads

  1. Forcing strict safe search on every search using ISA
    By Simon_Gibbs in forum Internet Related/Filtering/Firewall
    Replies: 17
    Last Post: 4th August 2012, 05:57 AM
  2. Replies: 2
    Last Post: 16th February 2011, 09:24 AM
  3. Replies: 0
    Last Post: 4th November 2010, 11:48 AM
  4. Search Multiple Search Engines At Once
    By DaveP in forum Links
    Replies: 0
    Last Post: 16th March 2010, 08:08 PM
  5. Input please
    By neon in forum Windows Server 2000/2003
    Replies: 15
    Last Post: 4th November 2009, 09:19 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •