Progressive select statement to fill combobox 1

[bamauto]

Need some help,
I'm using vb2008
oledb connection
I have one table in an access database with 4 important fields to find the result of a search through combobox selection.

EX.
combobox1 query on form load fills combobox1 with YEAR selection from database.

VEHICLEDATA database has fields of YEAR, MAKE, MODEL, ENGINE, FILTERNUMBER, ENGINECAPACITY.

as you can see we are looking up oil filter numbers by
vehicle application.

User selects the YEAR first from combobox1, the next step I can't quite figure the query....
Using combobox1 click event need to query for combobox2 to load its values based on a progressive type query.

i'm not real familiar with select statement, could someone help.
Eventually i want to fill remaining comboboxes for selection
progressively.

select year, select make, select model, select engine.
this order will eventually retreive the oil filter number and engine oil capacity from the database.

 

[MajP]

faq702-4640

 

[dhookom]

If this was all Access data and combo boxes, I would use the after update event of a parent combo to set the SQL property of the child.

  'after update of cboYear
  Dim strSQL as String
  strSQL = "SELECT MAKE " & _
           "FROM VEHICLEDATA " & _
           "WHERE [Year] = " & Me.cboYear " " & _
           "GROUP BY MAKE " & _
           "ORDER BY MAKE "
  Me.cboMake.RowSource = strSQL

The code in the after update event of cboMake might look like:

  'after update of cboMake
  Dim strSQL as String
  strSQL = "SELECT Model " & _
           "FROM VEHICLEDATA " & _
           "WHERE [Year] = " & Me.cboYear & _
           " AND Make ='" & Me.cboMake & "' " & _
           "GROUP BY Model " & _
           "ORDER BY Model "
  Me.cboModel.RowSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[bamauto]

Thankyou very much for your input, I will play with this and see what I can do with it.
I'm still new at this, I'm used to vb6, so SQL is new.
I'll get back to you......

 

[bamauto]

I'm not sure what type of update event you are talking about. ?UpdateText
Could you tell me a little more.
Thankyou.
This is my initial code for loading the first combobox:

Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Load combobox1 with year data for initial start
Try
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\VehicleData.accdb")
cn.Open()
cmd = New OleDbCommand("SELECT DISTINCT [Year] FROM(VehicleData) ORDER BY [Year] DESC", cn)
dr = cmd.ExecuteReader
While dr.Read
ComboBox1.Items.Add(dr(0))
End While
Catch
End Try
dr.Close()
cn.Close()
'End combobox1 initial load


End Sub


End Class

 

[dhookom]

bamauto,
You are asking a vb question in an Access Forms forum. My reply was based on code that would be used in an Access Form. I think you might have better luck getting an answer in a VB related forum or news group.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

He was previously so advised in another forum.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}