2 list boxes and sql and access

[Bulldog13]

Greets,

It s been awhile since I programmed, so I am pulling a blank here.

2 tables. We ll call them table PARENT_COMPANY and table CUSTOMER_INFO.

PARENT_COMPANY has ID (autonum) and Parent_Company_Name .

CUSTOMER_INFO has Name and Parent_Company(number). Parent_Company is a foreign key of the table PARENT_COMPANY.

Ok, now.

2 list boxes, each with a table.

The user clicks the first, selects a Parent_Company_Name. I can do that.

Next the 2nd list box is populated with all of the possible Names from Customer_Info (Parent_company has multiples customers). Obviously, they are linked between PARENT_COMPANY.ID and CUSTOMER_INFO.Parent_Company.

I am doing this from within a form within access 97. So all the info I found online involved openning, connecting etc to a DB which muttled me up since I am already in the DB. I remeber there was a way to do something like this utilizing subforms and child paret type things.

But at this point I would do it in pure VBA.

Thoughts?

 

[NT4Mike]

Create a main form for your 1st listbox with the company names that you want to use.

Create a subform for the Customer Information.

Add the following code to the AfterUpdate event for the 1st listbox.


Dim LSQL As String

LSQL = "select * from CUSTOMER_INFO"
LSQL = LSQL & " where Parent_Company = '" & Listbox & "'"

Form_frmCUSTOMER_INFO_sub.RecordSource = LSQL


Mike

 

[Bulldog13]

Couldn't get it going.

Can you explain the last line ?

I don t seem to have the .RecordSource property for my subform.

 

[EagleKeeper]

Originally posted by: Bulldog13
Couldn't get it going.

Can you explain the last line ?

I don t seem to have the .RecordSource property for my subform.

1) Which versio are you running.

Form_frmCUSTOMER_INFO_sub.RecordSource = LSQL
Instructs application to fill the form with the SQL query results generate using the LSQL variable string

 

[Bulldog13]

access 97

When I put a subform it doesn t seem to be named that, it is named child number number

 

[NT4Mike]

Form_frmCUSTOMER_INFO_sub.RecordSource......... where frmCUSTOMER_INFO_sub is the name of your subform.

Mike

 

[Bulldog13]

Thanks, but I did it in a slightly different way.

I ended up just using some vba.