Searching Access DB

[Summo]

hi there Sorry if the question has been asked before but i am banging my head against a wall at the moment.
i have play with programing before but not for a long time.

im doing a dB from my garage that i own, you know customer ,car ,year etc

i have set up a DB in access with 2 tables one has customer info the other has car info like reg,work done price etc.
that's the history lesson know here is the question.

i am able to enter information into both tables of the DB but what i would like to know is how can i
say do a search on a Registration of a car and get up all work done on the car thought VB

here is the code i use to input the data for the car info

provider = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\AUTOFIX DB.accdb"
 
        myConnection.ConnectionString = provider
 
        myConnection.Open()
 
        Dim str As String
 
        str = "insert into Car([Reg],[Work],[Date],[Next],[Price],[Cost]) values(?,?,?,?,?,?)"  
 
         'reg-registration
         'work-work done
         'date- next MOT (government car check)
         ' cost and price -----
 
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
 
        cmd.Parameters.Add(New OleDbParameter("[Reg]", CType(TextBox1.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("[Work]", CType(TextBox2.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("[Date]", CType(TextBox3.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("[Next]", CType(TextBox6.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("[Cost]", CType(TextBox5.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("[Price]", CType(TextBox4.Text, String)))
 
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()
 
        Catch ex As Exception
 
            MsgBox(ex.Message)
 
        End Try
 
        myConnection.Close()

hope someone can help

[jmcilhinney]

First things first, your database design is not really ideal. You should probably have at least three tables: Customer, Vehicle and Work. The primary key for Customer would be CustomerId and Vehicle would have a CustomerID column as a foreign key. Likewise, VehicleId would be the primary key for Vehicle and Work would have VehicleId column as a foreign key. You'd then get the VehicleId for the Vehicle record you were interested in and then query the Work table with that VehicleId in the WHERE clause.

As your database design stands, you'd query the Car table with the desired Reg value in the WHERE clause. If you don't know how to write a query with a WHERE clause, I'd suggest that you follow the SQL tutorial link in my signature below and learn the basics.

[FunkyDexter]

Welcome to the forum.

I added highlighting to your post to make the code more readable. If you want to do this yourself you can just press the button marked VB in the formatting bar above the post window. When it asks you for a style enter "VB".

As JM says, your design isn't really right. I'd suggest you take a look at that before you start worrying about querying it or you'll find yourself going back and forward a couple of times. JM has talked about primary and foreign keys. Do you know what they are and how to use them?

[jmcilhinney]

When it asks you for a style enter "VB".

Personally, I would suggest using VB.NET for the option rather than VB. In my opinion, the syntax highlighting is better.

[FunkyDexter]

Actually, yeah. The colours make more sense.

[Summo]

thanks every body i think i will go down the SQL root.


will do somw searching and get back soon

thanks again everybody