Selecting a list of Values from one Table.

[Hoopster]

I really need help here. In my last job I used a DataBase Program called
RBase and actually wrote a lot of Code producing some great Data. I was
approached by my Supervisor in my new Job and asked if I could design a few
DataBases for them for various things and I said sure. I didn't think there
could possibly be that much difference between RBase and Access. Maybe the
Commands might be a little different but I didn't think I would have that
much trouble picking them up.
I have four Tables, Customer Data which is going to hold all the Data
entered by the user, Customers which has a list of Customers and a Customer
ID Field, Description which has a list of Products the Description ID and
Customer ID and Item Numbers which has the Products Item Number the Item
Number ID and the Description ID.
All I am trying to do is in a Form, list the Product Description in a
Drop Down Box using the Customer ID where each Customer has a distingued list
of Products and the Item Number based on the result of the first Lookup
Result.
Before I would have written Code like, SELECT T1.Description FROM
Description T1, Customers T2 WHERE T1.Customer ID = T2.Customer ID. It looks
to me like the same Code in Access would be
SELECT("[Description]","Description","[Customer ID] = Customers![Customer
ID]"). I know this isn't right because it doesn’t work. Where am I going
wrong?

 

[Michel Walsh]

Hi,


In the GotFocus (procedure handling the) event of the second combo box:


Dim str as string
if Me.FirstComboBox.Value = vbNullString then
str= "SELECT Description FROM Description"
else
str="SELECT Description FROM Description WHERE
CustomerID=FORMS!FormNameHere!FirstComboBox"
end if

If Me.SecondComboBox.RowSource <> str then
Me.SecondComboBox.RowSource = str
End If


Indeed, if there is nothing as customerID in the first combo box, the list
will be all the possible descriptions, else, it will be limited to those
WHERE the reported customerID = the one reported by the first combo box. In
the string making the SQL statements, the all caps words are keyword.

Next, once we have the string of the SQL statement we will be using, we have
to "assign" it to the RowSource (of the second combo box). Since that
implies an automatic requery, which "can" take some time (mainly if your
data is on a remote computer, and you have a very slow connection), we
change it only if it is different from what it is already. That is just
some optimization that you can skip if you want.



Hoping it may help,
Vanderghast, Access MVP