Hi

I'm new to VB and SQL so if this looks a bit simple, please bear with
me.
I'm using VB4 16bit with an Access .mdb  
The file structure is as follows:

(table)
ITEM
ItemIDnum
ItemName

(table)
LOOKUP
ItemIDnum
CustIDnum

(table)
CUSTOMER
CustIDnum
Name
... etc.

The objective:
(using DAO) I load the ITEM.[ItemName] into a combo list (combo1).
I have a listbox on the same form (List1)
When an ItemName is selected in Combo1, I want a list of
<related> CUSTOMER.[CustName] to be loaded into List1.

Two questions:

a.  If useing SQL statements, do I still need to
define relationships within the database.

b.  Using SQL, how do I link these tables together in order to
accomplish
my objective ?

I've been experimenting with SELECT FROM WHERE so much now that I'm not
sure where I'm going any more :-[

Any help would be much appreciated. An e-mailes reply to

would be even better ;-)

Regards - Neil

Neil,

(a) Although you don't need to create the relationships in the database to
use SQL statements, I recommend that you do so as not to lose integrity
within your database.

(b) Since you say you are new to VB, I will try to cover everything that I
think will help.  I am using VB5 and the only thing that you might have to
change is the data access parts (like change As RecordSet to As Dynaset,
maybe).  Here is what I would do (In Form1).

Option Explicit
Dim db As Database

Private Sub Form_Load()
  Dim rs As Recordset
  Set db = OpenDatabase("C:\db1.mdb")
  Set rs = db.OpenRecordset("Select * From Item", , dbForwardOnly)
  Do Until rs.EOF
    Combo1.AddItem rs.Fields("ItemName")
    Combo1.ItemData(Combo1.NewIndex) = rs.Fields("ItemIDnum")
    rs.MoveNext
  Loop
  rs.Close
End Sub

Private Sub Combo1_Click()
  Dim rs As Recordset
  Dim sql$
  Dim i%
  List1.Clear
  i% = Combo1.ListIndex
  If i% >= 0 Then
    sql$ = "SELECT DISTINCTROW" & _
      "   Customer.CustIDnum," & _
      "   Customer.Name" & _
      " From" & _
      "   Customer INNER JOIN" & _
      "   LookUp ON Customer.CustIDnum = LookUp.CustIDnum" & _
      " Where LookUp.ItemIDnum = " & Combo1.ItemData(i%)
    Set rs = db.OpenRecordset(sql$, , dbForwardOnly)
    Do Until rs.EOF
      List1.AddItem rs.Fields("Name")
      List1.ItemData(List1.NewIndex) = rs.Fields("CustIDnum")
      rs.MoveNext
    Loop
    rs.Close
  End If
End Sub

Note how I use the ItemData property of Combo1 to get the Item ID to use in
the LookUp table which is then linked to the Customer table via the CustID
field.  Also, this assumes that all ID fields are numeric, if they aren't,
then you need single quotes around them (example: Where LookUp.ItemID = '"
& Combo1.ItemData(i%) "'").  Actually, if they aren't numeric, then you
won't be able to use the ItemData property anyway because it has to be
numeric.  If you run the above code and set a break after the "sql$ = ..."
line and type "?sql<enter>" in the debug window, you can copy that value to
a query in your Access database and then look at the design view to get an
idea of what it looks like.  And finally, the reason why I put the customer
ID into the ItemData of List1 is because somewhere down the road you will
probably want to select one of the customers to do something else and this
way you will have its ID number to work with.  When possible, I always use
the ItemData property to hold ID fields because I know I will need it at
sometime.  Also, some people might recommend a databound Combo and/or List
Box, I just prefer not to use the data controls, it is purely a matter of
opinion and comfort.

Hope that helps,
--
Lance King
Work: 770-801-4502

Take out "nospam." to send message.